PHP小码农

Appearance

分区索引策略（Partitioned Indexes） ​

分区表上的索引是「虚拟」的：你在父表上写一条 CREATE INDEX，PostgreSQL 不会在父表上生成任何索引文件，而是在每个分区上各建一个结构相同的子索引，再由父表上的「分区索引」把它们串起来。理解这条链路，是理解分区表索引行为、唯一约束限制和无阻塞建索引方案的前提。

本页讲清楚父索引与子索引的对应关系、ON ONLY 与自动级联的区别、ALTER INDEX ... ATTACH PARTITION 的用法、为什么分区表的唯一约束必须包含分区键，以及在超大分区表上如何不长时间阻塞写入地建索引。

前置知识

阅读本节前，建议先了解：声明式分区概述、创建与附加分区、分区裁剪（Partition Pruning）

基础概念 ​

分区索引是「虚拟」的 ​

分区表本身不存数据，它的索引也对应地不占存储。官方文档的原话是：在分区表上声明的索引或唯一约束与分区表本身一样是「虚拟」的——数据实际存放在各个分区表的子索引里。

text
父表 orders（分区表，无存储、无索引文件）
  └─ 分区索引 orders_user_id_idx            ← 虚拟，relkind = 'I'，0 字节
       ├─ orders_2026_01_user_id_idx        ← 真实 B-tree，占存储
       ├─ orders_2026_02_user_id_idx        ← 真实 B-tree，占存储
       └─ orders_2026_03_user_id_idx        ← 真实 B-tree，占存储

因此 pg_relation_size() 查父索引永远是 0，真正消耗空间的是子索引。

sql
SELECT c.relname,
       c.relkind,
       pg_size_pretty(pg_relation_size(c.oid)) AS size
FROM pg_class c
WHERE c.relname = 'orders_user_id_idx';
text
        relname        | relkind |  size
-----------------------+---------+---------
 orders_user_id_idx    | I       | 0 bytes
(1 row)

relkind = 'I' 就是「分区索引（partitioned index）」这个对象类型的标志，它由 PostgreSQL 11 引入。

父索引是「模板」，子索引是「实体」 ​

父索引只保存定义（列、操作符类、排序、WHERE 谓词等），子索引保存数据。两者的定义必须一一对应，否则无法关联。这条规则衍生出两个直接后果：

行为结果
在父表上直接 CREATE INDEX自动为所有已有分区建同结构子索引，之后新建/附加的分区也会自动继承
CREATE INDEX ... ON ONLY 父表只在父表登记一个无效（invalid）索引，不级联到任何分区
分区上已有的、结构匹配的索引可以用 ALTER INDEX ... ATTACH PARTITION 挂到父索引上
单独 DROP INDEX 某个被挂载的子索引报错，被依赖关系阻止

唯一约束为什么必须包含分区键 ​

这是分区表最容易踩的限制。分区表上的唯一约束（含主键）只能保证每个分区内部唯一，无法跨分区判重。因此内核强制要求：唯一索引的列必须包含全部分区键列。

sql
-- 错误！唯一约束缺少分区键 created_at
CREATE TABLE orders_bad (
  id         bigint NOT NULL,
  created_at date   NOT NULL
) PARTITION BY RANGE (created_at);

ALTER TABLE orders_bad ADD CONSTRAINT orders_bad_pk PRIMARY KEY (id);
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.

加上分区键后立刻合法，因为「同一分区内 (id, created_at) 唯一」加上「created_at 本身就决定了分区」两者合起来才等价于全局唯一。

invalid（无效）索引是什么 ​

索引有一个独立于「是否存在」的状态位：pg_index.indisvalid。它表示索引内容是否与表一致。ON ONLY 建出来的父索引、CREATE INDEX CONCURRENTLY 中途失败留下的索引，都是无效索引。

sql
SELECT c.relname AS index_name,
       i.indisvalid,
       i.indisready
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE c.relname LIKE 'orders_%_idx';
text
       index_name        | indisvalid | indisready
