企业官网建设流程全解析

从 PostgreSQL 10 引入声明式分区开始，到 12 这个版本，分区表算是真正到了可以放心上生产的地步。最近我把一个跑了快两年的日志类大表迁移到了 PG12 分区表，顺手把自动添加分区的整套机制也搭完了，整个过程踩了不少坑，也积累了一些心得。这篇文章就把实例和自动化方案一并整理出来，给正在折腾 PG12 分区表的同学一个参考。

1. PostgreSQL 12 分区表：能力和限制先摸清楚

1.1 为什么我选 12 而不是其他版本

很多人会问，现在新版本都出到 15、16 了，为什么还要折腾 PG12？答案很现实：存量系统就是 PG12，不可能为了一个分区功能就去做大版本升级。而且从功能完整度来看，PG12 的分区表已经覆盖了绝大多数业务诉求，甚至可以说，对大部分场景而言，12 和后面版本的区别并没有想象中那么大。

PG12 相对于更早版本，有几个关键改进值得先说出来：

• 支持了 ATTACH/DETACH PARTITION 的语法，可以在已有表的基础上挂接或卸载分区，不用非得通过 CREATE TABLE PARTITION OF 这种只能在建表时绑定分区的写法。
• 默认分区的概念在 PG12 之前就比较成熟了，但 12 里对默认分区的处理更稳妥，比如允许已经存在的默认分区在 ATTACH 新分区时做数据再分配。
• 分区裁剪能力进一步提升，特别是带参数化路径的查询，在 PG12 上更不容易出现走全分区的尴尬。
• 支持在分区表上创建唯一约束（前提是包含分区键），外键支持也更完整。

选 PG12 还有一个重要考量，就是对现有工具链和监控体系的兼容性。很多公司内部的备份、同步、慢查询分析工具都是基于 PG12 的，直接在这个版本上把分区方案落地，风险最小。

1.2 声明式分区和传统继承分区的区别

PG10 之前的版本要自己用表继承加 CHECK 约束来模拟分区，还要写一堆 RULE 或者触发器去路由数据，维护成本极高。声明式分区最核心的价值就是让数据库自己管理分区的约束匹配、数据路由和查询裁剪。

用传统继承方式做分区，你得在父表上写一个 BEFORE INSERT 触发器，根据插入行的字段值判断要进哪个子表，写完 SQL 还要保证 CHECK 约束和触发器逻辑一致。一旦分区边界改了，触发器也得同步改，漏一处就出乱子。声明式分区完全没有这个问题，插入数据时 PostgreSQL 会根据分区键自动路由到正确分区，匹配不到就报错（除非有默认分区）。

从查询优化角度看，声明式分区的 partition pruning 是优化器直接识别的，而继承式分区还需要 constraint exclusion 来配合，效率差距明显。PG12 对带有参数化条件的嵌套循环连接在分区裁剪上做了增强，这在报表类SQL里很常见。

2. 动手前必须定清楚的事情：分区键和分区设计

2.1 分区键怎么选：RANGE、LIST 还是 HASH

分区键是分区表设计中最关键的一步，没有之一。选错分区键，后续所有运维都会很难受。

从我的实践经验来看，绝大多数分区表都适合用 RANGE 按时间分区，尤其是日志、订单、流水这类持续增长、又经常按时间范围查询的数据。RANGE 分区的好处是边界直观，一个月或一天一个分区，删除老数据直接 DROP 分区即可，比 DELETE 大表要快好几个数量级。

LIST 分区适合那种分区键取值有限的场景，比如按业务类型、地域、状态来分。它的限制是分区数量不会太多，一旦某个枚举值数据量爆炸，单个分区还是会有压力。

HASH 分区在 PG11 就支持了，PG12 里更稳定。它解决的是数据均匀分布的问题，比如订单表按订单号 HASH 分成几十个分区。但 HASH 分区对范围查询不友好，查询条件不带分区键时基本就是全分区扫描，所以如果业务查询都是按某个ID精确查，HASH 分区可以考虑；要是查询经常带时间范围，还是老实选 RANGE。

2.2 分区粒度、命名规范和边界定义

