PHP小码农

Appearance

声明式分区概述 ​

分区（Partitioning）是把一张逻辑上完整的大表拆成多个物理上独立的小表的技术。应用仍然只看到父表一个名字，而 PostgreSQL 在内部按分区键（partition key）把每一行路由到对应的分区。它的最大价值不在于「查询更快」，而在于批量装载与批量删除可以退化成元数据操作——删掉一个月的数据从「删几千万行」变成「DROP 一张表」，这个差别往往是数量级的。

本节先讲清分区的收益与代价，再梳理 PostgreSQL 分区能力的演进史，重点讲透声明式分区（Declarative Partitioning）里「父表 + 分区 + 分区键」这三个概念，最后说明它相对表继承（Table Inheritance）的本质差异与硬性限制。

前置知识

阅读本节前，建议先了解：表继承概述

基础概念 ​

分区解决什么问题 ​

一张表随时间无限增长后，会同时遇到三类麻烦：

麻烦具体表现
索引过大B-tree 索引有几十 GB，无法全部缓存，深层查找变成随机 I/O
批量删除昂贵DELETE FROM t WHERE created_at < '2025-01-01' 要逐行标记删除，还要 VACUUM 回收
冷热数据混存上周热数据和三年前冷数据挤在同一批数据页里，缓存命中率被拖低

分区的思路是：既然绝大多数查询只关心最近一小段时间的数据，那就按时间切开，让热数据集中在少数分区、让过期数据整体消失。

官方文档给出的四条收益可以归纳为：

收益原理
查询性能提升分区替代了索引的上层结构，热分区的索引更容易常驻内存
全分区扫描更快访问某分区大部分数据时，顺序扫描优于跨全表的随机索引访问
批量装载与删除近似瞬时DROP TABLE / DETACH PARTITION 是元数据操作，且不产生 VACUUM 负担
冷数据可迁移到廉价存储按分区指定不同表空间（tablespace）

一条经验法则：表的大小超过服务器物理内存时，分区才真正划算。数据量不大时引入分区，只会增加规划期开销与维护复杂度。

父表、分区与分区键 ​

声明式分区由三个角色组成：

text
                    ┌───────────────────────────┐
                    │  orders（partitioned table）│  ← 父表：无自己的存储
                    │  PARTITION BY RANGE (...)  │
                    └─────────────┬─────────────┘
                                  │ 插入时按分区键路由
        ┌─────────────────────────┼─────────────────────────┐
        ▼                         ▼                         ▼
  orders_2026_01            orders_2026_02            orders_2026_03
  [2026-01-01, 02-01)       [2026-02-01, 03-01)       [2026-03-01, 04-01)
     分区（partition）          分区（partition）          分区（partition）
角色英文说明
父表partitioned table自身不含任何数据，只是「虚拟表」，负责路由与统一入口
分区partition普通表，真正持有数据；有各自的索引、约束、默认值、表空间
分区键partition key决定「这一行进哪个分区」的一列或多列（也可以是表达式）
分区边界partition bounds每个分区声明的取值范围，互不重叠

几个必须记住的事实：

事实说明
父表不存数据对父表 SELECT 会展开为对子分区的 Append/MergeAppend
分区是普通表可以直接 INSERT INTO orders_2026_01（需满足该分区的边界约束）
更新分区键会移动行新值不属于原分区时，PostgreSQL 自动把行搬到正确分区
普通表与分区表不能互转不能把普通表「变成」分区表，但可以 ATTACH / DETACH
修改父表结构会级联ALTER TABLE orders ADD COLUMN 会自动加到所有分区

RANGE / LIST / HASH 三种分区方式 ​

方式关键字划分依据典型用途支持裁剪
范围分区RANGE分区键落在某个左闭右开区间按时间、按 ID 段是（收益最大）
列表分区LIST分区键等于列举出的某个值按地区、状态、租户是
哈希分区HASH分区键哈希后的模数余数均匀打散、无自然分区键是（但仅等值）

范围分区是最常用的形式，因为时间几乎总是最重要的过滤维度：

sql
CREATE TABLE orders (
  id         bigint       NOT NULL,
  user_id    bigint       NOT NULL,
  amount     numeric(12,2) NOT NULL,
  created_at date         NOT NULL
) PARTITION BY RANGE (created_at);

声明式分区 vs 表继承 ​

在 PostgreSQL 10 之前，人们用表继承（INHERITS）手工模拟分区：父表加 CHECK 约束，靠 constraint_exclusion 来跳过无关子表。声明式分区把它变成了一等公民：