-------------------------+------------+------------
 orders_user_id_idx      | f          | t
 orders_2026_01_user_id_idx | t      | t
 orders_2026_02_user_id_idx | t      | t
(3 rows)

关键点：无效索引不参与约束校验，也不被规划器使用。把父唯一索引做成无效状态时，那段窗口内唯一性并没有被强制——这一点必须写进变更流程。

三种索引维护路径 ​

场景手段是否级联是否阻塞写
常规给整个分区表加索引CREATE INDEX ON parent (...)是（含未来分区）阻塞（每个分区各自持锁）
需要对超大表无阻塞建索引ON ONLY + 逐分区 CONCURRENTLY + ATTACH否，手工挂载父层不阻塞
分区已有索引，想纳入父索引管理ALTER INDEX parent ATTACH PARTITION child_idx是（挂载这一个）按文档默认 ACCESS EXCLUSIVE

语法与用法 ​

text
-- 1) 在父表上建索引：自动级联到全部分区
CREATE [ UNIQUE ] INDEX [ IF NOT EXISTS ] name ON parent_table ( column [, ...] )
  [ USING method ] [ INCLUDE ( column [, ...] ) ] [ WHERE predicate ];

-- 2) 只在父表上登记模板索引，不级联
CREATE [ UNIQUE ] INDEX name ON ONLY parent_table ( column [, ...] );

-- 3) 把分区上已有的等价索引挂到父索引
ALTER INDEX parent_index ATTACH PARTITION partition_index;

-- 4) 单独重建某个子索引
REINDEX INDEX [ CONCURRENTLY ] partition_index;

一个完整的「父表建索引」示例：

sql
CREATE INDEX orders_user_created_idx ON orders (user_id, created_at);
text
CREATE INDEX

CREATE INDEX 一条语句在父表层面只报告一次成功，但内部已经为 orders 下每个分区都建好了同名结构的子索引。

注意：在分区表上不能使用 CONCURRENTLY。官方文档明确写出这一限制，并给出了替代方案——用 ON ONLY 在父表上建无效索引，再逐分区并发创建、最后 ATTACH。

查看分区索引的层级 ​

sql
-- psql 中直接看父表
\d orders
text
               Partitioned table "public.orders"
   Column   |  Type   | Collation | Nullable | Default
------------+---------+-----------+----------+---------
 id         | bigint  |           | not null |
 user_id    | bigint  |           | not null |
 created_at | date    |           | not null |
Partition key: RANGE (created_at)
Indexes:
    "orders_user_created_idx" btree (user_id, created_at)
Number of partitions: 3 (Use \d+ to list them.)

\d orders 只显示一条 Indexes，因为分区索引在逻辑上就是一个索引；要看每个分区的物理索引，需要展开分区或用目录查询：

sql
SELECT parent.relname AS parent_index,
       child.relname  AS partition_index,
       pg_get_indexdef(child.oid) AS definition
FROM pg_inherits inh
JOIN pg_class parent ON parent.oid = inh.inhparent
JOIN pg_class child  ON child.oid  = inh.inhrelid
WHERE parent.relname = 'orders_user_created_idx';
text
     parent_index        |       partition_index           |              definition
-------------------------+---------------------------------+---------------------------------------
 orders_user_created_idx | orders_2026_01_user_created_idx | CREATE INDEX orders_2026_01_user_...
 orders_user_created_idx | orders_2026_02_user_created_idx | CREATE INDEX orders_2026_02_user_...
 orders_user_created_idx | orders_2026_03_user_created_idx | CREATE INDEX orders_2026_03_user_...
(3 rows)

pg_inherits 这张表同时承担「表的分区关系」与「索引的分区关系」两种父子链，索引链的 inhparent 指向父索引。

详细说明 ​

在父表上建索引：自动级联 ​

