PHP小码农

Appearance

REINDEX 重建索引 ​

索引是物理文件，会随着增删改而「磨损」：页面上留下带空洞的碎片、被删条目的空间无法被新条目完全复用、并发建索引失败后留下无人使用的 INVALID 索引。这些问题的共同解法是重建索引（Rebuild Index）——丢弃旧索引文件，按当前数据重新生成一份紧凑的索引。

PostgreSQL 提供 REINDEX 命令完成这件事。它看起来简单，实际藏着不少坑：默认会对索引加 ACCESS EXCLUSIVE 锁（阻塞读，不只是写）、CONCURRENTLY 模式会走六步流程、失败后会留下带 _ccnew / _ccold 后缀的残骸、有的对象形态根本不允许并发重建。本节把这些语义讲透，并给出可复制的排障与观测手段。

前置知识

阅读本节前，建议先了解：CREATE INDEX 基础、VACUUM 清理、表膨胀检测与处理

基础概念 ​

索引为什么会「磨损」 ​

B-Tree 页的填充与回收遵循一套「懒惰」策略：删除条目只做标记，页面被删空后通常要等到整页完全为空才回收给空闲空间映射（Free Space Map，FSM）。于是出现两种典型状态：

状态成因后果
碎片化大量随机插入与删除，页面填充率参差同样数据占用更多页，索引扫描 I/O 变多
页内空洞页面只删了部分键，剩余键仍占用该页空间无法被新键复用，索引持续变大

VACUUM 只负责清理堆表死元组，它不会整理索引页内部布局。要真正把索引「压实」，只能重建。

text
删除后的 B-Tree 叶子页（每格 = 一个索引项）
[ k1 ][ k3 ][ ~~ ][ ~~ ][ k7 ][ ~~ ]
                ↑ 已删除项留下的空洞，新项不保证能填进来
重建后：
[ k1 ][ k3 ][ k7 ][ 新 ][ 新 ][ 新 ]

REINDEX 到底做了什么 ​

REINDEX 的语义非常直白：按当前堆表数据，重新生成一份全新的索引文件，然后用它替换旧的。它不等价于 DROP INDEX + CREATE INDEX，因为：

维度DROP INDEX + CREATE INDEXREINDEX
索引定义需要你手写完整定义，容易写错自动沿用原定义，零犯错
依赖对象丢弃约束依赖关系需重建保留索引 OID 与依赖，约束不受影响
锁两步之间索引不存在，查询计划会退化全程持有原索引，锁语义更受控
并发模式需自己拆成 CREATE INDEX CONCURRENTLY内置 CONCURRENTLY 选项

一句话：能用 REINDEX 就别 DROP + CREATE，除非你需要顺带改变索引定义。

四种典型使用场景 ​

场景触发信号为什么 REINDEX
索引损坏查询报 index is corrupted / 页校验失败从堆表重建，比恢复备份更快
索引膨胀pgstatindex() 显示 avg_leaf_density 偏低、deleted_pages 偏高回收碎片，缩小体积
存储参数变更改了 fillfactor 等参数但只对新页面生效重建让新参数覆盖全部页面
修复 INVALID 索引\d 显示 INVALID（并发建索引失败留下）使索引重新变为可用
text
        [ 堆表 ]
           │  按当前数据重新扫描
           ▼
   ┌───────────────┐   ┌───────────────┐
   │  旧索引文件    │   │  新索引文件    │
   │  碎片多、膨胀  │ → │  紧凑、按新参数 │
   └───────────────┘   └───────────────┘
        丢弃                替换

语法与用法 ​

text
REINDEX [ ( option [, ...] ) ] { INDEX | TABLE | SCHEMA } [ CONCURRENTLY ] name
REINDEX [ ( option [, ...] ) ] { DATABASE | SYSTEM }    [ CONCURRENTLY ] [ name ]

其中 option 可以是：
    CONCURRENTLY [ boolean ]
    TABLESPACE new_tablespace
    VERBOSE [ boolean ]

各作用范围的语义：

形式作用对象能否 CONCURRENTLY能否在事务块内
REINDEX INDEX name单个索引能能
REINDEX TABLE name表上全部索引（含 TOAST）能能
REINDEX SCHEMA name模式内全部索引能不能
REINDEX DATABASE name库内全部索引（不处理系统目录）能不能
REINDEX SYSTEM name系统目录索引不能不能

最小可运行示例：

sql
DROP TABLE IF EXISTS events;

