PostgreSQL 的零停机索引变更：实用操作手册

为什么索引变更会导致停机（以及如何避免）

索引工作听起来无害——“只是”增加一个辅助结构。但在 PostgreSQL 中，构建、删除或替换索引会获取可能阻塞其他会话的锁。如果表很繁忙，等待会堆积，应用就开始感觉像坏掉了一样。

停机很少表现为一个干净的下线提示。它常常以页面卡顿、后台任务落后以及越来越多的请求在数据库上等待的队列出现。有人点“搜索”就超时，同时支持工具和管理界面也因为简单查询无法获得所需的锁而变得缓慢。

“只是夜间运行”会失败有两个常见原因。很多系统从未真正安静（全球用户、批处理作业、ETL、备份）。而且索引操作可能比你预期的要久，因为它们需要读取大量数据并竞争 CPU 与磁盘。如果维护窗口在构建中途结束，你就不得不在等待更久和中断工作之间做选择。

零停机的索引变更并非魔法。关键是选择最少阻塞的操作，设置护栏（超时和磁盘检查），并在运行时监视数据库。

本手册侧重于实用的生产习惯：

• 当需要保持读写流量时，优先使用并发（concurrent）索引构建。
• 监控锁和构建进度，以便及早响应。
• 如果变更导致回归或耗时过长，要有回滚路径。

本文不涵盖：深度索引设计理论、广泛的查询调优或会重写大量数据的模式重构。

索引工作背后的简单锁模型

PostgreSQL 使用锁在多个会话触及同一表时保持数据正确。锁只是一个规则，规定谁现在可以读或写某个对象，谁必须等待。

大多数时候你不会注意到锁，因为 PostgreSQL 可以使用轻量级模式让常规查询继续运行。DDL 不同。当你创建或删除索引时，PostgreSQL 需要对表有足够的控制来保持目录和数据一致。所需控制越多，其他会话可能被迫等待的时间就越长。

构建索引与使用索引的区别

从锁的角度看，使用索引通常开销很小。SELECT、UPDATE 和 DELETE 查询可以在其他会话同时读取或维护索引。

构建索引就不一样了。PostgreSQL 必须扫描表、对键进行排序或哈希，并把新的结构写到磁盘。那项工作需要时间，而时间是把“很小的锁”变成生产环境中“严重问题”的导火索。

CONCURRENTLY 改变了什么（和没有改变什么）

普通的 CREATE INDEX 会获取强锁，在整个构建期间阻塞写操作。CREATE INDEX CONCURRENTLY 的设计初衷是让索引构建期间常规读写继续流动。

但“并发”并不等于“无锁”。开始和结束仍会有短暂的锁窗口，如果其他对象持有不兼容的锁，构建仍可能失败或等待。

关键的结果是：

• 非并发构建会阻塞表上的插入、更新和删除。
• 并发构建通常允许读写，但可能被长事务拖慢或阻塞。
• 完成阶段仍然需要短暂的锁，因此非常繁忙的系统会看到短时间等待。

选择正确的方法：并发还是非并发

在更改索引时有两种主要选项：正常构建索引（速度快，但会阻塞），或使用 CONCURRENTLY 构建（通常对应用流量无阻塞，但更慢且对长事务更敏感）。

何时选择 CONCURRENTLY

当表正在提供真实流量且你不能暂停写入时，使用 CREATE INDEX CONCURRENTLY。通常在以下情况更安全：

• 表很大，普通构建可能需要几分钟或几小时。
• 表有稳定的写入，而非仅有读取。
• 你无法安排真正的维护窗口。
• 你需要先构建并验证，然后再删除旧索引。

何时接受普通索引构建

当表小、流量低或你有可控的窗口时，普通的 CREATE INDEX 可能没问题。它通常完成得更快且运行更简单。

如果在预发布环境中构建一直很快，并且你可以临时停止写操作（哪怕很短），可以考虑普通方式。

如果你需要唯一性，早做决定。CREATE UNIQUE INDEX CONCURRENTLY 可用，但如果存在重复值会失败。在许多生产系统中，查找并修复重复值才是真正的工作。