这是最省心的方式，也是 CREATE TABLE 之后立刻该做的第二件事。官方文档在声明式分区示例的第 3 步就是「在分区表上建立键列索引」，并强调：这会在每个分区上自动创建匹配索引，而且你之后创建或附加的任何分区也会自动拥有这个索引。

sql
CREATE INDEX orders_created_idx ON orders (created_at);
text
CREATE INDEX

级联带来的两个隐性成本：

  • 建索引期间会逐个锁住分区。 CREATE INDEX（非并发）在每张表上持 SHARE 锁，阻塞该分区的写入但允许读取。分区越多，整体耗时越长，持锁窗口也越零散。
  • 未来分区在创建时就要建索引。 CREATE TABLE ... PARTITION OF 或 ATTACH PARTITION 时会顺带把索引补齐，这会延长该语句的持锁时间。

如果分区已经很大，且不能容忍写入被阻塞，就应改用下面 ON ONLY 的路径。

ON ONLY：只建父索引，不级联 ​

ON ONLY 让 CREATE INDEX 停在父表这一层：登记一个有名字的索引对象，但不生成任何子索引，并把它标记为无效。

sql
CREATE INDEX orders_user_id_idx ON ONLY orders (user_id);
text
CREATE INDEX

此时查询父索引状态：

sql
SELECT indexrelid::regclass AS index_name, indisvalid, indisready
FROM pg_index
WHERE indexrelid = 'orders_user_id_idx'::regclass;
text
     index_name      | indisvalid | indisready
---------------------+------------+------------
 orders_user_id_idx  | f          | t
(1 row)

indisvalid = f 表示「父索引还不完整」。此时：

  • 规划器不会使用它做索引扫描（分区查询仍可用各自分区的子索引）；
  • 若它是 UNIQUE，唯一性不会被强制；
  • 它可以作为后续 ATTACH PARTITION 的挂载目标。

ON ONLY 的一个实用细节：必须显式给父索引命名，因为稍后 ATTACH PARTITION 要用这个名字引用它。

sql
-- 推荐：显式命名，便于后续挂载
CREATE INDEX orders_user_id_idx ON ONLY orders (user_id);
-- 不推荐：不命名，之后要先把名字查出来才能挂载
CREATE INDEX ON ONLY orders (user_id);

ATTACH PARTITION：把子索引挂上去 ​

ALTER INDEX ... ATTACH PARTITION 把一个分区上已存在的、定义等价的索引关联到父索引上。官方定义是：被命名索引必须位于被修改索引所在表的一个分区上，并且具有等价的定义。

sql
-- 在分区上单独建索引
CREATE INDEX orders_2026_01_user_id_idx ON orders_2026_01 (user_id);

-- 挂载到父索引
ALTER INDEX orders_user_id_idx ATTACH PARTITION orders_2026_01_user_id_idx;
text
CREATE INDEX
ALTER INDEX

挂载成功后，还会产生一个「反向依赖」：被挂载的子索引不能单独删除，且父索引被删除时它会自动一并删除。

sql
-- 错误！被挂载的子索引不能被单独 DROP
DROP INDEX orders_2026_01_user_id_idx;
-- 正确：删父索引，子索引随之消失
DROP INDEX orders_user_id_idx;

重复挂载同一个索引是安全的：官方说明「若被命名索引已经挂到该父索引上，则命令会在父索引当前无效时尝试校验父索引」。这使挂载脚本可以幂等重复执行。

无阻塞建索引：ON ONLY + CONCURRENTLY + ATTACH ​

这是官方推荐的、也是大分区表唯一可行的在线建索引路径。三步：

第一步，在父表上建模板索引（不级联，也不阻塞任何分区）。

sql
CREATE INDEX orders_user_id_idx ON ONLY orders (user_id);

第二步，对每个分区并发建同结构索引。CREATE INDEX CONCURRENTLY 只持 SHARE UPDATE EXCLUSIVE 锁，不阻塞该分区的写入。

