PHP小码农

Appearance

多级分区（子分区） ​

多级分区（Sub-partitioning，也叫复合分区 Composite Partitioning）把分区再分区：一级分区按某个维度切开后，每个一级分区内部继续用另一种分区策略切分，形成树形结构。典型结构是「先按时间 RANGE，再按 tenant_id HASH」或「先按 region LIST，再按 created_at RANGE」。

它的价值在于同时满足两个维度的切分需求：一级负责归档（按期 DROP 旧数据），二级负责进一步拆分（打散写入或裁剪到更小的子集）。代价是分区数量按乘法增长——4 个月 × 8 个哈希 = 32 个叶子分区，管理复杂度与规划开销同步上升。

前置知识

阅读本节前，建议先了解：声明式分区概述、范围分区（RANGE）、列表分区（LIST）、哈希分区（HASH）

基础概念 ​

多级分区在解决什么问题 ​

单一维度的分区往往不够用。考虑两种两难：

场景一：既要归档又要打散。 一张日志表需要按月 DROP 旧数据（必须 RANGE(时间)），但每个月的写入量太大，单分区写入竞争激烈，希望再打散。此时「RANGE 一级 + HASH 二级」就同时解决了归档与并发。

场景二：两个业务维度都要裁剪。 一张跨区域订单表，查询有时带 region，有时带 created_at。「LIST(region) 一级 + RANGE(created_at) 二级」能让两类查询分别在一、二级命中裁剪。

如果只有一个维度需要切分，多级分区就是过度设计——单级分区足够，且更简单。

树形结构：分区表也是分区表 ​

多级分区的本质是：一个分区本身可以是分区表。可以在 CREATE TABLE ... PARTITION OF 时再跟一个 PARTITION BY，让这个分区继续往下切。

text
                    events  (PARTITION BY RANGE (created_at))
                   /      |       \
        events_2026_01  events_2026_02  events_2026_03   ← 一级分区，各自也是分区表
         （PARTITION BY HASH (tenant_id)）
        /    |    |    \
   ..._p0  ..._p1 ..._p2 ..._p3                          ← 二级分区（叶子分区）

用 pg_partition_tree() 可以直观看到这棵树的形状：

text
                       name                       |  type   | level
--------------------------------------------------+---------+-------
 events                                           | p       |     0
 events_2026_01                                   | p       |     1
 events_2026_01_p0                                | r       |     2
 events_2026_01_p1                                | r       |     2
 events_2026_01_p2                                | r       |     2
 events_2026_01_p3                                | r       |     2
 events_2026_02                                   | p       |     1
 ...

type 列 p 表示该节点是分区表（还有下级），r 表示叶子分区（不再有下级）。两者都是真实存在的表，只是层级不同。

三对策略组合的对比 ​

一级与二级可以任选 RANGE / LIST / HASH，实践中常见以下几种组合：

组合典型场景一级裁剪二级裁剪分区数增长
RANGE → HASH时间归档 + 写入打散按时间按等值键月份数 × 哈希数
RANGE → LIST时间归档 + 按地区进一步隔离按时间按地区月份数 × 地区数
LIST → RANGE多租户隔离 + 租户内按期轮转按租户按时间租户数 × 月份数
HASH → RANGE先打散再按期分区按等值键按时间哈希数 × 月份数
LIST → HASH租户隔离 + 租户内打散按租户按等值键租户数 × 哈希数
RANGE → RANGE年 → 月两级时间分区按年按月年份数 × 月份数

选择组合的判断顺序是：先问「归档维度是什么」（决定一级），再问「其余维度里哪个查询最频繁」（决定二级）。

叶子分区才是数据真正落地的地方 ​

一个关键认知：只有最底层的叶子分区才存数据。一级分区（如果它还是分区表）本身不存任何行。

sql
-- 向父表插入数据，会一路路由到叶子分区
INSERT INTO events (event_id, tenant_id, created_at) VALUES (1, 42, '2026-01-15');

-- 查看数据落在哪个叶子分区
SELECT tableoid::regclass AS leaf FROM events WHERE event_id = 1;
text
      leaf
------------------
 events_2026_01_p2

这意味着：一级分区的行数统计永远为 0（在 pg_class.reltuples 里），只有叶子分区才有意义。

