企业官网建设流程全解析

1. 什么情况下值得为一张表做分区

先说个身边常见的事。某公司有个订单流水表，业务量不算特别夸张，每天几十万笔，但架不住常年不清理，单表数据量走到两三亿行以后，几类问题会陆续冒头：写入开始变慢，因为索引膨胀得厉害；按时间范围查最近订单的时候，明明命中索引，但查询计划却要扫很大一段索引页；更头疼的是历史数据想删又不敢删，一个大范围 DELETE 下去，锁表时间能把业务拖到告警。

很多人第一反应是"上分区表"。这个方向是对的，但分区表并不是万能药，也不是所有"表太大"的场景都适合。我个人的判断标准很简单：如果这张表有明确的自然维度可以用来切数据，比如时间、地域、业务线、租户ID，而且查询条件里非常频繁地带上这个维度，同时数据有明显的冷热分层——那么分区表能带来的收益立竿见影。反过来，如果表的数据量其实没那么大，只是查询写得烂，那分区不但救不了你，反而会在写路径上增加分区路由的开销，维护上也多出一堆事。

PostgreSQL 的分区能力历经了从 10 版本开始正式支持声明式分区的演进，到 12 以后路由性能大幅提升，到 14 以后支持了分区级统计信息和一些优化，到 16、17 又陆续补齐外部表分区、逻辑复制方面的能力。今天写这篇，核心不是讲理论，而是把我自己实际管理分区表过程中沉淀下来的一套操作路径和经验教训整理出来，给正在评估或已经踩在分区表这条路上的同学做个参考。

1.1 分区表解决的问题：为什么单表堆积会让性能不可控

单表数据量增长后，B-Tree 索引的高度会缓慢增加，但真正的杀手不是索引高度，而是索引的"局部性"变差。拿时间序列数据来说，每天新写入的数据散布在索引的各个页面上，如果老数据不清理，索引中热数据和冷数据混在一起，缓存命中率会持续下降。数据量越大，每次查询要访问的索引页面越多，随机 IO 越多，性能曲线就会急剧恶化而不是平缓下降。

还有一点容易被忽略：单表膨胀后，VACUUM 的负担也在加重。PostgreSQL 的 MVCC 机制决定了删除和更新会产生死元组，死元组多了就需要 VACUUM 回收空间。一张几百 GB 的表执行一次 VACUUM 可能要跑十几分钟甚至更久，期间对磁盘 IO 和 CPU 都有明显消耗。而分区表把数据切到各个子表里，每个子表可以独立做 VACUUM、独立做统计信息收集、独立做空间回收，维护操作的影响面就从"全表"缩小到了一个分区。

1.2 分区表的适用边界：哪些场景反而不该用

虽然分区表能解决很多问题，但它在某些场景下确实是"负优化"。这里写过不少教训，列几个最常见的反面场景：

• 没有固定查询维度，或者查询条件里极少带分区键。比如按用户ID做了 100 个分区，但业务查询经常只按订单号查，分区键完全没进 WHERE 条件。此时优化器要扫描所有分区再合并结果，性能比分表还差。
• 数据量其实不大，单表一两千万行但查询性能不佳。这种问题根因通常在索引设计、SQL 写法或者硬件能力，分区分了也解决不了根本问题，反而增加管理成本。
• 更新频繁且更新条件经常跨分区。分区键的更新在 PostgreSQL 里的代价比较高，因为行要从一个分区物理移动到另一个分区，相当于先 DELETE 再 INSERT。如果业务里经常改分区键字段，写入放大非常严重。
• 分区数量失控。有些同学把分区粒度做得非常细，一天一个分区，跑两年就是 730 个分区。PostgreSQL 虽然支持几千个分区，但几百个分区的查询计划生成时间已经开始有明显延迟，而且每个查询都要打开分区文件，文件句柄和缓存开销都不小。

分区表适合的场景，总结成一句话：数据量大，有自然切分维度，查询按维度走，冷热分层明显。四个条件同时满足才值得做，缺一个都要再掂量一下。

2. 声明式分区的基本盘：从建表到数据分布

PostgreSQL 从 10 版本开始引入了声明式分区，这是目前最推荐的分区方式。相比传统的表继承 + 触发器路由，声明式分区的最大优点在于：分区结构由系统管理，插入数据时自动路由到对应分区，查询时能自动做分区裁剪，不需要手动写一大堆触发器函数。

