PostgreSQL 加字段锁表？每种 ALTER TABLE 拿的是什么锁，以及如何安全上线迁移

一个只加一列可空字段的迁移，执行不到 1 毫秒就能完成。但它照样可能让你的应用挂掉：执行之前它必须先等一把锁，而在它等待期间，这张表上的其他所有查询都排在它后面等。本文用三个 psql 会话现场演示锁队列是怎么形成的，列出每种常见 ALTER TABLE 获取的锁以及它是否会重写整张表，并逐一讲解让迁移变安全的做法：lock_timeout 加重试、先 NOT VALID 再 VALIDATE，以及 CREATE INDEX CONCURRENTLY。下文的每条语句和每段输出都在 PostgreSQL 17.11 上实际运行过，这也是 Relyt One 所用的大版本（唯一一处关于 PostgreSQL 18 的说明在 18.6 上验证过）。

如果只记住四件事：

1. 大多数 ALTER TABLE 形式都会获取 ACCESS EXCLUSIVE 锁，它既阻塞写入，也阻塞读取。即使变更本身是瞬间完成的，语句也要先排队等这把锁。
2. 在它排队期间，同一张表上新来的查询都会排在它后面。一个长事务加一个迁移，就足以让所有访问这张表的请求全部卡住。
3. 给每个迁移设置 lock_timeout，让它在几秒后放弃，而不是无限期地阻塞流量，然后重试。
4. 把昂贵的检查和锁拆开：约束先以 NOT VALID 添加，之后再 VALIDATE；索引用 CONCURRENTLY 构建；避免会重写大表的语句。

本文其余部分会逐一演示这几点，并附上可复现的输出。

两张表：一百万个账户和两百万个订单。在一个用完即弃的数据库上，用 psql 运行：

CREATE TABLE accounts (
  id         bigint PRIMARY KEY,
  email      text NOT NULL,
  plan       varchar(20),
  score      integer NOT NULL DEFAULT 0,
  created_at timestamptz NOT NULL DEFAULT now()
);
INSERT INTO accounts (id, email, plan, score)
SELECT g, 'user' || g || '@example.com',
       (ARRAY['free', 'pro', 'team'])[1 + g % 3], g % 1000
FROM generate_series(1, 1000000) AS g;

CREATE TABLE orders (
  id         bigint PRIMARY KEY,
  account_id bigint NOT NULL,
  total      numeric(10, 2) NOT NULL
);
INSERT INTO orders
SELECT g, 1 + g % 1000000, (g % 10000) / 100.0
FROM generate_series(1, 2000000) AS g;
ANALYZE;

打开三个 psql 会话。在第一个会话里开启一个事务，读取这张表，并让事务一直保持打开。在真实系统里，这可能是一个慢报表、一个批处理任务，或者是应用忘了提交、处于 idle in transaction 状态的连接：

-- 会话 A
SET application_name = 'a_report';
BEGIN;
SELECT count(*) FROM accounts;
-- 保持事务打开

在第二个会话里运行迁移。添加一个可空列是教科书式的「安全」迁移：

-- 会话 B
SET application_name = 'b_migration';
ALTER TABLE accounts ADD COLUMN note text;

它卡住了。在第三个会话里，运行你的应用每分钟要跑上千次的那种查询：

-- 会话 C
SET application_name = 'c_app';
SELECT email FROM accounts WHERE id = 42;

它也卡住了，尽管它只读一行，而会话 A 也只是在读。再开第四个会话，问问 Postgres 谁在等谁：

SELECT pid, application_name, state, wait_event_type AS wait_type,
       pg_blocking_pids(pid) AS blocked_by, query
FROM pg_stat_activity
WHERE application_name IN ('a_report', 'b_migration', 'c_app')
ORDER BY application_name;

 pid | application_name |        state        | wait_type | blocked_by |                   query
-----+------------------+---------------------+-----------+------------+--------------------------------------------
 478 | a_report         | idle in transaction | Client    | {}         | SELECT count(*) FROM accounts;
 488 | b_migration      | active              | Lock      | {478}      | ALTER TABLE accounts ADD COLUMN note text;
 495 | c_app            | active              | Lock      | {488}      | SELECT email FROM accounts WHERE id = 42;
(3 rows)

你的 pid 会不一样。blocked_by 列说明了一切。C 并不是被 A 阻塞的。A 持有 ACCESS SHARE 锁，C 想要的是同一种模式，两者并不冲突。C 是被 B 阻塞的。B 想要 ACCESS EXCLUSIVE，它和所有锁模式都冲突，所以它要等 A。而一个新的锁请求，不仅要在与已授予的锁冲突时等待，与正在等待中的请求冲突时也要等待。这条规则是为了防止源源不断的读请求让迁移永远饿死，但它的副作用是：一个排队中的 ALTER TABLE 会变成挡在整张表前面的一堵墙。