维度表继承模拟声明式分区
边界定义手写 CHECK 约束FOR VALUES ... 声明式
边界冲突检测无，靠人工保证内核保证互不重叠
插入路由无，必须手动插对应子表（或写触发器）自动路由
查询裁剪需要 constraint_exclusion = on内置，且支持运行期裁剪
分区数量可扩展性差（规划期逐个遍历）好（PG 12+ 有显著优化）
索引与约束传播需手工维护自动传播到所有分区
多级分区可用，但管理复杂原生支持

结论很明确：新项目一律用声明式分区，表继承只保留给「子表结构不同、需要共享列」的真正继承场景。

分区的硬性限制 ​

sql
-- 错误！唯一约束必须包含全部分区键列
CREATE TABLE orders_bad (
  id         bigint NOT NULL,
  created_at date   NOT NULL,
  PRIMARY KEY (id)
) PARTITION BY RANGE (created_at);
text
ERROR:  unique constraint on partitioned table must include all partitioning columns
DETAIL:  PRIMARY KEY constraint on table "orders_bad" lacks column "created_at" which is part of the partition key.

这是分区表最需要提前规划的一条。它带来的连锁影响是：

影响说明
主键必须含分区键按 created_at 分区，则主键只能写成 (id, created_at)
外键引用分区表受限引用方也需要持有被引用方的唯一键组合
「全局唯一」不再有保证不同分区可以有相同 id
需要全局 ID 时用全局序列、uuidv7()（PostgreSQL 18+）或雪花 ID

语法与用法 ​

text
-- 创建分区表（父表）
CREATE TABLE table_name ( ... )
    PARTITION BY { RANGE | LIST | HASH } ( column_name [, ... ] )

-- 其中分区键也可以写成表达式
CREATE TABLE table_name ( ... )
    PARTITION BY RANGE ( (expression) )

-- 创建分区（三种 form）
CREATE TABLE partition_name PARTITION OF parent
    FOR VALUES { IN ( ... ) | FROM ( ... ) TO ( ... ) | WITH ( MODULUS n, REMAINDER m ) | DEFAULT }

-- 把已有表挂成分区
ALTER TABLE parent ATTACH PARTITION partition_name FOR VALUES partition_bound_spec

-- 摘下一个分区（变成独立普通表）
ALTER TABLE parent DETACH PARTITION partition_name [ CONCURRENTLY | FINALIZE ]

最简可运行示例：

sql
-- 父表：按 created_at 做月度范围分区
CREATE TABLE orders (
  id         bigint        NOT NULL,
  user_id    bigint        NOT NULL,
  amount     numeric(12,2) NOT NULL,
  created_at date          NOT NULL
) PARTITION BY RANGE (created_at);
text
CREATE TABLE
sql
-- 两个分区，边界左闭右开
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');
text
CREATE TABLE
CREATE TABLE

插入时无需指定分区，内核自动路由：

sql
INSERT INTO orders VALUES (1, 1001, 99.00, '2026-01-15');
INSERT INTO orders VALUES (2, 1002, 12.50, '2026-02-20');
text
INSERT 0 1
INSERT 0 1
sql
-- 直接查父表，实际只扫了对应分区
SELECT * FROM orders WHERE created_at = '2026-01-15';
text
 id | user_id | amount | created_at
----+---------+--------+------------
  1 |    1001 |  99.00 | 2026-01-15
(1 row)

系统目录里怎么看 ​

sql
-- 父表：relkind = 'p'，partitioned table
SELECT relname, relkind, relispartition
FROM pg_class
WHERE relname IN ('orders', 'orders_2026_01', 'orders_2026_02')
ORDER BY relname;
text
    relname     | relkind | relispartition
----------------+---------+----------------
 orders         | p       | f
 orders_2026_01 | r       | t
 orders_2026_02 | r       | t
(3 rows)
字段含义
relkind = 'p'这是一张分区表（父表）
relkind = 'r'普通表；分区在目录里就是普通表
relispartition = t该表是一个分区（有父表）

分区键本身存在 pg_partitioned_table 里：

sql
SELECT
  c.relname                     AS parent,
  p.partstrat                   AS strategy,
  pg_get_partkeydef(p.partrelid) AS key_def
FROM pg_partitioned_table p
JOIN pg_class c ON c.oid = p.partrelid;
text
 parent | strategy |     key_def
--------+----------+------------------
 orders | r        | RANGE (created_at)
(1 row)