2.1 三种分区方式怎么选

声明式分区支持三种分区策略：

实际业务里，时间序列数据用 RANGE 占绝对主流，因为时间天然有顺序，而且冷热数据边界清晰。如果是多租户平台，按 tenant_id 做 LIST 或 HASH 也常见。我个人不太推荐一开始就用 HASH，因为哈希分区对查询模式的限制比较大，后面想调整很麻烦。

2.2 一个典型的分区表建表实例

以一个订单流水表为例，假设按月份做 RANGE 分区：

CREATE TABLE orders ( id BIGSERIAL, tenant_id BIGINT NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), status VARCHAR(16), amount NUMERIC(12,2), PRIMARY KEY (id, created_at) ) PARTITION BY RANGE (created_at); CREATE TABLE orders_202501 PARTITION OF orders FOR VALUES FROM ('2025-01-01') TO ('2025-02-01'); CREATE TABLE orders_202502 PARTITION OF orders FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

这里有两个细节值得展开。

第一个是主键。PostgreSQL 要求分区表的主键必须包含分区键，所以这里把created_at加进了主键，id不能再单独当主键。这是声明式分区的硬性约束，很多人第一次建表在这里报错。

第二个是分区边界。RANGE 分区边界是"前闭后开"的，FOR VALUES FROM ('2025-01-01') TO ('2025-02-01')表示这个分区包含 1 月 1 日零点及其之后、直到 2 月 1 日零点之前的数据。所以2025-01-31 23:59:59落在orders_202501，而2025-02-01 00:00:00就落到orders_202502了。边界设计最忌讳的是留缺口，比如2025-01-01到2025-01-31这种写法，1 月 31 日的大部分数据都会找不到分区而坐落在父表上。

关于最后一点多说一句：父表本身也是一个可以存储数据的表。如果插入的数据找不到匹配的子分区，PostgreSQL 会默认把数据写到父表上。这不是报错，但它意味着这些数据完全不会被任何分区裁剪命中，查询会漏数据，而且父表和子表的数据总量对不上。为了防止这种情况，可以创建父表时加上CHECK (false) NO INHERIT约束，让任何写父表的动作直接报错：

CREATE TABLE orders ( ... ) PARTITION BY RANGE (created_at); ALTER TABLE orders ADD CONSTRAINT chk_no_data CHECK (false) NO INHERIT;

这样如果有数据试图落到父表，就会收到约束违规错误，马上就能发现是分区缺失还是边界设计问题。

3. 分区维护实操：从日常新增到批量清理

分区表的日常维护，说白了就是"增、删、换"三件事。分区建好了不代表一劳永逸，每个月要加新分区，历史数据要到点清理，还有时候要把一张已经跑很久的普通大表原地改造成分区表。这几件事里坑都不少，尤其最后一件，很多人贸然操作把业务搞挂了。

3.1 加新分区：分区索引要不要手动建

用上面的orders表举例，到了 2025 年 2 月底，要准备 3 月的分区了。直接执行建分区语句就行：

CREATE TABLE orders_202503 PARTITION OF orders FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');

建完后注意一个行为：PostgreSQL 会自动在子分区上创建和父表一致的索引。也就是说，父表上已有的索引和约束，新建的子分区会自动继承，这在 PG 11 之后已经是默认行为，不需要手动在每个子表上建索引。

但是有个细节，自动创建的索引名称会有编号后缀，比如orders_tenant_id_idx1、orders_tenant_id_idx2，时间长了索引文件名会变得不好认。如果你比较在意可维护性，可以在建子分区后顺手重命名索引。另外，如果是用CREATE INDEX在父表上新建索引，PG 会把索引递归应用到所有已有子分区，但不会自动应用到之后新建的分区——不对，这里要纠正一下，PG 11 之后新建分区会自动继承父表已有的索引。所以常见策略是：先建好父表和第一批次分区，然后在父表上统一建索引，后续新加分区时会自动带上索引。

新分区如果涉及默认分区的情况要小心。如果表上存在 DEFAULT 分区，PG 在加新分区时不会自动把 DEFAULT 分区里符合新边界的数据搬过来，数据会继续留在 DEFAULT 分区，造成查询剪裁不了。这种情况在建表时就要尽量避免默认分区，或者加完新分区后手动把 DEFAULT 里相应的数据迁回去。

3.2 清理旧分区：为什么要先 DETACH 再 DROP

