记录一下我们团队在 PostgreSQL 分区表这条路上从踩坑到跑顺的全过程。起因是一张日志表涨到了 120GB查询越来越慢清理数据像是在做心脏手术后来我们彻底切换到声明式分区表把维护节奏从“提心吊胆”变成了“按计划执行”。这篇文章就把分区表管理的完整思路、建表细节、维护脚本、性能调优和常见坑位都梳理一遍适合正在被大表拖垮、或者准备把表改成分区结构的同学参考。1. 为什么一张大表非得分区不可先说结论分区表不是银弹但对于“数据量持续增长、历史数据访问率低、清理需求明确”的场景它几乎是最省心的解法。我们用一张实际表举例大家感受一下痛点在哪。1.1 表大了以后数据库到底哪里疼一张 120GB 的日志表问题不是“慢”这么简单而是整套数据库机制都在退化。首先是查询即使你在log_time上建了 B-tree 索引范围查询的结果也要回表取所有字段索引层数变深、缓冲命中率下降IO 消耗肉眼可见地涨。其次是清理要删除三个月前的数据最容易想到的是DELETE FROM logs WHERE log_time ...但这个操作会生成大量 WAL 日志触发表膨胀还可能长时间持有锁在线业务根本扛不住。更麻烦的是空间回收。PostgreSQL 的普通表做DELETE后空间并不会直接归还操作系统必须 VACUUM FULL 或者重建表。一张 120GB 的表做 VACUUM FULL既吃磁盘又要停机窗口业务方一听说要停半小时直接炸锅。这还只是空间问题索引碎片、统计信息过时、autovacuum 跟不上写入频率每一项都是单张超大表逃不掉的债。1.2 声明式分区为什么能解痛声明式分区从 PostgreSQL 10 开始正式可用到 11、12 版本基本成熟。它的核心思路是逻辑上一张表物理上拆成多个独立子表。你依然可以对父表执行查询、写入、加索引但底层数据被分散到各个分区文件里。这样一来按时间范围删除数据就变成了DROP TABLE或DETACH PARTITION秒级完成不产生海量 DELETE不膨胀不长时间锁表。查询时优化器可以通过分区裁剪只扫描需要的分区而不是扫全表。每个分区又是独立表autovacuum、统计信息收集、索引重建都能精细化控制维护窗口被切割得很小。这套机制特别像衣柜整理把所有衣服堆在一个柜子里找一件衣服要翻遍整个柜子按季节分成几个抽屉后找当季衣服只用拉一个抽屉。1.3 哪些表适合分区哪些别瞎凑热闹分区表有代价别看到大表就分。日志、事件流、订单历史、监控指标这类“只增不改、按时间访问”的数据是首选。它们的共同特征是数据有明确的分区键通常是时间、历史数据几乎不会被更新、清理需求刚性。不适合分区的场景我也踩过。第一类是数据量本身不大比如只有几百万行分了反而多出几十张子表的元数据开销查询还要走 Append 节点性能不升反降。第二类是查询条件里几乎没有分区键比如业务端总是按user_id直接查最新记录但表是按时间分区这会导致每次都扫全部分区比单表更慢。第三类是分区键频繁被更新比如行数据经常跨分区迁移这会让 UPDATE 变成“先删后插”性能极差。1.4 三种分区方式的选型判断PostgreSQL 支持 RANGE、LIST、HASH 三种分区。RANGE 分区适合按时间、数值区间切分比如log_time、create_date、自增 ID 区间我们日志表用的就是按月 RANGE。LIST 分区适合枚举值比如按省份、业务线、订单状态切分分区数量和值域固定。HASH 分区适合没有天然区间但要求数据均匀分散的场景比如按用户 ID 分 16 个桶多用于读写分离的冷热均衡。最容易被忽略的是嵌套分区。比如先按年月做 RANGE再按地区做 LIST 子分区。嵌套能让裁剪更精细但也成倍增加分区数量和维护复杂度建议只在数据量确实需要时才考虑。选型的关键判断依据是业务查询通常带哪个等值或范围条件这个条件就是分区键。2. 建表实操从零做一张按月分区的日志表理论讲完直接上手。我们以一张名为logs的日志表为例演示整个建表过程。假设数据结构是日志 ID、日志时间、日志级别、日志内容。2.1 父表 DDL 的规范写法父表本身不存储任何数据它只是分区定义的载体。DDL 如下CREATE TABLE logs ( log_id bigint NOT NULL, log_time timestamptz NOT NULL, log_level text, message text ) PARTITION BY RANGE (log_time);这里有几个细节值得说。第一log_time我建议用timestamptz而不是timestamp否则不同时区的业务写入会对不齐边界导致数据进错分区。第二分区键列一定要NOT NULL虽然 PostgreSQL 在实现上允许为空并把它分到默认分区或报错但生产环境很容易出现 NULL 数据堆积在默认分区的惨案。第三父表上不需要建物理索引索引建在父表上会自动传播到所有子分区这一点后面细说。有人会纠结主键怎么建。如果是纯日志表我建议不要建主键最多建唯一索引。如果业务强制要求主键记住 PostgreSQL 的硬性限制声明式分区表的主键和唯一约束必须包含分区键列也就是说PRIMARY KEY (log_id)是不行的必须写成PRIMARY KEY (log_id, log_time)。2.2 分区子表的两种创建姿势第一种是直接使用PARTITION OF在创建子表时同时声明分区边界CREATE TABLE logs_2024_01 PARTITION OF logs FOR VALUES FROM (2024-01-01::timestamptz) TO (2024-02-01::timestamptz);注意 RANGE 分区的边界规则FROM是闭区间包含起始值TO是开区间不包含结束值。所以上面的写法表示“从 2024-01-01 00:00:00 起到 2024-02-01 00:00:00 之前”正好覆盖整个一月份。这个边界语义踩过坑的人很多我见过有同事把TO写成了 2 月 1 号结果 2 月 1 号零点那一条记录死活写不进去。第二种方式是先把子表建成普通表再用ATTACH挂载到父表上CREATE TABLE logs_2024_02 ( LIKE logs INCLUDING DEFAULTS INCLUDING CONSTRAINTS ); ALTER TABLE logs ATTACH PARTITION logs_2024_02 FOR VALUES FROM (2024-02-01::timestamptz) TO (2024-03-01::timestamptz);这种方式的场景一般是你要把一个已经存在的历史普通表合并进分区体系或者子表需要有特殊的物理属性比如放在不同表空间需要先建好再挂载。ATTACH时 PostgreSQL 会校验表结构和数据是否符合分区约束数据量大的时候耗时较长建议在低峰期执行。如果需要保证任何时间点写入都有去处可以创建默认分区CREATE TABLE logs_default PARTITION OF logs DEFAULT;默认分区会接收所有“没有对应分区”的数据。我个人的建议是宁可建默认分区保底也别让它不存在。否则一条未覆盖边界的数据会导致整个 INSERT 报错。但默认分区需要定期检查防止数据漏配后堆积。2.3 索引、主键与约束的三大坑位在父表上创建索引PostgreSQL 会自动在所有现有分区上创建对应的索引之后的子分区也会自动获得。这一点从 PG 11 开始生效非常省心CREATE INDEX idx_logs_time ON logs (log_time); CREATE INDEX idx_logs_level ON logs (log_level);但自动传播不覆盖主键和唯一约束的“全局唯一性”。分区表的唯一索引在每个分区内部单独维护跨分区无法保证唯一。所以上面才说主键必须包含分区键。如果业务要求全局唯一比如日志 ID 全表唯一只有两条路一是接受(log_id, log_time)联合唯一二是用应用层或汇总表保证二选一没有完美解。CHECK 约束可以用来辅助分区裁剪和保证数据边界。声明式分区会自动生成子分区边界约束你不需要手动添加。但如果你在父表上定义了额外的 CHECK 约束它会传播给所有子分区。比如规定日志等级只能取某些值ALTER TABLE logs ADD CONSTRAINT logs_level_check CHECK (log_level IN (INFO, WARN, ERROR));3. 日常维护加分区、删分区、查分区完整流程建完分区只是第一步真正的日常挑战在于每个月怎么加新分区、历史分区怎么清理、怎么快速查看当前分区体系状态。这一节全部是可落地的操作方案。3.1 手动增加新分区的标准操作按月分区的话每月月初需要创建下个月的分区。手动创建如下CREATE TABLE logs_2024_03 PARTITION OF logs FOR VALUES FROM (2024-03-01::timestamptz) TO (2024-04-01::timestamptz);创建子分区后不需要手动建索引父表索引会自动覆盖新分区这是 PG 11 的行为实测自动创建索引会有短暂的系统表活动但整体无感。要注意的是如果父表没有默认分区而新分区还没建好临近边界时写入就会失败所以预创建分区一定要提前别卡在边界点上。如果是ATTACH一个已有表PG 会扫描数据验证是否符合边界。PG 12 之后性能有所优化但大表附加时的校验开销仍然存在。低峰期执行是底线要求。3.2 用 PL/pgSQL 批量预创建未来分区手动建分区偶尔一次没问题长期靠手敲迟早翻车。我们用一段 PL/pgSQL 脚本做预创建传入起始月份和预创建数量即可DO $$ DECLARE m date : 2024-03-01::date; i int; tab text; BEGIN FOR i IN 0..11 LOOP tab : format(logs_%s, to_char(m, YYYY_MM)); EXECUTE format( CREATE TABLE IF NOT EXISTS %I PARTITION OF logs || FOR VALUES FROM (%L) TO (%L), tab, m, m interval 1 month ); m : m interval 1 month; END LOOP; END $$;这个脚本的原理很直接用date变量做为当前的起始边界循环 12 次每次用format拼出表名logs_2024_03这种格式。%I是安全引用标识符%L是安全引用字面量防止 SQL 注入和标识符异常。IF NOT EXISTS保证重复跑不会报错。我建议把这种脚本放到定时任务里比如每月 25 号自动预创建未来 3 个月分区。预创建数量不宜太多否则会产生大量空表反而增加系统表负担和 pg_dump 耗时。3.3 如何安全地删除和离线历史分区清理历史数据是所有运维最喜欢分区表的地方没有 DELETE没有膨胀一条 DROP 就完成。DROP TABLE logs_2023_01;DROP 会直接删除数据文件并立即释放空间。如果需要留存数据比如归档到冷存储先 DETACH 再转储ALTER TABLE logs DETACH PARTITION logs_2023_01;DETACH 之后logs_2023_01就变成一张独立普通表数据原封不动分区表不再对它有任何约束。你可以对它做pg_dump、迁移到归档库、或者继续保留在实例中。PG 14 之后支持并发操作ALTER TABLE logs DETACH PARTITION logs_2023_01 CONCURRENTLY;CONCURRENTLY版本不会长时间阻塞写入适合在线清理。但要注意DETACH 之后这张表与父表之间只靠名字关联必须自己管理后续处理流程别忘记录归档清单。3.4 快速掌握分区全貌的查询手段管理分区表最怕不知道自己有哪些分区、边界是什么。PostgreSQL 提供了一套系统视图最常用的是pg_partition_treeSELECT relid::regclass AS partition_name, parentrelid::regclass AS parent_name, isleaf, level FROM pg_partition_tree(logs::regclass);它会递归展示分区的层级关系。isleaf为 true 表示叶子分区level表示层级深度父表是 0。这张表尤其适合写自动化巡检脚本比如检查所有叶子分区里是否有超过预期边界范围的数据。查看每个分区的边界定义可以用pg_partitionsSELECT partition_name, partition_boundary FROM pg_partitions WHERE parent_table logs;partition_boundary字段直接显示这个分区的 FROM/TO 定义一眼就能确认边界有没有重叠、有没有漏档。另外pg_class.relispartition true可以快速查出实例里所有分区子表。4. 查询性能分区裁剪与执行计划调优分区表建好后大家最关心的一定是查询到底快了多少。这一节讲清楚分区裁剪的生效机制以及哪些写法会让裁剪静默失效。4.1 分区裁剪是什么怎么验证分区裁剪Partition Pruning是指优化器根据 WHERE 条件中的分区键值在生成执行计划时就排除掉不相关的分区。从 PG 11 开始enable_partition_pruning默认开启通常不需要额外设置。验证方式就是看执行计划EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM logs WHERE log_time 2024-03-10::timestamptz AND log_time 2024-03-11::timestamptz;如果裁剪生效计划输出中会看到类似Append Subplans 1 of 24表示只扫描了 24 个分区中的 1 个。如果看到Subplans 24说明裁剪完全没生效查询逻辑上扫描了所有分区。BUFFERS选项可以进一步确认实际读取的 buffer 数量。4.2 三类让裁剪失效的常见写法第一类是给分区键套函数最典型的就是WHERE date_trunc(month, log_time) 2024-03-01::timestamptz这种情况下优化器无法从函数表达式反推出分区键的取值边界只能放弃裁剪。正确写法是直接用范围条件WHERE log_time 2024-03-01::timestamptz AND log_time 2024-04-01::timestamptz第二类是类型不匹配导致隐式转换丢失边界。分区键是timestamptz却拿字符串直接比较WHERE log_time 2024-03-10 10:00:00这种写法在特定场景下可能触发临时转换导致无法准确裁剪。解决办法是显式类型转换比如2024-03-10 10:00:00::timestamptz。第三类是使用 OR 条件连接多个非分区键条件。优化器面对 OR 时会把可能性放大逐分区裁剪很困难。能拆成 UNION ALL 就拆不能拆就接受全分区扫描的现实。注意这些坑不是 SQL 写错而是优化器能力边界的问题理解这一点能省很多排查时间。4.3 统计信息与 autovacuum 策略分区表的统计信息是分区的每个子表独立收集。也就是说父表无法提供全局统计优化器在生成 Append 计划时会逐个分区评估。如果某些分区长期没有 ANALYZE估算行数会偏差很大导致嵌套循环变哈希连接这类计划抖动。我们的做法是每次批量创建新分区后立即对新分区做 ANALYZEANALYZE logs_2024_03;同时把 autovacuum 的参数按业务调整。日志表写入频繁autovacuum_vacuum_scale_factor默认值是 0.2对高频写入的大分区表来说太迟钝容易导致死元组堆积。建议对分区表单独设置ALTER TABLE logs SET (autovacuum_vacuum_scale_factor 0.05); ALTER TABLE logs SET (autovacuum_vacuum_threshold 1000); ALTER TABLE logs SET (autovacuum_analyze_scale_factor 0.05);这里有个关键认知父表的这些参数会传递给所有子分区所以只需要在父表设置一次。定期检查每个分区的膨胀情况时用pg_stat_user_tables看n_dead_tup和n_live_tup的比值如果某个分区死元组比例异常升高针对单个分区手动 VACUUM 即可不需要动整表。5. 自动化分区管理pg_partman 实战手工脚本管理分区在分区数量少的时候很轻松但当你有十几张表都按月分区还要维护保留策略时就该让专业工具出场了。PostgreSQL 生态里最成熟的方案是 pg_partman。5.1 安装与初始化pg_partman 的安装分两步。第一步在数据库里创建扩展CREATE EXTENSION pg_partman;第二步如果需要后台自动维护把pg_partman_bgw加到shared_preload_libraries并重启实例shared_preload_libraries pg_partman_bgw重启后还需要设置后台 worker 的参数比如pg_partman_bgw.interval 60 pg_partman_bgw.role postgres pg_partman_bgw.dbname mydbinterval 60表示每 60 分钟自动运行一次维护任务。这一步建议由 DBA 统一配置因为它会直接影响所有分区表的自动建分区和清理行为。5.2 创建分区任务用create_parent函数把一张表接入自动管理SELECT partman.create_parent( p_parent_table : public.logs, p_control : log_time, p_type : range, p_interval : 1 month, p_premake_count : 3 );各参数含义p_control是分区键列名p_type是分区方式p_interval是每个分区的区间长度p_premake_count是预创建的未来分区数量我建议至少保留 3防止跨月和突发流量。执行后 pg_partman 会自动创建未来 3 个月的分区并注册到它自己的配置表。如果表已经手工建过分区也可以在part_config表里调整配置但通常推荐新建表时就接入避免边界纠缠带来的麻烦。5.3 手工维护与保留策略即使配置了后台 worker我也建议每周手工执行一次维护确保任务可控SELECT partman.run_maintenance(public.logs);run_maintenance会根据配置自动创建缺失的未来分区并执行分区裁剪的约束检查。保留策略的配置很简单先更新配置表UPDATE partman.part_config SET retention 3 months, retention_keep_table false WHERE parent_table public.logs;retention 3 months表示保留最近 3 个月的数据retention_keep_table false表示直接把过期分区 DROP 掉。如果出于归档需求想保留表可以改成true它会先 DETACH 再保留为普通表归档后由下游任务处理。5.4 使用 pg_partman 的注意事项用这个工具有几个坑。第一个是权限后台 worker 使用的角色必须拥有父表的 ALL 权限否则自动建分区会频繁报错。第二个是时间戳类型p_control列如果是timestamptz分区边界自动生成时带时区最好不要和timestamp混用。第三个是默认分区pg_partman 默认不允许目标表存在 DEFAULT 分区因为这会干扰它的边界检查如果业务确实需要默认分区需要做一些额外配置建议严格执行“有默认分区就别用自动管理”的原则。还有一点要提醒pg_partman 更适用于纯分区管理场景如果一张表同时还有复杂的外键、物化视图依赖自动化 DROP 旧分区时会遇到依赖报错我建议这种情况下先手工处理依赖再接入自动维护。6. 常见问题排查实录最后这一节整理我们上线分区表后遇到过的真实问题和排查过程每条都是踩过的坑。6.1 唯一约束和主键的报错最常见的报错是这样的ERROR: unique constraint on partitioned table must include all partitioning columns原因在 2.3 节已经说过分区表的唯一约束必须在每个分区内独立保证如果不包含分区键数据插入 A 分区时无法知道 B 分区是否已有相同值。解决办法就是让主键或唯一索引包含分区键列。有一类业务场景是“全局 ID 需要唯一”但分区键又是时间这种只能接受联合唯一或者把 ID 统一交给应用层生成并做全局去重。6.2 更新分区键导致行跨分区PG14 前后的差异在 PG 14 之前如果 UPDATE 把一行数据的主键值改到另一个分区范围数据库会直接报错ERROR: new row for relation logs violates partition constraint这是因为旧版本不支持行迁移。解决思路只有两个一是业务上避免更新分区键二是升级到 PG 14 以上。PG 14 开始PostgreSQL 支持 UPDATE 行在分区之间自动移动内部逻辑是 DELETE 旧分区的一行再 INSERT 到新分区。注意行迁移虽然省心但代价是走了完整的 DML 路径高频更新分区键会放大写放大我们的经验是核心链路尽量还是别这么干。6.3 外键、序列、默认分区的连环坑分区表的外键分两种情况。PG 12 之前普通表不能引用分区表作为外键目标升级后才行。但即使支持了外键检查在分区表上的开销也远高于普通表性能敏感场景要谨慎。序列方面如果父表的 ID 列用了bigserial或者GENERATED AS IDENTITY子分区会自动继承默认值表达式全局序列保证不重复这个没问题。麻烦的是 pg_dump 恢复时如果只导出了分区表而忘了导出序列的当前值新数据可能会主键冲突。这块建议做恢复演练别在生产环境第一次试。默认分区是另一个隐藏炸弹。数据一旦落入默认分区它就成了“所有未匹配数据的集合体”查询性能会断崖式下降因为在默认分区上无法使用精确的边界裁剪。我们有一条巡检规则每天检查默认分区行数变化一旦有新数据立刻排查为什么边界没有覆盖。6.4 分区表查询没变快怎么办很多同学反馈分区之后查询反而更慢了。遇到这种问题先对照执行计划看Append Subplans后面显示的是几。如果依然是全部分区先检查 WHERE 条件是否包含分区键、是否有函数包裹、类型是否匹配。如果裁剪已经生效但还是慢下一步看每个分区的统计信息是否过期单独ANALYZE一下分区再跑一次。如果所有条件都正常那大概率是单分区的数据量依然过大比如月分区下每个月仍有 10GB 数据。这种场景我建议缩小分区粒度比如从月分区改成周分区或者考虑在分区内部再建立更精细的索引并用部分索引缩小范围。分区表不是万能钥匙它解决的问题是“数据可裁剪、维护可拆分”真正的查询性能瓶颈还得靠索引和 SQL 优化去解。写在最后做完这个分区表项目后我最深的体会是分区表的管理重点不在于建表那一刻而在于后续每一天的边界维护和巡检。把预创建分区做成定时任务、把默认分区纳入监控、把清理策略交给 pg_partman这三件事做好了分区表才真正省心。如果你也在规划分区表建议先把这篇文章里的脚本跑通一遍再决定是手工管理还是上自动化工具。数据量不会等你准备好才开始增长分区表值得提前布局。
PostgreSQL分区表实战：从120GB大表踩坑到声明式分区与性能调优
记录一下我们团队在 PostgreSQL 分区表这条路上从踩坑到跑顺的全过程。起因是一张日志表涨到了 120GB查询越来越慢清理数据像是在做心脏手术后来我们彻底切换到声明式分区表把维护节奏从“提心吊胆”变成了“按计划执行”。这篇文章就把分区表管理的完整思路、建表细节、维护脚本、性能调优和常见坑位都梳理一遍适合正在被大表拖垮、或者准备把表改成分区结构的同学参考。1. 为什么一张大表非得分区不可先说结论分区表不是银弹但对于“数据量持续增长、历史数据访问率低、清理需求明确”的场景它几乎是最省心的解法。我们用一张实际表举例大家感受一下痛点在哪。1.1 表大了以后数据库到底哪里疼一张 120GB 的日志表问题不是“慢”这么简单而是整套数据库机制都在退化。首先是查询即使你在log_time上建了 B-tree 索引范围查询的结果也要回表取所有字段索引层数变深、缓冲命中率下降IO 消耗肉眼可见地涨。其次是清理要删除三个月前的数据最容易想到的是DELETE FROM logs WHERE log_time ...但这个操作会生成大量 WAL 日志触发表膨胀还可能长时间持有锁在线业务根本扛不住。更麻烦的是空间回收。PostgreSQL 的普通表做DELETE后空间并不会直接归还操作系统必须 VACUUM FULL 或者重建表。一张 120GB 的表做 VACUUM FULL既吃磁盘又要停机窗口业务方一听说要停半小时直接炸锅。这还只是空间问题索引碎片、统计信息过时、autovacuum 跟不上写入频率每一项都是单张超大表逃不掉的债。1.2 声明式分区为什么能解痛声明式分区从 PostgreSQL 10 开始正式可用到 11、12 版本基本成熟。它的核心思路是逻辑上一张表物理上拆成多个独立子表。你依然可以对父表执行查询、写入、加索引但底层数据被分散到各个分区文件里。这样一来按时间范围删除数据就变成了DROP TABLE或DETACH PARTITION秒级完成不产生海量 DELETE不膨胀不长时间锁表。查询时优化器可以通过分区裁剪只扫描需要的分区而不是扫全表。每个分区又是独立表autovacuum、统计信息收集、索引重建都能精细化控制维护窗口被切割得很小。这套机制特别像衣柜整理把所有衣服堆在一个柜子里找一件衣服要翻遍整个柜子按季节分成几个抽屉后找当季衣服只用拉一个抽屉。1.3 哪些表适合分区哪些别瞎凑热闹分区表有代价别看到大表就分。日志、事件流、订单历史、监控指标这类“只增不改、按时间访问”的数据是首选。它们的共同特征是数据有明确的分区键通常是时间、历史数据几乎不会被更新、清理需求刚性。不适合分区的场景我也踩过。第一类是数据量本身不大比如只有几百万行分了反而多出几十张子表的元数据开销查询还要走 Append 节点性能不升反降。第二类是查询条件里几乎没有分区键比如业务端总是按user_id直接查最新记录但表是按时间分区这会导致每次都扫全部分区比单表更慢。第三类是分区键频繁被更新比如行数据经常跨分区迁移这会让 UPDATE 变成“先删后插”性能极差。1.4 三种分区方式的选型判断PostgreSQL 支持 RANGE、LIST、HASH 三种分区。RANGE 分区适合按时间、数值区间切分比如log_time、create_date、自增 ID 区间我们日志表用的就是按月 RANGE。LIST 分区适合枚举值比如按省份、业务线、订单状态切分分区数量和值域固定。HASH 分区适合没有天然区间但要求数据均匀分散的场景比如按用户 ID 分 16 个桶多用于读写分离的冷热均衡。最容易被忽略的是嵌套分区。比如先按年月做 RANGE再按地区做 LIST 子分区。嵌套能让裁剪更精细但也成倍增加分区数量和维护复杂度建议只在数据量确实需要时才考虑。选型的关键判断依据是业务查询通常带哪个等值或范围条件这个条件就是分区键。2. 建表实操从零做一张按月分区的日志表理论讲完直接上手。我们以一张名为logs的日志表为例演示整个建表过程。假设数据结构是日志 ID、日志时间、日志级别、日志内容。2.1 父表 DDL 的规范写法父表本身不存储任何数据它只是分区定义的载体。DDL 如下CREATE TABLE logs ( log_id bigint NOT NULL, log_time timestamptz NOT NULL, log_level text, message text ) PARTITION BY RANGE (log_time);这里有几个细节值得说。第一log_time我建议用timestamptz而不是timestamp否则不同时区的业务写入会对不齐边界导致数据进错分区。第二分区键列一定要NOT NULL虽然 PostgreSQL 在实现上允许为空并把它分到默认分区或报错但生产环境很容易出现 NULL 数据堆积在默认分区的惨案。第三父表上不需要建物理索引索引建在父表上会自动传播到所有子分区这一点后面细说。有人会纠结主键怎么建。如果是纯日志表我建议不要建主键最多建唯一索引。如果业务强制要求主键记住 PostgreSQL 的硬性限制声明式分区表的主键和唯一约束必须包含分区键列也就是说PRIMARY KEY (log_id)是不行的必须写成PRIMARY KEY (log_id, log_time)。2.2 分区子表的两种创建姿势第一种是直接使用PARTITION OF在创建子表时同时声明分区边界CREATE TABLE logs_2024_01 PARTITION OF logs FOR VALUES FROM (2024-01-01::timestamptz) TO (2024-02-01::timestamptz);注意 RANGE 分区的边界规则FROM是闭区间包含起始值TO是开区间不包含结束值。所以上面的写法表示“从 2024-01-01 00:00:00 起到 2024-02-01 00:00:00 之前”正好覆盖整个一月份。这个边界语义踩过坑的人很多我见过有同事把TO写成了 2 月 1 号结果 2 月 1 号零点那一条记录死活写不进去。第二种方式是先把子表建成普通表再用ATTACH挂载到父表上CREATE TABLE logs_2024_02 ( LIKE logs INCLUDING DEFAULTS INCLUDING CONSTRAINTS ); ALTER TABLE logs ATTACH PARTITION logs_2024_02 FOR VALUES FROM (2024-02-01::timestamptz) TO (2024-03-01::timestamptz);这种方式的场景一般是你要把一个已经存在的历史普通表合并进分区体系或者子表需要有特殊的物理属性比如放在不同表空间需要先建好再挂载。ATTACH时 PostgreSQL 会校验表结构和数据是否符合分区约束数据量大的时候耗时较长建议在低峰期执行。如果需要保证任何时间点写入都有去处可以创建默认分区CREATE TABLE logs_default PARTITION OF logs DEFAULT;默认分区会接收所有“没有对应分区”的数据。我个人的建议是宁可建默认分区保底也别让它不存在。否则一条未覆盖边界的数据会导致整个 INSERT 报错。但默认分区需要定期检查防止数据漏配后堆积。2.3 索引、主键与约束的三大坑位在父表上创建索引PostgreSQL 会自动在所有现有分区上创建对应的索引之后的子分区也会自动获得。这一点从 PG 11 开始生效非常省心CREATE INDEX idx_logs_time ON logs (log_time); CREATE INDEX idx_logs_level ON logs (log_level);但自动传播不覆盖主键和唯一约束的“全局唯一性”。分区表的唯一索引在每个分区内部单独维护跨分区无法保证唯一。所以上面才说主键必须包含分区键。如果业务要求全局唯一比如日志 ID 全表唯一只有两条路一是接受(log_id, log_time)联合唯一二是用应用层或汇总表保证二选一没有完美解。CHECK 约束可以用来辅助分区裁剪和保证数据边界。声明式分区会自动生成子分区边界约束你不需要手动添加。但如果你在父表上定义了额外的 CHECK 约束它会传播给所有子分区。比如规定日志等级只能取某些值ALTER TABLE logs ADD CONSTRAINT logs_level_check CHECK (log_level IN (INFO, WARN, ERROR));3. 日常维护加分区、删分区、查分区完整流程建完分区只是第一步真正的日常挑战在于每个月怎么加新分区、历史分区怎么清理、怎么快速查看当前分区体系状态。这一节全部是可落地的操作方案。3.1 手动增加新分区的标准操作按月分区的话每月月初需要创建下个月的分区。手动创建如下CREATE TABLE logs_2024_03 PARTITION OF logs FOR VALUES FROM (2024-03-01::timestamptz) TO (2024-04-01::timestamptz);创建子分区后不需要手动建索引父表索引会自动覆盖新分区这是 PG 11 的行为实测自动创建索引会有短暂的系统表活动但整体无感。要注意的是如果父表没有默认分区而新分区还没建好临近边界时写入就会失败所以预创建分区一定要提前别卡在边界点上。如果是ATTACH一个已有表PG 会扫描数据验证是否符合边界。PG 12 之后性能有所优化但大表附加时的校验开销仍然存在。低峰期执行是底线要求。3.2 用 PL/pgSQL 批量预创建未来分区手动建分区偶尔一次没问题长期靠手敲迟早翻车。我们用一段 PL/pgSQL 脚本做预创建传入起始月份和预创建数量即可DO $$ DECLARE m date : 2024-03-01::date; i int; tab text; BEGIN FOR i IN 0..11 LOOP tab : format(logs_%s, to_char(m, YYYY_MM)); EXECUTE format( CREATE TABLE IF NOT EXISTS %I PARTITION OF logs || FOR VALUES FROM (%L) TO (%L), tab, m, m interval 1 month ); m : m interval 1 month; END LOOP; END $$;这个脚本的原理很直接用date变量做为当前的起始边界循环 12 次每次用format拼出表名logs_2024_03这种格式。%I是安全引用标识符%L是安全引用字面量防止 SQL 注入和标识符异常。IF NOT EXISTS保证重复跑不会报错。我建议把这种脚本放到定时任务里比如每月 25 号自动预创建未来 3 个月分区。预创建数量不宜太多否则会产生大量空表反而增加系统表负担和 pg_dump 耗时。3.3 如何安全地删除和离线历史分区清理历史数据是所有运维最喜欢分区表的地方没有 DELETE没有膨胀一条 DROP 就完成。DROP TABLE logs_2023_01;DROP 会直接删除数据文件并立即释放空间。如果需要留存数据比如归档到冷存储先 DETACH 再转储ALTER TABLE logs DETACH PARTITION logs_2023_01;DETACH 之后logs_2023_01就变成一张独立普通表数据原封不动分区表不再对它有任何约束。你可以对它做pg_dump、迁移到归档库、或者继续保留在实例中。PG 14 之后支持并发操作ALTER TABLE logs DETACH PARTITION logs_2023_01 CONCURRENTLY;CONCURRENTLY版本不会长时间阻塞写入适合在线清理。但要注意DETACH 之后这张表与父表之间只靠名字关联必须自己管理后续处理流程别忘记录归档清单。3.4 快速掌握分区全貌的查询手段管理分区表最怕不知道自己有哪些分区、边界是什么。PostgreSQL 提供了一套系统视图最常用的是pg_partition_treeSELECT relid::regclass AS partition_name, parentrelid::regclass AS parent_name, isleaf, level FROM pg_partition_tree(logs::regclass);它会递归展示分区的层级关系。isleaf为 true 表示叶子分区level表示层级深度父表是 0。这张表尤其适合写自动化巡检脚本比如检查所有叶子分区里是否有超过预期边界范围的数据。查看每个分区的边界定义可以用pg_partitionsSELECT partition_name, partition_boundary FROM pg_partitions WHERE parent_table logs;partition_boundary字段直接显示这个分区的 FROM/TO 定义一眼就能确认边界有没有重叠、有没有漏档。另外pg_class.relispartition true可以快速查出实例里所有分区子表。4. 查询性能分区裁剪与执行计划调优分区表建好后大家最关心的一定是查询到底快了多少。这一节讲清楚分区裁剪的生效机制以及哪些写法会让裁剪静默失效。4.1 分区裁剪是什么怎么验证分区裁剪Partition Pruning是指优化器根据 WHERE 条件中的分区键值在生成执行计划时就排除掉不相关的分区。从 PG 11 开始enable_partition_pruning默认开启通常不需要额外设置。验证方式就是看执行计划EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM logs WHERE log_time 2024-03-10::timestamptz AND log_time 2024-03-11::timestamptz;如果裁剪生效计划输出中会看到类似Append Subplans 1 of 24表示只扫描了 24 个分区中的 1 个。如果看到Subplans 24说明裁剪完全没生效查询逻辑上扫描了所有分区。BUFFERS选项可以进一步确认实际读取的 buffer 数量。4.2 三类让裁剪失效的常见写法第一类是给分区键套函数最典型的就是WHERE date_trunc(month, log_time) 2024-03-01::timestamptz这种情况下优化器无法从函数表达式反推出分区键的取值边界只能放弃裁剪。正确写法是直接用范围条件WHERE log_time 2024-03-01::timestamptz AND log_time 2024-04-01::timestamptz第二类是类型不匹配导致隐式转换丢失边界。分区键是timestamptz却拿字符串直接比较WHERE log_time 2024-03-10 10:00:00这种写法在特定场景下可能触发临时转换导致无法准确裁剪。解决办法是显式类型转换比如2024-03-10 10:00:00::timestamptz。第三类是使用 OR 条件连接多个非分区键条件。优化器面对 OR 时会把可能性放大逐分区裁剪很困难。能拆成 UNION ALL 就拆不能拆就接受全分区扫描的现实。注意这些坑不是 SQL 写错而是优化器能力边界的问题理解这一点能省很多排查时间。4.3 统计信息与 autovacuum 策略分区表的统计信息是分区的每个子表独立收集。也就是说父表无法提供全局统计优化器在生成 Append 计划时会逐个分区评估。如果某些分区长期没有 ANALYZE估算行数会偏差很大导致嵌套循环变哈希连接这类计划抖动。我们的做法是每次批量创建新分区后立即对新分区做 ANALYZEANALYZE logs_2024_03;同时把 autovacuum 的参数按业务调整。日志表写入频繁autovacuum_vacuum_scale_factor默认值是 0.2对高频写入的大分区表来说太迟钝容易导致死元组堆积。建议对分区表单独设置ALTER TABLE logs SET (autovacuum_vacuum_scale_factor 0.05); ALTER TABLE logs SET (autovacuum_vacuum_threshold 1000); ALTER TABLE logs SET (autovacuum_analyze_scale_factor 0.05);这里有个关键认知父表的这些参数会传递给所有子分区所以只需要在父表设置一次。定期检查每个分区的膨胀情况时用pg_stat_user_tables看n_dead_tup和n_live_tup的比值如果某个分区死元组比例异常升高针对单个分区手动 VACUUM 即可不需要动整表。5. 自动化分区管理pg_partman 实战手工脚本管理分区在分区数量少的时候很轻松但当你有十几张表都按月分区还要维护保留策略时就该让专业工具出场了。PostgreSQL 生态里最成熟的方案是 pg_partman。5.1 安装与初始化pg_partman 的安装分两步。第一步在数据库里创建扩展CREATE EXTENSION pg_partman;第二步如果需要后台自动维护把pg_partman_bgw加到shared_preload_libraries并重启实例shared_preload_libraries pg_partman_bgw重启后还需要设置后台 worker 的参数比如pg_partman_bgw.interval 60 pg_partman_bgw.role postgres pg_partman_bgw.dbname mydbinterval 60表示每 60 分钟自动运行一次维护任务。这一步建议由 DBA 统一配置因为它会直接影响所有分区表的自动建分区和清理行为。5.2 创建分区任务用create_parent函数把一张表接入自动管理SELECT partman.create_parent( p_parent_table : public.logs, p_control : log_time, p_type : range, p_interval : 1 month, p_premake_count : 3 );各参数含义p_control是分区键列名p_type是分区方式p_interval是每个分区的区间长度p_premake_count是预创建的未来分区数量我建议至少保留 3防止跨月和突发流量。执行后 pg_partman 会自动创建未来 3 个月的分区并注册到它自己的配置表。如果表已经手工建过分区也可以在part_config表里调整配置但通常推荐新建表时就接入避免边界纠缠带来的麻烦。5.3 手工维护与保留策略即使配置了后台 worker我也建议每周手工执行一次维护确保任务可控SELECT partman.run_maintenance(public.logs);run_maintenance会根据配置自动创建缺失的未来分区并执行分区裁剪的约束检查。保留策略的配置很简单先更新配置表UPDATE partman.part_config SET retention 3 months, retention_keep_table false WHERE parent_table public.logs;retention 3 months表示保留最近 3 个月的数据retention_keep_table false表示直接把过期分区 DROP 掉。如果出于归档需求想保留表可以改成true它会先 DETACH 再保留为普通表归档后由下游任务处理。5.4 使用 pg_partman 的注意事项用这个工具有几个坑。第一个是权限后台 worker 使用的角色必须拥有父表的 ALL 权限否则自动建分区会频繁报错。第二个是时间戳类型p_control列如果是timestamptz分区边界自动生成时带时区最好不要和timestamp混用。第三个是默认分区pg_partman 默认不允许目标表存在 DEFAULT 分区因为这会干扰它的边界检查如果业务确实需要默认分区需要做一些额外配置建议严格执行“有默认分区就别用自动管理”的原则。还有一点要提醒pg_partman 更适用于纯分区管理场景如果一张表同时还有复杂的外键、物化视图依赖自动化 DROP 旧分区时会遇到依赖报错我建议这种情况下先手工处理依赖再接入自动维护。6. 常见问题排查实录最后这一节整理我们上线分区表后遇到过的真实问题和排查过程每条都是踩过的坑。6.1 唯一约束和主键的报错最常见的报错是这样的ERROR: unique constraint on partitioned table must include all partitioning columns原因在 2.3 节已经说过分区表的唯一约束必须在每个分区内独立保证如果不包含分区键数据插入 A 分区时无法知道 B 分区是否已有相同值。解决办法就是让主键或唯一索引包含分区键列。有一类业务场景是“全局 ID 需要唯一”但分区键又是时间这种只能接受联合唯一或者把 ID 统一交给应用层生成并做全局去重。6.2 更新分区键导致行跨分区PG14 前后的差异在 PG 14 之前如果 UPDATE 把一行数据的主键值改到另一个分区范围数据库会直接报错ERROR: new row for relation logs violates partition constraint这是因为旧版本不支持行迁移。解决思路只有两个一是业务上避免更新分区键二是升级到 PG 14 以上。PG 14 开始PostgreSQL 支持 UPDATE 行在分区之间自动移动内部逻辑是 DELETE 旧分区的一行再 INSERT 到新分区。注意行迁移虽然省心但代价是走了完整的 DML 路径高频更新分区键会放大写放大我们的经验是核心链路尽量还是别这么干。6.3 外键、序列、默认分区的连环坑分区表的外键分两种情况。PG 12 之前普通表不能引用分区表作为外键目标升级后才行。但即使支持了外键检查在分区表上的开销也远高于普通表性能敏感场景要谨慎。序列方面如果父表的 ID 列用了bigserial或者GENERATED AS IDENTITY子分区会自动继承默认值表达式全局序列保证不重复这个没问题。麻烦的是 pg_dump 恢复时如果只导出了分区表而忘了导出序列的当前值新数据可能会主键冲突。这块建议做恢复演练别在生产环境第一次试。默认分区是另一个隐藏炸弹。数据一旦落入默认分区它就成了“所有未匹配数据的集合体”查询性能会断崖式下降因为在默认分区上无法使用精确的边界裁剪。我们有一条巡检规则每天检查默认分区行数变化一旦有新数据立刻排查为什么边界没有覆盖。6.4 分区表查询没变快怎么办很多同学反馈分区之后查询反而更慢了。遇到这种问题先对照执行计划看Append Subplans后面显示的是几。如果依然是全部分区先检查 WHERE 条件是否包含分区键、是否有函数包裹、类型是否匹配。如果裁剪已经生效但还是慢下一步看每个分区的统计信息是否过期单独ANALYZE一下分区再跑一次。如果所有条件都正常那大概率是单分区的数据量依然过大比如月分区下每个月仍有 10GB 数据。这种场景我建议缩小分区粒度比如从月分区改成周分区或者考虑在分区内部再建立更精细的索引并用部分索引缩小范围。分区表不是万能钥匙它解决的问题是“数据可裁剪、维护可拆分”真正的查询性能瓶颈还得靠索引和 SQL 优化去解。写在最后做完这个分区表项目后我最深的体会是分区表的管理重点不在于建表那一刻而在于后续每一天的边界维护和巡检。把预创建分区做成定时任务、把默认分区纳入监控、把清理策略交给 pg_partman这三件事做好了分区表才真正省心。如果你也在规划分区表建议先把这篇文章里的脚本跑通一遍再决定是手工管理还是上自动化工具。数据量不会等你准备好才开始增长分区表值得提前布局。