在触碰生产之前的预检

大多数问题发生在命令真正开始之前。几个检查能帮助你避免两种大惊喜：意外阻塞和索引构建比预期耗时或占用更多空间。

1. 确保你不在事务内部。CREATE INDEX CONCURRENTLY 如果在 BEGIN 后运行会失败，有些 GUI 工具会悄悄把语句包装在事务中。如果不确定，请打开一个新的会话并只在该会话中运行索引命令。

2. 评估时间和磁盘预期。并发构建通常比普通构建更久，并在运行时需要额外的工作空间。为新索引加上临时开销做计划，并确认有足够的空闲磁盘。

3. 设置与目标匹配的超时。你希望在无法获取锁时快速失败，但又不希望由于过短的 statement timeout 在构建中途终止会话。

4. 采集基线数据。你需要证据证明变更有效并能快速发现回归。记录变更前的快照：慢查询时间、具有代表性的 EXPLAIN (ANALYZE, BUFFERS)，以及 CPU、IO、连接数和空闲磁盘的简要情况。

许多团队使用的安全会话设置（按需调整）：

-- Run in the same session that will build the index
SET lock_timeout = '2s';
SET statement_timeout = '0';

步骤：用 CONCURRENTLY 创建索引

当你需要保持应用流量并且能接受更长的构建时间时，使用 CREATE INDEX CONCURRENTLY。

首先，明确你要构建的内容：

• 明确列的顺序（这很重要）。
• 考虑部分索引是否足够。如果大多数查询只筛选到“active”行，部分索引会更小、更快且维护成本更低。

一个安全的执行流程是：写下目标和索引名，在事务块外运行构建，观察其完成，然后在移除其他索引前验证 planner 能够使用它。

-- Example: speed up searches by email for active users
CREATE INDEX CONCURRENTLY idx_users_active_email
ON public.users (email)
WHERE status = 'active';

-- Validate it exists
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'users';

-- Check the plan can use it
EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM public.users
WHERE status = 'active' AND email = '[email protected]';

为了审计记录进度，记下开始时间、结束时间和你看到的任何等待。在它运行时，你可以从另一个会话查询 pg_stat_progress_create_index。

验证不仅仅是“索引存在”。确认 planner 能选择它，然后观察部署后的真实查询时间。如果新索引未被使用，不要急着删除旧索引。先修正查询或索引定义。

步骤：在不阻塞的情况下替换或移除索引

最安全的模式是先添加新索引，让流量受益，然后再删除旧索引。这样你始终保留可回退的工作方案。

用更安全的顺序替换旧索引

1. 使用 CREATE INDEX CONCURRENTLY 创建新索引。

2. 验证它正在被使用。对你关心的慢查询运行 EXPLAIN，并观察索引使用情况随时间的变化。

3. 仅在确认后并发删除旧索引。如果风险高，可在完整的业务周期内同时保留两个索引再做删除。

删除索引：何时 CONCURRENTLY 有效（何时无效）

对于你自己创建的普通索引，DROP INDEX CONCURRENTLY 通常是正确选择。两个注意点：它不能在事务块内运行，并且在开始和结束时仍需要短暂锁，因此可能被长事务延迟。

如果索引是由 PRIMARY KEY 或 UNIQUE 约束产生的，通常不能直接删除。你必须用 ALTER TABLE 修改约束，这可能会获取更强的锁。把这类改动作为单独的、有计划的维护操作来处理。

重命名索引以提高可读性

重命名（ALTER INDEX ... RENAME TO ...）通常很快，但如果工具或迁移脚本引用索引名，需谨慎。一个更安全的习惯是从一开始就取一个清晰的名字。

如果仍然需要旧索引

有时两种查询模式需要不同的索引。如果重要查询仍依赖旧索引，就保留它。考虑调整新索引（列顺序、部分条件），而不是强行删除旧索引。

在索引构建时监控锁与进度