sql
-- 一级分区（分区表）没有自己的数据
SELECT relname, relkind, reltuples FROM pg_class WHERE relname = 'events_2026_01';
-- relkind = 'p'，插入的数据实际在叶子分区

-- 叶子分区才有数据
SELECT relname, relkind, reltuples FROM pg_class WHERE relname LIKE 'events_2026_01_p%';

逐级裁剪：每一级都要命中 ​

多级分区的裁剪是逐级进行的：规划器先按一级分区键裁剪出若干一级分区，再在每个保留下来的一个级分区里，按二级分区键继续裁剪。

要让查询享受完整的裁剪，条件必须同时命中每一级的分区键：

sql
-- 全裁剪：时间命中一级、tenant_id 命中二级
SELECT * FROM events WHERE created_at >= '2026-01-01' AND created_at < '2026-02-01' AND tenant_id = 42;

-- 只裁剪一级：只有时间条件，每个保留下来的月份会扫全部 4 个二级分区
SELECT * FROM events WHERE created_at >= '2026-01-01' AND created_at < '2026-02-01';

-- 完全无法裁剪：只有 tenant_id，每个月都要扫（除非规划器能证明某个二级分区不含该值）
SELECT * FROM events WHERE tenant_id = 42;

第三类查询值得特别说明：WHERE tenant_id = 42 配上 RANGE → HASH 结构时，每个一级分区下的 4 个二级分区都会被扫描，一共 4 × N个月 个子计划。如果这类查询是主力查询，说明一级选错了维度。

ATTACH/DETACH 在多级结构中的传播 ​

多级分区里，ATTACH / DETACH 的作用对象既可以是叶子分区，也可以是整棵子树：

操作效果
DETACH 一个叶子分区只摘下一个二级分区
DETACH 一个一级分区把整棵子树（一级 + 其全部二级）从父表摘下，子树内部结构保持
ATTACH 一个叶子分区需要其边界与一级分区兼容
ATTACH 一个一级分区表把整棵子树挂上去
sql
-- 把整个 2026_03 子树（含其 4 个二级分区）一次摘下
ALTER TABLE events DETACH PARTITION events_2026_03;

DETACH 之后的 events_2026_03 仍然是一个分区表（带 4 个二级分区），只是不再挂在 events 下。这带来一个便利：归档时可以直接 DETACH + DROP 一整棵子树，一次操作下线一个月的数据。

分区数量爆炸：必须正视的成本 ​

多级分区最容易被忽视的代价是分区数量的乘法增长：

一级分区数二级分区数叶子分区总数
12（月）448
24（月）8192
36（月）16576
60（月）321920

分区数量直接影响：

  1. 规划时间。查询规划器要为每个可能被扫描的叶子分区生成子计划，576 个分区时规划时间可达数十毫秒，远超执行时间。
  2. 元数据膨胀。每个分区在 pg_class、pg_inherits、pg_attribute 等系统表里都有多条记录，影响系统目录缓存与 DDL 速度。
  3. 维护成本。预建分区、备份、VACUUM 的脚本复杂度随叶子数线性上升。
  4. 锁申请。某些操作需要在父表及各级加锁，层级越深、分区越多，锁管理开销越大。

经验阈值：叶子分区总数控制在几百个以内，超过 1000 要非常谨慎。

sql
-- 统计当前分区表的叶子分区总数
SELECT count(*) AS leaf_partitions
FROM pg_class c
WHERE c.relispartition AND c.relkind = 'r'
  AND pg_partition_root(c.oid) = 'events'::regclass;

声明式分区与多级分区的版本基础 ​

多级分区本身不需要额外特性——它是 CREATE TABLE ... PARTITION OF ... PARTITION BY ... 的自然组合。但配套能力有版本差异：

版本与多级分区相关的能力
PostgreSQL 10声明式分区（RANGE/LIST），多级分区从此时可用
PostgreSQL 11HASH 分区加入，多级组合更灵活；支持 DEFAULT 分区
PostgreSQL 12pg_partition_tree() / pg_partition_ancestors() / pg_partition_root()，让树形结构可查询
PostgreSQL 14DETACH PARTITION CONCURRENTLY，减少摘分区时的锁影响
PostgreSQL 18DETACH PARTITION ... FINALIZE

