适用场景

本文适用于 PostgreSQL 13 及以上版本的生产库：为了避免普通 CREATE INDEX 长时间阻塞写入，运维或开发人员改用 CREATE INDEX CONCURRENTLY，但命令执行数十分钟后失败、被取消，或者发布系统超时退出。重新执行时又提示索引已存在，查询计划却仍然走顺序扫描。

本文重点处理三件事：确认索引究竟是在等待、构建还是已经失败；判断 INVALID 索引是否可以直接删除；在不长时间阻塞业务写入的前提下完成重建和验证。

现象描述

常见现场包括：

  • 发布日志出现 canceling statement due to statement timeout、死锁、磁盘空间不足或唯一性冲突；
  • \d public.orders 能看到索引，但索引名称后带有 INVALID；
  • CREATE INDEX CONCURRENTLY 重试时返回 relation already exists；
  • EXPLAIN 仍然显示 Seq Scan，而不是预期的 Index Scan 或 Bitmap Index Scan；
  • 表的写入量、WAL 或 I/O 增加，但索引没有带来查询收益。

不要只凭“索引文件存在”判断创建成功。PostgreSQL 是否允许查询规划器使用索引，以系统目录中的 pg_index.indisvalid 为准。

为什么并发建索引容易“看起来卡住”

普通 CREATE INDEX 只扫描一次表，但会阻塞目标表上的 INSERT、UPDATE 和 DELETE。CONCURRENTLY 为了允许业务继续写入，需要经历多个事务和两次表扫描，并在不同阶段等待旧事务或旧快照退出。

因此，命令长时间没有输出不一定是故障。它可能正在：

  1. 等待可能修改目标表的旧事务结束；
  2. 扫描表并构建索引；
  3. 校验第一次扫描期间产生的新数据；
  4. 等待早于第二次扫描的快照退出；
  5. 将索引标记为可供查询使用。

如果进程在完成前因超时、人工取消、死锁、表达式计算错误或唯一性冲突而退出，PostgreSQL 可能保留一个 INVALID 索引。它不会被查询规划器使用；若 indisready=true，后续写入仍需维护它，不能长期放置不管。

第一步：确认是在运行、等待还是已经失败

先查看正在执行的并发建索引任务：

SELECT
    p.pid,
    p.datname,
    p.relid::regclass AS table_name,
    p.index_relid::regclass AS index_name,
    p.command,
    p.phase,
    p.lockers_done,
    p.lockers_total,
    p.blocks_done,
    p.blocks_total,
    p.tuples_done,
    p.tuples_total
FROM pg_stat_progress_create_index AS p
ORDER BY p.pid;

关键字段的判断方法：

  • phase 表示当前阶段；若长时间停留在等待旧事务或等待快照阶段，应继续找阻塞者；
  • blocks_done / blocks_total 适合观察表扫描进度，但不同阶段可能重新计数；
  • lockers_done / lockers_total 表示已处理和待处理的旧事务数量；
  • 视图中没有目标任务，且客户端已经报错，通常表示构建进程已经退出，需要检查系统目录。

对正在等待的任务，查询阻塞它的会话：

SELECT
    a.pid AS waiting_pid,
    a.query_start AS waiting_since,
    blocker.pid AS blocker_pid,
    blocker.usename AS blocker_user,
    blocker.application_name AS blocker_app,
    blocker.xact_start AS blocker_xact_start,
    blocker.state AS blocker_state,
    left(blocker.query, 200) AS blocker_query
FROM pg_stat_activity AS a
CROSS JOIN LATERAL unnest(pg_blocking_pids(a.pid)) AS blocked_by(pid)
JOIN pg_stat_activity AS blocker ON blocker.pid = blocked_by.pid
WHERE a.pid IN (SELECT pid FROM pg_stat_progress_create_index)
ORDER BY blocker.xact_start NULLS LAST;

优先关注运行时间很长、状态为 idle in transaction 的阻塞会话。不要看到阻塞就直接执行 pg_terminate_backend()；应先确认会话所属应用、事务内容和回滚影响，再由业务负责人决定等待、取消查询还是终止连接。

第二步：盘点所有 INVALID 索引