分区粒度取决于业务查询习惯和分区数量控制。按天分区的缺点是分区数量膨胀快，一张一年数据量的表就有 365 个分区；按月分区是比较均衡的选择。如果日数据量特别大（每天上千万行），可以考虑按天甚至按周；如果数据量一般，按月最省心。

分区命名规范要尽早定，且全团队统一。我在生产环境常用的格式是：

• 按月分区：表名_y2024m12
• 按天分区：表名_d20241201

命名里带上年份和月份的好处是，写自动化脚本时可以直接用 to_char 生成分区名，不需要记录额外的映射表。另一个细节是分区的边界定义，PG12 里 RANGE 分区的边界是前闭后开，也就是 FOR VALUES FROM ('2024-12-01') TO ('2025-01-01') 包含 2024-12-01 这天的数据，但不包含 2025-01-01 的数据。这个特性决定了你在创建连续分区时，上一个分区的上界必须是下一个分区的下界，中间不能留空隙，也不允许重叠。

3. 实例拆解：把一张普通的月表改造成分区表

3.1 一次性完整建表 SQL 示例

直接拿我最近处理的线上订单流水表作为例子。原始表的定义大致是这样的：

CREATE TABLE operation_log ( id bigserial NOT NULL, biz_type varchar(32) NOT NULL, op_user_id bigint NOT NULL, op_content text, op_time timestamp NOT NULL DEFAULT now(), PRIMARY KEY (id, op_time) );

改造为按月 RANGE 分区表，DDL 如下：

CREATE TABLE operation_log ( id bigserial NOT NULL, biz_type varchar(32) NOT NULL, op_user_id bigint NOT NULL, op_content text, op_time timestamp NOT NULL DEFAULT now(), PRIMARY KEY (id, op_time) ) PARTITION BY RANGE (op_time);

这里有个非常容易踩的坑：分区表的主键必须包含分区键。如果业务主键是 id，那你得把 op_time 也加进主键，否则建表会直接报错。为什么？因为 PostgreSQL 需要保证主键在全局唯一，而唯一约束在分区表上必须包含分区键，才能通过各分区约束判断唯一性冲突。

然后手动创建两个初始分区：

CREATE TABLE operation_log_y2024m11 PARTITION OF operation_log FOR VALUES FROM ('2024-11-01 00:00:00') TO ('2024-12-01 00:00:00'); CREATE TABLE operation_log_y2024m12 PARTITION OF operation_log FOR VALUES FROM ('2024-12-01 00:00:00') TO ('2025-01-01 00:00:00');

3.2 用 ALTER TABLE ATTACH PARTITION 处理存量数据

如果已经有存量数据在普通表里，比如一张老表 operation_log_old 存了 10 月之前的数据，想把它变成分区表的一个分区，需要先在老表上补一个 CHECK 约束：

ALTER TABLE operation_log_old ADD CONSTRAINT operation_log_old_op_time_check CHECK (op_time >= '2024-10-01 00:00:00' AND op_time < '2024-11-01 00:00:00');

然后把老表挂到分区表下面：

ALTER TABLE operation_log ATTACH PARTITION operation_log_old FOR VALUES FROM ('2024-10-01 00:00:00') TO ('2024-11-01 00:00:00');

ATTACH 的时候 PostgreSQL 会做两件事：校验已有数据是否满足边界约束，然后继承父表的索引和约束设置。如果你的老表数据量非常大（上亿行），ATTACH 过程中扫描校验会持锁，建议在低峰期操作，或者先加 CHECK 约束让校验过程可以并行扫描。

来一个更快的技巧：数据量巨大时，可以先 ATTACH，然后在业务低峰期用CREATE INDEX ON ONLY在父表上定义索引，再单独给各分区建同名索引，最后用ALTER TABLE parent ALTER INDEX ... INHERIT把子分区索引关联上去。这套流程比直接在父表建索引要快。

3.3 数据写入和查询验证

分区表建好之后，插入数据会自动路由，不需要任何修改。看执行计划也能很清楚观察到分区裁剪：

EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM operation_log WHERE op_time >= '2024-12-01 00:00:00' AND op_time < '2024-12-02 00:00:00';