sql
CREATE INDEX CONCURRENTLY orders_2026_01_user_id_idx ON orders_2026_01 (user_id);
CREATE INDEX CONCURRENTLY orders_2026_02_user_id_idx ON orders_2026_02 (user_id);
CREATE INDEX CONCURRENTLY orders_2026_03_user_id_idx ON orders_2026_03 (user_id);

第三步，逐个挂载。

sql
ALTER INDEX orders_user_id_idx ATTACH PARTITION orders_2026_01_user_id_idx;
ALTER INDEX orders_user_id_idx ATTACH PARTITION orders_2026_02_user_id_idx;
ALTER INDEX orders_user_id_idx ATTACH PARTITION orders_2026_03_user_id_idx;

官方原文：当所有分区的索引都挂到父索引之后，父索引会被自动标记为有效。 验证一下：

sql
SELECT indexrelid::regclass AS index_name, indisvalid
FROM pg_index WHERE indexrelid = 'orders_user_id_idx'::regclass;
text
     index_name      | indisvalid
---------------------+------------
 orders_user_id_idx  | t
(1 row)

如果某个分区永久不会有这个索引（例如某分区是只读归档、明确不需要），父索引就会一直停留在无效状态，必须用第二种办法：把该分区的索引也建出来再挂上，或者接受这个索引永远无效。

唯一索引与分区键 ​

分区表上的唯一索引受两条约束同时限制：

  • 必须包含全部分区键列（否则报错，见「基础概念」）；
  • 唯一性只在每个分区内部成立。