其中 pg_partition_tree() 系列函数对多级分区尤其重要——单级分区时手写 pg_inherits 查询尚可，多级时几乎必须借助这些函数。

语法与用法 ​

先建父表，声明一级策略 ​

一级策略通常是归档维度：

text
CREATE TABLE table_name (
    column_name data_type [ column_constraint [ ... ] ]
    [, ... ]
)
PARTITION BY RANGE ( created_at );

建一级分区时声明二级策略 ​

一级分区的 DDL 在普通的 CREATE TABLE ... PARTITION OF 后面再加一个 PARTITION BY：

text
CREATE TABLE partition_name
    PARTITION OF parent_table
    FOR VALUES partition_bound_spec
    PARTITION BY HASH ( tenant_id );

建二级叶子分区 ​

叶子分区只带边界，不再跟 PARTITION BY：

text
CREATE TABLE leaf_name
    PARTITION OF level1_partition
    FOR VALUES WITH ( MODULUS m, REMAINDER r );

完整示例 ​

下面构建「RANGE(created_at) 一级 + HASH(tenant_id) 二级」的两级分区表：

sql
-- 1. 父表：按 created_at 范围分区
CREATE TABLE events (
    event_id   bigint      NOT NULL,
    tenant_id  int         NOT NULL,
    created_at timestamptz NOT NULL,
    payload    jsonb,
    PRIMARY KEY (created_at, tenant_id, event_id)
) PARTITION BY RANGE (created_at);

-- 2. 一级分区：2026 年 1 月，声明继续按 tenant_id 哈希分区
CREATE TABLE events_2026_01 PARTITION OF events
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01')
    PARTITION BY HASH (tenant_id);

-- 3. 二级分区：4 个叶子
CREATE TABLE events_2026_01_p0 PARTITION OF events_2026_01 FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE events_2026_01_p1 PARTITION OF events_2026_01 FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE events_2026_01_p2 PARTITION OF events_2026_01 FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE events_2026_01_p3 PARTITION OF events_2026_01 FOR VALUES WITH (MODULUS 4, REMAINDER 3);

查看树形结构 ​

pg_partition_tree() 返回整棵子树，含层级：

sql
SELECT relid::regclass AS name, relkind, level
FROM pg_partition_tree('events')
ORDER BY level, name;
text
                      name                       | relkind | level
-------------------------------------------------+---------+-------
 events                                          | p       |     0
 events_2026_01                                  | p       |     1
 events_2026_01_p0                               | r       |     2
 events_2026_01_p1                               | r       |     2
 events_2026_01_p2                               | r       |     2
 events_2026_01_p3                               | r       |     2

:regclass 转换能直接把 OID 显示成表名。relkind = 'p' 是分区表（中间节点），'r' 是普通表（叶子）。

查某个叶子分区的祖先链 ​

pg_partition_ancestors() 返回从父表到该分区的整条路径：

sql
SELECT pg_partition_ancestors('events_2026_01_p2');
text
 pg_partition_ancestors
------------------------
 events
 events_2026_01
 events_2026_01_p2

插入并观察逐级路由 ​

sql
INSERT INTO events (event_id, tenant_id, created_at, payload)
VALUES (1, 42, '2026-01-15 10:00:00+08', '{"a":1}'),
       (2, 43, '2026-01-20 11:00:00+08', '{"b":2}'),
       (3, 44, '2026-02-03 09:00:00+08', '{"c":3}');  -- 这行若无 2 月分区则报错

SELECT event_id, tableoid::regclass AS leaf FROM events ORDER BY event_id;
text
 event_id |       leaf
----------+------------------
        1 | events_2026_01_p2
        2 | events_2026_01_p1

注意第 3 行会失败，因为 2 月的一级分区还没建：

text
ERROR:  no partition of relation "events" found for row
DETAIL:  Partition key of the failing row contains (created_at) = (2026-02-03 09:00:00+08).

逐级裁剪演示 ​

同时命中两级条件：

sql
EXPLAIN (COSTS OFF)
SELECT * FROM events
WHERE created_at >= '2026-01-01' AND created_at < '2026-02-01'
  AND tenant_id = 42;
text
                          QUERY PLAN