下面的只读 SQL 可列出当前数据库中的无效索引、定义和占用空间：

SELECT
    ns.nspname AS schema_name,
    tbl.relname AS table_name,
    idx.relname AS index_name,
    pi.indisunique AS is_unique,
    pi.indisready AS is_ready,
    pi.indislive AS is_live,
    pg_size_pretty(pg_relation_size(idx.oid)) AS index_size,
    pg_get_indexdef(idx.oid) AS index_definition
FROM pg_index AS pi
JOIN pg_class AS idx ON idx.oid = pi.indexrelid
JOIN pg_class AS tbl ON tbl.oid = pi.indrelid
JOIN pg_namespace AS ns ON ns.oid = tbl.relnamespace
WHERE NOT pi.indisvalid
ORDER BY pg_relation_size(idx.oid) DESC;

几个状态不要混淆：

  • indisvalid=false：查询规划器不能安全使用该索引；
  • indisready=true：新写入仍会维护该索引；
  • indislive=false：索引处于删除流程中，应先确认是否有正在进行的维护操作。

同时保存最初的数据库错误日志。系统目录只能说明当前状态，无法还原失败原因。日志中应重点查找目标索引名、执行 PID、SQLSTATE、唯一性冲突、死锁、空间不足和语句超时。

第三步：根据失败原因选择修复路径

路径一：普通非唯一索引创建失败

对于刚创建、未绑定约束、确定没有查询可依赖的 INVALID 普通索引，最清晰的处理方式通常是并发删除后重新创建：

DROP INDEX CONCURRENTLY IF EXISTS public.idx_orders_pending_tenant_created_at;

CREATE INDEX CONCURRENTLY idx_orders_pending_tenant_created_at
ON public.orders (tenant_id, created_at DESC)
INCLUDE (id, amount)
WHERE status = 'pending';

这两条命令必须分别执行，不能放在 BEGIN ... COMMIT 事务块中。发布工具如果默认把整个迁移文件包进事务，需要为这一步单独关闭事务包装。

示例索引对应的查询应同时包含 tenant_id 和 status = 'pending'。如果业务 SQL 使用不同条件，即使索引有效，规划器也可能合理地不使用它。

路径二：保留原定义并在线重建

如果索引定义确认正确，也可以直接在线重建单个 INVALID 索引：

REINDEX INDEX CONCURRENTLY public.idx_orders_pending_tenant_created_at;

REINDEX INDEX CONCURRENTLY 同样不能在事务块中执行。它会创建临时索引并在切换后清理旧索引，期间需要额外磁盘空间和更多 I/O。执行前应确认表空间余量、业务低峰窗口和 I/O 告警阈值。

如果失败后遗留名称带 _ccnew 的 INVALID 临时索引，说明并发重建的新副本没有完成，官方建议删除该临时索引后重试；名称带 _ccold 的 INVALID 索引通常是切换后的旧副本未能删除，此时应在确认新索引有效后删除旧副本。不要仅凭后缀操作，必须同时核对 pg_get_indexdef()、有效状态和依赖关系。

路径三：唯一索引因重复数据失败

唯一索引构建失败时，先解决数据冲突，不能机械重试。以 (tenant_id, external_order_no) 为唯一键为例：

SELECT
    tenant_id,
    external_order_no,
    count(*) AS duplicate_count,
    array_agg(id ORDER BY id) AS row_ids
FROM public.orders
WHERE external_order_no IS NOT NULL
GROUP BY tenant_id, external_order_no
HAVING count(*) > 1
ORDER BY duplicate_count DESC
LIMIT 100;

确认重复记录的业务归属后，通过合并、作废或补偿流程清理数据，再创建唯一索引：

CREATE UNIQUE INDEX CONCURRENTLY uk_orders_tenant_external_no
ON public.orders (tenant_id, external_order_no)
WHERE external_order_no IS NOT NULL;

并发唯一索引在第二次扫描开始后就可能对其他事务执行唯一性检查，即使命令最终失败，其他会话也可能提前收到唯一冲突。上线前应让应用侧正常处理唯一约束错误，并避免把该错误误报成数据库不可用。

