Aurora PostgreSQL表分区大小、pg_partman及性能优化咨询

“容量大于2GB就应考虑分区”是面向小型OLTP场景的早期通用建议，完全不适用于TB级超大表场景，硬套这个阈值会生成上千个分区，反而会因为分区元数据过多、查询规划时间变长带来额外开销，运维成本也会失控。
结合Aurora PostgreSQL的生产实践，单分区的合理容量区间是10GB~100GB，不需要死卡固定数值，核心参考两个判断标准：

• 单分区行数控制在5000万以内，最高不要超过1亿行，避免单分区B树索引深度超过4层，导致单次索引查询的随机IO开销陡增
• 分区粒度匹配业务查询模式：比如90%以上的在线查询都只覆盖1个月内的数据，就按周/月设置分区粒度，保证单次查询命中的分区数不超过3~5个，避免出现一次查询扫描几十个分区的情况
  你之前测试只分10个分区，单分区容量达400GB、约7亿行，本质上每个分区还是达到了大表量级，索引维护、数据扫描、vacuum清理的开销都没有本质下降，性能达不到预期是正常的。按当前总数据量测算，把单分区控制在50GB左右，总共约80个分区，这个量级对PostgreSQL来说完全没有压力，运维难度也很低。

不是强制要求，但强烈推荐使用，无特殊定制需求不建议手动维护分区。
手动维护分区的成本会随分区数量上涨快速升高：需要提前编写脚本创建未来分区、定期清理/归档过期分区、保证每个分区的索引/约束/权限配置和父表完全一致，任何一个步骤遗漏都可能触发生产故障——比如忘建分区导致写入报错、漏建索引导致分区裁剪失效扫描全表，这类问题在超大表场景下非常常见。
Aurora PostgreSQL已经原生内置pg_partman扩展，不需要额外编译安装，执行create extension pg_partman;即可启用，它可以自动完成分区全生命周期管理，支持范围、列表等常用分区策略，还能自动同步父表的索引、约束配置，能把分区运维的工作量降到极低。除非团队已经有经过长期生产验证的成熟分区自动化运维脚本，否则完全没必要重复造轮子。

以下都是Aurora PostgreSQL场景下经过生产验证的优化手段，按投入产出比从高到低排序：

• 冷热数据拆分：4TB级业务表通常存在大量访问频率极低的历史冷数据，比如超过6个月的订单、日志类记录，优先把这部分数据从在线主表剥离，归档到低成本存储后通过外表提供低频访问，在线主表只保留高频访问的热数据，通常能把在线表体量压缩60%~80%，性能提升最为直接
• 索引针对性优化：不要给所有查询字段单独建B树索引，优先为高频查询条件构建联合覆盖索引，把查询需要返回的字段都包含在索引中，避免回表开销；如果是按时间顺序写入的时序类数据，在时间分区键上用BRIN索引替代B树索引，索引体积可缩小99%，范围查询性能完全满足需求；不要创建全局索引，分区表的全局索引写入维护成本极高，所有索引都要和分区对齐
• 适配Aurora架构特性调优：开启Aurora并行查询参数，大表扫描、聚合类查询可以自动利用多核CPU并行处理，性能可提升数倍；存算分离架构下可适当调大shared_buffers参数（Aurora做过专属优化，可设置为实例内存的75%，远高于开源PostgreSQL的25%默认建议），针对大查询适当调高work_mem参数，避免排序、哈希操作溢写磁盘
• SQL与日常运维优化：所有查询必须携带分区键过滤条件，避免无差别扫描全部分区；禁止select *写法，仅返回业务需要的字段；固定维度的聚合统计逻辑用物化视图定期预计算刷新，不要每次实时扫全表聚合；针对大表单独调优autovacuum触发参数，避免死元组堆积导致表膨胀、统计信息失真，定期重建索引减少碎片
• 写入链路优化：用批量写入代替单条写入，高频更新的字段不要加入索引，降低索引维护开销

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