--------------------------------------------------------------
 Seq Scan on events_2026_01_p2
   Filter: ((created_at >= ...) AND (created_at < ...) AND (tenant_id = 42))

一二级都裁剪到位，只留一个叶子。

只命中一级条件：

sql
EXPLAIN (COSTS OFF)
SELECT * FROM events
WHERE created_at >= '2026-01-01' AND created_at < '2026-02-01';
text
                          QUERY PLAN
--------------------------------------------------------------
 Append
   ->  Seq Scan on events_2026_01_p0
   ->  Seq Scan on events_2026_01_p1
   ->  Seq Scan on events_2026_01_p2
   ->  Seq Scan on events_2026_01_p3

一级裁到 1 个，但二级的 4 个叶子全扫。

详细说明 ​

一级策略应当选「归档维度」 ​

一级策略决定「怎么下线数据」，所以选型必须服务于归档。绝大多数情况下，一级用 RANGE(时间)：

sql
CREATE TABLE logs (...) PARTITION BY RANGE (created_at);
CREATE TABLE logs_2026_01 PARTITION OF logs FOR VALUES FROM ('2026-01-01') TO ('2026-02-01') PARTITION BY HASH (device_id);

原因是时间归档是最普遍的需求——旧数据整块过期，DETACH + DROP 是秒级操作。若一级用 HASH，则无法按时间下线，每月清理只能逐行 DELETE。

一级用 LIST 的场景是「多租户下每个租户独立归档」：

sql
CREATE TABLE tenant_data (...) PARTITION BY LIST (tenant_id);
-- 每个租户一个一级分区，租户内部再按月分区
CREATE TABLE tenant_data_t42 PARTITION OF tenant_data FOR VALUES IN (42) PARTITION BY RANGE (created_at);

二级策略应当选「高频查询维度」 ​

二级策略服务于「查询裁剪」或「写入打散」，因此要选实践中查询最常带的条件列，或者需要分摊写入的高基数列。

sql
-- 查询主力是 (tenant_id, ...)，二级就用 HASH(tenant_id) 或 LIST(tenant_id)
CREATE TABLE events_2026_01 PARTITION OF events
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01')
    PARTITION BY HASH (tenant_id);

如果二级选了一个查询从不使用的维度，那这些二级分区只是白白增加了分区数量与规划开销。

逐级裁剪的机制与 EXPLAIN 表现 ​

裁剪是逐级的：规划器在一级上做一次裁剪，得到一个保留下来的分区列表；再对每个保留的一级分区，在其二级上做裁剪。EXPLAIN 输出中可以看到 Append 的嵌套结构：

sql
EXPLAIN (COSTS OFF)
SELECT * FROM events WHERE created_at >= '2026-01-01' AND created_at < '2026-02-01' AND tenant_id = 42;

若期间跨了两个月：

sql
EXPLAIN (COSTS OFF)
SELECT * FROM events WHERE created_at >= '2026-01-01' AND created_at < '2026-03-01' AND tenant_id = 42;
text
                          QUERY PLAN
--------------------------------------------------------------
 Append
   ->  Seq Scan on events_2026_01_p2
         Filter: ((created_at >= ...) AND (created_at < ...) AND (tenant_id = 42))
   ->  Seq Scan on events_2026_02_p2
         Filter: ((created_at >= ...) AND (created_at < ...) AND (tenant_id = 42))

可以看到：一级裁到 2 个月，每个月内部二级都裁到同一个余数 p2。这体现了逐级裁剪的「分治」本质——每一级独立裁剪，结果取交集。

维护操作的传播与锁 ​

操作一层叶子整个一级子树
VACUUM单个叶子需逐叶子做（父表 VACUUM 不含数据）
DETACH摘一个叶子摘整棵树（含全部下级）
DROP删一个叶子删整棵树
CREATE INDEX ON parent自动在所有叶子建同
ATTACH挂一个叶子（边界须与一级兼容）挂一棵子树

DETACH 整个一级子树是归档的利器：

sql
BEGIN;
ALTER TABLE events DETACH PARTITION events_2026_01;  -- 摘掉整棵树
DROP TABLE events_2026_01;                            -- 叶子随子树一起删除
COMMIT;