sql
-- 合法：主键包含分区键 created_at
CREATE TABLE orders (
  id         bigint NOT NULL,
  user_id    bigint NOT NULL,
  created_at date   NOT NULL,
  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- 合法：在父表上建唯一索引，也要带分区键
CREATE UNIQUE INDEX orders_id_created_uq ON orders (id, created_at);
text
CREATE TABLE
CREATE UNIQUE INDEX

想让「id 全局唯一」而分区键是 created_at，就只能靠替代方案：

sql
-- 方案：用小尺寸的未分区表承载全局唯一键 + 外键约束
CREATE TABLE orders_id_registry (
  id bigint PRIMARY KEY
);

CREATE TABLE orders (
  id         bigint NOT NULL REFERENCES orders_id_registry (id),
  created_at date   NOT NULL
) PARTITION BY RANGE (created_at);

代价是每次插入多一次外部表唯一性检查。它换来的是「分区带来的写入吞吐」与「全局唯一」之间的可接受折中。

索引随分区的增删而随动 ​

分区索引的自动行为覆盖了整个生命周期：

动作索引侧的结果
CREATE TABLE ... PARTITION OF自动为新区间建出全部父索引对应的子索引
ALTER TABLE ... ATTACH PARTITION对待附加表，先补齐缺失索引再挂载
ALTER TABLE ... DETACH PARTITION子索引与父索引解除关联，成为独立索引，随表一起被摘下
DROP TABLE partition该分区的子索引随之删除
DROP INDEX parent_index全部子索引一并删除

验证「新分区自动带索引」：

sql
CREATE TABLE orders_2026_04 PARTITION OF orders
  FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');
sql
SELECT indexrelid::regclass AS index_name, indisvalid, indisready
FROM pg_index
WHERE indrelid = 'orders_2026_04'::regclass;
text
             index_name              | indisvalid | indisready
-------------------------------------+------------+------------
 orders_2026_04_pkey                 | t          | t
 orders_2026_04_id_created_uq        | t          | t
(2 rows)

DETACH 之后，原来挂在父索引上的子索引会断开依赖：

sql
ALTER TABLE orders DETACH PARTITION orders_2026_01;
-- 此时 orders_2026_01_user_id_idx 已是普通索引，可以单独 DROP
DROP INDEX orders_2026_01_user_id_idx;

索引膨胀与 REINDEX ​

每个分区各自是一张普通表，因此各自独立膨胀。时间序列场景下，最新分区写入最频繁，往往膨胀最严重；老分区几乎只读，基本不需要重建。

sql
-- 找出膨胀最明显的前 5 个子索引
SELECT indexrelid::regclass AS index_name,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size,
       idx_scan
FROM pg_stat_user_indexes
WHERE relname LIKE 'orders_2026%'
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 5;
text
            index_name             |  size  | idx_scan
-----------------------------------+--------+----------
 orders_2026_09_pkey               | 220 MB |  9142203
 orders_2026_09_user_id_idx        | 176 MB |   812300
 orders_2026_08_pkey               | 214 MB |  9011884
 orders_2026_08_user_id_idx        | 170 MB |   799211
 orders_2026_07_pkey               | 211 MB |  8874012
(5 rows)

重建时有一个容易忽略的限制：对分区索引（或分区表）执行 REINDEX 不能在事务块内运行，因为它在内部按分区逐个处理。

sql
-- 错误！对分区索引 REINDEX 不能放在事务块里
BEGIN;
REINDEX INDEX orders_user_id_idx;
COMMIT;
text
ERROR:  REINDEX INDEX cannot run inside a transaction block

正确做法是逐个分区重建，需要在线执行就加 CONCURRENTLY：

sql
REINDEX INDEX CONCURRENTLY orders_2026_09_user_id_idx;

分区索引与裁剪的配合 ​

分区裁剪决定「扫哪些分区」，索引决定「在这些分区里怎么扫」。两者是正交的，但顺序有讲究：

  1. 先裁剪，把候选分区收敛到少数几个（甚至一个）；
  2. 再在每个候选分区内选择索引扫描或顺序扫描。
sql
EXPLAIN (COSTS OFF)
SELECT * FROM orders
WHERE created_at >= '2026-02-01' AND created_at < '2026-03-01'
  AND user_id = 42;
text
                            QUERY PLAN
-------------------------------------------------------------------
 Index Scan using orders_2026_02_user_id_idx on orders_2026_02 orders
   Index Cond: (user_id = 42)
   Filter: ((created_at >= '2026-02-01') AND (created_at < '2026-03-01'))
(3 rows)

计划里既体现了裁剪（只剩 orders_2026_02），也体现了子索引选择（用 user_id 索引）。注意索引名变成了子索引名——这说明裁剪发生在索引选择之前。

一个常见陷阱：分区键列本身不需要额外建索引。因为裁剪已经把范围缩小到「整张分区」，再在分区内用分区键做索引扫描通常没意义。

sql
-- 不推荐：为分区键单独建索引，收益有限
CREATE INDEX orders_created_at_only_idx ON orders (created_at);

只有当查询「不按分区键裁剪、却要按分区键排序」时，这个索引才有意义。

索引统计与巡检 ​

分区表上的索引数量是「父索引数 × 分区数」，很容易失控，需要常态化巡检。

sql
-- 统计每个父索引及其子索引总占用
SELECT parent.relname AS parent_index,
       count(*) AS partitions,
       pg_size_pretty(sum(pg_relation_size(child.oid))) AS total_size
FROM pg_inherits inh
JOIN pg_class parent ON parent.oid = inh.inhparent
JOIN pg_class child  ON child.oid  = inh.inhrelid
WHERE parent.relkind = 'I'
GROUP BY parent.relname
ORDER BY sum(pg_relation_size(child.oid)) DESC;
text
      parent_index        | partitions | total_size
--------------------------+------------+------------
 orders_user_created_idx  |        240 | 38 GB
 orders_pkey              |        240 | 41 GB
 orders_id_created_uq     |        240 | 41 GB
(3 rows)

再看「有没有落单的、从未被挂载的无效父索引」：

sql
SELECT c.relname AS invalid_index, t.relname AS parent_table
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
JOIN pg_class t ON t.oid = i.indrelid
WHERE c.relkind = 'I' AND NOT i.indisvalid;
text
   invalid_index    | parent_table
--------------------+--------------
 orders_region_idx  | orders
(1 row)

出现无效父索引通常意味着「建模板时中途放弃」或「有分区没挂完」，应尽快补齐并挂载，否则唯一性不受保护。

ON ONLY 的其他用途与限制 ​

ON ONLY 不只用于建索引，它表达的语义是「只作用于这一层，不递归到子对象」。在分区表语境下它对应两个实用场景和一个限制：

sql
-- 用途一：给父索引设存储参数，先建模板
CREATE INDEX orders_user_id_idx ON ONLY orders (user_id) WITH (fillfactor = 90);

-- 用途二：在父表上尝试重建索引定义（不级联）
REINDEX INDEX orders_user_id_idx;   -- 作用于整棵索引树

限制在于：ON ONLY 建出的索引无法被查询直接使用，也不参与唯一性约束，因此在补齐挂载之前，它只是一个「占位符」。任何使用 ON ONLY 的变更脚本都必须包含「挂载 + 校验 indisvalid」这两个收尾步骤。

sql
-- 收尾校验：全部分区的子索引都挂上了吗？
SELECT count(*) FILTER (WHERE NOT indisvalid) AS invalid_count
FROM pg_index
WHERE indexrelid = 'orders_user_id_idx'::regclass;
text
 invalid_count
---------------
             0
(1 row)

实战示例 ​

第 1 步：建立分区表与父索引 ​

sql
CREATE TABLE orders (
  id         bigint NOT NULL,
  user_id    bigint NOT NULL,
  amount     numeric(12,2) NOT NULL,
  created_at date   NOT NULL,
  PRIMARY KEY (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 INDEX orders_user_created_idx ON orders (user_id, created_at);
text
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE INDEX

CREATE INDEX 之后，两个分区各自拥有一个 user_id, created_at 子索引。

第 2 步：确认级联结果 ​

sql
SELECT parent.relname AS parent_index, child.relname AS partition_index
FROM pg_inherits inh
JOIN pg_class parent ON parent.oid = inh.inhparent
JOIN pg_class child  ON child.oid  = inh.inhrelid
WHERE parent.relname = 'orders_user_created_idx'
ORDER BY child.relname;
text
       parent_index        |        partition_index
---------------------------+--------------------------------
 orders_user_created_idx   | orders_2026_01_user_created_idx
 orders_user_created_idx   | orders_2026_02_user_created_idx
(2 rows)

第 3 步：用 ON ONLY 建一个不级联的模板索引 ​

sql
CREATE INDEX orders_amount_idx ON ONLY orders (amount);
text
CREATE INDEX
sql
SELECT indexrelid::regclass AS index_name, indisvalid, indisready
FROM pg_index WHERE indexrelid = 'orders_amount_idx'::regclass;
text
     index_name     | indisvalid | indisready
--------------------+------------+------------
 orders_amount_idx  | f          | t
(1 row)

此时它是无效索引，既不被规划器使用，也不会有任何子索引。

第 4 步：并发建子索引并挂载 ​

sql
CREATE INDEX CONCURRENTLY orders_2026_01_amount_idx ON orders_2026_01 (amount);
CREATE INDEX CONCURRENTLY orders_2026_02_amount_idx ON orders_2026_02 (amount);
text
CREATE INDEX
CREATE INDEX
sql
ALTER INDEX orders_amount_idx ATTACH PARTITION orders_2026_01_amount_idx;
ALTER INDEX orders_amount_idx ATTACH PARTITION orders_2026_02_amount_idx;
text
ALTER INDEX
ALTER INDEX

第 5 步：确认父索引转为有效 ​

sql
SELECT indexrelid::regclass AS index_name, indisvalid
FROM pg_index WHERE indexrelid = 'orders_amount_idx'::regclass;
text
     index_name     | indisvalid
--------------------+------------
 orders_amount_idx  | t
(1 row)

全部子索引挂载完成后，父索引自动变有效，可以正常参与约束校验与查询规划。

第 6 步：验证唯一性确实被强制 ​

sql
INSERT INTO orders VALUES (1, 100, 9.90, '2026-01-05');
INSERT INTO orders VALUES (1, 100, 9.90, '2026-02-05');  -- 不同分区，允许
text
INSERT 0 1
INSERT 0 1
sql
-- 错误！同一分区内 (id, created_at) 重复
INSERT INTO orders VALUES (1, 100, 9.90, '2026-01-06');
text
ERROR:  duplicate key value violates unique constraint "orders_2026_01_pkey"
DETAIL:  Key (id, created_at)=(1, 2026-01-06) already exists.

这正说明了分区表唯一约束的真实语义：跨分区不判重，分区内严格唯一。

第 7 步：观察查询如何使用子索引 ​

sql
EXPLAIN (COSTS OFF)
SELECT * FROM orders
WHERE created_at >= '2026-02-01' AND created_at < '2026-03-01'
  AND user_id = 100
  AND amount > 100;
text
                                  QUERY PLAN
------------------------------------------------------------------------------
 Index Scan using orders_2026_02_user_created_idx on orders_2026_02 orders
   Index Cond: ((user_id = 100) AND ...)
   Filter: (amount > 100)
(3 rows)

裁剪先定位到 orders_2026_02，再在该分区内选择 user_created 子索引，amount 条件退化为过滤条件——因为它没有可用的子索引。

注意事项 ​

  • 分区表上不能使用 CREATE INDEX CONCURRENTLY。 官方文档明确列出这一限制，必须走 ON ONLY + 逐分区 CONCURRENTLY + ATTACH 的路径。

    sql
    -- 错误！分区表不支持带 CONCURRENTLY 的建索引
CREATE INDEX CONCURRENTLY orders_user_id_idx ON orders (user_id);

  • 唯一约束必须包含全部分区键列。 这是内核限制，不是配置项，事后调整代价极高。

    sql
    -- 正确：把分区键补进唯一键
PRIMARY KEY (id, created_at)

  • 无效索引不强制唯一性。 用 ON ONLY 建完父唯一索引到挂载完成之间，存在一段「唯一性不受保护」的窗口，必须写进变更流程。

    sql
    -- 变更前先确认不是无效索引
SELECT indisvalid FROM pg_index WHERE indexrelid = 'orders_id_created_uq'::regclass;

  • 被挂载的子索引不能被单独删除。 依赖关系会阻止它，需要先删父索引或先 DETACH 对应分区。

    sql
    -- 错误！
DROP INDEX orders_2026_01_user_id_idx;

  • 对分区索引 REINDEX 不能在事务块内执行。 它会递归处理整棵索引树。

    sql
    -- 正确：逐分区在线重建
REINDEX INDEX CONCURRENTLY orders_2026_09_user_id_idx;
    text
    错误写法：BEGIN; REINDEX INDEX orders_user_id_idx; COMMIT;
ERROR:  REINDEX INDEX cannot run inside a transaction block

  • ON ONLY 建索引时必须显式命名。 匿名索引之后无法被 ATTACH PARTITION 引用。

    sql
    -- 推荐
CREATE INDEX orders_user_id_idx ON ONLY orders (user_id);

  • 不要为分区键重复建索引。 裁剪已经把范围收敛到整分区，分区键索引在分区内通常无用。

    sql
    -- 不推荐
CREATE INDEX orders_created_idx ON orders (created_at);

  • 挂载的索引定义必须完全等价。 列、顺序、操作符类、排序、INCLUDE、WHERE 谓词有任何差异都会被拒绝。不要指望先挂载、日后再改定义。

    sql
    -- 挂载前先比对两侧定义
SELECT pg_get_indexdef('orders_user_id_idx'::regclass),
       pg_get_indexdef('orders_2026_01_user_id_idx'::regclass);

  • 父索引删除会连带删除所有子索引。 这是设计行为，但影响面极大：分区数越多、数据量越大，误删的代价越高。

    sql
    -- 先看清会影响多少个子索引
SELECT count(*) FROM pg_inherits WHERE inhparent = 'orders_user_id_idx'::regclass;

  • 分区索引数量的增长是乘法级的。 父索引数 × 分区数，必须纳入容量与 DDL 时间评估。

    sql
    SELECT count(*) AS total_partition_indexes
FROM pg_inherits inh
JOIN pg_class parent ON parent.oid = inh.inhparent
WHERE parent.relkind = 'I';

最佳实践 ​

  • 父索引与分区表在同一个变更里创建。 先建表就顺手建索引，避免新分区自动带索引的行为被遗漏。

    sql
    CREATE TABLE orders_2026_04 PARTITION OF orders
  FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');
CREATE INDEX orders_2026_04_user_id_idx ON orders_2026_04 (user_id);
ALTER INDEX orders_user_id_idx ATTACH PARTITION orders_2026_04_user_id_idx;

  • 唯一约束一律把分区键写进键列。 把这条当作建模规范，能避免上线后才发现的返工。

    sql
    PRIMARY KEY (id, created_at)

  • 大分区表建索引走 ON ONLY + CONCURRENTLY + ATTACH 三步法。 这是官方给出的在线建索引方案，父层不阻塞、子层逐分区不阻塞。

    sql
    CREATE INDEX orders_user_id_idx ON ONLY orders (user_id);
CREATE INDEX CONCURRENTLY orders_2026_01_user_id_idx ON orders_2026_01 (user_id);
ALTER INDEX orders_user_id_idx ATTACH PARTITION orders_2026_01_user_id_idx;

  • 变更收尾必须校验 indisvalid。 挂载脚本写成幂等形式，最后用一条查询断言父索引已有效。

    sql
    SELECT bool_and(indisvalid) AS all_valid FROM pg_index
WHERE indexrelid = 'orders_user_id_idx'::regclass;

  • 把索引随动写进分区轮转脚本。 预建分区、附加分区之后都要检查索引是否齐全，避免出现「新分区没索引」。

    sql
    SELECT count(*) AS missing_indexes
FROM pg_class t
JOIN pg_partition_tree('orders') p ON p.relid = t.oid
WHERE p.isleaf AND t.relname <> 'orders'
  AND NOT EXISTS (SELECT 1 FROM pg_index i WHERE i.indrelid = t.oid);

  • 用统一定式命名子索引，便于识别与批量操作。 默认生成的 分区名_列名_idx 已足够直观，不要手工改成难以批量匹配的名字。

    text
    orders_2026_04_user_id_idx   ← 分区名 + 列名 + idx

  • 只对确有需要的分区建索引。 老归档分区几乎只读，通常只需要主键索引；给它加业务索引只会增加存储和写入的维护成本。

    sql
    SELECT c.relname, count(*) AS indexes
FROM pg_inherits inh
JOIN pg_class c ON c.oid = inh.inhrelid
WHERE inh.inhparent = 'orders'::regclass
GROUP BY c.relname ORDER BY indexes DESC;

  • 定期清理无效与孤立索引。 巡检 relkind = 'I' AND NOT indisvalid 的父索引，以及 indisvalid = false 的子索引。

    sql
    SELECT c.relname FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE NOT i.indisvalid;

  • 索引重建按分区逐个在线执行，避开业务高峰。 REINDEX INDEX CONCURRENTLY 只作用于单个分区，影响面可控。

    sql
    REINDEX INDEX CONCURRENTLY orders_2026_09_user_created_idx;

  • 把「父索引数 × 分区数」纳入变更评审。 每新增一个父索引，实际要付出的是「分区数量」份的 DDL 与存储。

    sql
    SELECT (SELECT count(*) FROM pg_inherits WHERE inhparent = 'orders'::regclass) AS partitions,
       (SELECT count(*) FROM pg_class WHERE relkind = 'I') AS parent_indexes;

下一节

下一节：分区表最佳实践（Partitioning Best Practices）——把分区键选择、分区粒度、索引策略与运维流程整合成一套可落地的设计检查清单。

参考链接 ​