删除历史数据对分区表来说是小菜一碟，直接 DROP TABLE 一个子分区，底层文件就没了，不会产生大量的 DELETE 和 VACUUM 负担。但直接 DROP 有一个运维上的隐患：如果生产环境有误操作或者业务需要回溯历史，DROP 掉的表数据就彻底找不回来了。

所以我自己管理生产分区表时，删除流程是标准的"先分离、后删除"：

-- 第一步：把子分区从分区表结构中分离，数据还在 ALTER TABLE orders DETACH PARTITION orders_202301; -- 第二步：确认无误后，再独立删除 DROP TABLE orders_202301;

分离之后，orders_202301变成一张普通独立表，和orders父表不再有任何关系，但表里的数据是完整的。这给了你一个缓冲期：可以先确认数据确实不需要了，再 DROP。如果业务上要求保留更长时间，分离后也可以直接备份这张独立表，再决定要不要删。

批量清理多个旧分区时，可以写一个简单的 SQL 拼出来要执行的语句，比如按月生成 12 条ALTER TABLE orders DETACH PARTITION ...，在维护窗口里批量执行。要特别注意大分区 DETACH 过程中对父表的锁可能持续一段时间，因为要更新分区元数据，所以建议在业务低峰期操作。

3.3 把已有大表改造成分区表：一次尽量不锁长 SQL 的路线

这是个高频问题：业务跑了大半年，orders单表已经有几千万数据，这时候要改成分区表，怎么办？

最稳妥、对生产影响最小的方式是"新建分区表 + 分批次迁移 + 切换"三步走：

1. 先建好分区结构的orders_new表，包含所有需要的子分区和索引。
2. 把原表的旧数据按时间范围分批插入到新表：INSERT INTO orders_new SELECT * FROM orders WHERE created_at >= ... AND created_at < ...。
3. 全部迁移完后，在维护窗口把原表改名备份，orders_new改名为orders，业务切换过来。

这个方案对在线业务的影响最小，但有两个问题要处理好。一是数据迁移期间源表的数据如果还在继续写入，需要注意增量数据的一致性问题。简单做法是切换前先停止写入，或者用触发器记录增量。二是分批迁移时要注意批次大小，单批百万行级别的 INSERT 在事务内执行没问题，但批次不要太大，避免事务日志膨胀和锁占用过长。

如果数据量不大，也可以直接用ALTER TABLE orders ATTACH PARTITION的方式把原表变成某个分区。比如想把orders_old变成orders表的一个历史分区，可以先在父表上建好对应边界的分区，然后：

ALTER TABLE orders ATTACH PARTITION orders_old FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

ATTACH 时会校验表里的数据是否符合边界约束，如果存在不匹配的行会操作失败，所以执行前要把数据理干净。这种方式适合"已经有一张独立表想并进分区表"的场景，不适合在线大表的整体改造。

3.4 自动创建新分区：定时任务而不是触发器

每个月手工建一次分区其实也没多麻烦，但总有忙起来忘记的时候，而分区一漏，新数据直接落父表，查询数据缺失，排查又要花半天。生产环境我强烈建议把分区创建做成自动化。

比较常见的做法是用pg_cron扩展定时执行建分区脚本。比如每月 1 日凌晨 1 点跑一次，创建未来两个月的分区，留一个月余量，防止边界意外：

DO $$ DECLARE start_date date; end_date date; BEGIN FOR i IN 1..2 LOOP start_date := date_trunc('month', now() + (i || ' month')::interval)::date; end_date := date_trunc('month', now() + ((i+1) || ' month')::interval)::date; EXECUTE format( 'CREATE TABLE IF NOT EXISTS orders_%s PARTITION OF orders FOR VALUES FROM (%L) TO (%L)', to_char(start_date, 'YYYYMM'), start_date, end_date ); END LOOP; END $$;

CREATE TABLE IF NOT EXISTS能保证即使任务重复执行也不会报错。如果表数量特别多，建议把分区清单记录在一张管理表里，任务跑完后比对比对，有缺失的补建，生成告警。

用触发器自动建分区也能实现，但我个人不建议。在建分区的瞬间会有 DDL 锁，如果恰好是写入高峰时段，一个触发器 DDL 可能连锁影响多个插入请求。定时任务的窗口可控，更安全。

4. 分区表查询优化的几个关键细节