但要注意：DETACH 需要在父表 events 上取 ACCESS EXCLUSIVE 锁（PostgreSQL 14+ 可用 DETACH PARTITION CONCURRENTLY，但要求该分区表没有 DEFAULT 分区）。详见分区维护（DETACH / DROP）。

每级都可以有自己的索引 ​

在分区表上 CREATE INDEX 会自动下发到全部叶子；也可以对某个一级分区单独建索引：

sql
-- 全部叶子都建
CREATE INDEX events_tenant_idx ON events (tenant_id, event_id);

-- 只给某个一级子树建（其下叶子都建）
CREATE INDEX events_2026_01_tenant_idx ON events_2026_01 (tenant_id, event_id);
text
postgres=# \d events_2026_01_p2
              Table "public.events_2026_01_p2"
Indexes:
    "events_2026_01_p2_pkey" PRIMARY KEY, btree (created_at, tenant_id, event_id)
    "events_2026_01_p2_tenant_idx" btree (tenant_id, event_id)

多级分区下，主键/唯一约束必须包含每一级的分区键列。因此 PRIMARY KEY (created_at, tenant_id, event_id) 是必需的——created_at 是一级键、tenant_id 是二级键，二者都得在内。

分区数量爆炸的规划时间影响 ​

这是多级分区最实在的痛点。PostgreSQL 的规划器要为每个「可能被扫描的叶子分区」生成子计划，规划时间与候选叶子数大致成正比。

构造一个直观的对比：把 60 个月 × 8 个哈希的 RANGE → HASH 表拿来对比单级 RANGE 表：

sql
-- 单级：60 个分区
EXPLAIN (ANALYZE, COSTS OFF) SELECT * FROM events_single WHERE created_at >= '2026-01-01' AND created_at < '2026-02-01';
text
 Planning Time: 0.8 ms
 Execution Time: 0.05 ms
sql
-- 多级：60 × 8 = 480 个叶子，只裁剪了一级，二级全扫
EXPLAIN (ANALYZE, COSTS OFF) SELECT * FROM events_multi WHERE created_at >= '2026-01-01' AND created_at < '2026-02-01';
text
 Planning Time: 9.4 ms      ← 二级未裁剪时规划开销显著上升
 Execution Time: 0.12 ms

当规划时间远大于执行时间时，就说明分区数量已成为负担。缓解手段：

  1. 让二级条件也参与查询，把候选叶子数降下来；
  2. 合并二级（例如把 8 个哈希减到 4 个）；
  3. 使用 prepared 语句，让规划只做一次（PostgreSQL 12+ 对分区表的参数化查询能在执行期裁剪）；
  4. 减少保留的一级分区，及时归档旧月份。

prepared statement 与运行时裁剪 ​

多级分区 + 参数化查询是规划开销的重灾区。PostgreSQL 的通用计划（generic plan）会跳过参数值相关的裁剪，导致每次执行都扫全部叶子。可以用 plan_cache_mode 观察与调整：

sql
PREPARE q(timestamptz, timestamptz, int) AS
SELECT * FROM events WHERE created_at >= $1 AND created_at < $2 AND tenant_id = $3;

EXPLAIN (ANALYZE, COSTS OFF) EXECUTE q('2026-01-01', '2026-02-01', 42);
text
                  QUERY PLAN
-----------------------------------------------
 Seq Scan on events_2026_01_p2 (actual rows=...)
 Planning Time: 1.2 ms
 Execution Time: 0.09 ms
sql
-- 强制每次重新规划，以便利用参数值做裁剪
SET plan_cache_mode = force_custom_plan;

代价是每次执行都要重新规划；在多级分区下这往往是划算的（裁剪收益 > 规划成本）。

用 pg_partition_tree 做维护与巡检 ​

多级分区下，巡检脚本必须借助 pg_partition_tree() 才能正确遍历：

sql
-- 列出所有叶子分区及其大小
SELECT relid::regclass AS leaf,
       pg_size_pretty(pg_total_relation_size(relid)) AS size
FROM pg_partition_tree('events')
WHERE relkind = 'r'
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;
text
              leaf              |  size
--------------------------------+---------
 events_2026_03_p0              | 512 MB
 events_2026_03_p1              | 508 MB
 events_2026_01_p2              | 61 MB
 ...

批量维护也可以用 pg_partition_tree() 驱动：

