PostgreSQLNotes

第 13 章：分区表

zjc 于 2026-01-13 发布

这是《PostgreSQL 零基础实战指南》的独立章节版。本章从概念、实操和生产排查三个视角展开，代码块保留了原书可直接运行的版本。 分区表把逻辑大表拆成多个物理子表，目标是提升可管理性、剪枝性能和维护效率，不是所有大表都适合分区。

13.1 分区类型

CREATE TABLE events (
    id bigint,
    event_date date NOT NULL
) PARTITION BY RANGE (event_date);
类型 适合
RANGE 时间、序号区间
LIST 租户、地区、枚举
HASH 均匀打散
多级分区 时间 + 租户等

13.2 创建分区

CREATE TABLE events_2026_08 PARTITION OF events
FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');

默认分区：

CREATE TABLE events_default PARTITION OF events DEFAULT;

默认分区会承接无匹配数据，可能掩盖分区配置错误，使用要谨慎。

13.3 自动分区

PostgreSQL 原生不会自动创建所有未来分区。常见做法：

  1. 定时任务提前创建；
  2. 应用按模板创建；
  3. 使用扩展；
  4. 运维平台管理。

示例存储过程：

CREATE OR REPLACE FUNCTION create_event_partition(day date)
RETURNS void AS $$
BEGIN
    EXECUTE format(
        'CREATE TABLE IF NOT EXISTS %I PARTITION OF events FOR VALUES FROM (%L) TO (%L)',
        'events_' || to_char(day, 'YYYY_MM'),
        date_trunc('month', day),
        date_trunc('month', day) + interval '1 month'
    );
END;
$$ LANGUAGE plpgsql;

13.4 分区剪枝

EXPLAIN
SELECT count(*)
FROM events
WHERE event_date >= '2026-08-01'
  AND event_date < '2026-09-01';

运行时参数可能导致运行时剪枝，执行计划仍需查看实际分区数量。分区键上的函数包装会削弱剪枝。

13.5 索引

父表创建分区索引：

CREATE INDEX ON events(event_date, user_id);

会在各分区创建对应索引。也可以对单个分区维护索引。

13.6 主键与唯一约束

分区表唯一约束必须包含分区键：

ALTER TABLE events
ADD CONSTRAINT pk_events PRIMARY KEY (event_date, id);

如果业务唯一键不含时间，需要全局唯一方案，如 UUID、序列或应用约束。

13.7 分区维护

删除旧分区：

DROP TABLE events_2023_08;

摘除分区：

ALTER TABLE events DETACH PARTITION events_2023_08;

比大规模 DELETE 更高效，也更容易归档。

13.8 分区数量

过多分区会带来：

  1. 计划时间增加；
  2. 锁管理成本；
  3. 内存和元数据压力；
  4. autovacuum 调度压力。

建议按数据量、保留期和查询窗口选择粒度。常见日志表按月或按天，不要默认按小时。

13.9 什么时候分区

适合：

  1. 数据量大；
  2. 查询常按时间或租户过滤；
  3. 需要快速归档删除；
  4. 单表索引过大；
  5. 冷热存储管理。

不适合：

  1. 小表；
  2. 查询无分区条件；
  3. 分区后仍访问所有分区；
  4. 唯一约束难以调整。

本章小结

分区表的核心收益是分区剪枝和生命周期管理。设计时先确定分区键和粒度，保证高频查询能剪枝，并提前准备自动创建和归档流程。

思考题

  1. RANGE、LIST、HASH 如何选择？
  2. 分区表唯一约束有什么限制？
  3. 为什么要提前创建分区？
  4. 分区是否总能提升查询性能？
  5. DETACH 与 DELETE 有什么差异？