跳转到主要内容

26 变更模式，服务照常

用锁超时、NOT VALID 约束、并发索引和扩展收缩，在繁忙数据库上发布模式变更而不造成停顿。

现在你知道队列为什么会形成了。一个 ALTER 在报表后面等待排他锁，后面的每个请求也都在等待，即使只是普通读取（一个等待中的锁会阻塞后面的所有人）。你的连接池被填满，网站因为一次“什么也没做”的迁移而宕机。GoCardless 记录过完全相同的情况：一次很快的外键迁移排在长时间读取后面，API 请求又排在迁移后面，API 大约宕机了 15 秒。

这一章讲的是另一半：周二下午两点发布模式变更，而不形成那条队列。

模式变更造成伤害，不是因为它们慢，而是因为它们锁住了什么以及锁了多久。因此，每条语句都要问两个问题：它取得什么锁，以及它持有锁多久？几乎每个危险变更都有安全版本：同一个变更拆成几个步骤，让弱锁持有几分钟，或让强锁只持有几毫秒。下面就是这些拆分，以及测量结果。

所以，迁移的第一条规则与 DDL 毫无关系。

规则一：lock_timeout 和重试

在每个迁移会话上设置 lock_timeout。它到期后，语句会失败（canceling statement due to lock timeout），离开队列，后面的流量也会重新流动，即使报表仍然没有结束。

动手验证 · lab 25-migrations
select lab.prove('after the lock timeout, the lookup completes while the report is still open',
  (select id from dblink_get_result('web') t(id bigint)) = 42
  and (select state from pg_stat_activity where pid = :report_pid) = 'idle in transaction');

完整实验脚本

这个错误说明系统正在正常工作。请求排队几秒不会被察觉；无上限的等待就是一次故障。失败的语句什么也没有改变，因此要在有上限的尝试次数内重试它（只有超时而没有重试，会把本来能完成的迁移变成失败的发布）：

# retry a DDL statement up to 10 times with a short lock_timeout
for i in $(seq 1 10); do
  if psql "$DATABASE_URL" -v ON_ERROR_STOP=1 \
       -c "set lock_timeout = '2s'" \
       -c "alter table events add column note text"; then
    exit 0
  fi
  echo "attempt $i failed, retrying" >&2
  sleep $((i * 2))
done
exit 1

把它放进迁移工具的连接初始化设置中，设为 1 到 5 秒：足够长，可以穿过短事务之间的空隙；又足够短，可以让停顿不被察觉。如果重试仍然不断失败，就找出阻塞者，通常是长事务或处于 idle in transaction 的会话（backend_xmin 会指出阻碍清理的事务）。

还要设置一个宽松的 statement_timeout，这样意外重写 500 GB 的表会在几分钟内失败，而不是持有排他锁一小时。只有在并发构建索引（以及执行同类操作 reindex concurrently 和 drop index concurrently）时，才把这两个超时都关闭（单独运行并发构建，不设超时，并检查索引是否有效）。

每次变更都是系统目录编辑、扫描或重写

拿到锁之后，重要的是操作会持续多久。变更要么只编辑几行系统目录记录（无论表有多大都只需几毫秒），要么扫描每一行来检查某件事，要么重写表及其索引；后一种操作的时间、磁盘和 WAL 都会随着表增长。在 events 上（275 MB、200 万行），不同做法的区别，是一次不被察觉的发布与一次持续 9 秒的冻结；在 CI 的 1,000 行数据上，你根本分辨不出它们。

因此，要在用生产数据恢复出的副本上测试迁移（这也验证你的备份），并观察两个数字。重写会改变 pg_relation_filenode，也就是表所在的文件；扫描会增加 seq_scan，即表的完整扫描次数。

目标锁是 ShareUpdateExclusive，它既不阻塞读取，也不阻塞写入（大多数 ALTER TABLE 形式都会取得 ACCESS EXCLUSIVE）。下面的每种安全模式，都会让变更中耗时较长的部分在这种锁下运行。

常量默认值没有代价；易变默认值会重写

从 PostgreSQL 11 开始，添加带常量默认值的列只改系统目录。易变默认值不是：

alter table events add column source text not null default 'web';
select pg_relation_filenode('events');
alter table events add column ingest_id uuid default gen_random_uuid();
select pg_relation_filenode('events');
Time: 65.657 ms
 pg_relation_filenode
                16943
...
Time: 9236.902 ms (00:09.237)
 pg_relation_filenode
                17220

第一个 filenode 就是表原来拥有的那个：常量没有触碰任何行。Postgres 把 'web' 存在系统目录中，读取旧行时再提供它。UUID 需要每行有不同的值，因此 Postgres 在 AccessExclusive 下重写了全部 200 万行，耗时 9 秒。规则是易变性，而不是有没有调用函数：now() 是稳定的，只计算一次，会像常量一样保存。