在我们的这次运行中，会话 A 在开始 8 秒后提交，B 随即立刻完成，而 C 这个单行查询耗时 6.2 秒。在真实流量下，这段时间里所有访问 accounts 的请求都会堆积起来，连接池被耗尽，这次故障看起来像是一条单行迁移引起的。确实是它引起的，但并不是因为迁移本身慢。

lock_timeout 会取消等锁时间超过上限的语句。重复同样的实验，但给会话 B 两秒：

-- 会话 B
SET application_name = 'b_migration';
SET lock_timeout = '2s';
ALTER TABLE accounts ADD COLUMN note text;

ERROR:  canceling statement due to lock timeout

这一次 C 最多只等了两秒（我们这次运行中是 1.8 秒，而不是 6.2 秒）：它只等到 B 达到上限、退出队列为止。迁移失败了，而这正是目的：它以很小的代价失败，而不是把整张表拖垮。接下来重试即可。

要让这个做法在实践中奏效，有两个细节。

在迁移的事务里使用 SET LOCAL。 SET LOCAL 只在当前事务结束前有效，之后自动恢复，因此不会泄漏到这条连接上接下来运行的任何东西里：

BEGIN;
SET LOCAL lock_timeout = '2s';
ALTER TABLE orders
  ADD CONSTRAINT orders_account_fk
  FOREIGN KEY (account_id) REFERENCES accounts (id) NOT VALID;
COMMIT;

如果你的迁移要经过事务模式的连接池，这一点很重要。每个事务可能落到不同的服务端连接上，所以前一个事务里的普通 SET 并不能保证作用于 ALTER TABLE。Relyt One 的池化连接字符串经过的是事务级池化的 PgBouncer。请通过直连连接字符串运行迁移，并且无论如何都把这项设置放在事务内部。

带退避地重试，但只针对锁超时。 很多迁移工具支持重试；如果你用的工具不支持，一个小小的包装脚本就够了。下面这个脚本只在迁移因等锁而放弃时重试，第一次成功即停止，五次尝试后失败退出。其他任何错误（例如 SQL 里的拼写错误）都会立即失败。它匹配的是英文错误文本，所以迁移会话的 lc_messages 要保持为英文：

#!/usr/bin/env bash
# 用法：./migrate.sh migration.sql
# 仅在迁移因等锁而放弃时重试。
set -u
for attempt in 1 2 3 4 5; do
  if output=$(psql "$DATABASE_URL" -X -v ON_ERROR_STOP=1 -f "$1" 2>&1); then
    echo "$output"
    exit 0
  fi
  echo "$output" >&2
  if ! grep -q "canceling statement due to lock timeout" <<<"$output"; then
    exit 1  # 其他错误：修复它，不要重试
  fi
  if [ "$attempt" -lt 5 ]; then
    echo "attempt $attempt hit the lock timeout; retrying in $((attempt * 5))s" >&2
    sleep $((attempt * 5))
  fi
done
echo "giving up after 5 attempts" >&2
exit 1

我们把上面的外键迁移保存为 0042_orders_account_fk.sql，在另一个会话持有一个打开的事务（该事务更新了 accounts 中的一行）时运行这个包装脚本。第一次尝试触发了超时，那个事务提交后，第二次尝试成功：

BEGIN
SET
psql:0042_orders_account_fk.sql:5: ERROR:  canceling statement due to lock timeout
attempt 1 hit the lock timeout; retrying in 5s
BEGIN
SET
ALTER TABLE
COMMIT

由于这个文件把所有内容包在一个事务里，失败的尝试会被完整回滚，重试从干净的状态开始。只对这类文件做重试。不能在事务中运行的语句（例如 CREATE INDEX CONCURRENTLY）失败时可能会留下残留（下文会细讲），下一次尝试就会被它绊住。

在繁忙的表上执行迁移之前，也值得先找出会阻塞它的那个事务：

SELECT pid, state, now() - xact_start AS xact_age, left(query, 50) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND pid <> pg_backend_pid()
ORDER BY xact_start
LIMIT 5;

一个已经打开了好几分钟的事务，尤其是处于 idle in transaction 状态的事务，会让每一次尝试都超时。idle_in_transaction_session_timeout（默认关闭）可以自动结束这类会话。

你可以自己测量。在事务里运行语句，提交前读取 pg_locks，然后回滚。要判断 Postgres 有没有重写整张表，可以比较前后的文件节点：重写会把表的新副本写入一个新文件。

CREATE VIEW my_locks AS
SELECT relation::regclass AS table_name, mode
FROM pg_locks
WHERE pid = pg_backend_pid()
  AND locktype = 'relation'
  AND relation IN ('accounts'::regclass, 'orders'::regclass)
