技术小册

主题

分区表与扩展：把大表拆成可管理的小块 ​

一句话：分区是把一张逻辑表拆成多张物理子表，用分区键把查询和维护限定在一个子集；代价是约束变严、计划变复杂。

适用 PostgreSQL 16/17。分区语法从 PostgreSQL 10 起为声明式，HASH 分区、默认分区从 11 起支持。

为什么需要 ​

单表到几亿行后，问题不是「能不能存」，而是：

  • 按时间删历史数据，DELETE 会产生大量死元组，Vacuum 追不上。
  • 每次查询都要扫全部索引，索引越来越大。
  • 单表 Vacuum、备份、DDL 都变成整表操作。

按时间分区后，删除历史数据变成 DROP 一个子表；查询带时间条件时，优化器只扫相关分区；每个分区可以独立 Vacuum、独立建索引。

核心机制 ​

声明式分区 ​

父表只定义结构，不存数据；子表通过 PARTITION OF 绑定分区键范围。写入时，父表按分区键路由到对应子表。

三种策略：

  • RANGE：按范围，最常用的是时间。
  • LIST：按离散值，如地区、状态。
  • HASH：按哈希取模，用于均匀打散、避免热点。

多级分区可以组合，例如先按时间 RANGE，再按 HASH 打散。但每多一级，分区数量和计划复杂度都上升。

分区裁剪 ​

裁剪（partition pruning）发生在两个阶段：

  • 计划时：WHERE created_at >= '2026-02-01' AND created_at < '2026-03-01' 能直接排除其他月份。
  • 执行时：参数化查询、PreparedStatement 的具体参数在初始化计划时才知道，运行期再裁剪。

相关参数：enable_partition_pruning=on（默认）、constraint_exclusion=partition（默认）。注意 constraint_exclusion 是另一套基于 CHECK 约束的排除机制，不要和分区裁剪混为一谈。

裁剪生效的前提是条件直接作用在分区键上。WHERE date_trunc('month', created_at) = ... 这种包了函数的条件，通常裁不掉。

约束限制 ​

这是分区最容易踩坑的地方：

  • 主键/唯一约束必须包含分区键。因为唯一性只能在单个分区内保证，跨分区的全局唯一无法用普通唯一索引实现。
  • 没有全局唯一索引。需要全局唯一时，要么把分区键加入唯一键，要么用外部发号/去重。
  • ON CONFLICT 依赖唯一约束；唯一约束不含分区键时，不能按原语义做跨分区 upsert。
  • 外键：PostgreSQL 12+ 支持从分区表引用其他表，也支持其他表引用分区表；更早版本有限制。
  • CHECK 约束：子表可以有自己的约束；ATTACH PARTITION 时如果已有等价的 CHECK，可以避免全表校验扫描。

索引与维护 ​

父表上建的索引会自动在每个分区上创建对应索引（PostgreSQL 11+）。大表上直接 CREATE INDEX 会锁住整个分区表；更稳的做法是：

sql
-- 先在父表建一个 invalid 索引，再逐个分区建索引并 attach
CREATE INDEX ON ONLY orders (user_id);
CREATE INDEX CONCURRENTLY ON orders_2026_01 (user_id);
ALTER INDEX orders_user_id_idx ATTACH PARTITION orders_2026_01_user_id_idx;

DETACH PARTITION ... CONCURRENTLY（PostgreSQL 14+）可以在不长时间阻塞 DML 的情况下摘除分区，适合在线归档。它不能在事务块里执行，且最终 detach 仍要短暂拿锁。

最小上手 ​

sql
-- PostgreSQL 16/17
CREATE TABLE orders (
  id         bigint GENERATED ALWAYS AS IDENTITY,
  user_id    bigint NOT NULL,
  status     text   NOT NULL,
  created_at timestamptz NOT NULL,
  amount     numeric(12,2) NOT NULL,
  PRIMARY KEY (id, created_at)          -- 唯一键必须包含分区键
) PARTITION BY RANGE (created_at);

CREATE TABLE orders_2026_01 PARTITION OF orders
  FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

CREATE TABLE orders_2026_02 PARTITION OF orders
  FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

CREATE TABLE orders_default PARTITION OF orders DEFAULT;

