PostgreSQL 一条 ALTER 为什么堵住所有 SELECT：pg_blocking_pids 找根源 + lock_timeout 实测
在 PostgreSQL 17.11 测试库、100 万行合成数据上复现：一个 idle in transaction 的会话让 ALTER TABLE 排队，排在后面的普通 SELECT 被堵 9 秒；加上 lock_timeout = '3s' 后只堵 2 秒。附各类 ALTER 的耗时实测和只读锁等待链 SQL。
ALTER TABLE 要 AccessExclusiveLock，它一旦排队，后面连最普通的 SELECT 都得等。实测没设 lock_timeout 时 SELECT 等了 9 秒，设了 3 秒超时后只等 2 秒。先用 pg_blocking_pids 找到 idle in transaction 的根源，再给 DDL 加 lock_timeout 和重试。
适用对象
- 加可空列、加常量默认值的列只改系统表，实测约 0.03 秒；加 clock_timestamp() 这类易变默认值、改 int→bigint 会重写整表，100 万行约 0.7–0.95 秒。
- ALTER 要 AccessExclusiveLock。会话 A 改了一行没提交，ALTER 排队 10 秒；排在 ALTER 后面的 SELECT 本来和 A 不冲突，也被堵了 9 秒。
- 只读 SQL 用 pg_blocking_pids() 画出链条：SELECT ← ALTER ← A（idle in transaction）。根源只有一个，处理 A，不要先杀 ALTER。
- 给 DDL 设 SET lock_timeout = '3s'：实测 ALTER 3.03 秒后放弃，SELECT 只等了 2.03 秒。配合重试循环，是迁移脚本最便宜的保险。
先看 ALTER 本身要多久
同一张表依次执行几种常见变更。PostgreSQL 11 起，加带常量默认值的列不再重写表；但易变默认值（如 clock_timestamp()）和改列类型仍要重写全表，期间一直持有 AccessExclusiveLock。
本节判断
- 重写类操作的耗时随表大小线性增长。79 MB 不到 1 秒，几十 GB 的表就是十几分钟到几十分钟，整个过程表都不可读写。这类变更要放到低峰期，或者改成「加新列 → 分批回填 → 切换」的方式。
| 操作 | 第 1 轮 | 第 2 轮 | 说明 |
|---|---|---|---|
| ADD COLUMN note1 text（可空） | 0.029 | 0.026 | 只改系统表 |
| ADD COLUMN note2 text DEFAULT 'n/a' | 0.026 | 0.028 | 常量默认值，不重写 |
| ADD COLUMN note3 timestamptz DEFAULT clock_timestamp() | 0.864 | 0.739 | 易变默认值，重写全表 |
| ALTER COLUMN uid TYPE bigint | 0.950 | 0.798 | 改类型，重写全表 |
| CREATE INDEX CONCURRENTLY idx_orders_uid | 0.428 | 0.397 | 不阻塞读写，但不能放在事务块里 |
复现：SELECT 被一条排队的 ALTER 堵住
会话 A：BEGIN; UPDATE orders SET status='paid' WHERE id=1; 然后不提交（idle in transaction），持有表上的 RowExclusiveLock。2 秒后会话 B 执行 ALTER TABLE orders ADD COLUMN …，需要 AccessExclusiveLock，与 A 冲突，开始排队。再过 1 秒，会话 C 执行 SELECT count(*) FROM orders WHERE id <= 10，只需要 AccessShareLock。
C 和 A 本来不冲突，但它排在 B 后面。A 在第 12 秒提交，B 才拿到锁，C 才返回。两轮结果一致：B 用时 10.00 秒，C 用时 9.00 秒。
第二个场景只改一处：B 在 ALTER 前先执行 SET lock_timeout = '3s'。B 在 3.03 秒后报 canceling statement due to lock timeout 并退出队列，C 随即返回，总共只等了 2.03 秒。
| 会话 | 无 lock_timeout | lock_timeout = '3s' |
|---|---|---|
| B：ALTER TABLE … ADD COLUMN | 10.004 / 10.003（等 A 提交后成功） | 3.028 / 3.027（超时报错退出） |
| C：普通 SELECT | 9.003 / 9.003 | 2.026 / 2.026 |
普通 SELECT 被堵的时间
ALTER 无 lock_timeout
9.0 s
ALTER 设 lock_timeout=3s
2.0 s
PostgreSQL 17.11 测试库，合成数据；会话 A 保持 idle in transaction 约 12 秒。
只读 SQL 怎么画出这条链
等待期间我跑了下载区的只读 SQL（只查 pg_stat_activity、pg_locks 和 pg_blocking_pids()，不 cancel、不 terminate）。第一段输出两行：ALTER 的 blocked_by 是 A 的 pid，等了 2.5 秒；SELECT 的 blocked_by 是 ALTER 的 pid，等了 1.5 秒。注意 SELECT 的阻塞者是 ALTER 而不是 A，这正是锁队列的特征。
第二段找根源：挡住了别人、自己没被挡的会话。只有一行，state = idle in transaction，事务已开 4.5 秒，最后一条语句是那句 UPDATE。第三段按表汇总锁模式：RowExclusiveLock 已授予 1 个，AccessExclusiveLock 和 AccessShareLock 各有 1 个在排队。
本节判断
- 处理顺序：先找业务方让根源会话提交或回滚；确需处理时先 pg_cancel_backend(root_pid)，无效再 pg_terminate_backend(root_pid)。只处理根源，不要先杀排队的 ALTER 或查询。
- 要看到其他会话的 query，执行账号需要是超级用户或 pg_read_all_stats 成员；否则 query 列会显示 insufficient privilege。
迁移脚本的三道保险
这三项都只影响设置它的会话或账号，不需要改全局配置，也不需要重启。
我在每个 DDL 迁移里都会加的设置
- 01
1
lock_timeout + 重试
每条 DDL 前 SET lock_timeout = '3s'（按业务容忍度调）。失败后等几秒重试，最多重试 N 次，全部失败就告警，让人来看是谁拿着锁。
- 02
2
statement_timeout 兜底
重写类操作给一个上限，避免一句 ALTER 在高峰期跑十几分钟。
- 03
3
治理空闲事务
给应用账号设 idle_in_transaction_session_timeout（比如 5 分钟），定期跑只读 SQL 第四段，看有没有超过 60 秒的空闲事务。
参考依据
以下来源用于确认市场趋势、政策背景和术语边界；具体落地方案仍以客户的数据范围、权限和交付目标为准。
常见问题
lock_timeout 和 statement_timeout 有什么区别？
lock_timeout 只限制等锁的时间，拿到锁之后执行多久不管；statement_timeout 限制整条语句的总时长。DDL 两个都要：前者防止排队堵别人，后者防止重写全表跑太久。
CREATE INDEX CONCURRENTLY 也会被长事务影响吗？
会。它不阻塞读写，但需要等待在它开始前启动的事务结束；有长事务时它会一直等，而且失败后会留下一个 INVALID 索引，需要手动删除后重建。
这些数字能代表生产环境吗？
不能。这是本机测试库、100 万行合成数据，只说明机制和量级。生产上并发更高，一条排队的 ALTER 几秒内就能占满连接池。