PostgreSQL 分区表实战

一、大表的痛,分区来治

业务跑了两三年,那张 events / logs / orders 表悄悄涨到了上亿行。然后一切开始变慢:

• 查询慢:就算有索引,BTree 在亿级表上的随机 IO 也得几秒;范围扫描更是灾难。
• 索引大:索引本身几百 GB,加载进 shared_buffers 的比例越来越低,缓存命中率掉头向下。
• VACUUM 慢:PostgreSQL 的 MVCC 靠多版本实现,DELETE/UPDATE 留下死元组,VACUUM 要扫整张表才能回收。表越大,窗口越长,膨胀(bloat)越严重。
• 删旧数据慢:DELETE FROM events WHERE created_at < '2024-01-01' 看着无害,实际上要扫盘、写 WAL、产生海量死元组,再触发一轮漫长的 VACUUM。删一个月的数据可能跑几个小时,还锁得你心慌。

分区表(partitioning)的思路很朴素:别把一张大表当一个整体扛,拆成若干物理上的小表(分区),逻辑上还是一张表。好处直接命中上面四个痛点:

• 查询带分区键时,只扫相关的几个小分区(分区裁剪),IO 量指数级下降。
• 每个分区有独立索引,热分区的索引能整个住进内存。
• VACUUM 按分区进行,单位小、跑得快,膨胀可控。
• 删旧数据直接 DROP TABLE 旧分区,瞬间完成,零死元组——这是分区表最香的一点。

一句话:分区不是为了让单条查询变快多少,而是让"大表的日常运维"重新变得可承受。

二、三种分区类型,选对是第一步

PostgreSQL 10 开始原生支持声明式分区(declarative partitioning),三种类型:

RANGE 分区:按范围,最常用

按某个字段的范围划分,典型场景是时间。按月、按周、按天分区,日志/事件/订单类业务的首选。

CREATE TABLE events (
    id          BIGSERIAL,
    created_at  TIMESTAMPTZ NOT NULL,
    user_id     BIGINT,
    payload     JSONB,
    PRIMARY KEY (id, created_at)   -- 分区键必须在主键里,见后文
) PARTITION BY RANGE (created_at);

LIST 分区:按枚举值

按某个字段的离散取值划分,典型场景是地区、租户、类别。多租户 SaaS 按 tenant_id 分区很常见。

CREATE TABLE users (
    id        BIGSERIAL,
    region    TEXT NOT NULL,
    name      TEXT,
    PRIMARY KEY (id, region)
) PARTITION BY LIST (region);

CREATE TABLE users_cn PARTITION OF users FOR VALUES IN ('CN');
CREATE TABLE users_us PARTITION OF users FOR VALUES IN ('US', 'CA');
CREATE TABLE users_other PARTITION OF users DEFAULT;  -- 兜底

HASH 分区:均匀散列

没有明确的时间或分类维度,但表就是大,想拆开均衡负载和存储,用 HASH。PG 按分区键的哈希值取模落到固定分区。

CREATE TABLE sessions (
    id            UUID PRIMARY KEY,        -- 分区键是 id,天然在主键里
    user_id       BIGINT,
    last_seen_at  TIMESTAMPTZ
) PARTITION BY HASH (id);

CREATE TABLE sessions_0 PARTITION OF sessions FOR VALUES WITH (MODULUS 8, REMAINDER 0);
CREATE TABLE sessions_1 PARTITION OF sessions FOR VALUES WITH (MODULUS 8, REMAINDER 1);
-- ... 一直到 sessions_7

怎么选? 有时间维度优先 RANGE(90% 的场景就是它);有稳定的分类维度且分布相对均匀,LIST;实在没有维度又必须拆,HASH 兜底。别为了分区而分区——HASH 分区对单条点查帮助有限,主要价值是分散写入和 VACUUM 压力。

三、创建:父表 + 子分区

声明式分区的核心是两张"脸":对应用是一张逻辑父表,对存储是若干物理子分区。父表只存元数据,数据全在子分区。