对于逐行值，要把它拆开：先添加不带默认值的列（只改系统目录），再 set default，让新行得到默认值（也只改系统目录），最后按批次对旧行执行回填。

深入阅读：没有行拥有默认值时，默认值存在哪里

常量进入了 pg_attribute，成为该列的“缺失值”：atthasmissing = t、attmissingval = {web}。旧元组的属性数少于表的属性数，Postgres 会从这里填上缺口。add column ts2 timestamptz default now() 耗时 17 ms，filenode 保持为 17220，而每个已有行都会报告迁移发生的时刻，这可能不是你想要的结果。生成列遵循相同逻辑：虚拟生成列（PostgreSQL 18+ 的默认形式）只改系统目录，存储生成列会重写表（小测试表的 filenode 从 19978 变为 19986）。

动手验证 · lab 25-migrations
select lab.prove('a constant default does not rewrite: same filenode',
  pg_relation_filenode('events') = :fn0);
select lab.prove('every existing row reports the moment of the migration',
  (select count(distinct ts2) from events) = 1);
-- the rewrite is shown on a 200,000 row copy of events to keep the lab quick
select lab.prove('a volatile default (gen_random_uuid()) rewrites: new filenode',
  pg_relation_filenode('events_copy') <> :cfn);

完整实验脚本

先通过 CHECK，再设置 NOT NULL

set not null 必须证明不存在空值，因此会在 AccessExclusive 下扫描整张表，并在从磁盘读取每一页时阻塞读取。使用 CHECK 作为过渡：

-- 1. brief AccessExclusive, no scan: new rows are checked from now on
alter table events
  add constraint events_user_id_nn check (user_id is not null) not valid;
-- 2. ShareUpdateExclusive: scans, but reads and writes continue
alter table events validate constraint events_user_id_nn;
-- 3. brief AccessExclusive, no scan: Postgres trusts the validated CHECK
alter table events alter column user_id set not null;
-- 4. the CHECK is now redundant
alter table events drop constraint events_user_id_nn;

第 3 步才是巧妙之处（PostgreSQL 12+）：一个证明 col is not null 的有效 CHECK，可以让 SET NOT NULL 跳过扫描。在 PostgreSQL 18 上可以跳过 CHECK：直接把 NOT NULL 本身添加为 NOT VALID，然后验证它。

深入阅读：统计扫描次数，以及 PostgreSQL 18 的路径

seq_scan 在 pg_stat_user_tables 中会统计完整扫描。直接执行 set not null 会让它从 11 增加到 12（在我们已缓存的表上耗时 130 ms）。在上述模式中，第 2 步使它增加一次（耗时 87 ms，流量可以在锁下通过），第 3 步没有增加，耗时不到 4 ms。

动手验证 · lab 25-migrations
begin;
select xact_seq_scans('events') as s0 \gset
alter table events add constraint events_user_id_nn check (user_id is not null) not valid;
select lab.prove('step 1, add check ... not valid: no scan',
  xact_seq_scans('events') = :s0);
alter table events validate constraint events_user_id_nn;
select lab.prove('step 2, validate: one scan',
  xact_seq_scans('events') = :s0 + 1);
alter table events alter column user_id set not null;
select lab.prove('step 3, set not null with a valid CHECK in place: no scan',
  xact_seq_scans('events') = :s0 + 1);
rollback;

完整实验脚本

在 18 中：

-- no scan
alter table events
  add constraint events_user_id_not_null not null user_id not valid;
-- scans, under a weak lock
alter table events validate constraint events_user_id_not_null;

两者之间，插入空值已经会被拒绝。需要注意的是：验证之前，\d 已经显示 not null，pg_attribute.attnotnull 也已经是 true。应当检查 pg_constraint.convalidated。

动手验证 · lab 25-migrations
select lab.prove('18: add not null ... not valid takes AccessExclusive',
  my_strongest('events') = 'AccessExclusiveLock');
select lab.prove('before validation, pg_constraint.convalidated is false',
  (select not convalidated from pg_constraint where conname = 'events_user_id_not_null'));

完整实验脚本

先添加 CHECK NOT VALID，再验证

永远不要一步给大表添加 CHECK。先以 NOT VALID 添加它（短暂持锁，不扫描，并立即对新写入强制执行），然后再执行 VALIDATE CONSTRAINT（在 ShareUpdateExclusive 下扫描），正如上面 NOT NULL 模式的第 1、2 步。如果验证发现坏行，它会失败，但约束会保留下来，在你修复旧数据时继续保护新写入。

动手验证 · lab 25-migrations
alter table events add constraint events_no_login check (kind <> 'login') not valid;
-- validate fails with check_violation (caught in the lab)
select lab.prove('if validation hits a violating row it fails, and the constraint stays, not valid',
  (select not convalidated from pg_constraint where conname = 'events_no_login'));

完整实验脚本

