PostgreSQL分区分表

当单表突破千万行，查询变慢、索引膨胀、归档困难会集中爆发。PostgreSQL 声明式分区是应对这类增长最直接的手段：应用代码几乎不改，数据库帮你把一张逻辑表拆成多个物理分区，查询时只扫描相关分区。

本文基于 PostgreSQL 14+，覆盖三种分区方式、分区键和粒度选择、EXPLAIN 实测、已有表迁移为分区表，以及自动建分区和归档等生产运维操作。

1. 分区、分表、分库分表：三十秒区分

维度	数据库分区	分表	分库分表
谁来拆	数据库引擎	应用或中间件	应用或中间件
数据在哪	同一个库，多个 partition	同一个库，多张独立表	多个库，多张表
应用感知	基本不感知，面对父表	必须知道路由到哪张表	必须知道去哪个库哪张表
解决的核心问题	单表太大	业务隔离或生命周期差异	单库实例整体扛不住

一句话压缩：

• 分区：告诉数据库怎么切，数据库帮你切。
• 分表：自己建很多表，代码要知道去哪张。
• 分库分表：自己管很多库和表，代码或中间件要知道去哪库哪表。

大多数系统一辈子只需要普通表加好索引。只有当单个数据库实例整体成为瓶颈，分库分表才值得讨论。

2. 三种分区方式

2.1 RANGE 分区：按范围切

最常见的方式。数据按连续值的范围落入不同分区，典型场景是按时间分区。

建表：