因为分区键是 op_time，并且条件限定在一天内，优化器只会去扫描 12 月那个分区，这就是分区裁剪生效的表现。如果业务 SQL 习惯在 op_user_id 上加条件但没带 op_time，那对不起，PG12 会强制扫描所有分区。

4. 自动添加分区的几种方案对比

分区表建好了，只是万里长征第一步。真正让人头疼的是分区不会自己长出来——到了月底，如果下个月的分区还没有创建，数据写入直接会报错：

ERROR: no partition of relation "operation_log" found for row DETAIL: Partition key of the failing row contains (op_time) = (2024-12-01 00:00:00).

这时候再来手动补建分区，生产就等着挨骂。所以必须把自动添加分区做成一个可靠的机制。

4.1 方案一：pg_partman 扩展

pg_partman 是 PG 生态里最专业的分区管理工具，PostgreSQL 官方社区很多 DBA 都在用。它的核心功能就是按照模板自动创建、删除、轮转分区，并且能维护分区索引。

CREATE EXTENSION pg_partman; -- 把已有分区表纳入 pg_partman 管理 SELECT partman.create_parent( p_parent_table => 'public.operation_log', p_control => 'op_time', p_type => 'range', p_interval => '1 month', p_premake => 3 );

premake 参数很关键，控制着预创建未来分区的数量。比如设置为 3，就会自动把未来 3 个月的分区建好。

日常维护靠 pg_partman 的 run_maintenance 函数，需要配置定时任务去调用：

SELECT partman.run_maintenance(p_analyze := true);

pg_partman 的好处是省心，功能全面，坏处是要引入新扩展，而且对于简单的按月分区来说，杀鸡用了牛刀。

4.2 方案二：外部定时任务 + 动态SQL脚本

如果不想引入额外扩展，最简单的方案就是写一个通用 SQL 脚本，配合系统的 crontab 定时执行。脚本逻辑通常是用 plpgsql 函数判断下个月的分区是否存在，不存在就动态生成 DDL 并执行。

这个方案的好处是零依赖，可控性极高，整个逻辑都在自己的眼皮底下。坏处是自己要处理各种边界情况，比如跨年、分区被手动误删、命名规范变更等。但对于大部分团队来说，自己写反而更贴合实际情况。

4.3 方案三：事件触发器自动拦截

事件触发器可以在 DML 操作前拦截没有分区的数据写入，动态帮它建分区。比如写一个 BEFORE INSERT 的触发器，当发现目标分区不存在时先执行建分区 DDL，再插入数据。

这个方案看起来很智能，但我不建议在生产环境用。原因很简单：

• 触发器的逻辑在事务中间执行 DDL，PostgreSQL 对事务中 DDL 的处理粒度很粗糙，某条数据触发建分区后，如果事务回滚，DDL 也会被回滚，意味着下次还得重复建。
• 多个并发写入同时触发建分区时，会产生 DDL 竞争，一个成功另一个报错，高并发下非常容易引发业务异常。
• 触发器里的 EXECUTE 动态 SQL 权限处理麻烦，而且出了问题很难排查。

所以自动分区这件事，我坚定的主张是"定时任务+提前预创建"，而不是"用的时候再创建"。宁可定时任务提前多建几个分区，也不要在业务写入路径里插入任何可能导致延迟或异常的分区逻辑。

5. 我实际在用的自动分区实现：定时函数 + 预创建

下面是我在线上真正跑着的方案，用 plpgsql 函数做分区创建，crontab 做调度，非常轻量。

5.1 核心函数逻辑详解

核心函数的作用是根据当前日期，判断未来 N 个月的分区是否都已经存在，如果不存在则自动创建。我保留了一个通用模板：

