PostgreSQL 表分区（partitioning）：让 10 亿行表也能秒查

单张 PG 表超过几亿行后：

索引体积膨胀，新插入慢
VACUUM / 索引重建动辄几小时
删旧数据扫全表慢

分区把一张大表按某列（通常是时间）分成多个物理子表，PG 透明 routing
查询到对应分区。下面是声明式分区（PG 10+）的完整流程。

1. 按月分区一张事件表

-- 主表（不能直接插数据，只是定义结构 + 分区策略）
CREATE TABLE events (
    id BIGSERIAL,
    user_id BIGINT NOT NULL,
    type TEXT NOT NULL,
    payload JSONB,
    occurred_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (id, occurred_at)   -- partition key 必须在 PK 里
) PARTITION BY RANGE (occurred_at);

-- 索引：每个分区自动继承
CREATE INDEX events_user_id_idx ON events (user_id);
CREATE INDEX events_type_idx ON events (type);

2. 创建实际分区

CREATE TABLE events_2026_05 PARTITION OF events
  FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');
CREATE TABLE events_2026_06 PARTITION OF events
  FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');
CREATE TABLE events_2026_07 PARTITION OF events
  FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');

INSERT 进 events 时 PG 自动 routing：

INSERT INTO events (user_id, type, occurred_at)
  VALUES (42, 'login', '2026-06-15 10:00');
-- 实际写入 events_2026_06

3. 查询：partition pruning

EXPLAIN SELECT count(*) FROM events
WHERE occurred_at >= '2026-06-01' AND occurred_at < '2026-07-01';

-- Aggregate
--   ->  Seq Scan on events_2026_06
-- 只扫一个分区！其它分区直接 skip

如果 WHERE 没限定 partition key，PG 会扫所有分区，性能可能反而比单表差。
所有查询都要带 partition key 条件。

4. 自动建未来分区

每月手动建分区容易忘。用 pg_partman
扩展：

CREATE EXTENSION pg_partman;

SELECT partman.create_parent(
    p_parent_table => 'public.events',
    p_control => 'occurred_at',
    p_type => 'range',
    p_interval => '1 month',
    p_premake => 6   -- 提前 6 个月建分区
);

加个定时任务定期跑维护：

SELECT partman.run_maintenance();

自动建未来分区 + 自动删过期分区（如果配了 retention）。

5. 删历史数据：秒杀

传统方案：DELETE FROM events WHERE occurred_at < '2024-01-01'
— 几亿行 DELETE，VACUUM 几小时。

分区方案：

DROP TABLE events_2024_01;
-- 或：
ALTER TABLE events DETACH PARTITION events_2024_01;
-- DETACH 让分区脱离主表但保留物理；可以备份后再 DROP

DDL 操作，毫秒级完成 + 释放磁盘空间。

6. 分区策略选择

策略	适合
RANGE	按时间 / 数值范围（最常用）
LIST	按枚举值（如 country='CN' 一个分区）
HASH	按 hash 模 N，均匀分布；适合 user_id 这种高基数

-- LIST
CREATE TABLE orders (...) PARTITION BY LIST (country);
CREATE TABLE orders_cn PARTITION OF orders FOR VALUES IN ('CN');
CREATE TABLE orders_us PARTITION OF orders FOR VALUES IN ('US');
CREATE TABLE orders_other PARTITION OF orders DEFAULT;

-- HASH
CREATE TABLE users (...) PARTITION BY HASH (id);
CREATE TABLE users_p0 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE users_p1 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE users_p2 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE users_p3 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 3);

7. 索引在分区表里

-- 在主表上建索引 = 在所有分区上建对应索引
CREATE INDEX events_user_id_idx ON events (user_id);

-- 新分区自动继承
CREATE TABLE events_2026_08 PARTITION OF events
  FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');
\d events_2026_08   -- 看到 user_id 索引自动存在

PG 12 之后这是默认行为；PG 11 需要手动给每个分区单独建。

8. UNIQUE 约束的限制

-- ❌ 主表 UNIQUE 必须包含 partition key
ALTER TABLE events ADD UNIQUE (user_id);   -- ERROR

-- ✅
ALTER TABLE events ADD UNIQUE (user_id, occurred_at);

跨分区的全局唯一性 PG 原生不支持。如果业务要"id 全表唯一"，
要么把 id 加 partition key 一起当唯一，要么用 UUID（碰撞概率天文）。

9. INSERT 性能

分区表 INSERT 比单表稍慢（PG 要算 routing），但写大表反而更快
（小分区 + 小索引 = 写少）。10 亿行规模分区表能比单表快 5-10 倍写入。

10. ATTACH / DETACH 分区

把已经存在的表挂为新分区：

CREATE TABLE events_2025_archived (... 同 events 结构 ...);
-- 数据 COPY 进去...

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

ATTACH 时 PG 会扫描确认数据都满足分区条件（耗时）。生产建议先
ALTER TABLE ... ADD CONSTRAINT events_2025_check CHECK (occurred_at >= '2025-01-01' AND occurred_at < '2025-02-01') 让 PG
跳过扫描。

11. 跨分区 query 优化

-- 这条会扫所有分区
SELECT * FROM events WHERE user_id = 42;

-- 加 partition key 限定能 prune
SELECT * FROM events
WHERE user_id = 42 AND occurred_at >= '2026-01-01';

设计分区时考虑常见查询的 WHERE 条件。

12. 监控

-- 各分区大小
SELECT relname, pg_size_pretty(pg_relation_size(oid)) AS size
FROM pg_class
WHERE relname LIKE 'events_%'
ORDER BY pg_relation_size(oid) DESC;

-- 各分区行数（统计估算，快）
SELECT relname, reltuples::bigint AS rows
FROM pg_class
WHERE relname LIKE 'events_%';

踩过的坑

分区数过多（> 1000）→ planner 慢，每次查询都要遍历所有分区元数据。
保持 < 1000 分区，或合并老分区。
没配 partman maintenance / 没自动建未来分区 → INSERT 找不到分区报错。
应用 down 直到手动建。
跨分区 UNIQUE 不可能 → 业务设计阶段就要明确"全表唯一" vs
"分区内唯一"。
老 PG 9.x 用继承（INHERITS）实现 partition，PG 10+ 的声明式
partition 完全不同 + 更好用。不要混淆。