PostgreSQL能否基于关联实现列表分区及相关方案咨询

背景说明 #

先明确涉及的表结构：

Table "public.Foo"
Column  | Type     |
------------------+-----------------------------+
foo_id  | integer  | PK
bar_id  | integer  | FK to bars
....

Table "public.Bar"
Column  | Type     |
------------------+-----------------------------+
bar_id  | integer  | PK
....

Table "public.very_big"
Column  | Type     |
------------------+-----------------------------+
foo_id  | integer
....

Bar与Foo为一对多关系，Bar总数不足50个，每个Bar对应数百个Foo；very_big表行数超2亿，通过foo_id关联Foo，希望基于bar_id对其做列表分区（仅生成1-50个分区），而非基于foo_id生成数千个不符合PostgreSQL建议的分区，同时不想新增bar_id列。你还考虑了两种替代方案，现在有三个核心疑问，下面逐一解答：

1. 基于关联查询的列表分区是否真的不可行？ #

没错，这种方案确实不可行。PostgreSQL的分区规则必须直接依赖表自身的列，无法通过跨表关联查询来定义分区键——分区的路由逻辑需要在数据写入、查询时快速计算，跨表关联会让这个过程变得低效且无法被PostgreSQL的分区引擎识别。

另外你提到的关联关系变更问题也是致命的：如果Foo的bar_id发生修改，very_big中对应的数据理论上需要跨分区移动，但因为分区规则不直接绑定bar_id，PostgreSQL无法自动处理这种移动，最终会导致数据所在分区和实际关联关系不匹配，后续查询会出现数据遗漏或错误扫描分区的问题。

2. 包含大量非连续值的列表分区是否存在性能问题？ #

肯定会有性能影响，主要体现在两个方面：

• 查询规划器开销增加：当列表分区包含大量离散的foo_id值时，查询规划器需要逐个匹配这些值来确定要扫描的分区，随着值的数量增多，规划时间会明显变长，在高并发或复杂查询场景下，这个开销会被进一步放大。
• 数据分布不均风险：手动划分的foo_id组如果对应的数据量差异悬殊，部分分区可能会变得异常庞大，完全失去分区优化查询性能的意义，甚至单个大分区的查询速度会比未分区的表更慢。

再加上你提到的关联变更导致分区失效、新增Foo无法匹配预定义规则的问题，这种方案的维护成本会非常高，不推荐使用。

3. 是否有其他可行解决方案？ #

如果暂时不想接受去范式化方案，可以考虑以下两个方向，但都有明显的局限性：

• 表达式分区结合不可变函数：创建一个输入foo_id返回对应bar_id的函数，基于这个函数的返回值做列表分区。但注意：这个函数必须被定义为immutable（不可变），否则PostgreSQL不允许将其作为分区键。而一旦Foo和Bar的关联关系发生变更，函数的返回值就会改变，违反immutable的要求——所以这个方案仅适用于Foo与Bar的关联永远不会变更的场景。
• 传统分区表继承+触发器路由：这是PostgreSQL 10之前常用的分区实现方式，通过触发器在写入very_big时查询Foo表获取bar_id，再将数据路由到对应分区。但这种方式的写入性能远不如原生分区，面对2亿行的大表，触发器的开销会非常显著，且维护复杂度极高。

综合来看，去范式化新增bar_id列的方案其实是最务实的选择：虽然存在数据冗余，但分区规则简单清晰，查询和写入都能享受原生分区的性能优化；关联关系变更时，可以通过触发器或批量同步任务来更新very_big的bar_id，整体的性能和维护成本都是最优的。

内容的提问来源于stack exchange，提问作者Whelchel