即使使用 CREATE INDEX CONCURRENTLY，你也应实时观察正在发生的情况。大多数意外事件来自两件事：你未注意到的阻塞会话，或将构建卡住的长事务。

发现阻塞会话（谁在阻塞谁）

先找出在等待锁的会话：

SELECT
  a.pid,
  a.usename,
  a.application_name,
  a.state,
  a.wait_event_type,
  a.wait_event,
  now() - a.xact_start AS xact_age,
  left(a.query, 120) AS query
FROM pg_stat_activity a
WHERE a.wait_event_type = 'Lock'
ORDER BY xact_age DESC;

如果你需要确切的阻塞者，跟随 blocked_pid 到 blocking_pid：

SELECT
  blocked.pid  AS blocked_pid,
  blocking.pid AS blocking_pid,
  now() - blocked.xact_start AS blocked_xact_age,
  left(blocked.query, 80)  AS blocked_query,
  left(blocking.query, 80) AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY (pg_blocking_pids(blocked.pid))
WHERE blocked.wait_event_type = 'Lock';

观察构建进度与“卡住”信号

PostgreSQL 会暴露索引构建进度。如果长时间看不到任何进展，去找长事务（常见的是处于 idle 状态但持有旧快照的会话）。

SELECT
  pid,
  phase,
  lockers_total,
  lockers_done,
  blocks_total,
  blocks_done,
  tuples_total,
  tuples_done
FROM pg_stat_progress_create_index;

同时关注系统压力：磁盘 IO、复制延迟和上升的查询时间。并发构建对可用性更友好，但仍然需要读取大量数据。

在生产中行之有效的简单规则：

• 如果进度在推进且用户影响低，则继续等待。
• 如果构建被你无法安全结束的长事务卡住，则取消并重排。
• 如果 IO 在高峰流量期间影响面向客户的查询，则暂停。
• 只有作为最后手段才终止，并且在确认会话在做什么后再操作。

为了团队沟通，把更新说清楚：开始时间、当前阶段、被阻塞的对象（如果有）以及下次检查时间。

回滚计划：如何安全回退

只有在你在开始之前就计划好退出方案，索引变更才会保持低风险。最安全的回滚往往不是戏剧性的撤销，而是简单地停止新构建并保留旧索引。

常见的索引工作失败方式

大多数生产失败是可预见的：构建触发超时、有人在事件期间取消、服务器磁盘不足，或构建与常规流量竞争导致面向用户的延迟飙升。

使用 CREATE INDEX CONCURRENTLY 时，取消通常对应用是安全的，因为查询会继续运行。代价是清理工作：被取消或失败的并发构建可能会留下一个无效的索引。

安全取消与清理规则

取消并发构建并不像普通事务那样回滚。PostgreSQL 可能会留下一个存在但对 planner 无效的索引。

-- Cancel the session building the index (use the PID you identified)
SELECT pg_cancel_backend(\u003cpid\u003e);

-- If the index exists but is invalid, remove it without blocking writes
DROP INDEX CONCURRENTLY IF EXISTS your_index_name;

在删除之前，确认你看到的是什么：

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 = 'your_index_name';

如果 indisvalid = false，它不会被使用，删除是安全的。

替换现有索引时的实用回滚清单：

• 在新索引完全构建并验证之前保留旧索引。
• 如果新构建失败或被取消，使用并发方式删除无效的新索引。
• 如果你已经删除了旧索引，用 CREATE INDEX CONCURRENTLY 重新创建以恢复之前的状态。
• 如果磁盘压力导致失败，先释放空间再重试。
• 如果超时导致失败，安排一个更安静的窗口而不是强行重试。

示例：你为一个管理搜索启动了新索引，运行了 20 分钟，随后磁盘告警触发。取消构建， 并发删除无效索引，让旧索引继续服务。释放空间后再重试，无需用户可见的中断。

导致意外中断的常见错误

大多数与索引相关的中断并不是因为 PostgreSQL “慢”。它们发生是因为一个小细节把一次安全的变更转变为阻塞操作。

1) 在事务内启动并发构建