CREATE OR REPLACE FUNCTION public.auto_create_range_partitions( target_table regclass, partition_column text, interval_months int DEFAULT 1, premake_count int DEFAULT 3 ) RETURNS void AS $$ DECLARE p_schema text; p_table text; p_curr_date date; p_start_date date; p_end_date date; p_part_name text; v_partition_exists boolean; v_sql text; BEGIN SELECT n.nspname, c.relname INTO p_schema, p_table FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.oid = target_table; p_curr_date := current_date; FOR i IN 1..premake_count LOOP p_start_date := date_trunc('month', p_curr_date) + interval '1 month' * (i - 1); p_end_date := date_trunc('month', p_curr_date) + interval '1 month' * i; p_part_name := p_table || '_y' || to_char(p_start_date, 'yymm'); SELECT EXISTS ( SELECT 1 FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname = p_schema AND c.relname = p_part_name ) INTO v_partition_exists; IF NOT v_partition_exists THEN v_sql := format( 'CREATE TABLE %I.%I PARTITION OF %I.%I FOR VALUES FROM (%L) TO (%L)', p_schema, p_part_name, p_schema, p_table, p_start_date, p_end_date ); EXECUTE v_sql; RAISE NOTICE 'created partition: %.% for % to %', p_schema, p_part_name, p_start_date, p_end_date; END IF; END LOOP; END; $$ LANGUAGE plpgsql;

这个函数有几个值得说明的设计点。

第一，用 regclass 作为入参，可以自动规避 schema 写错的问题。第二，分区名由 to_char 动态生成，避免手写字符串拼接的管理成本。第三，用分区边界作为判断条件去查询 pg_class，而不是只看名字，防止出现分区名字存在但边界对应不上的情况。第四，创建分区时用 format 函数加 %I %L 占位符，避免 SQL 注入问题。

第一点补充说明：regclass 传入时，函数体里通过 pg_class / pg_namespace 解析出 schema 和 table 名，这比直接传一个字符串做实参更加类型安全。实际工作中我习惯这样调用：

SELECT public.auto_create_range_partitions('public.operation_log', 'op_time', 1, 3);

每天凌晨 3 点执行一次，运行时会先检查当前月、下个月、下下个月的分区。如果有哪个月的分区缺失，函数会自动补上，有连续执行也不会重复创建，因为存在性判断挡住了。

5.2 crontab 定时调度配置

函数写好之后，定时调度就很简单了。我用的 crontab 配置如下：

30 3 1 * * psql -h 127.0.0.1 -U appuser -d appdb -c "SELECT public.auto_create_range_partitions('public.operation_log', 'op_time', 1, 3);" >> /var/log/pg_partition.log 2>&1

这里有几个要点：

• 每个需要自动建分区的表，单独一行 crontab，方便出问题时单独排查。
• psql 连接尽量走 unix socket 或者是内网地址，避免把密码写在命令行里。推荐用 pgpass 文件或者环境变量管理密码。
• 只要分区在这个月的任意一天创建好了就行，不用反复执行。不过我的函数做了存在性判断，就算每天执行也不会重复建分区，所以安全的做法反而是每天跑一次，保证未来 3 个月的分区永远都在。

5.3 分区的预创建策略与业务高峰应对

预创建的分区数量我建议不要太少。很多人只预创建 1 个月，结果月末哪天业务方突然要求补数据，或者数据量突增，刚好碰到分区没建，就抓瞎了。我通常设置为 3 到 6 个月。

预创建分区还有一个额外的好处：可以提前在分区上做索引维护、统计信息收集，不用等到数据写入时才临时抢资源。有些团队会在月初凌晨写个批量任务，对上个月的分区做 ANALYZE，同时把未来分区建好，两件事放在同一个窗口里完成，生产环境的性能波动也会更小。

6. 分区表运维中的那些坑和排查技巧

6.1 高频报错和对应解法汇总

第一个高频报错就是前面提到的 no partition of relation found for row。遇到这个错误，优先检查你是不是把自动建分区的任务停了。如果任务正常运行，多半是分区边界计算错误。比如函数里用了 p_curr_date 而不是 date_trunc 之后的日期，导致生成的分区名和边界错位。

第二个高频问题是分区边界重叠或者空隙。这在手动创建分区时特别常见。解决办法是建分区前先查询 pg_partitioned_table 和 pg_class 确认现有边界：

SELECT c.relname, pg_get_expr(partdef.relpartbound, partdef.oid) FROM pg_class c JOIN pg_partition partdef ON partdef.parentrelid = 'operation_log'::regclass JOIN pg_class part ON part.oid = partdef.oid