分区表建好、维护跑起来，只是第一步。真正让分区表发挥价值的是查询能不能准确命中目标分区。"分区裁剪"是分区表性能的核心机制：优化器根据查询 WHERE 条件中的分区键值，跳过无关分区，只扫描必要的分区。

4.1 分区裁剪什么时候会失效

分区裁剪失效是线上问题里最常见的坑。拿订单表举例：

SELECT * FROM orders WHERE created_at >= '2025-02-01' AND created_at < '2025-03-01';

这条 SQL 在created_at带索引的情况下，优化器应该只扫描orders_202502这一个分区。但如果你把条件写成函数套在分区键上：

SELECT * FROM orders WHERE to_char(created_at, 'YYYY-MM') = '2025-02';

优化器就没办法做裁剪了，因为to_char(created_at, 'YYYY-MM')对每一行都要计算，无法直接推断出要扫描哪个分区。这个查询会扫描全部已有分区再合并，性能直接退化到全表扫描级别。

分区键上不要套函数，这个原则在分区表里比普通表更严格。普通表里函数包索引列会导致索引失效，分区表里同样会导致剪裁失效，但后果更严重——所有分区都会被打开。另一个常见的裁剪失效原因是隐式类型转换，比如分区键是timestamptz，但查询传入的是timestamp字符串且没有显式转换，优化器可能无法匹配边界。建议在查询里统一用明确的时间类型参数。

4.2 分区键更新的代价：一次跨分区移动的实际开销

如果业务里有把created_at从 1 月改成 2 月这样的操作，得明确知道 PG 会怎么做。分区键的 UPDATE，如果目标行的新值属于另一个分区，PostgreSQL 会执行"删除旧行 + 插入新行"两个操作。在这期间，原来的旧行位置会产生一个死元组，新位置插入一行，如果这个操作频繁发生，表膨胀会非常明显，而且旧分区的 VACUUM 压力会变大。

我的经验是，分区键应该是"写入后基本不变"的字段。如果业务确实有修改时间的需求，建议设计层面就避免，或者修改后允许数据落在一个"修正分区"里再做后续整理。这里有个小技巧：如果 UPDATE 语句的条件里不包含分区键，PG 会在执行 UPDATE 前先检查新值和旧值是否跨分区，如果没跨分区就原地更新，代价就和一个普通 UPDATE 一样。反过来，如果条件里包含分区键，就能提前锁定要更新的分区，执行效率更高。所以 UPDATE 条件里尽量带上分区键，这既能提升裁剪效率，也能避免不必要的跨分区检查。

4.3 索引设计的取舍：分区本地索引和全局唯一性

分区表上创建的索引默认是分区本地索引，也就是说每个分区上各有一个独立的索引，覆盖该分区自己的数据。这样做的优点是索引体积小、重建快、VACUUM 独立，缺点是如果在父表上建 UNIQUE 约束，分区键必须包含在唯一约束里，否则无法保证全局唯一。

需要全局唯一性但不用分区键做唯一键时，就得换思路。比如orders表要求id全局唯一，但分区键是created_at，直接UNIQUE (id)建不了。一个折中办法是在父表上建普通索引，然后靠业务或应用层保证唯一性；另一种办法是把分区键纳入唯一约束：UNIQUE (id, created_at)，这样每个分区内的id唯一，但不同分区里是可以出现相同id的——如果你的业务能保证id生成器全局唯一，那约束形同虚设，但语义上还是不太严谨。

还有一点关于索引维护的实操经验：如果某个分区已经不再写入（比如历史月份已经在归档冷分区），可以适当评估是否还需要保留那些只为热查询服务的索引。比如冷分区上的二级索引，平时查询根本不会命中，索引却占着磁盘空间且影响插入性能。对历史分区可以 DROP 掉部分冗余索引，只保留主键，能省不少空间，后续如果需要再重建索引，也只是针对单个分区的事，非常快。

5. 几个管理工具和监控建议

分区表的管理不止是建表和删表，监控维度和普通表有区别。如果不针对分区做监控，很容易出现"父表数据量看着不大、但某些分区爆炸"的情况。

5.1 查询各分区数据量的标准 SQL

日常巡检时，可以直接查pg_partition_tree看分区树结构，也可以查pg_class.reltuples看估算行数。更实用的脚本是按分区统计实际数据量和索引大小，比如：

SELECT inhrelid::regclass AS partition_name, COALESCE(c.reltuples, 0) AS estimated_rows, pg_size_pretty(pg_total_relation_size(inhrelid)) AS total_size FROM pg_inherits i JOIN pg_class c ON c.oid = i.inhrelid WHERE inhparent = 'orders'::regclass ORDER BY estimated_rows DESC;