sql
DO $$
DECLARE r record;
BEGIN
  FOR r IN SELECT relid FROM pg_partition_tree('events') WHERE relkind = 'r' LOOP
    EXECUTE format('VACUUM (ANALYZE) %s', r.relid::regclass);
  END LOOP;
END $$;

与 pg_partman 的配合 ​

手工维护多级分区的预建、轮转、清理非常繁琐，pg_partman 扩展正是为此设计的。它支持多级分区配置（sub_partitioning），可以自动按模板创建一级与二级分区、自动 DETACH 过期分区。

sql
-- pg_partman 中配置子分区（示意，具体 API 随版本变化）
SELECT partman.create_parent(
    p_parent_table => 'public.events',
    p_control      => 'created_at',
    p_type         => 'range',
    p_interval     => '1 month'
);

使用前需评估扩展的维护负担与升级兼容性，但「多级 + 自动化」几乎是必经之路——手工创建 480 个叶子分区不现实。

多级分区下的默认分区 ​

每一级都可以有 DEFAULT，但实践中建议只在最需要兜底的那一级设 DEFAULT，否则 DEFAULT 渗透会带来麻烦：

sql
-- 一级设 DEFAULT（兜底所有未建月份）
CREATE TABLE events_default PARTITION OF events DEFAULT
    PARTITION BY HASH (tenant_id);
CREATE TABLE events_default_p0 PARTITION OF events_default FOR VALUES WITH (MODULUS 4, REMAINDER 0);
-- ...

风险与单级相同：DEFAULT 里一旦有数据，新建分区就要搬数据；且 DEFAULT 子树无法用 DETACH PARTITION CONCURRENTLY（PostgreSQL 要求父表无 DEFAULT 分区）。

何时不该用多级分区 ​

多级分区不是「更高级所以更好」。以下情况应当避免：

情况建议
只需要一个维度裁剪用单级分区
两个维度中有一个查询频率很低只按高频维度分区，另一维度建索引
叶子分区总数会超过几千减少一级或二级的分区数
表总量在千万行以内通常不需要多级分区
团队缺乏分区运维经验先从单级分区起步
sql
-- 单级分区 + 复合索引，往往比多级分区更简单有效
CREATE TABLE orders (...) PARTITION BY RANGE (created_at);
CREATE INDEX orders_tenant_created_idx ON orders (tenant_id, created_at);

实战示例 ​

下面完整构建一张「区域订单表」，需求是：

  1. 按 created_at 月度归档（可快速下线旧数据）；
  2. 每个月的查询常带 region 条件，希望月内再按 region 裁剪；
  3. 保留 3 个月数据。

这是一个典型的「RANGE → LIST」两级结构。

第 1 步：建父表，声明一级 RANGE ​

sql
CREATE TABLE orders (
    order_id   bigint      NOT NULL,
    region     text        NOT NULL,
    created_at timestamptz NOT NULL,
    amount     numeric(12,2) NOT NULL,
    PRIMARY KEY (created_at, region, order_id)
) PARTITION BY RANGE (created_at);

主键包含一级键 created_at 与二级键 region，满足「唯一约束须含全部分区键」的要求。

第 2 步：建 3 个月的一级分区，声明二级 LIST ​

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

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

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

第 3 步：为每个一级分区建二级 LIST 叶子 ​

sql
DO $$
DECLARE
  m      text;
  parent text;
BEGIN
  FOREACH m IN ARRAY ARRAY['2026_01','2026_02','2026_03'] LOOP
    parent := 'orders_' || m;
    EXECUTE format('CREATE TABLE %I_east  PARTITION OF %I FOR VALUES IN (''east'')', parent, parent);
    EXECUTE format('CREATE TABLE %I_west  PARTITION OF %I FOR VALUES IN (''west'')', parent, parent);
    EXECUTE format('CREATE TABLE %I_north PARTITION OF %I FOR VALUES IN (''north'')', parent, parent);
    EXECUTE format('CREATE TABLE %I_other PARTITION OF %I DEFAULT', parent, parent);
  END LOOP;
END $$;

共 3 × 4 = 12 个叶子分区。

第 4 步：查看树形结构 ​

sql
SELECT relid::regclass AS name, relkind, level
FROM pg_partition_tree('orders')
ORDER BY level, name;
text
           name            | relkind | level