-- 1. 父表,声明分区策略
CREATE TABLE events (
    id          BIGSERIAL,
    created_at  TIMESTAMPTZ NOT NULL,
    user_id     BIGINT,
    payload     JSONB,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- 2. 创建子分区,绑定范围
CREATE TABLE events_2024_01 PARTITION OF events
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE events_2024_02 PARTITION OF events
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- 3. 子分区还可以再分区(多级分区)
CREATE TABLE events_2024_03 PARTITION OF events
    FOR VALUES FROM ('2024-03-01') TO ('2024-04-01')
    PARTITION BY RANGE (user_id);

范围是左闭右开[from, to),相邻分区的边界严丝合缝不会漏数据。插入时 PG 根据分区键自动路由到对应子分区;找不到匹配分区会直接报错(no partition of relation "events" found for row)——除非你建了 DEFAULT 分区兜底。

索引在父表上建,会自动传播到所有子分区(含未来新建的):

CREATE INDEX idx_events_user ON events (user_id);
CREATE INDEX idx_events_payload ON events USING GIN (payload);

四、分区裁剪:分区的灵魂

分区不是白拆的——只要 SQL 的 WHERE 条件能推导出分区键的取值,查询规划器就会只扫描相关分区,跳过其他所有分区。这叫分区裁剪(partition pruning)。

EXPLAIN SELECT * FROM events
WHERE created_at >= '2024-02-10' AND created_at < '2024-02-15';

好的执行计划里,你应该只看到 events_2024_02 一个分区被扫描,其他月份的分区根本不出现。裁剪有两种:

• 静态裁剪:条件是常量,规划阶段就裁好;
• 运行时裁剪:条件是参数(如 PREPARE 或 JOIN 上的分区键),执行阶段才能裁。PG 13+ 对 JOIN 上的分区键裁剪支持大幅改善。

踩坑提醒:跨分区查询不会自动"只扫一个分区"。如果你写 SELECT * FROM events WHERE user_id = 123,没带 created_at,PG 会扫所有分区——分区表反而可能比单表更慢(多了分区遍历的开销)。所以分区表上的查询,养成习惯带上分区键。

EXPLAIN 是你唯一的朋友。上线任何关键查询前,看一眼执行计划确认裁剪生效,比任何调优经验都靠谱。

五、维护:自动建新分区,直接删旧分区

分区表上线后,运维上有两件必须自动化的事。

1. 持续创建未来分区。 别等插入报错才发现下个月的分区还没建。两种做法:

• pg_partman:社区最成熟的分区管理扩展,自动维护分区、支持范围/列表分区、可配置提前创建多少个未来分区。生产推荐。
• 定时任务:cron / pg_cron 跑一个存储过程,每天 CREATE TABLE ... PARTITION OF ... 把未来 N 天的分区建好。

-- pg_cron 每天凌晨创建未来 7 天的分区
SELECT cron.schedule('create_events_partitions', '0 3 * * *', $$SELECT partman.create_parent(...)$$);

2. 干净利落删旧数据。 这是分区表最大的运维红利:

-- 旧做法:DELETE 几千万行 + 漫长 VACUUM
DELETE FROM events WHERE created_at < '2024-01-01';

-- 分区表做法:DROP 一个分区,毫秒级,零死元组
DROP TABLE events_2024_01;
-- 或者:从父表 detach,数据保留待观察,确认无误再 DROP
ALTER TABLE events DETACH PARTITION events_2024_01;

DETACH 是更稳的姿势:先脱离父表(查询不再扫它),数据还在,确认没问题再 DROP。并发和锁开销也可控,PG 14+ 支持 ALTER TABLE ... DETACH PARTITION ... CONCURRENTLY。

六、何时该分区:大表 + 明确维度

不是所有表都该分区。我见过给几十万行的配置表硬上 HASH 分区的,查询反而变慢——分区遍历的固定开销在小表上完全盖过收益。

该分区的信号(建议同时满足两条以上):

• 行数到千万级或更高,存储几十 GB 起步;
• 有明确的查询维度(时间、租户、地区),且大部分查询天然带这个维度;
• 有规律的"删旧数据"需求(日志、事件、埋点);
• VACUUM 窗口已经成为运维负担,膨胀严重。

不该分区的场景:

• 小表(几十万行以内)——索引和常规优化就够;
• 没有任何稳定查询维度,所有查询都按各种字段乱来——分了也裁不掉,等于白分;
• 写入压力极大但每个分区还得均匀承受——分区不是写性能银弹,HASH 分区对单条插入还有额外开销。

经验值:亿级表 + 时间维度 = 几乎无脑上 RANGE 分区;千万级以下,先优化索引、查询、连接池配置,别急着分区。

七、几个一踩一个准的坑

• 分区键必须包含在主键/唯一约束里。PG 要求唯一性约束必须包含所有分区键列,否则没法跨分区保证唯一。这就是为什么前面主键写成 PRIMARY KEY (id, created_at) 而不是 PRIMARY KEY (id)。需要全局唯一 id?用 UUID 或应用层生成,别指望单列唯一约束。
• 跨分区查询不裁剪。前面强调过,再说一次:查询不带分区键 = 扫所有分区。这条不注意,分区表会比单表更慢。
• DEFAULT 分区是双刃剑。兜底很方便,但一旦数据落进 DEFAULT,后续按范围查询时 PG 有时无法精准裁剪,而且想把 DEFAULT 拆分迁移出来很麻烦。尽量靠"提前创建未来分区"而非 DEFAULT 兜底。
• FOREIGN KEY 指向分区表有限制,PG 12 之前不支持,现在虽然支持但跨分区约束有性能开销,设计时注意。
• 分区数别太多。上千个分区会让规划时间显著变长(PG 14 后改善很多,但仍有成本)。按月分能覆盖几年的就别按天分。

八、分区 vs 分库分表:别混淆

最后澄清一个常见误区。分区(partitioning)是单库内的拆分,所有分区共用一个 PostgreSQL 实例、一份 WAL、一套事务逻辑。它解决的是"单表太大"的问题,简单、透明、应用无感知。

分库分表(sharding) 是把数据散到多个独立的数据库实例上,每个分片都是自包含的。它解决的是"单库扛不住"的问题——写入吞吐、连接数、存储容量超出了单机上限。代价很大:跨分片事务、分布式查询、全局唯一 ID、应用路由……复杂度暴涨,是最后手段。

实践中:先垂直拆(按业务分库)→ 再水平分区(PG 分区表)→ 实在不行才水平分库分表。绝大多数业务停在第二步就够了,真正需要分库分表的场景远比网上文章暗示的少。

小结

• 分区表的本质是"逻辑一张表,物理多张表",核心收益是查询只扫相关分区 + DROP 旧分区替代 DELETE + 小分区 VACUUM 更快。
• 三种类型:RANGE(时间,最常用)、LIST(枚举/租户)、HASH(无维度兜底)。
• PARTITION BY 建父表,CREATE TABLE ... PARTITION OF 建子分区;索引建在父表自动下发。
• 分区裁剪是灵魂,查询必须带分区键才能裁,EXPLAIN 是唯一的验证手段。
• 运维两件事自动化:持续建未来分区(pg_partman / cron)、DROP 或 DETACH 旧分区删数据。
• 分区键必须进主键/唯一约束;跨分区查询不裁剪;分区数别过千。
• 适用门槛:大表(千万/亿级)+ 明确查询维度;小表别分区。
• 分区 ≠ 分库分表,前者单库内拆分(简单),后者多库(复杂,最后手段)。

金句:分区表不是为了跑得更快,而是为了让大表跑得更久。 把亿级数据的日常查询和运维,重新拉回可承受的尺度——这才是分区的真正价值。