INSERT INTO orders (user_id, status, created_at, amount) VALUES
  (42, 'paid', '2026-01-15 10:00+08', 99.00),
  (42, 'paid', '2026-02-15 10:00+08', 129.00);

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, amount
FROM orders
WHERE created_at >= '2026-02-01' AND created_at < '2026-03-01';

期望计划里只有一个 Append 分支指向 orders_2026_02，其他分区不出现。如果出现所有分区，检查条件是否直接作用在 created_at 上。

HASH 与 LIST：

sql
CREATE TABLE events (id bigint, tenant_id bigint NOT NULL)
  PARTITION BY HASH (tenant_id);

CREATE TABLE events_p0 PARTITION OF events
  FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE events_p1 PARTITION OF events
  FOR VALUES WITH (MODULUS 4, REMAINDER 1);

CREATE TABLE tasks (id bigint, region text NOT NULL)
  PARTITION BY LIST (region);

CREATE TABLE tasks_cn PARTITION OF tasks FOR VALUES IN ('cn');
CREATE TABLE tasks_us PARTITION OF tasks FOR VALUES IN ('us');

生产动作 ​

  • 提前建分区：用 pg_partman 或定时任务提前创建未来 N 个月的分区；等到写入时才建，会走默认分区，后续 attach 要处理数据。
  • 默认分区的坑：默认分区里一旦有数据，新增分区 ATTACH 时可能失败或需要全表校验。做法是先 DETACH 默认分区，建新分区，再把默认分区挂回。
  • 归档与删除：DETACH PARTITION ... CONCURRENTLY 后 DROP TABLE，比大批量 DELETE 快，也不产生死元组。
  • 分区数量：控制在几千以内；分区太多会让计划时间、锁管理、连接开销上升。按天分区一年 365 个通常可接受，按小时分区要谨慎。
  • 统计信息：每个分区是独立表，ANALYZE 要覆盖到；父表的统计信息不能代替子表。
  • Vacuum：autovacuum 按分区独立调度；大分区可以单独设置 autovacuum_vacuum_scale_factor=0.01。
  • 排障路径：现象「查询扫了所有分区」→ EXPLAIN 看 Append 分支数 → 条件是否直接作用在分区键、是否被函数包裹、是否使用了运行期参数 → 必要时 EXPLAIN (ANALYZE) 确认运行期裁剪。

边界 ​

  • 分区不是分库分表：所有分区仍在同一个实例、同一组磁盘上；单机容量和写入上限没有变。
  • 分区键选错会反噬：按高基数列（如 user_id）做 RANGE，会产生大量小分区；按时间分区但查询不带时间条件，裁剪不生效。
  • 唯一性受限：不能既按时间分区，又要求 id 全局唯一而不把 created_at 放进唯一键。
  • 跨分区查询仍有开销：Append 要逐个分区执行；分区太多时，计划和执行都变慢。
  • 小表不要分区：几百万行以内，普通索引和 Vacuum 足够；分区增加管理成本。
  • 扩展不是内核：Citus、TimescaleDB 需要单独安装和运维；升级 PostgreSQL 大版本时要确认扩展兼容性。

扩展生态 ​

扩展解决什么代价
Citus分布式分片、分布列、参考表、协同定位 JOIN多节点运维，跨分片查询和事务有额外开销
TimescaleDB时序 Hypertable、自动 chunk、压缩、保留策略面向时序负载，通用业务表不适合
PostGIS地理空间类型和索引索引和函数学习成本
postgres_fdw把远端 PostgreSQL/其他库当外部表查询跨网络延迟，下推能力有限

Citus 的分片是真正的多节点水平扩展；分区表是单机内的管理手段。两者解决的不是同一个问题。

讲清楚 ​

分区表是把一张大表按分区键拆成多个物理子表。它最大的价值不是「查询更快」，而是把维护动作变小：删历史数据变成删分区，Vacuum 和索引按分区独立进行，查询带分区键时只扫相关子表。代价是约束变严——主键和唯一键必须包含分区键，跨分区唯一性要靠别的机制；分区键选错或查询不带分区键，裁剪不生效，反而多了一层 Append。它仍然是单机方案，真正的水平扩展要看 Citus 这类分布式扩展。上线前最该确认的是：分区键是否匹配主要查询、未来分区是否提前创建、默认分区里有没有会阻塞 attach 的数据。

把一门技术讲透到既懂也会用

技术小册 · 持续更新