如果发现某个月的数据插不进去，大概率是边界设置不对。

第三种常见情况是 ATTACH 分区时锁冲突。ATTACH 需要获取父表的 ACCESS EXCLUSIVE 锁，如果业务正在高峰时期写入，ATTACH 会阻塞 DML，甚至反过来被长事务阻塞。所以，生产环境做 ATTACH 一定要避开业务高峰。

6.2 索引继承和统计信息不能忘

PG12 在父表上创建索引时，如果已有分区，会自动在所有已有分区上创建对应索引；但之后新加的分区会不会自动带索引？答案是会的，只要索引是在父表上建的，后续通过 CREATE TABLE PARTITION OF 创建分区时，新分区会自动继承父表的索引定义。

但注意，通过 ATTACH 挂上去的分区不会自动创建对应索引。因为 PG 认为这个分区以前是独立表，可能已经有自己的索引，挂接后应该沿用自身索引。如果你在老表 ATTACH 之前忘了建索引，之后就得手动补齐。这个坑我踩过一次，当时 10 月份的查询走全表扫描，慢得怀疑人生。

统计信息也是一样。分区表的统计信息是每个分区独立收集的。如果新分区刚创建但是还没有数据，ANALYZE 跑不跑无所谓；一旦有大量数据写入，建议结束后立刻 ANALYZE 对应的分区。自动化任务里加上ANALYZE public.operation_log;会保险很多。

6.3 性能表现和小技巧补遗

分区表能不能带来性能提升，关键在于分区裁剪是否生效。PG12 里，当查询条件中的分区键是常量时，裁剪一定生效；但如果分区键出现在函数的参数中，部分场景下裁剪效果会打折。例如：

WHERE op_time >= now() - interval '7 days'

这种写法 PG12 是可以裁剪的，因为 now() 在计划阶段会被计算成常量。但如果你的代码里是写成where op_time >= $1这种 prepared statement，PG12 对它的裁剪能力其实也比较成熟了，前提是参数类型和分区键完全匹配。

一个减少维护负担的做法是创建默认分区（DEFAULT PARTITION）。有了默认分区后，如果自动建分区任务运行失败，数据至少不会报错写入失败，而是进入默认分区，这给了我们一个修复问题的缓冲时间。

但默认分区有个副作用：查询计划在包含默认分区时可能变差。因为优化器在判断分区裁剪时，不能像普通分区那样直接排除默认分区，需要额外校验默认分区里是否可能有相关数据。如果默认分区里一直没数据，代价很小；一旦累积了大量意外数据，查询性能就会明显下降。我的建议是，默认分区要有，但同时也必须有监控告警，一旦默认分区里有数据就马上告警并人工介入。

另一个小技巧是，如果分区数特别多（超过几百个），可以考虑对不需要的分区做 DETACH 而不是 DROP，方便出问题时排查。当然，归档日志类的数据干脆 DROP，不用手软。

还有一点，PG12 的 VACUUM 在分区表上也有改进，但每个分区还是建议单独维护，不要只对父表做 VACUUM。自动化脚本里加上分区级别的 vacuum，可以有效防止事务 ID 回卷导致的性能问题。

7. 最后再多说一句踩坑后的体会

这套分区自动管理方案上线后，最大的感受就是午夜再没有因为分区缺失被叫醒过。回想起来，分区表本身并不复杂，真正复杂的是边界条件和自动化机制的健壮性。建分区函数里预留 3 个月的分区是我的习惯，宁可一次性多建几个也不要半夜报警。

另外一点比较深刻的体会是，写自动化脚本时要永远假设它会在最不合适的时刻出错——比如跨年那一刻、闰年、服务器重启后。分区边界用 date_trunc 生成，跨年不需要额外处理，因为 PG 的时间函数天然能算对 12 月到下一年 1 月的边界。这也是我强烈推荐用 date_trunc 而不是手拼日期字符串的原因。

如果你刚准备上 PG12 分区表，我建议先拿一张非核心的表试运行两个月，把所有流程走通、把所有坑踩一遍，再推广到核心大表上。分区表是个好工具，但真正让它在生产环境稳定运转的，是这些看起来不起眼的自动化和巡检机制。