partstrat 的取值是 r（range）、l（list）、h（hash）三种之一，对应 PARTITION BY 的三种方式。

父子关系表 pg_inherits ​

分区的父子关系不靠什么特殊字段，而是复用继承机制的 pg_inherits：

sql
SELECT
  child.relname  AS partition,
  parent.relname AS parent_table
FROM pg_inherits
JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
JOIN pg_class child  ON pg_inherits.inhrelid  = child.oid
ORDER BY child.relname;
text
   partition    | parent_table
----------------+--------------
 orders_2026_01 | orders
 orders_2026_02 | orders
(2 rows)

查看分区边界与层级 ​

PostgreSQL 12 起提供了三个专门的函数，是排查分区结构最顺手的工具：

sql
-- 递归列出整棵分区树：relid / parentrelid / isleaf / level
SELECT * FROM pg_partition_tree('orders');
text
       relid        | parentrelid  | isleaf | level
--------------------+--------------+--------+-------
 orders             |              | f      |     0
 orders_2026_01     | orders       | t      |     1
 orders_2026_02     | orders       | t      |     1
(3 rows)
sql
-- 从某个分区向上找所有祖先（最近的在前）
SELECT relid, relid::regclass AS ancestor FROM pg_partition_ancestors('orders_2026_01');
text
   relid   |    ancestor
-----------+---------------
 16387     | orders_2026_01
 16380     | orders
(2 rows)

pg_partition_root() 则用于从任意一个分区反查最顶层父表，在多级分区里非常有用。

详细说明 ​

分区表是「虚拟」的，不占存储 ​

CREATE TABLE ... PARTITION BY 之后，父表在磁盘上只有一个空的关系文件（0 字节），所有数据都在分区里：

sql
SELECT pg_size_pretty(pg_relation_size('orders'))         AS parent_size,
       pg_size_pretty(pg_relation_size('orders_2026_01')) AS part_size;
text
 parent_size | part_size
-------------+-----------
 0 bytes     | 8192 bytes
(1 row)

这解释了两个常见困惑：

  • 给父表建索引不会立即产生一个集中式索引——它只是在每个分区上生成对应的「子索引」。
  • 父表的 reltuples 不会精确反映数据量——统计信息需要 ANALYZE 逐分区收集。

三种分区方式的路由规则 ​

理解路由规则，才能明白为什么插入会报错、为什么某行「跑错」了分区。

方式路由判断未命中时
RANGElower <= key < upper报错（除非有 DEFAULT 分区）
LISTkey IN (...)报错（除非有 DEFAULT 分区）
HASHhash(key) % modulus = remainder永不未命中（模数完备时）
sql
-- 范围：上界是排他的，2026-02-01 属于第二个分区
INSERT INTO orders VALUES (10, 1, 1.00, '2026-02-01');
SELECT tableoid::regclass AS which_partition FROM orders WHERE id = 10;
text
 which_partition
-----------------
 orders_2026_02
(1 row)

更新分区键会「搬行」 ​

如果 UPDATE 改动了分区键，导致新值不再属于原分区，PostgreSQL 会在同一个语句内删除旧行、插入新行：

sql
-- 把 1 月的数据改到 3 月：没有 3 月分区时会报错
UPDATE orders SET created_at = '2026-03-05' WHERE id = 1;
text
ERROR:  no partition of relation "orders" found for row
DETAIL:  Partition key of the failing row contains (created_at) = (2026-03-05).

这条错误的信息量很大：它没有说「越界」，而是说「这一行的分区键没能路由到任何分区」。补充一个 3 月分区即可成功：

sql
CREATE TABLE orders_2026_03 PARTITION OF orders
  FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

UPDATE orders SET created_at = '2026-03-05' WHERE id = 1;
text
CREATE TABLE
UPDATE 1

分区键的选择 ​

分区键的选择基本决定了分区方案能否长期维护。常见策略：

分区键类型适用场景注意
时间列（date/timestamptz）日志、订单、事件表最常用，配合轮转删除极佳
业务枚举（region、status）地域、租户、分类值域必须稳定，枚举增删代价低
主键哈希无自然分区维度，只需打散无裁剪收益，靠并行提升吞吐
复合键时间 + 租户逐级裁剪，两级都参与裁剪

一个关键判断标准：分区键必须高频率出现在查询的 WHERE 里，否则裁剪失效，分区只剩维护成本。

sql
-- 好：查询几乎总带时间范围，可裁剪
SELECT count(*) FROM orders WHERE created_at >= '2026-01-01';