---------------------------+---------+-------
 orders                    | p       |     0
 orders_2026_01            | p       |     1
 orders_2026_02            | p       |     1
 orders_2026_03            | p       |     1
 orders_2026_01_east       | r       |     2
 orders_2026_01_north      | r       |     2
 orders_2026_01_other      | r       |     2
 orders_2026_01_west       | r       |     2
 ...（其余月份同理）

第 5 步：写入数据并观察逐级路由 ​

sql
INSERT INTO orders (order_id, region, created_at, amount)
SELECT g,
       (ARRAY['east','west','north','south'])[1 + (g % 4)],
       timestamptz '2026-01-01' + (g % 90) * interval '1 day',
       (random() * 1000)::numeric(12,2)
FROM generate_series(1, 10000) AS g;

ANALYZE orders;
sql
SELECT tableoid::regclass AS leaf, count(*)
FROM orders
GROUP BY tableoid
ORDER BY 1;
text
        leaf          | count
----------------------+-------
 orders_2026_01_east  |  1258
 orders_2026_01_north |  1243
 orders_2026_01_other |  1260   ← south 落在这里
 orders_2026_01_west  |  1239
 orders_2026_02_east  |  ...
 ...

可以看到 south 因为没有专属的二级分区，落入了 _other（DEFAULT）。

第 6 步：验证逐级裁剪 ​

同时带时间与区域条件：

sql
EXPLAIN (ANALYZE, COSTS OFF)
SELECT * FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2026-02-01'
  AND region = 'east';
text
                         QUERY PLAN
---------------------------------------------------------------
 Seq Scan on orders_2026_01_east (actual rows=1258 loops=1)
   Filter: ((created_at >= ...) AND (created_at < ...) AND (region = 'east'))
 Planning Time: 0.6 ms
 Execution Time: 0.4 ms

只命中单个叶子分区。

第 7 步：归档最旧月份（整棵子树下线） ​

sql
BEGIN;
ALTER TABLE orders DETACH PARTITION orders_2026_01;
DROP TABLE orders_2026_01;   -- 12 → 8 个叶子，一次删除
COMMIT;
text
DETACH TABLE
DROP TABLE

验证子树已被移除：

sql
SELECT count(*) AS remaining_leaves
FROM pg_partition_tree('orders') WHERE relkind = 'r';
text
 remaining_leaves
------------------
                8

第 8 步：清理后确认剩余分区可用 ​

sql
EXPLAIN (COSTS OFF)
SELECT * FROM orders WHERE created_at >= '2026-02-01' AND created_at < '2026-03-01' AND region = 'west';
text
                        QUERY PLAN
--------------------------------------------------------------
 Seq Scan on orders_2026_02_west
   Filter: ((created_at >= ...) AND (created_at < ...) AND (region = 'west'))

归档后剩余分区结构完好，裁剪依旧有效。

注意事项 ​

  • 不要为了「看起来更专业」而用多级分区。 单一维度能满足需求时，多级分区只会让运维变复杂、规划变慢。

  • 一级键与二级键都必须进入唯一约束。 漏掉任何一个都无法建主键/唯一约束。

    text
    ERROR:  unique constraint on partitioned table must include all partitioning columns
DETAIL:  PRIMARY KEY constraint on table "orders" lacks column "created_at" which is part of the partition key.

  • 二级未命中条件时，二级分区会被全部扫描。 这是多级分区最常见的性能陷阱。

    sql
    -- 不推荐：只有二级键条件，每个一级分区下的所有二级叶子都要扫描
SELECT * FROM orders WHERE region = 'east';  -- 扫 3 个月 × 各自全部叶子

  • 叶子分区数量必须受控。 一级 × 二级的乘积可能迅速失控，计划期规划开销会明显放大。

    sql
    -- 巡检：叶子分区总数
SELECT count(*) FROM pg_partition_tree('orders') WHERE relkind = 'r';

  • 不要给每个一级分区都挂 DEFAULT 二级分区。 DEFAULT 会阻塞新叶子的创建，且使 DETACH PARTITION CONCURRENTLY 不可用。

    sql
    -- 不推荐：每个一级分区下都挂 DEFAULT