CREATE INDEX CONCURRENTLY 不能运行在事务块内。许多迁移工具默认把每个变更包装在一个事务中。结果要么是硬错误（最好情况），要么是在部署时产生混乱并不断重试。

在运行迁移前，确认你的工具能在无外层事务的情况下运行该语句，或把迁移拆成一个特殊的非事务步骤。

2) 在高峰流量期间启动

并发索引构建减少了阻塞，但仍会增加负载：更多读取、更多写入，以及对 autovacuum 的压力。在流量高峰的部署窗口启动构建，是制造看起来像中断的缓慢状况的常见原因。

选择一个安静时间段，并像对待任何生产维护那样对待它。

3) 忽视长事务

单个长事务可能会阻碍并发构建的清理阶段。索引可能看起来在推进，然后在接近完成时因等待旧快照消失而停滞。

养成习惯：在开始前检查长事务，如果进度停滞再检查一次。

4) 删除错误对象（或破坏约束）

团队有时凭记忆按名字删除索引，或删除支撑约束的索引。如果删除了错误的对象，可能会丢失唯一性约束或立即回退查询性能。

快速安全检查：在目录中核对索引名，确认它是否支撑某个约束，复核模式与表，且把“创建新索引”和“删除旧索引”分开。开始前准备好回滚命令。

现实示例：加速管理后台搜索

一个常见痛点是在预发布感觉很快但生产中非常慢的管理搜索。假设你有一个大型 tickets 表（数千万行），在内部管理面板背后，客服经常搜索“某客户的未关闭工单，按最新排序”。

查询如下：

SELECT id, customer_id, subject, created_at
FROM tickets
WHERE customer_id = $1
  AND status = 'open'
ORDER BY created_at DESC
LIMIT 50;

对 (customer_id, status, created_at) 的全索引能帮助查询，但它会为每次工单更新（包括关闭的工单）增加写开销。如果大多数行不是 open，部分索引通常是更简单的收益：

CREATE INDEX CONCURRENTLY tickets_open_by_customer_created_idx
ON tickets (customer_id, created_at DESC)
WHERE status = 'open';

在生产中的安全时间线：

• 预检：确认查询形态稳定并且表有足够空闲磁盘用于新索引构建。
• 构建：在独立会话中运行 CREATE INDEX CONCURRENTLY，并设置明确的超时。
• 验证：运行 ANALYZE tickets; 并确认 planner 使用新索引。
• 清理：一旦确信，使用 DROP INDEX CONCURRENTLY 删除任何已冗余的旧索引。

成功的标志：

• 管理搜索从数秒降至几十毫秒。
• 构建期间常规读写继续工作。
• 构建时 CPU 与磁盘 IO 升高但仍在可接受范围内。
• 你可以给出清晰的前后对比数据：查询时间、扫描行数和锁历史。

快速清单与后续步骤

把索引工作当成一次小型的生产发布来处理：准备、运行时监控、然后在清理前验证结果。

开始前：

• 设置超时，避免意外锁无限等待。
• 确认有足够的空闲磁盘空间用于新索引构建。
• 查找可能拖慢构建的长事务。
• 选择低流量窗口并定义“完成”的标准。
• 现在就写下你的回滚计划。

运行时：

• 监视阻塞与锁等待链。
• 用 pg_stat_progress_create_index 跟踪构建进度。
• 关注应用症状：错误率、超时和与该表相关的慢端点。
• 如果锁等待增加或用户可见超时飙升，准备取消。
• 记录发生的事情：开始时间、结束时间和任何告警。

完成后，确认索引有效，运行一两个关键查询查看计划与时间是否改善，然后再以非阻塞方式移除旧索引。

如果你经常做这类工作，把它变成可重复的交付步骤：一份小的运行手册、在近似生产数据的预发布环境中排练、以及明确的负责人监视构建。

如果你使用 AppMaster (appmaster.io) 来构建内部工具或管理面板，把数据库变更（如索引构建）作为与后端更新相同的发布清单项来处理会很有帮助：有度量、有监控，并且可以快速执行回滚。