CREATE TABLE events (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id    bigint      NOT NULL,
  status     text        NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_events_user ON events (user_id);

-- 最常用形式：重建单个索引
REINDEX INDEX idx_events_user;

-- 并发重建（不长期阻塞读写，但更慢、更耗资源）
REINDEX INDEX CONCURRENTLY idx_events_user;

-- 重建一张表上的所有索引
REINDEX TABLE events;
text
DROP TABLE
CREATE TABLE
CREATE INDEX
REINDEX
REINDEX
REINDEX

另一种等价写法（选项式），PostgreSQL 12 起支持：

sql
-- 与 REINDEX INDEX CONCURRENTLY 等价
REINDEX (CONCURRENTLY) INDEX idx_events_user;

-- 带详细输出
REINDEX (VERBOSE) TABLE events;

详细说明 ​

默认锁语义：为什么 REINDEX 会阻塞读 ​

这是最常见的认知误区：「重建索引用的是读操作，应该不阻塞查询吧？」

事实是：REINDEX 默认会先对被重建的索引本身加 ACCESS EXCLUSIVE 锁，再对父表加 ACCESS EXCLUSIVE 锁。

  • 对索引加 ACCESS EXCLUSIVE：任何想使用该索引的查询都被阻塞。因为解析器/规划器需要对该索引加 ACCESS SHARE 锁。
  • 对表加 ACCESS EXCLUSIVE：任何对该表的读写都被阻塞。
sql
-- 会话 A：重建一张大表的索引
REINDEX TABLE events;      -- 长时间持有 ACCESS EXCLUSIVE

-- 会话 B：立刻被阻塞，即使只是 SELECT
SELECT count(*) FROM events WHERE user_id = 42;   -- 卡在锁等待
text
会话 B 的等待视图：
  wait_event_type = 'Lock'
  wait_event      = 'relation'
  pg_locks.mode   = 'AccessShareLock'（被 A 的 AccessExclusiveLock 挡住）

结论：生产大表绝不要裸跑 REINDEX，除非你能接受一段不可读写的窗口。

共享内存 / 单用户模式除外

在单用户模式（postgres --single）下执行 REINDEX 时，锁不适用。这也是官方推荐的「修复系统目录索引」路径。

REINDEX CONCURRENTLY 的六步流程 ​

CONCURRENTLY 模式只持有 SHARE UPDATE EXCLUSIVE 锁——它不阻塞读写，只与其它 DDL、VACUUM、其它 REINDEX CONCURRENTLY 互斥。代价是它必须做两遍索引构建：

text
步骤 1：在系统目录中登记一个「临时索引」，定义与目标索引完全相同
步骤 2：第一遍扫描 —— 构建临时索引（记录快照）
        期间对表的新写入，临时索引也会同步维护
步骤 3：第二遍扫描 —— 把第一遍之后产生的增量变更补进临时索引
步骤 4：交换 —— 把临时索引升格为正式索引（改名、迁移约束与依赖），
        把它标记为 indisvalid = true
步骤 5：把旧索引标记为 indisready = false（在新事务已看不到它）
步骤 6：删除旧索引
sql
-- 注意：CONCURRENTLY 不能出现在事务块里
REINDEX INDEX CONCURRENTLY idx_events_user;
text
REINDEX

一次并发重建的开销近似「两个索引的总和」：磁盘上同时存在新旧两份索引，期间所有写入要维护两个索引，构建过程中产生大量 I/O。索引越多，总时长与资源占用越高，这正是 reindexdb -j 并行度要谨慎设置的原因。

失败与 INVALID 索引处理 ​

并发构建可能因为死锁、唯一性冲突（UNIQUE 索引遇到并发写入导致重复键）等原因中断。中断会留下残骸，命名规则如下：

后缀含义处理方式
_ccnew已构建但尚未完成交换的临时索引可 DROP，或再次并发重建让流程走完
_ccold已被替换、等待删除的旧索引可 DROP
_ccnew1、_ccold2 …同名冲突时的数字后缀同上
sql
-- 查看遗留的无效索引
SELECT c.relname AS index_name,
       i.indisvalid,
       i.indisready
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE NOT i.indisvalid OR NOT i.indisready;
text
     index_name      | indisvalid | indisready
---------------------+------------+------------
 idx_events_user_ccnew | f        | t

psql 的 \d events 也会把这些索引标记为 INVALID。

恢复路径：

sql
-- 路径一：直接丢掉残骸（如果重建本身不再需要）
DROP INDEX CONCURRENTLY idx_events_user_ccnew;

-- 路径二：再次并发重建（让交换真正完成）
REINDEX INDEX CONCURRENTLY idx_events_user;

只有 INDEX 形式能并发修复 INVALID 索引

官方明确：唯一能对 INVALID 索引执行并发重建的形式是 REINDEX INDEX CONCURRENTLY。表级/模式级/库级并发重建遇到无效索引会报错。

不能在事务块中执行的形态与其它限制 ​

以下形态会直接报错 REINDEX ... cannot run inside a transaction block：

sql
-- 错误！SCHEMA / DATABASE / SYSTEM 不能出现在事务块内
BEGIN;
REINDEX DATABASE mydb;
ROLLBACK;

其它硬限制：

限制说明
REINDEX SYSTEM 不支持并发系统目录索引不允许并发重建
排他约束索引不能并发重建例如 EXCLUDE 约束底层索引，REINDEX ... CONCURRENTLY 会报错
分区表不能直接并发重建REINDEX TABLE 一个分区表时，子分区索引需逐个处理
临时表索引不能并发重建临时表索引不支持 CONCURRENTLY
sql
-- 错误示例：排他约束的索引不支持并发重建
ALTER TABLE bookings ADD CONSTRAINT no_overlap
  EXCLUDE USING gist (room_id WITH =, during WITH &&);

REINDEX INDEX CONCURRENTLY no_overlap;   -- ERROR
text
ERROR:  cannot reindex an exclusion constraint concurrently

权限与运行环境 ​

执行 REINDEX 需要：

  • 被重建索引所在表的 MAINTAIN 权限（PostgreSQL 17 起新增，替代此前要求表 owner 的做法）；
  • REINDEX SCHEMA / DATABASE / SYSTEM 需要相应对象的拥有者权限，或 pg_maintain 角色成员资格。
sql
-- 授予某角色维护权限
GRANT MAINTAIN ON events TO app_maintainer;

-- 把角色加入 pg_maintain 预定义角色（覆盖面更广）
GRANT pg_maintain TO app_maintainer;
text
GRANT
GRANT

另一个容易忽略的细节：REINDEX 执行期间会临时把 search_path 改成 pg_catalog, pg_temp，这是为了防止用户自定义函数/操作符类劫持重建过程。所以不要依赖 search_path 来找自定义操作符类，需要时用 schema 限定名。

进度观测：pg_stat_progress_create_index ​

CREATE INDEX 与 REINDEX 共享同一个进度视图 pg_stat_progress_create_index，每个正在执行的后端一行。关键列：

列含义
pid执行该命令的后端进程号
datname / relid数据库名 / 表 OID
index_relid正在构建的索引 OID（并发模式会先指向临时索引）
commandCREATE INDEX / CREATE INDEX CONCURRENTLY / REINDEX / REINDEX CONCURRENTLY
phase当前阶段（见下表）
lockers_total / lockers_done等待的锁持有者总数 / 已完成数
blocks_total / blocks_done需扫描的总块数 / 已完成块数
tuples_total / tuples_done需处理的元组总数 / 已完成数
partitions_total / partitions_done分区总数 / 已完成分区数
sql
SELECT pid,
       command,
       phase,
       blocks_done || '/' || blocks_total  AS blocks,
       tuples_done || '/' || tuples_total  AS tuples,
       lockers_done || '/' || lockers_total AS lockers
FROM pg_stat_progress_create_index;
text
  pid  |      command       |           phase            | blocks  |  tuples  | lockers
-------+--------------------+----------------------------+---------+----------+---------
 28411 | REINDEX CONCURRENTLY| index validation: scanning | 8642/90 | 98021/9 | 0/0

并发的 REINDEX CONCURRENTLY 会依次进入这些 phase：

text
initializing
waiting for writers before build
building index
waiting for writers before validation
index validation: scanning index
index validation: sorting tuples
index validation: scanning table
waiting for old snapshots
waiting for readers before marking dead
waiting for readers before dropping

若 phase 长期停在某个 waiting for ...，通常说明有长事务或长查询挡住了流程，可以去排查 pg_stat_activity 里 xact_start 很老的事务。

reindexdb：批量重建的客户端工具 ​

对多张表、多个索引做批量重建，用命令行工具 reindexdb 比手写 SQL 更省事。

bash
# 重建整个数据库的所有索引
reindexdb --dbname=mydb --all

# 并发重建（不阻塞业务）
reindexdb --dbname=mydb --concurrently --table=events

# 只重建指定索引
reindexdb --dbname=mydb --index=idx_events_user

# 并行重建多张表的索引（4 个并行连接）
reindexdb --dbname=mydb --jobs=4 --table=events

# 只重建系统目录索引
reindexdb --dbname=mydb --system

# 组合：对整个模式并发重建，输出详细日志
reindexdb --dbname=mydb --concurrently --schema=public --verbose
text
选项速查：
  -a, --all            重建所有数据库
      --concurrently   使用 REINDEX CONCURRENTLY
  -i, --index=NAME     只重建该索引（可重复）
  -j, --jobs=N         并行任务数（每任务一个连接）
  -q, --quiet          安静模式
  -s, --system         只重建系统目录索引
  -S, --schema=NAME    只重建该模式
  -t, --table=NAME     只重建该表
      --tablespace=NAME 把重建结果放进指定表空间
  -v, --verbose        详细输出

--jobs 与 --system 不兼容

reindexdb --jobs 会开启多个数据库连接并行工作，因此不能与 --system（单用户模式相关）组合使用，也不能同时重建整个系统目录。并发重建一个库里的多个索引时，注意默认连接数上限。

重建会不会顺带「修复」损坏 ​

会，但要看损坏类型。REINDEX 从堆表重新读取数据构建索引，因此索引侧的物理损坏可以被修复。但如果是堆表本身的元组损坏，重建时读到坏页面就会失败并报错——这种情况需要更复杂的手段（如 zero_damaged_pages 或从备份恢复），REINDEX 无能为力。

sql
-- 单用户模式下修复系统目录索引（官方推荐路径）
-- 先停库，再执行：
-- postgres --single -D /var/lib/postgresql/data mydb
-- 然后在单用户提示符下：
-- REINDEX SYSTEM mydb;
text
REINDEX

实战示例 ​

下面用 300 万行的 events 表，完整演示「检测膨胀 → 评估策略 → 并发重建 → 验证」的闭环。

步骤一：造数据并制造碎片 ​

sql
DROP TABLE IF EXISTS events;

CREATE TABLE events (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id    bigint      NOT NULL,
  status     text        NOT NULL,
  payload    text,
  created_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO events (user_id, status, payload)
SELECT (random() * 100000)::bigint,
       (ARRAY['pending','running','done','failed'])[1 + (random() * 3)::int],
       repeat('x', 40)
FROM generate_series(1, 3000000);

CREATE INDEX idx_events_user ON events (user_id);

-- 制造碎片：反复删除与插入大量随机 user_id
DELETE FROM events WHERE user_id % 7 = 0;
INSERT INTO events (user_id, status, payload)
SELECT (random() * 100000)::bigint, 'done', repeat('y', 40)
FROM generate_series(1, 800000);

VACUUM ANALYZE events;
text
DROP TABLE
CREATE TABLE
INSERT 0 3000000
CREATE INDEX
DELETE 428571
INSERT 0 800000
VACUUM

步骤二：检测索引膨胀并估算收益 ​

sql
CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT 'idx_events_user'::text AS idx,
       pg_size_pretty(pg_relation_size('idx_events_user')) AS size,
       (pgstatindex('idx_events_user')).avg_leaf_density  AS avg_leaf_density,
       (pgstatindex('idx_events_user')).leaf_fragmentation AS leaf_frag,
       (pgstatindex('idx_events_user')).deleted_pages     AS deleted_pages;
text
        idx        |  size   | avg_leaf_density | leaf_frag | deleted_pages
-------------------+---------+------------------+-----------+---------------
 idx_events_user   | 112 MB  | 62.41            | 47.86     | 18

avg_leaf_density 62% 意味着近四成叶子页空间是空的；leaf_fragmentation 接近 48% 说明逻辑相邻的键在物理上散得很开。典型的「值得重建」信号。

步骤三：并发重建并观测进度 ​

sql
-- 会话 A：并发重建（可在业务高峰期执行，但更慢）
REINDEX INDEX CONCURRENTLY idx_events_user;
text
REINDEX
sql
-- 会话 B：另开一个连接观察进度
SELECT phase, blocks_done || '/' || blocks_total AS blocks
FROM pg_stat_progress_create_index
WHERE command = 'REINDEX CONCURRENTLY';
text
             phase              |  blocks
--------------------------------+---------
 index validation: scanning index| 4210/9000

步骤四：验证重建效果 ​

sql
SELECT pg_size_pretty(pg_relation_size('idx_events_user')) AS size_after,
       (pgstatindex('idx_events_user')).avg_leaf_density  AS density_after,
       (pgstatindex('idx_events_user')).leaf_fragmentation AS frag_after;
text
 size_after | density_after | frag_after
------------+---------------+------------
 74 MB      | 89.72         | 3.11

体积从 112 MB 降到 74 MB（-34%），叶子密度升到约 90%，碎片率降到 3%。重建后建议补一次 ANALYZE：

sql
ANALYZE events;
text
ANALYZE

注意事项 ​

  • 默认 REINDEX 会阻塞读。 它先锁索引（ACCESS EXCLUSIVE），使用该索引的 SELECT 全部卡住。

    sql
    -- 错误认知：以为这里只影响写
REINDEX TABLE events;   -- 实际上 SELECT ... WHERE user_id = 1 也被阻塞

  • CONCURRENTLY 不是「免费的午餐」。 它要写两份索引、扫描两遍，总耗时可能翻倍，且需要更大的磁盘空间。

  • CONCURRENTLY 不能在事务块内执行。 应用层如果用框架把 SQL 包在事务里，会直接失败。

    sql
    -- 错误！
BEGIN;
REINDEX INDEX CONCURRENTLY idx_events_user;
COMMIT;

  • 失败会留下 _ccnew / _ccold 残骸。 必须专门巡检并清理，否则这些无效索引会持续消耗磁盘与写入开销。

    sql
    -- 巡检无效索引
SELECT indexrelid::regclass, indisvalid, indisready
FROM pg_index WHERE NOT indisvalid;

  • 不要在业务高峰对「所有索引」做并发重建。 REINDEX DATABASE CONCURRENTLY 会依次处理海量索引，期间写放大会持续很久。

  • REINDEX SYSTEM 与 --jobs 冲突，且不能在事务内。 系统目录重建应在维护窗口用单用户模式完成。

  • 排他约束、临时表索引不能并发重建。 误用会直接报错，需改用非并发模式或另寻方案。

  • 重建不等于修堆表。 堆表页面损坏时 REINDEX 会失败，别指望它能「自愈」一切。

    sql
    -- 若重建时报块级 I/O 错误，说明堆表有损坏，需走备份恢复流程
REINDEX TABLE events;   -- ERROR: invalid page in block 1234

  • 重建后统计信息不一定更新。 索引变了，但优化器的行数估算依赖 pg_statistic，务必补 ANALYZE。

最佳实践 ​

  • 先量化再重建。 用 pgstatindex() 看 avg_leaf_density / leaf_fragmentation，用 pg_relation_size() 看体积，有明确收益再动手。

    sql
    SELECT pg_size_pretty(pg_relation_size('idx_events_user')) AS size,
       (pgstatindex('idx_events_user')).avg_leaf_density    AS density;

  • 优先 CONCURRENTLY，除非处于维护窗口。 生产库几乎总应选并发模式。

    sql
    REINDEX INDEX CONCURRENTLY idx_events_user;

  • 重建后立刻 ANALYZE。 让优化器拿到最新分布，避免计划退化。

    sql
    REINDEX INDEX CONCURRENTLY idx_events_user;
ANALYZE events;

  • fillfactor 提前设好再重建才有意义。 重建会把新参数应用到最后一份索引上。

    sql
    ALTER INDEX idx_events_user SET (fillfactor = 90);
REINDEX INDEX CONCURRENTLY idx_events_user;

  • 用进度视图估算剩余时间。 按 blocks_done / blocks_total 与已用时间线性外推，决定是否让任务继续。

    sql
    SELECT command, phase,
       round(100.0 * blocks_done / nullif(blocks_total,0), 1) AS pct
FROM pg_stat_progress_create_index;

  • 批量重建交给 reindexdb，并限制并行度。 并行度等于同时打开的连接数，别压垮实例。

    bash
    reindexdb --dbname=mydb --concurrently --table=events --jobs=2 --verbose

  • 把「无效索引巡检」纳入日常运维。 每周查一次 pg_index.indisvalid = false，及时清理残骸。

    sql
    SELECT indexrelid::regclass AS bad_index, indisvalid, indisready
FROM pg_index WHERE NOT indisvalid OR NOT indisready;

  • 别把 REINDEX 当常规操作。 健康的 B-Tree 不需要频繁重建；只在检测到膨胀、损坏或参数变更时执行。

  • 记录重建前后的指标，形成基线。 体积、avg_leaf_density、关键查询的 Buffers 变化，都是下次决策的依据。

    sql
    SELECT indexrelid::regclass AS idx,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_index WHERE indrelid = 'events'::regclass;

  • 重建前确认磁盘余量。 并发重建期间新旧两份索引并存，需要约「原索引大小 + 新索引大小」的额外空间。

    bash
    df -h /var/lib/postgresql/data

下一节

继续学习：索引膨胀检测

参考链接 ​