CREATE TABLE orders (
    order_id    BIGINT GENERATED ALWAYS AS IDENTITY,
    user_id     BIGINT NOT NULL,
    amount      NUMERIC(12, 2) NOT NULL,
    status      TEXT NOT NULL DEFAULT 'pending',
    region      TEXT NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (order_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_2026_03 PARTITION OF orders
    FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

FROM 是闭区间，TO 是开区间。'2026-02-01' 的数据落入 orders_2026_02。

查询与写入：

-- 带分区键条件，PostgreSQL 只扫描 2026_02 分区
SELECT * FROM orders
WHERE created_at >= '2026-02-01'
  AND created_at <  '2026-03-01';

-- 插入自动路由到正确分区
INSERT INTO orders (user_id, amount, status, region, created_at)
VALUES (1001, 299.00, 'paid', 'cn-east', '2026-02-15 10:30:00+08');

适合时间序列数据：日志、订单、任务记录、审计流水。查询几乎总带时间范围，且需要按时间归档。

2.2 LIST 分区：按枚举值切

数据按离散值落入不同分区。表结构和 RANGE 相同，只有分区方式和主键不同：

-- 仅展示差异部分，表字段同 2.1
CREATE TABLE orders (
    ...
    PRIMARY KEY (order_id, region)
) PARTITION BY LIST (region);

CREATE TABLE orders_cn PARTITION OF orders
    FOR VALUES IN ('cn-east', 'cn-south', 'cn-north');

CREATE TABLE orders_us PARTITION OF orders
    FOR VALUES IN ('us-east', 'us-west');

适合按地区、租户、业务线等有限枚举值隔离的场景。查询带 region 条件时只扫描对应分区。

2.3 HASH 分区：按哈希值均匀打散

数据按分区键的哈希值均匀分布到固定数量的分区：

-- 仅展示差异部分，表字段同 2.1
CREATE TABLE orders (
    ...
    PRIMARY KEY (order_id, user_id)
) PARTITION BY HASH (user_id);

CREATE TABLE orders_h0 PARTITION OF orders
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);
-- h1, h2, h3 同理，REMAINDER 分别为 1, 2, 3

适合没有天然范围或枚举边界但需要打散数据的场景。注意 MODULUS 一旦确定不能轻易改变，从 4 扩到 8 需要重建整个分区结构。

2.4 DEFAULT 分区

兜底所有不匹配任何已定义分区的数据（PostgreSQL 11+）：

CREATE TABLE orders_default PARTITION OF orders DEFAULT;

RANGE 场景下忘了建未来分区时数据会落入 DEFAULT 而不是报错。但 DEFAULT 不是保险——如果数据持续落入 DEFAULT，说明分区规划有问题，事后迁移比一开始建好分区麻烦得多。

3. 分区键与粒度选择

这是分区设计中最关键的两个决策，选错了分区等于白做。

3.1 分区键怎么选

分区键决定了数据怎么切、查询能不能裁剪分区。选择逻辑：

查询最常带什么 WHERE 条件？
  → 时间范围    → created_at，用 RANGE
  → 地区/租户   → region/tenant_id，用 LIST
  → 用户 ID     → user_id，用 HASH

数据按什么维度增长？
  → 按时间追加  → RANGE（最常见）
  → 按类别固定  → LIST

归档按什么维度？
  → 按月/季度删旧数据 → 分区键必须是时间

核心原则：分区键 = 查询最常用的过滤条件 ∩ 归档最自然的维度。如果查询条件和归档维度冲突，优先选查询条件——归档可以用其他方式解决，但每次查询都扫全量分区无法接受。

3.2 分区粒度怎么定

粒度决定每个分区的数据量。太粗（按年）失去分区意义，太细（按天）管理开销爆炸。

经验值，以 RANGE 按时间分区为例：

每月写入量	建议粒度	一年分区数
< 10 万行	按季度或半年	2-4 个
10 万 ~ 500 万行	按月	12 个
> 500 万行	按周或按天	52-365 个

判断标准：每个分区保持在 100 万 ~ 5000 万行之间。低于 100 万行说明粒度太细，管理开销大于收益；超过 5000 万行说明粒度太粗，分区内索引仍然膨胀。

4. Partition Pruning：分区为什么能快

4.1 原理

Partition pruning 是查询优化器的能力：当查询条件包含分区键时，直接排除无关分区，只扫描数据可能存在的分区。36 个月度分区查 1 个月，只访问 1 个分区的索引和数据文件。

4.2 EXPLAIN 对比：分区前 Vs 分区后

1000 万行 orders 表，查询 2026 年 2 月的订单。

分区前——全表扫描：

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

Seq Scan on orders_flat  (cost=0.00..284371.00 rows=831024 width=72)
                         (actual time=0.027..2834.109 rows=833219 loops=1)
  Filter: ((created_at >= '2026-02-01'::timestamptz)
       AND (created_at <  '2026-03-01'::timestamptz))
  Rows Removed by Filter: 9166781
  Buffers: shared hit=12043 read=138912
Planning Time: 0.125 ms
Execution Time: 2847.332 ms

扫描 1000 万行，过滤掉 916 万行，读 15 万数据页。2847 ms。

分区后——只扫描 1 个分区：

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

Append  (cost=0.42..18293.71 rows=833219 width=72)
        (actual time=0.031..274.856 rows=833219 loops=1)
  Subplans Removed: 11
  ->  Bitmap Heap Scan on orders_2026_02 orders_1
        (cost=0.42..18293.71 rows=833219 width=72)
        (actual time=0.029..261.443 rows=833219 loops=1)
        Recheck Cond: ((created_at >= '2026-02-01'::timestamptz)
                   AND (created_at <  '2026-03-01'::timestamptz))
        Heap Blocks: exact=11284
        Buffers: shared hit=11301
Planning Time: 0.342 ms
Execution Time: 287.651 ms

Subplans Removed: 11——11 个分区被裁剪，只扫描 orders_2026_02，读 1.1 万数据页。287 ms，是分区前的 1/10。

4.3 Pruning 失效的两个主要原因

查询不带分区键：

-- 分区键是 created_at，但查询只有 user_id → 扫描所有分区
SELECT * FROM orders WHERE user_id = 1001;

分区键被函数包裹：

-- 错误：date() 包裹后优化器无法推断范围
SELECT * FROM orders WHERE date(created_at) = '2026-02-15';

-- 正确：用范围条件替代
SELECT * FROM orders
WHERE created_at >= '2026-02-15' AND created_at < '2026-02-16';

5. 分区表的约束与限制

5.1 主键和唯一约束必须包含分区键

最常见的坑。PostgreSQL 要求分区表上的所有唯一约束（包括主键）必须包含分区键列：

-- 错误：主键不包含分区键 created_at
CREATE TABLE orders (
    order_id   BIGINT PRIMARY KEY,
    created_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);
-- ERROR: unique constraint on partitioned table must include all partitioning columns

-- 正确：主键包含分区键
CREATE TABLE orders (
    order_id   BIGINT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL,
    PRIMARY KEY (order_id, created_at)
) PARTITION BY RANGE (created_at);

原因：每个分区是独立物理表，PostgreSQL 无法跨分区检查唯一性。如果业务需要 order_id 全局唯一，需要在应用层保证（全局 sequence 或 UUID）。

5.2 外键限制

分区表可以引用其他表的外键，但其他表不能引用分区表的列作为外键。需要这种关系时，在应用层维护引用完整性，或把被引用的表设计为非分区表。

5.3 分区数量

没有硬性上限，但分区过多会让查询规划变慢、DDL 变重、文件描述符消耗增加。

• 几十到几百个：正常。
• 上千个：规划时间明显上升。
• 上万个：粒度太细，建议合并。

6. 生产运维

6.1 自动创建未来分区

PostgreSQL 分区表的硬规则：插入数据没有匹配分区且没有 DEFAULT 分区时，INSERT 直接报错。必须提前建好未来分区。

推荐方案：pg_partman + pg_cron

CREATE EXTENSION pg_partman;
CREATE EXTENSION pg_cron;

-- 注册分区表，提前创建未来 3 个月分区
SELECT partman.create_parent(
    p_parent_table := 'public.orders',
    p_control      := 'created_at',
    p_type         := 'range',
    p_interval     := '1 month',
    p_premake      := 3
);

-- 每天凌晨自动维护
SELECT cron.schedule(
    'partman_maintenance',
    '0 2 * * *',
    $$SELECT partman.run_maintenance()$$
);

pg_partman 在 AWS RDS PostgreSQL 和大多数托管数据库上可用。如果环境不支持 pg_partman，核心逻辑是：用动态 SQL 拼 CREATE TABLE ... PARTITION OF，配合 IF NOT EXISTS 检查 pg_class 防重复创建，可以放在 pg_cron 定时任务或应用启动时执行（用 pg_advisory_lock 防并发）。

6.2 归档与清理旧分区

分区表最大的运维优势：删一整个分区是 O(1)，不是百万行 DELETE。

-- 第一步：分离旧分区（PG14+ 不阻塞查询）
ALTER TABLE orders
    DETACH PARTITION orders_2025_01 CONCURRENTLY;

-- 第二步：处理分离后的独立表
DROP TABLE orders_2025_01;              -- 直接删除
-- 或
ALTER TABLE orders_2025_01 SET SCHEMA archive;  -- 移到归档 schema

DETACH PARTITION CONCURRENTLY 是 PostgreSQL 14 引入的关键特性。14 之前 DETACH 会加 ACCESS EXCLUSIVE 锁阻塞所有查询，14+ 只加短暂的 SHARE UPDATE EXCLUSIVE 锁。与 DELETE 对比：删 83 万行 DELETE 需要几分钟并产生大量 WAL 和 vacuum 压力，DROP TABLE 瞬间完成。

6.3 迁移：已有普通表转分区表

实际中很少从零设计分区表，绝大多数是表已经大了才想分区。PostgreSQL 不支持直接 ALTER TABLE ... PARTITION BY，需要手动迁移。

标准流程：

-- 1. 重命名旧表
ALTER TABLE orders RENAME TO orders_old;

-- 2. 创建同结构的分区父表
CREATE TABLE orders (
    order_id    BIGINT GENERATED ALWAYS AS IDENTITY,
    user_id     BIGINT NOT NULL,
    amount      NUMERIC(12, 2) NOT NULL,
    status      TEXT NOT NULL DEFAULT 'pending',
    region      TEXT NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (order_id, created_at)
) PARTITION BY RANGE (created_at);

-- 3. 把旧表作为第一个分区挂载
-- 旧表必须有匹配的 CHECK 约束，或者 PG 会扫描全表验证数据范围
ALTER TABLE orders_old ADD CONSTRAINT chk_range
    CHECK (created_at >= '2020-01-01' AND created_at < '2026-06-01');

ALTER TABLE orders
    ATTACH PARTITION orders_old
    FOR VALUES FROM ('2020-01-01') TO ('2026-06-01');

-- 4. 创建后续分区
CREATE TABLE orders_2026_06 PARTITION OF orders
    FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');

关键细节：

• ATTACH PARTITION 时如果旧表没有匹配的 CHECK 约束，PostgreSQL 会对全表做一次扫描验证数据是否都在声明范围内。大表上这个扫描可能很慢，提前加 CHECK 约束可以跳过扫描。
• 旧表的主键必须包含分区键。如果旧表主键是 (order_id)，需要先改成 (order_id, created_at) 才能 ATTACH。
• 挂载完成后，旧表变成分区表的一个子分区，后续新数据自动路由到对应的月度分区。

6.4 索引管理

在父表创建索引会自动应用到所有已有和未来分区：

CREATE INDEX idx_orders_user_id ON orders (user_id);
-- PostgreSQL 自动在每个分区创建对应的本地索引

核心优势：每个分区的索引只包含该分区数据，热分区索引足够小可以完全留在内存中。删除分区时（DROP TABLE）对应索引自动清理。

6.5 运维检查清单

-- 1. 查看所有分区及数据量
SELECT
    c.relname AS partition_name,
    pg_get_expr(c.relpartbound, c.oid) AS bound,
    pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size,
    pg_stat_get_live_tuples(c.oid) AS live_rows
FROM pg_inherits inh
JOIN pg_class c ON c.oid = inh.inhrelid
WHERE inh.inhparent = 'orders'::regclass
ORDER BY c.relname;

-- 2. DEFAULT 分区是否积压数据（应该为 0）
SELECT count(*) FROM orders_default;

-- 3. 确认 partition pruning 开启
SHOW enable_partition_pruning;  -- 应返回 on

7. 决策框架

7.1 什么时候该用分区

满足以下信号中两个或以上：

• 单表超过 1000 万行，且持续增长。
• 大多数查询天然带分区键条件。
• 历史数据删除是痛点：DELETE 引起锁等待、WAL 膨胀、vacuum 压力。
• 热数据集中在最近一段时间，但索引被全量历史拖大。
• 运维需要按时间快速归档。

7.2 什么时候不该用分区

• 表只有几百万行，索引优化就能解决。
• 主要慢查询不包含分区键。
• 业务没稳定，分区键可能很快变。
• 每个分区只有几千行，管理开销大于收益。

7.3 何时升级到分表或分库分表

分表信号：不同业务的 schema 或生命周期差异大，分区表的唯一约束限制无法满足业务模型。

分库分表信号：单库 CPU、IO、连接数、存储长期接近上限，已用尽索引优化、查询改写、分区、垂直扩容等手段。分库分表是最重方案，进入前先确认是否用尽了单库内所有手段。

8. 实战案例

AI 任务平台的 image_generation_tasks 表，按 created_at 做月度 RANGE 分区。数据按月持续增长，查询总带时间范围，归档按月 DETACH。主键 (task_id, created_at)，task_id 全局唯一由 sequence 保证。应用启动时用 advisory lock 创建未来 3 个月分区，每季度 DETACH PARTITION CONCURRENTLY 清理 12 个月前的旧分区。活跃调度数据拆到独立普通表，不与历史表混合。

参考资料

• PostgreSQL 官方文档：Table Partitioning
• PostgreSQL 官方文档：CREATE TABLE
• pg_partman：分区自动管理扩展
• pg_cron：数据库内定时任务
• PostgreSQL 14 Release Notes：DETACH PARTITION CONCURRENTLY