CREATE TABLE orders_2026_01_other PARTITION OF orders_2026_01 DEFAULT;

  • 多级分区的中间节点不存数据。 对一级分区表做 SELECT count(*) 会扫描其全部叶子；对一级分区做 VACUUM 不等于对其叶子做 VACUUM。

    sql
    -- 正确做法：借助 pg_partition_tree 逐叶子维护
SELECT relid::regclass FROM pg_partition_tree('orders') WHERE relkind = 'r';

  • DETACH 整棵子树仍需父表 ACCESS EXCLUSIVE 锁。 归档窗口要选在低峰期，或使用 DETACH PARTITION CONCURRENTLY（PostgreSQL 14+，要求无 DEFAULT 分区）。

  • 跨级边界变更代价高。 想改变二级分区策略（如从 HASH 改成 LIST），必须重建整个一级子树并搬运数据，不存在原地转换。

  • 不要在多级结构里混用不一致的二级策略。 例如 1 月按 HASH(4)、2 月按 HASH(8)，会让维护脚本和查询假设都变得脆弱。

  • 迁移大表到多级分区需要停机窗口或双写方案。 ATTACH PARTITION 需要扫描校验数据，多级结构下校验的是整棵子树。

最佳实践 ​

  • 一级选归档维度、二级选查询维度。 这是一条几乎可以照搬的规则：一级决定下线方式，二级决定裁剪能力。

    sql
    -- 一级 RANGE(时间) 便于归档，二级 LIST(region) 便于裁剪
CREATE TABLE orders_2026_01 PARTITION OF orders
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01')
    PARTITION BY LIST (region);

  • 叶子总数保持在几百以内。 规划开销与叶子数量正相关，超过 1000 个叶子要重新评估二级分区数。

  • 用 pg_partition_tree() 驱动所有巡检与维护脚本。 不要手写多层 pg_inherits 递归查询。

    sql
    SELECT relid::regclass AS leaf FROM pg_partition_tree('orders') WHERE relkind = 'r';

  • 让主键覆盖所有层级的分区键。 这是硬性要求，也在设计阶段就决定了业务主键形态。

    sql
    PRIMARY KEY (created_at, region, order_id)   -- 一级键 + 二级键 + 业务键

  • 预建分区时把「当前月 + 未来若干月」都建好。 避免插入时因缺分区报错，也为归档留缓冲。

    sql
    DO $$
DECLARE m date;
BEGIN
  FOR m IN SELECT generate_series(date_trunc('month', now()),
                                  date_trunc('month', now()) + interval '2 months',
                                  interval '1 month')::date LOOP
    EXECUTE format('CREATE TABLE IF NOT EXISTS orders_%s PARTITION OF orders FOR VALUES FROM (%L) TO (%L) PARTITION BY LIST (region)',
                   to_char(m, 'YYYY_MM'), m, (m + interval '1 month')::date);
  END LOOP;
END $$;

  • 归档时整棵子树一起 DETACH + DROP。 比逐叶子删除快得多，且一次清理一个完整时间窗。

    sql
    BEGIN;
ALTER TABLE orders DETACH PARTITION orders_2025_12;
DROP TABLE orders_2025_12;
COMMIT;

  • 对参数化查询考虑 force_custom_plan。 多级分区下，为利用参数值裁剪，重新规划往往比复用通用计划更划算。

    sql
    SET plan_cache_mode = force_custom_plan;

  • 在二级分区键上建足索引。 当二级条件经常缺席时，二级键上的索引是唯一的兜底手段。

    sql
    CREATE INDEX orders_region_idx ON orders (region, created_at);

  • 定期核对分区均匀性与增长趋势。 各叶子分区体积悬殊时，说明分区键或边界划分不合理。

    sql
    SELECT relid::regclass AS leaf, pg_size_pretty(pg_total_relation_size(relid)) AS size
FROM pg_partition_tree('orders') WHERE relkind = 'r'
ORDER BY pg_total_relation_size(relid) DESC;

  • 多级分区务必引入自动化（如 pg_partman）。 手工维护几十上百个叶子的创建、轮转、清理几乎必然出错。

下一节

下一节：创建与附加分区——学习 CREATE TABLE ... PARTITION OF、ATTACH / DETACH PARTITION 的完整操作与锁行为。

参考链接 ​