先添加 NOT VALID 外键，再验证

一步添加外键会在阻塞两张表写入的同时扫描子表。按相同方式拆开：

-- ShareRowExclusive on both tables: blocks writes briefly, no scan
alter table events add constraint events_project_fk2
  foreign key (project_id) references projects (id) not valid;
-- ShareUpdateExclusive on events, RowShare on projects: traffic continues
alter table events validate constraint events_project_fk2;

add 操作仍然会阻塞父表的写入，而父表可能是最繁忙的表，并且像其他操作一样排队（GoCardless 就是被这个问题绊倒的），所以要给它设置 lock_timeout。还要自己并发地为引用列建索引；没有这个索引，每次从 projects 删除数据都会扫描 events（有目的地选择约束）。

选择与表流量匹配的索引构建方式

Postgres 有两种构建索引的方式，选择可以用一句话说清。普通的 CREATE INDEX 做的工作最少，但在完成前会阻止表上的所有写入；CREATE INDEX CONCURRENTLY 允许写入通过，却要付出两倍读取、大量等待以及中途失败的可能。

下面是在没有其他操作运行时，以两种方式构建同一个 (project_id, created_at) 索引，目标表是 events：

   build    | table_scans | pages_read |  wal  | index_pages
------------+-------------+------------+-------+-------------
 plain      |           1 |      35175 | 54 MB |        7703
 concurrent |           2 |      70348 | 54 MB |        7703

同一个索引，同样的 WAL，表读取次数却翻倍：在我们的机器上，并发构建耗时约为普通构建的 1.5 倍。

这是基准测试看到的代价。用户感受到的代价，则是写入发生了什么。构建索引期间，四个客户端尽可能快地向 events 插入数据：

构建方式构建期间完成的插入最慢插入
create index41 到 56723 到 751 ms
create index concurrently1,848 到 2,11916 到 103 ms
表 26-1 选择与表流量匹配的索引构建方式

普通构建期间到达的每次插入，都要等它全部完成。在 events 上，这不到一秒；在 200 GB 的表上，这就是整个构建过程，而每次结账、注册和 webhook 都在队列中等待。

区别只有一个锁。普通构建的 ShareLock 允许读取通过，却阻塞每次写入都需要的 RowExclusiveLock；并发构建的 ShareUpdateExclusiveLock 两者都不阻塞。

深入阅读：我们如何测量写入停顿，以及构建中途捕获的锁

四个 pgbench 客户端运行单行插入，持续 5 秒；构建在开始 1.5 秒后启动，pgbench -l 记录每次插入的延迟。构建是串行的（没有并行工作进程），因此上面的每一页和每一个 WAL 字节都属于构建；时间是五次运行的中位数，表已缓存。

普通构建中，最慢插入的耗时就是构建的耗时：每个等待中的插入都在构建提交的瞬间完成，而少数在“构建期间”完成的插入，是在构建取得锁之前刚好落地的。并发构建的 16 到 103 ms 属于磁盘和 CPU 的普通竞争。普通构建后让迁移事务保持打开，就能把这一状态定格下来：

 pid  |             query              |       mode       | granted | blocked_by
