为什么要分区

PostgreSQL的单表最大允许32TB（默认值8KB的情况下），单表太大会引入很多问题（当然这里是基于默认的情况，：

1、表越大，索引创建的时间越久 (create index concurrently，慢，而且需要2遍以上的扫描，还可能留下invalid的索引)

2、垃圾清理，单表的垃圾回收vacuum (ShareUpdateExclusive锁，自斥) 目前只支持串行，所以单表越大，垃圾回收的时间越长。笔者遇到很多次几个TB的单表跑了几天的autovacuum，还没有完 - -

3、年龄回收，和单表串行vacuum类似，表越大，扫描的越慢，对于9.6以前的版本更加恶劣 (vm文件还没有引入all_frozen的标记位，冻结过的也要扫描)

4、一个逻辑上的大表，可能占满文件系统，使用 分区表之后可以将不同的表放置在不同的物理空间上 ，从而达到冷数据放在廉价的物理机器上，热点数据放置在性能强劲的机器上。

当然还有一些其他的限制，比如（参考源码定义，

 •标识符长度：63

•单表上的索引数量：无限制

•单个索引的列个数：32

•单数据库下对象的数量：1,431,650,303

•函数最多可以用的参数个数：#define INDEX_MAX_KEYS 32

•一个索引可以允许的最多列个数：#define PARTITION_MAX_KEYS 32

•分区表允行的分区列数：#define NUM_SPINLOCK_SEMAPHORES 128

•元组的列个数：#define MaxHeapAttributeNumber 1600 /* 8 * 200 */

分区的好处

1.拆分成一个个子表，那么就可以实现逻辑意义上的"并行"vacuum，多个vacuum进程可以同时作用于多个子表，包括年龄冻结、死元组回收、创建索引等维护性动作

2.通过分区，可以实现类似冷热分离的效果，对于不常访问的子表，可以将其放在一般的媒介上面，比如SATA，对于频繁访问的热表，可以放在SSD上

3.批量的加载和删除可以用删除或者detach子表实现，还可以避免大量删除导致的vacuum，源自官网：Bulk loads and deletes can be accomplished by adding or removing partitions, if the usage pattern is accounted for in the partitioning design. Dropping an individual partition using DROP TABLE, or doing ALTER TABLE DETACH PARTITION, is far faster than a bulk operation. These commands also entirely avoid the VACUUM overhead caused by a bulk DELETE.

4.通过分区裁剪，可以定位到具体某一个子表，扫描的数据是分区前数据的一部分，可以有效提升性能，同时也意味着可以有更高的缓冲命中率

限制

声明式分区

•分区表的唯一约束（也就是主键）必须包括所有的分区键列。存在这个限制是因为构成约束的各个索引只能在它们自己的分区中直接执行唯一性；因此，分区结构本身必须保证在不同的分区中不存在重复。•没有办法创建一个跨越整个分区表的排除性约束。只可能在每个叶子分区上单独设置这样的约束。同样，这个限制源于不能执行跨分区限制。•INSERT上的BEFORE ROW触发器不能改变哪个分区是新行的最终目的地。•在同一个分区树中混合临时和永久关系是不允许的。因此，如果分区表是永久的，它的分区也必须是永久的，同样，如果分区表是临时的，也必须是临时的。当使用临时关系时，分区树的所有成员必须来自同一个会话。•分区不能有父表中不存在的列。在使用CREATE TABLE创建分区时，不可能指定列，也不可能在事后使用ALTER TABLE向分区添加列。表可以通过ALTER TABLE ...添加为一个分区。只有当表的列与父表完全匹配时，才可以用ALTER TABLE ...添加为分区。•分区表的CHECK和NOT NULL约束总是被其所有分区继承。不允许在分区表上创建标有NO INHERIT的CHECK约束。如果在父表中存在相同的约束，那么你不能在分区的列上删除一个NOT NULL约束。•只要没有分区，就支持使用ONLY来添加或删除分区表的约束。一旦存在分区，使用ONLY将导致错误。相反，对分区本身的约束可以被添加和（如果它们在父表中不存在）放弃。•由于分区表本身没有任何数据，试图在一个分区表上使用TRUNCATE ONLY将总是返回一个错误。

继承式分区

•没有自动的方法来验证所有的CHECK约束是互斥的。创建生成子表并创建和/或修改相关对象的代码比手工编写每个对象更安全。•索引和外键约束适用于单个表，而不是其继承的子表，因此它们有一些需要注意的地方。•这里显示的方案假设一行的关键列的值从未改变，或者至少没有改变到需要移到另一个分区的程度。由于CHECK约束，试图这样做的UPDATE将会失败。如果你需要处理这种情况，你可以在子表上设置合适的更新触发器，但是这使得结构的管理更加复杂。•如果你使用手动VACUUM或ANALYZE命令，不要忘记你需要在每个子表上单独运行它们。•带有ON CONFLICT子句的INSERT语句不太可能像预期的那样工作，因为ON CONFLICT动作只在指定的目标关系上出现唯一违反的情况下采取，而不是其子关系。•除非应用程序明确知道分区方案，否则将需要触发器或规则来将行发送到所需的子表。触发器的编写可能很复杂，而且会比声明式分区内部执行的元组路由慢得多。

何时开始分区

没有强制规则来规定多大的表必须分区，对于某些表，30GB可能开始需要分区，对于较低负载的表，可能是1TB。建议DBA根据各厂的业务特性和硬件性能考虑多大的表需要分区，建议SSD硬盘在单表接近40GB时开始考虑分区。

比较傻瓜式的建议(SSD，多核)：

1.不频繁更新、删除的表：记录数20亿，表占用空间200 GB。就可以考虑分表了。2.频繁更新、删除、插入的表：记录数2亿，表占用空间20 GB。就可以考虑分表了。

另外，无谓的分区或者糟糕的分区，反而会带来性能损失！大量的metacache，会耗费巨大的内存，查询规划的时间也会增加