# PostgreSQL 加字段锁表？每种 ALTER TABLE 拿的是什么锁，以及如何安全上线迁移 为什么一条单行 ALTER TABLE 就能让整张表的查询全部卡住？本文列出 PostgreSQL 17 上常见迁移各自获取的锁、哪些会重写整张表，以及用 lock_timeout、NOT VALID 和 CONCURRENTLY 让生产环境不停服的做法。 **一个只加一列可空字段的迁移，执行不到 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` 运行： ```sql 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` 状态的连接： ```sql -- 会话 A SET application_name = 'a_report'; BEGIN; SELECT count(*) FROM accounts; -- 保持事务打开 ``` 在第二个会话里运行迁移。添加一个可空列是教科书式的「安全」迁移： ```sql -- 会话 B SET application_name = 'b_migration'; ALTER TABLE accounts ADD COLUMN note text; ``` 它卡住了。在第三个会话里，运行你的应用每分钟要跑上千次的那种查询： ```sql -- 会话 C SET application_name = 'c_app'; SELECT email FROM accounts WHERE id = 42; ``` 它也卡住了，尽管它只读一行，而会话 A 也只是在读。再开第四个会话，问问 Postgres 谁在等谁： ```sql 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; ``` ```text 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 持有 ACCESS SHARE；迁移 B 在等待 ACCESS EXCLUSIVE；应用查询 C、D、E 在等的是 B，而不是 A](/blog/assets/adf6ceef3127f699.svg) 在我们的这次运行中，会话 A 在开始 8 秒后提交，B 随即立刻完成，而 C 这个单行查询耗时 **6.2 秒**。在真实流量下，这段时间里所有访问 `accounts` 的请求都会堆积起来，连接池被耗尽，这次故障看起来像是一条单行迁移引起的。确实是它引起的，但并不是因为迁移本身慢。 ## 方法 1：设置锁超时，然后重试 `lock_timeout` 会取消等锁时间超过上限的语句。重复同样的实验，但给会话 B 两秒： ```sql -- 会话 B SET application_name = 'b_migration'; SET lock_timeout = '2s'; ALTER TABLE accounts ADD COLUMN note text; ``` ```text ERROR: canceling statement due to lock timeout ``` 这一次 C 最多只等了两秒（我们这次运行中是 1.8 秒，而不是 6.2 秒）：它只等到 B 达到上限、退出队列为止。迁移失败了，而这正是目的：它以很小的代价失败，而不是把整张表拖垮。接下来重试即可。 要让这个做法在实践中奏效，有两个细节。 **在迁移的事务里使用 `SET LOCAL`。** `SET LOCAL` 只在当前事务结束前有效，之后自动恢复，因此不会泄漏到这条连接上接下来运行的任何东西里： ```sql 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` 要保持为英文： ```bash #!/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` 中的一行）时运行这个包装脚本。第一次尝试触发了超时，那个事务提交后，第二次尝试成功： ```text 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`）失败时可能会留下残留（下文会细讲），下一次尝试就会被它绊住。 在繁忙的表上执行迁移之前，也值得先找出会阻塞它的那个事务： ```sql 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 有没有重写整张表，可以比较前后的文件节点：重写会把表的新副本写入一个新文件。 ```sql 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; ``` ```text table_name | mode ------------+--------------------- accounts | AccessExclusiveLock accounts | ShareLock (2 rows) rewritten ----------- t (1 row) ``` （`\gset` 是 `psql` 命令，把结果保存到变量中。）下表中的每条语句，我们都用这种方法在一百万行的 `accounts` 表和两百万行的 `orders` 表上运行过。对于「阻塞」两列，我们在一个打开的事务里持有每把锁，并从另一个会话以 500 ms 的 `lock_timeout` 尝试执行单行 `SELECT` 和单行 `UPDATE`；其余内容则根据 [PostgreSQL 锁冲突表](https://www.postgresql.org/docs/17/explicit-locking.html#LOCKING-TABLES)中的锁模式推导得出。 | 迁移 | 表上的锁 | 阻塞读 | 阻塞写 | 重写整张表 | 在我们测试表上的耗时 | | ------------------------------------------------------ | ------------------------ | --- | ----- | -------- | ------------------ | | `ADD COLUMN note text` | `ACCESS EXCLUSIVE` | 是 | 是 | 否 | 不到 1 ms | | `ADD COLUMN status text NOT NULL DEFAULT 'active'` | `ACCESS EXCLUSIVE` | 是 | 是 | 否 | 不到 1 ms | | `ADD COLUMN added_at timestamptz DEFAULT now()` | `ACCESS EXCLUSIVE` | 是 | 是 | 否 | 不到 10 ms | | `ADD COLUMN token uuid DEFAULT gen_random_uuid()` | `ACCESS EXCLUSIVE` | 是 | 是 | **是** | 3.4 s | | `ALTER COLUMN score TYPE bigint`（原为 `integer`） | `ACCESS EXCLUSIVE` | 是 | 是 | **是** | 2.5 s | | `ALTER COLUMN plan TYPE varchar(50)`（原为 `varchar(20)`） | `ACCESS EXCLUSIVE` | 是 | 是 | 否 | 2 ms | | `ALTER COLUMN plan TYPE text`（原为 `varchar(20)`） | `ACCESS EXCLUSIVE` | 是 | 是 | 否 | 不到 1 ms | | `ALTER COLUMN plan TYPE varchar(10)`（原为 `varchar(20)`） | `ACCESS EXCLUSIVE` | 是 | 是 | **是** | 2.3 s | | `ALTER COLUMN plan SET NOT NULL` | `ACCESS EXCLUSIVE` | 是 | 是 | 否，但会扫描全表 | 0.24 s | | `DROP COLUMN`、`RENAME COLUMN` | `ACCESS EXCLUSIVE` | 是 | 是 | 否 | 不到 5 ms | | `ADD CONSTRAINT … CHECK (…) NOT VALID` | `ACCESS EXCLUSIVE` | 是 | 是 | 否 | 13 ms | | `VALIDATE CONSTRAINT`（检查约束） | `SHARE UPDATE EXCLUSIVE` | 否 | 否 | 否，但会扫描全表 | 78 ms | | `CREATE INDEX` | `SHARE` | 否 | **是** | 否 | 1.4 s | | `CREATE INDEX CONCURRENTLY` | `SHARE UPDATE EXCLUSIVE` | 否 | 否 | 否 | 比 `CREATE INDEX` 慢 | | `ALTER COLUMN … SET STATISTICS` | `SHARE UPDATE EXCLUSIVE` | 否 | 否 | 否 | 不到 1 ms | 外键会锁住两张表，所以单独列一张表。`orders` 是引用表，`accounts` 是被引用表： | 迁移 | `orders` 上的锁 | `accounts` 上的锁 | 阻塞哪张表的写入 | 耗时 | | ----------------------------- | ------------------------ | --------------------- | ---------- | ------------ | | `ADD FOREIGN KEY` | `SHARE ROW EXCLUSIVE` | `SHARE ROW EXCLUSIVE` | **两张表都阻塞** | 1.8 s（检查每一行） | | `ADD FOREIGN KEY … NOT VALID` | `SHARE ROW EXCLUSIVE` | `SHARE ROW EXCLUSIVE` | 两张表都阻塞 | 3 ms（只检查新行） | | `VALIDATE CONSTRAINT`（外键） | `SHARE UPDATE EXCLUSIVE` | `ROW SHARE` | 都不阻塞 | 1.2 s | 这些耗时来自本地容器，会随表的大小和硬件而变化，所以请把它们当作相对值，而不是绝对值。这两张表里有几点常常让人意外： - **这里所有添加、删除、重命名列或修改列类型的语句都会获取 `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` 放行读取，但会阻塞写入**，在整个构建期间都是如此。 ## 方法 2：现在 NOT VALID，稍后 VALIDATE `ADD CONSTRAINT` 通常会在持锁期间检查每一条已有的行。`NOT VALID` 跳过这一检查：约束立即对新插入和更新的行生效，已有的行之后由 `VALIDATE CONSTRAINT` 检查，而它获取的锁要弱得多。在上面的表中，直接添加外键会让两张表的写入都阻塞 1.8 秒。拆成两步之后，阻塞部分只用了 3 ms，而 1.2 秒的验证过程中读写都能正常进行。 迁移 1 就是方法 1 中的那个文件；如果你在那里已经运行过，只剩下迁移 2： ```sql -- 迁移 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` 状态，但仍会对新的写入生效。修复数据后再验证一次即可。 ### 对 NOT NULL 用同样的技巧 在 PostgreSQL 17 上，`SET NOT NULL` 没有 `NOT VALID` 形式，它会在 `ACCESS EXCLUSIVE` 下扫描整张表。但如果已经有一个经过验证的 `CHECK` 约束能证明该列没有空值，它就会跳过扫描。用 `client_min_messages = debug1` 可以看到这个判断： ```sql 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; ``` ```text 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` 方式。 ## 方法 3：并发构建索引 `CREATE INDEX CONCURRENTLY` 获取的是 `SHARE UPDATE EXCLUSIVE` 而不是 `SHARE`，所以构建期间写入可以照常进行。作为代价，正如[官方文档](https://www.postgresql.org/docs/17/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY)所说，它要扫描两遍表，并等待可能使用或修改该索引的现有事务结束，因此工作量更大、耗时更长。它还有两条自己的规则。 它不能在事务块里运行： ```sql BEGIN; CREATE INDEX CONCURRENTLY accounts_email_idx ON accounts (email); ROLLBACK; ``` ```text 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';`，并通过直连连接执行。 另外，如果它失败了，会留下一个无效索引。在有重复值的列上建唯一索引，就能很容易地看到这一点： ```sql CREATE UNIQUE INDEX CONCURRENTLY accounts_plan_key ON accounts (plan); SELECT indexrelid::regclass AS index_name, indisvalid FROM pg_index WHERE indrelid = 'accounts'::regclass; ``` ```text 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` 和重试。另外请比较相对耗时，而不是绝对耗时：分支的计算实例规格可能与生产环境不同。 [免费开始使用](/auth/signin?ref=blog-postgres-migration-locks)