-- 差：查询只按 user_id，无法裁剪，要扫所有分区
SELECT count(*) FROM orders WHERE user_id = 1001;

声明式分区不继承「主键全局唯一」 ​

这是最容易踩的坑。分区表上的唯一约束是每个分区各自唯一：

sql
-- 主键包含分区键，语法合法
CREATE TABLE events (
  id         bigint NOT NULL,
  created_at date   NOT NULL,
  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);
text
CREATE TABLE
sql
CREATE TABLE events_2026_01 PARTITION OF events
  FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE events_2026_02 PARTITION OF events
  FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

-- 同一个 id 出现在两个分区里，完全合法
INSERT INTO events VALUES (1, '2026-01-10');
INSERT INTO events VALUES (1, '2026-02-10');
text
CREATE TABLE
CREATE TABLE
INSERT 0 1
INSERT 0 1

需要全局唯一时，替代方案有：

sql
-- 方案一：全局序列 / identity 列
id bigint GENERATED BY DEFAULT AS IDENTITY

-- 方案二：时间有序 UUID（PostgreSQL 18+），避免随机 UUID 的写放大
id uuid NOT NULL DEFAULT uuidv7()

何时不该分区 ​

分区不是越多越好，下列情况通常不该引入分区：

反例原因
表只有几百万行、能装进内存分区只增加规划开销
查询模式里根本没有分区键无裁剪收益，纯粹增加复杂度
分区键会频繁变更每次 UPDATE 都可能搬行，写放大严重
分区数量会失控增长（如按天 × 多年）规划时间与元数据开销上升
需要跨分区的全局唯一约束分区表无法直接表达

分区数量是隐性成本

每个分区都是一次规划期的候选对象。数百个分区通常没问题，但上千个分区时规划时间会变得可观。PostgreSQL 12 起对此做了大幅优化，但仍应把分区数量控制在合理范围，必要时用多级分区「先粗后细」。

声明式分区与逻辑复制、触发器 ​

分区表支持逻辑复制与行级 BEFORE 触发器，但有版本门槛：

能力最低版本
在分区表上定义行级 BEFORE 触发器PostgreSQL 13
将分区表作为整体进行逻辑复制（发布父表）PostgreSQL 13
分区表的 MERGE 支持（含跨分区移动行）PostgreSQL 15
sql
-- PostgreSQL 13+：发布父表，其所有分区自动被发布
CREATE PUBLICATION pub_orders FOR TABLE orders;

注意：BEFORE 触发器不能改变行要去的分区（不能把行重定向到别的分区）。

实战示例 ​

场景：为一张订单表设计按月的范围分区，验证路由、裁剪与目录信息。

第 1 步：建父表与三个分区

sql
DROP TABLE IF EXISTS orders CASCADE;

CREATE TABLE orders (
  id         bigint        NOT NULL,
  user_id    bigint        NOT NULL,
  amount     numeric(12,2) NOT NULL,
  created_at date          NOT NULL
) 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');

第 2 步：灌入跨越三个分区的数据

sql
INSERT INTO orders (id, user_id, amount, created_at)
SELECT
  g,
  (g % 500) + 1,
  (random() * 1000)::numeric(12,2),
  DATE '2026-01-01' + (g % 90)
FROM generate_series(1, 300000) AS g;
text
INSERT 0 300000

第 3 步：确认各行确实落到了不同分区

sql
SELECT tableoid::regclass AS partition, count(*)
FROM orders
GROUP BY 1
ORDER BY 1;
text
   partition    | count
----------------+-------
 orders_2026_01 | 99999
 orders_2026_02 | 90333
 orders_2026_03 |109668
(3 rows)

tableoid 是每个表都有的系统列，表示该行所在的物理表——在分区表上，它直接告诉你行落在哪个分区。

第 4 步：观察裁剪效果

sql
EXPLAIN (COSTS OFF)
SELECT count(*) FROM orders WHERE created_at >= '2026-02-01' AND created_at < '2026-03-01';
text
               Aggregate
               ->  Append
                     Subplans Removed: 2
                     ->  Seq Scan on orders_2026_02
                           Filter: ((created_at >= '2026-02-01'::date) AND (created_at < '2026-03-01'::date))
(4 rows)

Subplans Removed: 2 就是裁剪的结果：三个分区里只保留了 1 个。

第 5 步：查看分区树的层级信息

sql
SELECT
  relid::regclass AS node,
  parentrelid::regclass AS parent,
  isleaf,
  level
FROM pg_partition_tree('orders');
text
      node       |    parent    | isleaf | level