发布前的预防配置

1. 不给建索引设置过短的语句超时

大表索引耗时受表大小、I/O、旧事务和并发写入影响。可只对当前维护会话调整超时，而不是修改全局配置：

SET lock_timeout = '5s';
SET statement_timeout = '2h';
CREATE INDEX CONCURRENTLY idx_orders_pending_tenant_created_at
ON public.orders (tenant_id, created_at DESC)
INCLUDE (id, amount)
WHERE status = 'pending';

lock_timeout 用于避免在获取必要锁时无限等待；statement_timeout 必须覆盖完整构建和等待时间。命令失败后仍要检查 INVALID 索引，不能认为超时会自动清理所有对象。

2. 先治理长事务

维护窗口前检查长事务和空闲事务：

SELECT
    pid,
    usename,
    application_name,
    state,
    xact_start,
    now() - xact_start AS transaction_age,
    left(query, 200) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND pid <> pg_backend_pid()
ORDER BY xact_start;

若应用长期保留事务或快照，并发索引可能在扫描前后长时间等待。应先修复事务边界，并设置与业务相符的 idle_in_transaction_session_timeout，而不是每次维护时人工杀连接。

3. 控制同表维护并发

同一张表一次只能运行一个并发索引构建。不要让多个发布任务同时为同表创建索引；应在迁移平台按表串行，并为任务设置唯一执行标识，避免超时后自动并发重试。

修复后的验证清单

先确认索引状态：

SELECT
    idx.relname AS index_name,
    pi.indisvalid,
    pi.indisready,
    pi.indislive,
    pg_size_pretty(pg_relation_size(idx.oid)) AS index_size
FROM pg_index AS pi
JOIN pg_class AS idx ON idx.oid = pi.indexrelid
WHERE pi.indexrelid = 'public.idx_orders_pending_tenant_created_at'::regclass;

期望三个布尔值均为 true。随后使用真实业务条件验证执行计划：

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, amount, created_at
FROM public.orders
WHERE tenant_id = 42
  AND status = 'pending'
ORDER BY created_at DESC
LIMIT 50;

验证时重点看：

  • 是否出现目标索引，以及实际返回行数是否符合预期；
  • shared read、shared hit 和总执行时间是否比基线改善；
  • 发布期间数据库 I/O、WAL、复制延迟和业务 P95 是否异常；
  • 全库 INVALID 索引盘点结果是否归零；
  • 索引是否真的服务于高频查询，避免创建成功后长期无人使用。

小表或低选择性查询继续使用顺序扫描可能是正确选择，不要通过 SET enable_seqscan = off 伪造成功。应以真实参数、真实数据分布和多次执行结果判断收益。

注意事项

  • DROP INDEX CONCURRENTLY、CREATE INDEX CONCURRENTLY 和 REINDEX ... CONCURRENTLY 都不要放入事务块；
  • 维护期间需要为新旧索引同时预留磁盘空间，不能只按最终索引大小估算；
  • 不要删除支撑主键、唯一约束或复制身份的索引，操作前检查依赖；
  • INVALID 唯一索引不能证明现有数据满足唯一性，必须单独检查重复数据；
  • 迁移系统应记录开始时间、结束时间、索引名、表名、SQLSTATE 和最终有效状态，但不要记录连接密码；
  • 若版本低于 PostgreSQL 12，不能使用 REINDEX CONCURRENTLY，应根据实际版本选择删除重建或维护窗口。

总结

并发建索引不是一次普通 DDL：它通过多事务、两次扫描和等待旧快照换取持续写入能力。失败后最危险的不是命令报错，而是把残留的 INVALID 索引误认为已经生效，让查询继续慢、写入继续承担维护成本。

可靠的处理顺序是：先用 pg_stat_progress_create_index 判断当前阶段，再用 pg_blocking_pids() 找阻塞者；任务退出后盘点 pg_index 状态并保存错误原因；普通索引按边界删除重建，唯一索引先清理重复数据；最后同时验证索引有效性、执行计划和业务指标。把这套检查纳入数据库迁移平台，才能让在线建索引真正具备可观测、可恢复和可验收的闭环。

参考资料