ORDER BY 1, 2;

BEGIN;
SELECT pg_relation_filenode('accounts') AS before \gset
ALTER TABLE accounts ALTER COLUMN score TYPE bigint;
SELECT * FROM my_locks;
SELECT pg_relation_filenode('accounts') <> :before AS rewritten;
ROLLBACK;

 table_name |        mode
------------+---------------------
 accounts   | AccessExclusiveLock
 accounts   | ShareLock
(2 rows)

 rewritten
-----------
 t
(1 row)

（\gset 是 psql 命令，把结果保存到变量中。）下表中的每条语句，我们都用这种方法在一百万行的 accounts 表和两百万行的 orders 表上运行过。对于「阻塞」两列，我们在一个打开的事务里持有每把锁，并从另一个会话以 500 ms 的 lock_timeout 尝试执行单行 SELECT 和单行 UPDATE；其余内容则根据 PostgreSQL 锁冲突表中的锁模式推导得出。

外键会锁住两张表，所以单独列一张表。orders 是引用表，accounts 是被引用表：

这些耗时来自本地容器，会随表的大小和硬件而变化，所以请把它们当作相对值，而不是绝对值。这两张表里有几点常常让人意外：

• 这里所有添加、删除、重命名列或修改列类型的语句都会获取 ACCESS EXCLUSIVE，包括那些瞬间完成的。「瞬间」描述的是锁被持有多久，而不是需不需要这把锁。它们都可能引发锁队列。
• 有默认值并不意味着要重写。 从 PostgreSQL 11 开始，非 volatile 的默认值只在系统目录里存一份，已有的行在被读取时才取用它。now() 在这里算作非 volatile：它只在 ALTER TABLE 运行时求值一次，所有已有的行拿到的都是同一个时间戳。而 gen_random_uuid() 或 clock_timestamp() 这类 volatile 默认值需要每行一个不同的值，所以整张表会被重写。
• 放宽 varchar 是免费的，收窄则不是。 从 varchar(20) 改为 varchar(50) 或 text 只会修改系统目录。改为 varchar(10) 在 PostgreSQL 17 上重写了整张表。
• integer 改 bigint 一定会重写。 值在磁盘上的大小变了。在大表上，这是最需要事先规划的迁移：新增一个 bigint 列，分批回填，再在一个短事务里完成切换。
• 外键会阻塞它所指向的那张表的写入，而不仅仅是你正在修改的那张表。给 orders 加外键时，accounts 上的 UPDATE 也被挡住了。
• CREATE INDEX 放行读取，但会阻塞写入，在整个构建期间都是如此。

ADD CONSTRAINT 通常会在持锁期间检查每一条已有的行。NOT VALID 跳过这一检查：约束立即对新插入和更新的行生效，已有的行之后由 VALIDATE CONSTRAINT 检查，而它获取的锁要弱得多。在上面的表中，直接添加外键会让两张表的写入都阻塞 1.8 秒。拆成两步之后，阻塞部分只用了 3 ms，而 1.2 秒的验证过程中读写都能正常进行。

迁移 1 就是方法 1 中的那个文件；如果你在那里已经运行过，只剩下迁移 2：

-- 迁移 1：很快，但仍然需要拿锁，所以保留 lock_timeout
BEGIN;
SET LOCAL lock_timeout = '2s';
ALTER TABLE orders
  ADD CONSTRAINT orders_account_fk
  FOREIGN KEY (account_id) REFERENCES accounts (id) NOT VALID;
COMMIT;

-- 迁移 2：扫描全表，但不阻塞读写
ALTER TABLE orders VALIDATE CONSTRAINT orders_account_fk;

如果 VALIDATE 发现了违反约束的行，它会失败，约束保持 NOT VALID 状态，但仍会对新的写入生效。修复数据后再验证一次即可。

在 PostgreSQL 17 上，SET NOT NULL 没有 NOT VALID 形式，它会在 ACCESS EXCLUSIVE 下扫描整张表。但如果已经有一个经过验证的 CHECK 约束能证明该列没有空值，它就会跳过扫描。用 client_min_messages = debug1 可以看到这个判断：

ALTER TABLE accounts ADD CONSTRAINT plan_not_null CHECK (plan IS NOT NULL) NOT VALID;
ALTER TABLE accounts VALIDATE CONSTRAINT plan_not_null;

SET client_min_messages = debug1;
ALTER TABLE accounts ALTER COLUMN plan SET NOT NULL;
RESET client_min_messages;

ALTER TABLE accounts DROP CONSTRAINT plan_not_null;

DEBUG:  existing constraints on column "accounts.plan" are sufficient to prove that it does not contain nulls

