默认分区整不明白？捡了芝麻，丢了西瓜

前言

写这篇文章的缘由是，我在浏览 Greenplum 最佳实践^[1]的时候，看到这么一句话

不要使用默认分区。默认分区总是会被扫描，更重要的是很多情况下会导致溢出而造成性能不佳。

我立马眉头一皱，难道 GP 的优化器这么搓？于是立马去试验了一下，发现并不是这样 (GP7)，看样子是 7 版本以前对于 postgres-planner 的最佳实践 (ORCA 会裁剪)。回到 PG，在 11 版本中便支持了默认分区，由于 PG 仍尚未原生支持 Interval 分区，因此只能提前创建好足够多的分区，否则一旦插入了一条不满足既定分区规则的行便会报错：ERROR: no partition of relation xxx found for row。这个时候，默认分区的好处就出来了，默认分区就相当于一个垃圾桶，来者不拒，凡是不满足规则的行都会存入到该默认分区中，这种行为看似短期内利好了应用，但无疑不是长久之计，危害种种。

何如

举个栗子，该表现在有一个默认分区

postgres=# \d+ ptab01                                            Partitioned table "public.ptab01" Column |           Type           | Collation | Nullable | Default | Storage | Compression | Stats target | Description --------+--------------------------+-----------+----------+---------+---------+-------------+--------------+------------- id     | integer                  |           | not null |         | plain   |             |              |  tm     | timestamp with time zone |           |          |         | plain   |             |              | Partition key: RANGE (tm)Not-null constraints:    "ptab01_id_not_null" NOT NULL "id"Partitions: ptab01_202301 FOR VALUES FROM ('2023-01-01 00:00:00+08') TO ('2023-02-01 00:00:00+08'),            ptab01_202302 FOR VALUES FROM ('2023-02-01 00:00:00+08') TO ('2023-03-01 00:00:00+08'),            ptab01_default DEFAULT

该默认分区同样也有约束，很明了 — 前面两个分区上的约束取反，因此不满足分区规则的行便"满足"了这个默认分区的规则，即所谓的来者不拒。

Partition of: ptab01 DEFAULTPartition constraint: (NOT ((tm IS NOT NULL) AND (((tm >= '2023-01-01 00:00:00+08'::timestamp with time zone) AND (tm < '2023-02-01 00:00:00+08'::timestamp with time zone)) OR ((tm >= '2023-02-01 00:00:00+08'::timestamp with time zone) AND (tm < '2023-03-01 00:00:00+08'::timestamp with time zone)))))
postgres=# insert into ptab01 values(1,'2023-01-15');INSERT 0 1postgres=# insert into ptab01 values(2,'2023-02-15');INSERT 0 1postgres=# insert into ptab01 values(3,'2023-03-15');            ---会插入默认分区中INSERT 0 1postgres=# select tableoid::regclass,count(*) from ptab01 group by 1;    tableoid    | count ----------------+------- ptab01_default |     1            ---默认分区 ptab01_202302  |     1 ptab01_202301  |     1(3 rows)

短期内，应用舒爽了，但是时间一长，随着默认分区里的数据越来越多，危害便接踵而至，首先如前文所述：

1.对于 GP7 以前的版本，按照三水的原话“永远不要使用默认分区，never，因为总是会扫描”2.其次，如果现在想新增一个三月的子分区会怎样？(数据现在已经在默认分区里了)

是的，对于后者，会直接报错：

postgres=# create table ptab01_202303 partition of ptab01 for values from ('2023-03-01') to ('2023-04-01');ERROR:  updated partition constraint for default partition "ptab01_default" would be violated by some row

那数据库是怎么知道这个默认分区里面有没有冲突的数据呢？有没有一种快捷的方式呢？很遗憾，需要挨个扫一遍，因为需要精确扫描确保没有数据冲突 (倒是不会导致 rewrite)，除了顺序扫描我也暂时想不到还有啥简便的方式了。因此对于这种情况，只能手动处理冲突的数据

postgres=# delete from ptab01_default ;DELETE 1postgres=# create table ptab01_202303 partition of ptab01 for values from ('2023-03-01') to ('2023-04-01');CREATE TABLE

不难想象，如果堆了大几百 GB 的默认分区，后面要进行维护，比如新增分区，那无疑是一件苦差事，这不又回到了以前的老大难问题：数据量大 → 为了好维护进行分区 → 没有维护好，又全部进入了默认分区。同理，如果你想将一个表挂载为默认分区，也需要全表扫一遍。

那如果真遇到了这种情况，该怎么办？其实 postgres-howto 里面已经给出了许多解决方案：

1. 把默认分区 detach 掉，处理完冲突数据后再 attach 回来，12 以后 attach 只需要 4 级锁，在 14 中也支持了 detach concurrently：In postgresql 14, DETACH PARTITION partitioned_table_1 CONCURRENTLY，SHARE UPDATE EXCLUSIVE lock on the parent table

2. 默认的 create table xxx partition of 会全程获取 8 级锁，因此最佳实践是先使用 including like 的语法，然后再 attach，当然这种方法针对的是 12 以后的版本，在 12 以前，Attach 在父表和被连接分区上都要加上 8 级锁 🔐。

3. 同理，想要新增分区给表添加约束时 (方法同样适用于添加 FK、PK 等)，如果直接执行类似 alter table t add constraint c_id_is_positive check (id > 0)；在此期间，会扫描所有数据，全程 8 级锁，因此最佳实践应该是 alter table t add constraint c_id_is_positive check (id > 0) not valid;，这样的话只需要简短的 8 级锁 (搭配 lock_timeout 和 retry)，最后再 alter table t validate constraint c_id_is_positive;，这个校验步骤就只需要 4 级锁了，不阻塞读写。值得注意的是，当 NOT VALID 约束添加之后，新的数据写入会立即进行检查 (而旧数据尚未验证，可能会违反约束)。

简而言之，遵循锁的获取原则^[2]：

•够用即可：使用满足条件的锁中最弱的锁模式

•越快越好：如果可能，可以用 (长时间的弱锁+短时间的强锁) 替换长时间的强锁

•递增获取：遵循 2PL 原则申请锁；越晚使用激进锁策略越好；在真正需要时再获取。

•相同顺序：获取锁尽量以一致的顺序获取，从而减小死锁的几率

后记

默认分区，作为最佳实践，建议能不用就不用，如果真用了，需要定期巡检，确保默认分区里的数据量不要过大。默认分区可以看做是一个过渡阶段，期待原生 PG 可以早日支持 Interval 分区！

另外，postgres-howto 系列暂时告一段落，我也要开始我的经验谈连载了，也会放在此网站中：🔗 https://postgres-howto.cn/，第一篇就以分区表最佳实践为例吧。

参考

https://github.com/Vonng/pg/blob/master/app/sql-lock.md^[3]

https://github.com/yydzero/yydzero.github.io/blob/master/articles/gpdb-best-practice.md^[4]

References

[1] 最佳实践: https://github.com/yydzero/yydzero.github.io/blob/master/articles/gpdb-best-practice.md
[2] 获取原则: https://github.com/Vonng/pg/blob/master/app/sql-lock.md
[3]: https://github.com/Vonng/pg/blob/master/app/sql-lock.md
[4]: https://github.com/yydzero/yydzero.github.io/blob/master/articles/gpdb-best-practice.md