PostgreSQL LIST分区搭配默认分区的合理分区数量上限咨询

场景背景 #

我计划移除分区键无数据或数据量极少的物理分区，改用默认分区来减少多张表的LIST分区数量：

• 将某表从8000个分区减至1000个分区+默认分区
• 另一表从6000个分区减至4000个分区+默认分区

但发现当存在n个普通分区搭配默认分区时，默认分区的分区约束会包含n个条目（示例如下）。随着ANY (ARRAY[988, 989, 990, ...]的数组长度不断增加，想知道普通分区搭配默认分区时，表的普通分区数量的安全/合理上限是多少？

示例SQL与约束展示 #

CREATE TABLE tst_t (
    c1 int4 NOT NULL,
    c2 int8 NOT NULL,
    c3 int8 NOT NULL
)
PARTITION BY LIST (c1);

CREATE TABLE tst_t_988 PARTITION OF tst_t  FOR VALUES IN (988);
CREATE TABLE tst_t_989 PARTITION OF tst_t  FOR VALUES IN (989);
CREATE TABLE tst_t_990 PARTITION OF tst_t  FOR VALUES IN (990);
CREATE TABLE tst_t_default PARTITION OF tst_t DEFAULT;

执行\d+ tst_t_default后看到的默认分区约束：

Table "public.tst_t_default"
 Column |  Type   | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
 c1     | integer |           | not null |         | plain   |             |              |
 c2     | bigint  |           | not null |         | plain   |             |              |
 c3     | bigint  |           | not null |         | plain   |             |              |
Partition of: tst_t DEFAULT
Partition constraint: (NOT ((c1 IS NOT NULL) AND (c1 = ANY (ARRAY[988, 989, 990]))))
Access method: heap

回答 #

1. 核心影响因素
   默认分区的约束数组长度主要影响两个方面：

• 查询性能：数组过大时，数据库执行查询需要遍历数组判断数据是否命中默认分区，会增加CPU开销，尤其是默认分区频繁被查询或存在全表扫描的场景。
• 元数据管理：分区约束的元数据存储在系统表中，过大的数组会占用更多系统表存储空间，可能导致元数据查询（如\d、查询pg_constraint）变慢。

2. 合理上限参考
   PostgreSQL官方没有明确的硬性上限，结合生产经验给出参考：

• 若查询对性能敏感度低、元数据操作频率低，10000个以内的普通分区搭配默认分区通常可接受。
• 若存在频繁跨分区查询、默认分区数据量较大，建议将普通分区数量控制在2000-5000个以内，避免约束判断带来的性能损耗。
• 若默认分区极少被访问，可适当放宽上限，但不建议超过20000个，否则元数据维护会变得笨重。

3. 替代优化方案
   如果普通分区数量接近上述上限，可考虑：

• 将多个低数据量的分区合并为一个范围分区（比如把连续或逻辑相关的分区键值合并到一个分区中），减少普通分区总数。
• 定期清理无数据的分区，避免默认分区约束数组持续膨胀。
• 若分区键是离散值且数量极多，考虑改用HASH分区替代LIST分区，从根源上减少分区数量。

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