------+--------------------------------+------------------+---------+------------
 3932 | create index plain_idx on even | ShareLock        | t       | {}
 3933 | insert into events (account_id | RowExclusiveLock | f       | {3932}

同一时刻发出的主键查找立即返回。

动手验证 · lab 25-migrations
select lab.prove('a plain build holds a Share lock on events',
  exists (select 1 from pg_locks where pid = :b_mig and relation = 'events'::regclass
             and mode = 'ShareLock' and granted));
select lab.prove('an INSERT waits for it, on a relation lock',
  (select wait_event_type || ':' || wait_event from pg_stat_activity where pid = :b_web) = 'Lock:relation'
  and exists (select 1 from pg_locks where pid = :b_web and relation = 'events'::regclass
                 and mode = 'RowExclusiveLock' and not granted));
select lab.prove('a concurrent build reads it twice',
  :cic_scans = 2 and :cic_pages between 1.9 * :events_pages and 2.1 * :events_pages);
select lab.prove('while a primary key lookup goes straight through',
  (select k from dblink('b_hold', 'select kind from events where id = 42') t(k text)) is not null);

完整实验脚本

深入阅读：并行构建：内存换来工作进程，工作进程换来时间

B-树构建会通过 max_parallel_maintenance_workers（默认 2）在多个工作进程间拆分扫描和排序，但 maintenance_work_mem（默认 64 MB）为整个构建设定预算，而且包括领导者（leader）在内的每个进程都需要其中 32 MB。因此，在默认设置下，允许两个工作进程也只会实际使用一个。下面是我们在 8 核机器上进行七次构建的中位数：

maintenance_work_mem允许的工作进程数进程数构建耗时中位数
64 MB010.82 s
1 GB011.06 s
64 MB220.53 s
256 MB230.50 s
1 GB450.37 s
表 26-2 选择与表流量匹配的索引构建方式

单靠内存没有带来任何收益（排序 200 万个键时，溢写磁盘的代价很低）。内存带来的是工作进程，而工作进程带来了时间收益：使用五个进程时，速度是原来的 2.2 倍。对于有空闲核心的服务器上的大构建，要在迁移会话中同时提高这两个设置。并发构建只会并行化第一次扫描。要进行全服务器范围的配置，请参阅每个 autovacuum 工作进程也会占用 maintenance_work_mem。

动手验证 · lab 25-migrations
select lab.prove('at the defaults (64 MB, 2 workers allowed) the build gets only one worker: each needs 32 MB',
  current_setting('maintenance_work_mem') = '64MB'
  and current_setting('max_parallel_maintenance_workers') = '2'
  and (select scans - :s0 = 2 from events_reads()));
select lab.prove('with 256 MB it gets both workers: three processes scan the table',
  (select scans - :s0 = 3 from events_reads()));

完整实验脚本

如果你得问它是否繁忙，就使用并发构建

只有在表上没有人写入时，才使用普通的 CREATE INDEX：新表或空表（包括在同一迁移中更早创建的表，此时普通构建能让表、索引和约束保持原子性）；真正的维护窗口，且写入确实已停止，而不是“通常很安静”；或无人写入的表，例如查找表、归档表，或已经停止接收行的分区。

生产环境中任何会接收写入的表，都使用 CREATE INDEX CONCURRENTLY。如果你得问一张表是否繁忙，就把它当作繁忙。

普通构建不是糟糕的默认值：它更快、具有事务性，而且失败时能干净地回滚。它只是不适合那些你最常需要建立索引的大型繁忙表。

单独运行并发构建，不设超时，并检查索引是否有效

  1. 为这条语句关闭 lock_timeout，这是规则一：lock_timeout 和重试的例外。并发构建的锁即使在等待时也不会阻塞读取或写入，因此超时不会保护任何流量。更糟的是，Postgres 会把它应用到构建等待其他事务的场景：1 秒超时会取消一个卡在慢报表后面的构建，并留下无效索引。为构建设置 lock_timeout = 0 和 statement_timeout = 0。（普通构建要保留它：只要它在等待锁，就已经会阻塞后面的写入。）
  2. 单独运行。 它拒绝在事务块中运行，因此要用工具的“无事务”开关，为它提供自己的迁移。同一张表上的第二个并发构建会排在第一个后面。
  3. 观察它。 pg_stat_progress_create_index 会显示阶段、已完成的页数和它正在等待的会话（current_locker_pid），等待对象可能来自数据库中的任何位置：对该表尚未提交的写入、repeatable read 事务、打开的游标或正在运行的查询都可能成为等待对象。空闲的 read committed 会话不算。
  4. 完成后检查 indisvalid。 失败、取消或超时的构建会留下无效索引（并发构建失败时）。重试前，先并发删除它。
让并发索引迁移可以重复运行

失败后，create index concurrently if not exists 是一个陷阱：无效索引已经存在，所以它什么也不做，却报告成功。先删除同名的无效索引，再创建它，并在每次发布后检查 indisvalid。

深入阅读：并发构建做了什么，逐阶段看

普通构建只有一次遍历：加锁、扫描、排序、写入树、提交。并发构建面对的是不断变化的表，因此会分阶段运行（文档）。我们在一次构建期间每隔几毫秒采样 pg_stat_progress_create_index；开始时 events 中有一个未提交的插入，并有一个空闲的 repeatable read 事务打开；两者都在 0.3 秒后释放：

                 phase                  | blocks_done | blocks_total | locker
----------------------------------------+-------------+--------------+--------
 waiting for writers before build       |           0 |            0 |   2081
 building index: scanning table         |       34375 |        35173 |
 building index: sorting live tuples    |       35173 |        35173 |
 building index: loading tuples in tree |           0 |            0 |
 index validation: scanning index       |        7415 |         7703 |
 index validation: sorting tuples       |           0 |            0 |
 index validation: scanning table       |       34855 |        35173 |
 waiting for old snapshots              |       35172 |        35173 |   2082

（locker 是 current_locker_pid；每一行是该阶段看到的最后一个样本。）

从上到下读取。索引进入系统目录时是无效的，因此新事务知道它存在。构建会等待已经在写这张表的事务（插入事务，pid 2081），扫描全部 35,173 页，排序 200 万个条目，然后把索引标记为 ready（indisready）：此时写入会开始维护它。验证阶段读取新索引的 7,703 页，并再次读取表，把期间到达的行加入进去。最后，它会等待所有更早的快照（repeatable read 会话，pid 2082），并把自己标记为有效。普通构建只会显示三个“building index”阶段。

动手验证 · lab 25-migrations
select lab.prove('a plain build that is only waiting for its lock already blocks the inserts behind it',
  (select not granted from pg_locks where pid = :b_web and relation = 'events'::regclass));
select lab.prove('a 1s lock_timeout cancels a concurrent build waiting on an old snapshot, leaving an invalid index',
  (select indisready and not indisvalid from pg_index where indexrelid = 'kind_idx'::regclass));
select lab.prove('a second concurrent build on the same table queues behind the first',
  exists (select 1 from pg_locks where pid = :b_mig and relation = 'events'::regclass
             and mode = 'ShareUpdateExclusiveLock' and not granted));

完整实验脚本

深入阅读：哪些会话会拖住并发构建

最后的等待会覆盖数据库中快照早于验证阶段的每个事务，无论它访问了哪张表。我们测试了每种会话：

构建开始时打开的会话会拖住构建吗？
处于事务中空闲、read committed，读取或写入 accounts否
处于事务中空闲、repeatable read，读取 accounts是：“waiting for old snapshots”
在 accounts 上打开游标的 read committed 会话是：“waiting for old snapshots”
仍在任何表上运行查询是：“waiting for old snapshots”
向 events 未提交的插入是：“waiting for writers before build”
表 26-3 单独运行并发构建，不设超时，并检查索引是否有效

Read committed 每条语句都会获取新快照，因此空闲会话不会持有任何东西；repeatable read 会一直保留快照，直到事务结束。线索是 backend_xmin 在 pg_stat_activity 中的值：只要某个会话的这一值早于构建，它就会阻碍构建，甚至一个写成单条长语句的监控循环也包括在内。构建等待时没有危害（插入会直接通过）；它只是无法结束。

还有两个意外。构建的锁会与该表上的清理冲突：autovacuum 会让开，防回卷清理不会，于是构建会等待。唯一索引构建从第二次扫描开始就强制唯一性：当我们的索引仍无效、构建停留在“waiting for old snapshots”阶段时，一次重复插入因 23505 失败。一次失败留下的无效索引，会一直拒绝重复值，直到你删除它。

动手验证 · lab 25-migrations
select lab.prove('an idle read committed transaction that read another table does not hold it up',
  (select indisvalid from pg_index where indexrelid = 'kind_idx'::regclass)
  and (select state from pg_stat_activity where pid = :b_hold) = 'idle in transaction');
select lab.prove('an idle repeatable read transaction on another table parks it in "waiting for old snapshots"',
  (select current_locker_pid = :b_hold and wait_event = 'virtualxid' from build_progress)
  and pg_blocking_pids(:b_cic) = array[:b_hold]);
select lab.prove('while the unique build waits, still invalid, a duplicate insert is already rejected (23505)',
  (select not indisvalid from pg_index where indexrelid = 'handles_handle_key'::regclass));

完整实验脚本

深入阅读：并发构建失败时

失败的普通构建会回滚。失败的并发构建（唯一索引中出现重复值、死锁、取消或超时）会留下一个无效索引，并以 INVALID 标记出现在 \d 输出中。查询永远不会使用它；如果它已经通过第一次扫描，之后的每次写入仍然会维护它（我们有一次很晚才取消构建，它在 20,000 次插入中持续增长）；而且它的名称会与你的重试冲突。找出它们，并发删除它们，修复原因，再次构建：

select indexrelid::regclass, indisvalid, indisready
  from pg_index where not indisvalid;

drop index concurrently projects_account_name_key;  -- a failed unique build

indisready 表明你拥有的是哪一种索引。遇到重复值的唯一索引构建会在第一次扫描中失败，因此留下的索引尚未 ready，写入会跳过它。验证阶段失败或被取消的索引已经 ready，在你删除它之前，写入都会为它付出代价。

动手验证 · lab 25-migrations
select lab.prove('every write still maintains it: it grew with 20,000 inserts',
  pg_relation_size('kind_idx') > :k0);
select lab.prove('and leaves an index behind, indisvalid = false (and here indisready = false)',
  (select not indisvalid and not indisready from pg_index
    where indexrelid = 'projects_account_name_key'::regclass));
create unique index concurrently if not exists projects_account_name_key
  on projects (account_id, name);
select lab.prove('"if not exists" after a failure does nothing and reports success: still invalid',
  (select not indisvalid from pg_index where indexrelid = 'projects_account_name_key'::regclass));

完整实验脚本

并发构建唯一索引，然后把它挂接上

直接添加唯一约束，会在 AccessExclusive 下构建它的索引。先并发构建索引，再把它挂接上。挂接只改系统目录，但仍然需要短暂的 AccessExclusive，因此要在 lock_timeout 下像其他 ALTER 一样运行：

create unique index concurrently users_email_key on users (email);
alter table users add constraint users_email_key unique using index users_email_key;

主键也是相同做法（primary key using index），前提是列已经是 NOT NULL；如果还不是，挂接操作会执行 SET NOT NULL，并在该锁下扫描表。

永远不要在线上表上运行普通 REINDEX。它会在索引上取得 AccessExclusive，而且因为规划任何查询都会锁住该表的每个索引，即使从不访问被重建索引的主键查找，也会一直等到提交。改用 reindex index concurrently（PostgreSQL 12+）。分区表完全拒绝 concurrently，因此要并发构建每个分区的索引，再把它们挂接到父表的索引上。

深入阅读：使用 REINDEX CONCURRENTLY 重建；普通 REINDEX 也会阻塞读取

对于膨胀的索引（何时重建）或受排序规则库变更影响的索引，reindex index concurrently（PostgreSQL 12+）会在旧索引旁边构建一个 ..._ccnew 副本，然后交换两者；所用锁及注意事项与并发创建相同。如果失败，删除残留的 _ccnew 后重试；残留的 _ccold 表示它已经成功，只有旧副本需要删除。

普通 REINDEX 是紧急情况下人们最容易使用的办法，却比普通 CREATE INDEX 更糟：它还会在索引上取得 AccessExclusiveLock，而且规划任何查询都会锁住表上的每个索引。普通重建 events_created_at_idx 时，select kind from events where id = 42 明明不会使用该索引，却一直等到提交，正如 REINDEX 文档所警告的那样。

动手验证 · lab 25-migrations
select lab.prove('and that blocks even a primary key lookup: planning it opens every index on the table',
  (select wait_event from pg_stat_activity where pid = :b_web) = 'relation');
select lab.prove('reindex concurrently builds a second copy beside the old one, named _ccnew',
  (select command = 'REINDEX CONCURRENTLY' from build_progress)
  and to_regclass('events_created_at_idx_ccnew') is not null);

完整实验脚本

深入阅读：分区表：逐个构建分区索引，然后挂接

对分区表执行 create index concurrently 会失败（cannot create index on partitioned table "events_m" concurrently），而在父表上执行普通构建，会同时对每个分区取得 ShareLock。并发构建各个部分，再把它们组装起来。在 events 的三个按月分区上：

-- 1. the parent's index: catalog only, 2 ms, marked invalid
create index events_m_kind_idx on only events_m (kind);

-- 2. each partition, concurrently, then attach it (one pair per partition)
create index concurrently events_m_2026_07_kind_idx on events_m_2026_07 (kind);
alter index events_m_kind_idx attach partition events_m_2026_07_kind_idx;
-- ... and the same for events_m_2026_08 and events_m_2026_09

父表的索引会在最后一个分区索引挂接后自行变为有效。之后使用 partition of 创建的分区会自动获得匹配的索引，并以普通方式构建；这没问题，因为它们是空的。对一个已经包含行的表执行 attach partition，也会在锁下以普通方式构建缺失的索引，因此要在挂接表之前给它建好索引。分区章节提供了生成这些语句的工具。

动手验证 · lab 25-migrations
select lab.prove('a plain create index on the parent takes Share on every partition',
  strongest(:'pm_locks', 'events_m_2026_07') = 'ShareLock'
  and strongest(:'pm_locks', 'events_m_2026_08') = 'ShareLock'
  and strongest(:'pm_locks', 'events_m_2026_09') = 'ShareLock');
select lab.prove('after the last attach, it turns valid on its own',
  (select indisvalid from pg_index where indexrelid = 'events_m_kind_idx'::regclass)
  and (select count(*) from pg_inherits where inhparent = 'events_m_kind_idx'::regclass) = 3);

完整实验脚本

扩宽类型没有代价；大多数其他类型变更都会重写

放宽限制没有代价。增加限制、改变标度或改变表示方式，都会重写表以及该列上的每个索引。在一张 100,000 行的表上：

变更会重写吗？
varchar(20) 到 varchar(50)，varchar(n) 到 text否
text 到 varchar(100)，即使所有值都放得下是
int 到 bigint是
numeric(12,2) 到 numeric(12,4)是
timestamp 到 timestamptz，会话处于 UTC否
timestamp 到 timestamptz，任何其他时区是
表 26-4 扩宽类型没有代价；大多数其他类型变更都会重写

因此，要用 CHECK 约束表达长度限制。对于时间戳，转换会把每个旧值当作会话时区中的时间来读取。如果保存的值确实是 UTC，先设置 timezone = 'UTC'：字节不会改变，也不会发生重写。如果它们保存的是本地钟表时间，就把会话设置到那个时区并接受重写；把它们按 UTC 转换虽然可以跳过重写，却会让每个值按偏移量发生移动。

integer 主键在 21 亿处插入失败，而 alter column id type bigint 会在 AccessExclusive 下重写表及其每个索引：在这种规模下会停机数小时。在线修复是一个由本章各部分组成的小项目，安全完成需要数周，而且完全可以避免：从第一天起就使用 bigint。

对于已经存在的表，要把每个序列与它的最大值画在一起，并在它远未达到最大值时就发出告警：

select schemaname, sequencename, data_type, last_value, max_value,
       round(100.0 * last_value / max_value, 1) as pct_used
  from pg_sequences
 order by pct_used desc nulls last;

有一个陷阱：从 PostgreSQL 10 之前升级的数据库，可能有 serial 列，类型为 integer，却由 bigint 序列支持。它们报告的比例不到百分之一，而列本身却已经接近溢出，因此要把 last_value 与列类型的取值上限比较，而不只是与序列的 max_value 比较。

深入阅读：从 int 到 bigint 的问题

在线交换的顺序如下，这样可以让外键始终保持生效：

  1. 添加并同步。 添加 id_new bigint，允许为空、没有默认值（只改系统目录），并添加一个触发器，在每次插入和更新时设置 new.id_new := new.id。
  2. 回填。 按批次遍历主键，对已有行执行回填。
  3. 在不持有长锁的情况下让它变成 NOT NULL： add constraint ... check (id_new is not null) not valid，然后 validate constraint，就像上面 NOT NULL 模式的第 1、2 步。（在 PostgreSQL 18 中使用 not null id_new not valid 和 validate。）
  4. 并发地在 id_new 上建立唯一索引。
  5. 对每个引用列重复相同操作。 每个子表的 parent_id 都获得一个 bigint 双胞胎列，以及自己的触发器、回填和 NOT NULL；然后从这个双胞胎列向父表的 id_new 建立外键，以 NOT VALID 添加并验证。外键可以引用唯一索引，因此不必等待新的主键。
  6. 在一个短事务中交换。 设置 lock_timeout 并重试：删除旧外键和旧主键，在新索引上执行 add primary key using index（不扫描：经过验证的 CHECK，或 PostgreSQL 18 的 not-null 约束，已经证明不存在空值），重命名父表和每个子表中的列，把标识或序列移到新列（先执行 alter sequence ... as bigint，因为 int 序列会在 2,147,483,647 处停止），然后删除触发器。新的外键已经验证过，因此完整性从未中断。
  7. 之后再删除旧列。 等到没有代码再读取它们，在下一个发布中删除。

上面的类型表，已通过比较每次变更前后的 pg_relation_filenode 核对过。每个“否”都保留了表文件；每个“是”都产生了新文件。

动手验证 · lab 25-migrations
-- rewrites() compares pg_relation_filenode('tc') before and after the change
select lab.prove('varchar(20) to varchar(50): no rewrite', not rewrites('alter column v20 type varchar(50)'));
select lab.prove('text to varchar(100): rewrite, even though every value fits', rewrites('alter column t2 type varchar(100)'));
select lab.prove('int to bigint: rewrite', rewrites('alter column i type bigint'));
select lab.prove('numeric(12,2) to numeric(12,4): rewrite', rewrites('alter column n2 type numeric(12,4)'));
select lab.prove('timestamp to timestamptz with the session in UTC: no rewrite',
  not rewrites('alter column ts1 type timestamptz', 'UTC'));
select lab.prove('timestamp to timestamptz in another time zone: rewrite',
  rewrites('alter column ts2 type timestamptz', 'America/New_York'));

完整实验脚本

在序列还剩几年可用时开始交换。一个从 2,147,483,646 重新开始的 int 标识列，只能插入两行，然后就会拒绝此后的每次插入：

ERROR:  nextval: reached maximum value of sequence "tickets_id_seq" (2147483647)

于是，文章中的监控查询会显示 tickets_id_seq 已达到 100%。

动手验证 · lab 25-migrations
do $$
begin
  insert into tickets (title) values ('c');
  raise exception 'no error';
exception when sqlstate '2200H' then
  if sqlerrm <> 'nextval: reached maximum value of sequence "tickets_id_seq" (2147483647)' then
    raise exception 'NOT PROVED: %', sqlerrm;
  end if;
end $$;
select lab.prove('the third insert fails: nextval reached the int maximum (2147483647)', true);
select lab.prove('pg_sequences shows tickets_id_seq at 100 percent of its maximum',
  (select round(100.0 * last_value / max_value, 1) = 100.0
   from pg_sequences where sequencename = 'tickets_id_seq'));

完整实验脚本

重命名要用扩展与收缩发布

rename column 只改系统目录。问题在于，几十个应用进程都知道旧名字：代码和模式不可能在同一瞬间改变，因此谁先改变都会破坏另一个。永远不要做让新旧代码无法同时存活的变更。把它拆成扩展和收缩，并在两者之间发布：

  ┌──────────────┐  ┌──────────────┐  ┌──────────────┐  ┌──────────────┐
  │ 1. expand    │  │ 2. deploy    │  │ 3. backfill  │  │ 4. deploy    │
  │ add new col  │─▶│ write both,  │─▶│ copy old to  │─▶│ read new,    │
  │ (nullable)   │  │ read old     │  │ new, batched │  │ write both   │
  └──────────────┘  └──────────────┘  └──────────────┘  └──────┬───────┘
                                                               │
                    ┌──────────────┐  ┌──────────────┐         │
                    │ 6. contract  │  │ 5. deploy    │         │
                    │ drop old col │◀─│ stop writing │◀────────┘
                    │              │  │ old column   │
                    └──────────────┘  └──────────────┘

用这种方式把 events.kind 重命名为 event_type，需要三次迁移和三次发布。这就是零停机的真实代价，也是谨慎选择名称的最佳理由。触发器或视图可以替代部分应用代码，但二者都不能消除这些阶段。同样的步骤也可以用来拆分列、合并表，也可以改变 API 发出的 ID（通过同时接受两种 ID，让 API 脱离整数 ID）。

按主键逐步遍历，在短批次中回填

update events set status = payload->>'status' 是一个跨越 200 万行的事务：产生 200 万个新行版本和 200 万个死行版本（更新就是插入），每行都锁到提交，阻碍清理，让整张表大小的 WAL 流向副本；如果在最后一行失败，所有工作都会回滚。要分批：

  • 按范围遍历主键（id > $1 and id <= $1 + 10000 and status is null），永远不要使用 OFFSET。每个批次的代价相同，而 status is null 让任务可以重启。
  • 每个批次都提交，让每个批次的耗时远低于一秒。过程可以做到这一点（过程可以提交事务）；我们的任务约 16 秒填完了 200 万行。
  • 预期会膨胀： 快速回填可能在 autovacuum 追上之前让表翻倍，因此大型任务要在中途执行清理。
  • 把它作为可恢复的任务放在模式迁移之外运行，并在副本延迟时暂停它。

批量加载新行是另一种任务，有更好的工具（冻结分区，挂接已构建的分区，再分析父表）。

先删除代码，再删除列

删除列只改系统目录，但会取得 AccessExclusive，因此要设置 lock_timeout。危险在应用：明确写出该列名的代码会失败，没写列名的代码也可能失败，例如在启动时缓存了列清单的 ORM。先发布忽略该列的代码，等到所有旧进程都消失，再删除它。

删除表会在每张通过外键与它相连的表上取得 AccessExclusive。先逐条删除这些外键，每条语句都设置一个较短的 lock_timeout，这样就没有一条语句需要同时取得所有锁；如果想保留廉价的撤销方案，可以先重命名表，下一次发布再删除。

事务性 DDL 会持有所有锁直到提交

Postgres 的 DDL 具有事务性，因此大多数工具会把每次迁移包进事务，不会出现只执行一半的迁移。代价是：每把锁都会一直持有到提交。

begin;
alter table accounts add column region text;
alter table users    add column region text;
update accounts set region = 'us';
-- still holding AccessExclusive on accounts and users
commit;

update 之后，pg_locks 仍然显示两张表上的 AccessExclusiveLock，对任意一张表的每个查询都会排队，直到 commit。

动手验证 · lab 25-migrations
begin;
alter table accounts add column region text;
alter table users    add column region text;
update accounts set region = 'us';
select lab.prove('after the update, the transaction still holds AccessExclusive on accounts and users',
  my_strongest('accounts') = 'AccessExclusiveLock' and my_strongest('users') = 'AccessExclusiveLock');
rollback;

完整实验脚本

你持有的每一把锁，都会让下一个等待变得更糟。因此：每次迁移只处理一张有风险的表，并且模式迁移中不做数据变更。（MySQL 会分别提交每条 DDL 语句；参见DDL 具有事务性。）

本章要点

  • 每次迁移都设置 lock_timeout（1 到 5 秒）并重试。等待锁的 DDL 会阻塞后面的所有操作，甚至包括读取。
  • 判断变更是只改系统目录、扫描还是重写，并在生产规模的数据上测试。
  • 常量默认值会立即完成；gen_random_uuid() 这样的易变默认值会重写。
  • 以 NOT VALID 添加 CHECK、FOREIGN KEY 和 NOT NULL 约束，然后在流量可以通过的锁下执行 VALIDATE。
  • 普通 create index 会在整个构建期间停止写入；concurrently 让写入继续，但读取量翻倍。普通方式只用于无人写入的表。
  • 并发构建时关闭 lock_timeout，并检查残留的 INVALID 索引。永远不要在线上表上运行普通 reindex。
  • int 到 bigint、text 到 varchar(n) 以及标度变更都会重写。
  • 重命名用扩展与收缩发布；回填要在迁移之外作为按主键范围分批运行的任务。

延伸阅读

Eight Kilobytes，© 2026 Flagon, Inc. All rights reserved.，依 All rights reserved 授权使用，详见归属声明。

表格阅读