注意reltuples是 VACUUM 和 ANALYZE 之后才更新的估算值，不代表精确行数。需要精确行数时，用COUNT(*)但成本较高，可以放在维护窗口执行。更推荐的方式是对分区表定期执行ANALYZE，让统计信息保持新鲜，这样优化器做分区裁剪时能更准确估算每个分区的行数。PG 14 之后支持了分区级统计信息，对裁剪后的估算质量有很大提升，但前提是 ANALYZE 跑得勤快。

5.2 数据分布的合理性检查

分区表的收益依赖数据是否均衡落到各个分区。HASH 分区天然均衡，但 RANGE 分区如果业务有明显热点，比如某一天订单量暴增，那个分区的数据量会远大于其他同期分区，这个分区后续的写入、查询、VACUUM 都会成为瓶颈。

日常巡检可以关注两个指标：各分区数据量的方差，以及热分区和冷分区的占比。如果发现某个分区的体量是平均值的 5 倍以上，要评估是否需要把该分区再做二级拆分，或者考虑把该段的查询直接指向更细粒度的独立表。RANGE 分区一个常见的热点来源是分区边界设置不贴合业务节奏——比如业务活动常发生在每天特定几小时，但按小时建分区，导致某些小时分区过大。这种场景可以按"周"甚至"季节"调整边界，让分区大小更均衡。

5.3 WAL 和备份策略上的注意点

分区表的每个子分区在物理上都是一张独立的表文件，所以 WAL 日志和基础备份层面和普通表差异不大，但恢复时有一点要特别注意：如果只恢复了部分分区或其中某个分区文件损坏了，父表和分区之间的元数据关系可能不一致。备份策略上建议对整个 PostgreSQL 集群做一致的物理备份，而不是单表导出。逻辑备份（如pg_dump）对分区表会默认导出整个分区树结构，恢复时也会带上子分区，但大分区表的逻辑备份速度通常很慢，生产环境大规模分区表更适合用物理备份方案。

日常运维里，我为分区表单独配置了一个备份巡检任务，不只检查备份是否成功，还检查备份里分区计数和源库是否一致。有过一次备份集里少了一个新分区的情况，原因是备份任务执行期间正好在建分区，备份快照和元数据不完全一致，后来把备份时间和分区创建时间错开，问题就再没出现。

6. 实践总结和踩坑清单

分区表用久了回头看，真正决定成败的往往不是建表那一步，而是后续的运维习惯和数据模型设计。这里把我觉得最值得记住的经验整理成清单。

分区键选择的第一原则是"稳定且可裁剪"。写入后不会变、查询条件里频繁出现、本身有业务语义，满足这三个条件的字段才是好分区键。单纯为了分区而刻意选一个业务用不上的字段，通常会带来更多麻烦。

边界管理的核心是"宁宽勿窄、预留余量"。生产环境的新分区至少要提前创建一个月，避免月底忘了建分区导致数据落父表。分区边界用统一的起止时间，不要留缺口。如果业务增长可能超预期，可以多预留几个未来分区用CREATE TABLE IF NOT EXISTS批量建好，多建不碍事，少建才要命。

清理策略上坚持"分离再删除"原则，DETACH 步骤虽然多一条命令，但它在误操作和回溯场景下能救命。同时建议在 DETACH 之后的独立表上做一个标记，比如改名加_archived_202501后缀，防止后续清理任务误删。

查询优化上，分区键永远不要套函数，UPDATE 条件尽量包含分区键，冷分区索引定期瘦身。这些都是常规经验，但实际生产里栽过跟头的案例我见过不止一个。

监控上，行数估算依赖 ANALYZE 的频度，新增分区后记得安排一次 ANALYZE，尤其对热分区。定期跑一条分区数据量分布统计 SQL，能提前发现分区体量失衡和边界异常。

最后再分享一个实际操作中的体会：分区表一旦上线，就是一张需要持续运维的表，它把"数据量大的问题"转换成了"分区管理的问题"，后者虽然比前者好对付，但也需要制度和工具的支撑。建表时花半小时把自动建分区、定期清理、监控统计这三件事设计好，后面可以省下无数个排查数据的深夜。反过来，如果只建了分区表却没有任何运维配套，往后的坑可能比不分区的单表还多。