-----------------+--------------+--------+-------
 orders          |              | f      |     0
 orders_2026_01  | orders       | t      |     1
 orders_2026_02  | orders       | t      |     1
 orders_2026_03  | orders       | t      |     1
(4 rows)

第 6 步：对比「删一月数据」的两种方式

sql
-- 方式 A：DETACH + DROP，近乎瞬时
ALTER TABLE orders DETACH PARTITION orders_2026_01;
DROP TABLE orders_2026_01;

-- 方式 B（不推荐，仅作对比）：逐行删除
-- DELETE FROM orders WHERE created_at >= '2026-01-01' AND created_at < '2026-02-01';

DETACH PARTITION 在 PostgreSQL 12 起只需 SHARE UPDATE EXCLUSIVE 锁级别的能力，比方式 B 的巨大 WAL 与 VACUUM 开销低得多。

注意事项 ​

  • 分区表的唯一约束必须包含全部分区键列。 这是内核限制，不是配置问题。
sql
-- 错误！unique constraint on partitioned table must include all partitioning columns
CREATE TABLE t (id bigint PRIMARY KEY, d date) PARTITION BY RANGE (d);
  • 没有匹配分区时插入会直接失败。 生产环境一定要有预建分区或 DEFAULT 分区兜底。
sql
-- 错误！no partition of relation "orders" found for row
INSERT INTO orders VALUES (999, 1, 1.00, '2027-01-01');
  • 父表与普通表之间不能互相转换。 已经是大表时，只能「建分区表 → 迁数据」或「逐个 ATTACH」。
sql
-- 错误！"legacy" is not a partitioned table
ALTER TABLE legacy PARTITION BY RANGE (created_at);
  • 不同分区的边界不得重叠。 重叠会在创建分区时报错。
sql
-- 错误！partition "orders_2026_02" would overlap partition "orders_2026_03"
CREATE TABLE orders_dup PARTITION OF orders
  FOR VALUES FROM ('2026-01-15') TO ('2026-03-01');
  • UPDATE 分区键可能触发跨分区搬行。 频繁改动分区键列是明确的性能反模式。
sql
-- 不推荐：把时间列当作可随意修改的普通字段
UPDATE orders SET created_at = now() WHERE id = 1;
  • 父表上的 ALTER TABLE 会级联到所有分区。 分区数量多时，ADD COLUMN 这类操作的持锁时间会随分区数增长。
sql
ALTER TABLE orders ADD COLUMN remark text;   -- 会对每个分区加锁

  • 不要在分区表上依赖「父表索引」的物理存在。 父表的索引是「模板」，实际索引在各分区上。

  • 分区键用表达式时要留意可裁剪性。 表达式必须能在规划期求值到常量，且不会被函数「包住」分区键列。

sql
-- 不推荐：函数包住分区键，破坏裁剪
SELECT * FROM orders WHERE date_trunc('month', created_at) = '2026-02-01';

最佳实践 ​

  • 时间序数据一律按月或按天做 RANGE 分区，并预留足够的分区。 提前预建未来 3~12 个月的分区，避免插入报错。
sql
CREATE TABLE orders_2026_04 PARTITION OF orders
  FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');
  • 务必设置 DEFAULT 分区作为兜底，但要监控它。 它能让越界数据不丢，但也会阻止后续新分区的创建（需先移出越界行）。
sql
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

  • 分区键必须出现在高频查询的 WHERE 中。 否则分区只带来成本。

  • 先规划主键，再规划分区键。 分区键一旦确定，主键就被迫包含它，事后改动代价极高。

sql
PRIMARY KEY (id, created_at)   -- 分区键 created_at 必须在内
  • 用 enable_partition_pruning 保持默认 on，并通过 EXPLAIN 的 Subplans Removed 验证裁剪真的发生。
sql
SHOW enable_partition_pruning;
EXPLAIN (COSTS OFF) SELECT * FROM orders WHERE created_at = '2026-02-10';
  • 大批量删除优先用 DETACH + DROP，而不是 DELETE。
sql
ALTER TABLE orders DETACH PARTITION orders_2026_01;
DROP TABLE orders_2026_01;
  • 定期用 pg_partition_tree() 盘点分区结构，确认没有遗漏的区间。
sql
SELECT relid::regclass, isleaf, level FROM pg_partition_tree('orders');
  • 分区数量失控时考虑多级分区。 例如先按年 RANGE、再按月 RANGE，把单层扇出压下来。

下一节

继续学习：范围分区（RANGE）

参考链接 ​