1前言

昨天②群里面，筒子们聊分区表聊得火热，借此机会也说几句。分区表，基本都会用到，但是笔者从业这么久，也碰到了不少与分区表有关的问题，大坑小坑无数，在此也整理一下合订本，让各位少踩坑。

2利

分区表的好处不言而喻，简单列举几个：

1. 拆分成多个子表，那么就可以实现逻辑意义上的并行 vacuum，多个 vacuum 进程可以同时作用于多个子表，包括年龄冻结、死元组回收、创建索引等维护性动作
2. 通过分区，可以实现类似冷热分离的效果，对于不常访问的子表，将其放在一般的媒介上面，对于频繁访问的对象，可以放在高端存储上
3. 批量的加载和删除可以用删除或者 detach 子表实现，还可以避免大量删除导致的 vacuum
4. 通过分区裁剪，可以定位到具体某一个子表，扫描的数据是分区前数据的一部分，可以有效提升性能，同时也意味着可以有更高的缓冲命中率

3弊

说完了好处，再聊一聊分区表的坑：

1. 分区表要最大化性能的前提是——裁剪，如若不指定分区键，或者指定了分区键但是裁剪不了（典型场景是函数稳定性，stable、immutable 函数作为过滤条件时，可用于过滤不需要访问的子表），那么与单表的性能肯定不能相提并论

2. 在 12 以前的版本，分区表性能很差，功能也差

参照过往案例合订本??：

• 活久见——不同用户不同执行计划（分区表在预估行数的时候，代码逻辑可能会去判断子表的查询权限）
• 不同用户的执行计划居然会不一样？（分区表在预估行数的时候，代码逻辑可能会去判断子表的查询权限）
• 恼人的原生分区（ 在 11 版本里面，分区裁剪的行为实际上第一次生成执行计划的时候会扫描所有子分区）
• 令人费解的分区表与表空间（在 11 的版本里面分区表父表指定的表空间完全没用，需要自己指定子表，普通表生效。而14的版本里面，分区表又生效了。）
• 修改列类型又掉坑了！（修改索引列，对于分区表有所出入，字段长度由小改大，虽然所有子表不会重写，但是所有索引会重写！由大改小规则不变，整个分区表和索引都会发生重写）
• 聊一聊分区表的几个参数（partition wise-join 和 partition wise-aggregation）
• 一起 pg_pathman 的有趣案例（在 explain 生成执行计划的时候就在不断地读取索引）

3. 没有自动分区，意味着插入了不满足现有规则的数据会报错，需要通过 UDF 来实现，影响性能，当然 pg_pathman （HASH, RANGE partitioning）和 pg_partman（Time or serial id partitioning）支持，二者各有侧重，但是毕竟作为第三方插件，BUG 以及质量都难以保证，难以维护，笔者记录处理的 pg_pathman 的 BUG 就不下 10 个。

4. 12 以前的版本创建子分区（create table ... partition of），使用 AccessExclusiveLock，对于频繁读写的场景，可能根本获取不到 8 级锁

5. 没有全局索引，这个也是被吐槽最多的地方：分区表的唯一约束必须包括所有的分区键列。存在这个限制是因为构成约束的各个索引只能在它们自己的分区中直接执行唯一性；因此，分区结构本身必须保证在不同的分区中不存在重复。

6. 分区表如果不按照规范使用，比如不裁剪、裁剪未生效，分区表之间复杂连接，会导致锁的数量呈几何级上升，很容易导致性能瓶颈，也很容易导致 out of shared memory

4规范

1. 分区设计中，最终的数据要均匀分布到各个分区中，预防木桶效应
2. 分区使用中，要指定分区键，进行分区裁剪
3. 糟糕的分区方法和选择，比没有分区更加糟糕
4. 当表大小超过了机器内存大小时，可以考虑分区，傻瓜式建议：不频繁更新和删除的表，达到了 20 亿、表 200 GB；频繁更新和删除的表，达到了 2 已、表大小 30 GB
5. 控制分区数量，随着分区数量的增加，性能会衰减，并且对象过多操作系统可能先尿了
6. 实际使用过程中，可以根据需要使用不同粒度进行分区

5小结

分区表是双刃剑，用得好事半功倍，用得差事倍功半。

最后宣传一下，筹划许久的《PostgreSQL实战精英课》已经正式上限了，此门课程聚焦于实战，注重实战技能的提升，囊括多个经典案例的解析，让你在运维 PostgreSQL 的时候不再无所适从，不知如何下手，PostgreSQL 的势头如此火热，人才需求越来越大，感兴趣的小伙伴不要错过啦，可以联系我 ( _xiongcc )或者添加客服微信 ( pgccc400 )。