如果没有这个检查约束，同一条语句打印的则是 DEBUG: verifying table "accounts"，也就是全表扫描。列一旦成为 NOT NULL，这个检查约束就多余了，可以删除。

PostgreSQL 18 让这一步更简短。它接受 ALTER TABLE accounts ADD CONSTRAINT plan_nn NOT NULL plan NOT VALID，我们在 18.6 上验证过，之后的 VALIDATE CONSTRAINT 获取的是 SHARE UPDATE EXCLUSIVE。PostgreSQL 17 会以语法错误拒绝这条语句，所以在 Relyt One 上请使用上面的 CHECK 方式。

CREATE INDEX CONCURRENTLY 获取的是 SHARE UPDATE EXCLUSIVE 而不是 SHARE，所以构建期间写入可以照常进行。作为代价，正如官方文档所说，它要扫描两遍表，并等待可能使用或修改该索引的现有事务结束，因此工作量更大、耗时更长。它还有两条自己的规则。

它不能在事务块里运行：

BEGIN;
CREATE INDEX CONCURRENTLY accounts_email_idx ON accounts (email);
ROLLBACK;

ERROR:  CREATE INDEX CONCURRENTLY cannot run inside a transaction block

很多迁移工具会把每个文件包在一个事务里，所以请查一下你的工具如何针对单个迁移关闭这一行为。它仍然要等锁，所以也要给它设置超时。SET LOCAL 在事务之外不起作用（Postgres 会回应 WARNING: SET LOCAL can only be used in transaction blocks），所以请在它之前紧挨着使用会话级的 SET lock_timeout = '2s';，并通过直连连接执行。

另外，如果它失败了，会留下一个无效索引。在有重复值的列上建唯一索引，就能很容易地看到这一点：

CREATE UNIQUE INDEX CONCURRENTLY accounts_plan_key ON accounts (plan);
SELECT indexrelid::regclass AS index_name, indisvalid
FROM pg_index WHERE indrelid = 'accounts'::regclass;

ERROR:  could not create unique index "accounts_plan_key"
DETAIL:  Key (plan)=(free) is duplicated.

    index_name     | indisvalid
-------------------+------------
 accounts_pkey     | t
 accounts_plan_key | f
(2 rows)

（DETAIL 行里的重复值每次运行可能不同。）无效索引永远不会被查询使用。但取决于构建在哪个阶段失败，写入可能仍需维护它（查看同一系统目录中的 indisready）。因 lock_timeout 而放弃的并发构建同样会留下无效索引：我们在一张有长时间运行的写事务的表上执行过一次，它超时后留下了 indisvalid = f。无论原因是什么，都用 DROP INDEX CONCURRENTLY accounts_plan_key; 删除该索引，然后重试。

1. 在上面的表中查到这条语句。如果它获取的是 ACCESS EXCLUSIVE，或任何会阻塞写入的锁（SHARE、SHARE ROW EXCLUSIVE），无论它多快，都需要设置锁超时。
2. 在迁移事务的开头执行 SET LOCAL lock_timeout = '2s'（或你的应用能容忍的停顿时长），超时后重试。
3. 开始之前，在 pg_stat_activity 中检查长时间运行的会话和 idle in transaction 会话。
4. 外键和检查约束以 NOT VALID 添加，再在单独的步骤中 VALIDATE。
5. 在 PostgreSQL 17 上，通过一个经过验证的 CHECK 约束来添加 NOT NULL。
6. 用 CONCURRENTLY 构建和删除索引，在事务之外执行，配合会话级的 SET lock_timeout，完成后检查是否有 indisvalid = false。
7. 把任何会重写整张表的操作（integer 改 bigint、volatile 默认值、收窄类型）当作一个项目来对待，而不是一次迁移。
8. 通过直连连接运行迁移，不要经过事务级连接池。

上面的表能告诉你一条语句获取什么锁，却无法告诉你在你的数据上扫描或重写要花多长时间，也无法告诉你你的数据能否通过 VALIDATE 这一步。为此你需要生产规模的数据，而拿到一份副本往往才是最难的部分。

在 Relyt One 上，每个分支都是父分支的写时复制分叉，包含完整的数据集，并拥有自己的计算实例。从生产分支创建一个分支，在上面用 \timing on 运行迁移文件，用上文的文件节点检查判断它是否会重写整张表，并在正式执行之前弄清楚 VALIDATE 会不会在旧数据上失败。然后删除这个分支。生产环境完全不会感知到这次实验。

分支唯一无法演练的是锁队列本身，因为分支上没有其他任何东西在运行。它告诉你的是迁移拿到锁之后要持有多久；在生产环境中，保护你不至于在等锁时出问题的，仍然是 lock_timeout 和重试。另外请比较相对耗时，而不是绝对耗时：分支的计算实例规格可能与生产环境不同。

免费开始使用