SKIP LOCKED 队列与在线 DDL

准备中…重置数据集

这一节是本阶段的应用篇：把前面的锁知识用在两个最常见的实际问题上。

用 Postgres 做任务队列

先建一张任务表：

DROP TABLE IF EXISTS jobs;
CREATE TABLE jobs (
id         bigserial PRIMARY KEY,
status     text NOT NULL DEFAULT 'pending',
payload    text NOT NULL,
locked_at  timestamptz
);
INSERT INTO jobs (payload) SELECT 'job ' || i FROM generate_series(1, 10) i;
CREATE INDEX jobs_pending ON jobs (id) WHERE status = 'pending';
SELECT count(*) AS 待处理 FROM jobs WHERE status = 'pending';

▶ 运行⌘/Ctrl + Enter

注意那个部分索引——只索引 pending 的行。任务完成后 status 一变，这行就自动从索引里消失了。哪怕历史任务积累到上亿行，这个索引也只有几千项（阶段四讲过这个模式）。

朴素写法为什么是错的

-- ❌ 有竞态
SELECT id FROM jobs WHERE status = 'pending' ORDER BY id LIMIT 1;
UPDATE jobs SET status = 'running' WHERE id = $1;

两个 worker 同时 SELECT，会拿到同一个 id，然后同一个任务被执行两次。

加上 FOR UPDATE 能解决重复，但引入了新问题：

FOR UPDATE 会让 worker 排队编排回放 · 非实时执行

Session A

Session B

▶ 开始◀ 上一步重置0 / 5

正确写法：SKIP LOCKED

BEGIN;
SELECT id, payload
FROM jobs
WHERE status = 'pending'
ORDER BY id
FOR UPDATE SKIP LOCKED
LIMIT 3;

▶ 运行⌘/Ctrl + Enter

COMMIT;

▶ 运行⌘/Ctrl + Enter

SKIP LOCKED 的语义是：遇到已被别人锁住的行，直接跳过，继续往下找。

SKIP LOCKED 让 worker 各取所需编排回放 · 非实时执行

Session A

Session B

▶ 开始◀ 上一步重置0 / 4

生产环境常用的是一条语句搞定「取出并标记」：

UPDATE jobs
SET status = 'running', locked_at = now()
WHERE id IN (
SELECT id FROM jobs
WHERE status = 'pending'
ORDER BY id
FOR UPDATE SKIP LOCKED
LIMIT 2
)
RETURNING id, payload;

▶ 运行⌘/Ctrl + Enter

队列表的三个必备设计

1. 处理超时的兜底。 worker 崩溃时行锁随连接释放，但 status 已经是 running 了——任务卡死。需要一个清扫任务把 locked_at 过老的行改回 pending。

2. 队列表膨胀极快。 每个任务至少经历 pending → running → done 两次更新，全是死元组。这类表必须单独调低 autovacuum 阈值（阶段三）：

ALTER TABLE jobs SET (autovacuum_vacuum_scale_factor = 0.01);

更彻底的做法是处理完就删除，把已完成的任务归档到另一张表。

3. LISTEN / NOTIFY 替代轮询。 每 100ms 轮询一次数据库，即使队列是空的也在消耗资源。NOTIFY 让入队方主动唤醒 worker：

-- 入队方
NOTIFY job_queue;
-- worker 方
LISTEN job_queue;

注意 NOTIFY 在事务提交时才真正发出，且不保证送达（worker 断线期间的通知会丢）。所以它是「减少轮询频率」的优化，不能取代轮询兜底。

在线 DDL

锁那一节讲了为什么 ACCESS EXCLUSIVE 危险。这里给出常见变更的安全做法。

建索引

CREATE INDEX CONCURRENTLY jobs_payload_idx ON jobs (payload);

▶ 运行⌘/Ctrl + Enter

SELECT indexrelid::regclass::text AS 索引, indisvalid AS 有效
FROM pg_index WHERE indexrelid = 'jobs_payload_idx'::regclass;

▶ 运行⌘/Ctrl + Enter

CONCURRENTLY 只拿 SHARE UPDATE EXCLUSIVE，不阻塞读写。代价：

• 要扫两遍表，慢得多；
• 不能在事务块里执行（所以它必须单独占一个代码块）；
• 可能失败，留下一个 indisvalid = false 的废索引。失败后必须先 DROP INDEX CONCURRENTLY 再重来——废索引不会被查询使用，但会拖累所有写入。

上线后一定要检查：

SELECT indexrelid::regclass::text AS 索引, indisvalid
FROM pg_index WHERE NOT indisvalid;

▶ 运行⌘/Ctrl + Enter

加列

写法	是否安全
ADD COLUMN c int	✅ 瞬间完成（只改元数据）
ADD COLUMN c int DEFAULT 42	✅ PG 11 起也是瞬间的（默认值存在 catalog 里，读时补上）
ADD COLUMN c int NOT NULL 无默认值	❌ 直接报错，除非表是空的
ADD COLUMN c int GENERATED ALWAYS AS (...) STORED	⚠️ 要重写整张表

即使是「瞬间完成」的操作，也依然要拿 ACCESS EXCLUSIVE——瞬间完成 ≠ 安全，它仍然会排在长事务后面并堵住整个队列。所以：

SET lock_timeout = '3s';
ALTER TABLE jobs ADD COLUMN retry_count int DEFAULT 0;

▶ 运行⌘/Ctrl + Enter

SET lock_timeout = 0;
SELECT 'lock_timeout 已恢复' AS 状态;

▶ 运行⌘/Ctrl + Enter

加约束

直接 ADD CONSTRAINT 会全表扫描校验，期间持有强锁。分两步做：

ALTER TABLE orders ADD CONSTRAINT chk_amount_positive
CHECK (amount >= 0) NOT VALID;
SELECT conname AS 约束名, convalidated AS 已校验历史数据
FROM pg_constraint WHERE conname = 'chk_amount_positive';

▶ 运行⌘/Ctrl + Enter

NOT VALID 让约束立即对新数据生效，但跳过历史数据的校验——只需要一个瞬间的锁。然后单独校验历史数据：

ALTER TABLE orders VALIDATE CONSTRAINT chk_amount_positive;
SELECT conname AS 约束名, convalidated AS 已校验历史数据
FROM pg_constraint WHERE conname = 'chk_amount_positive';

▶ 运行⌘/Ctrl + Enter

VALIDATE 只拿 SHARE UPDATE EXCLUSIVE——不阻塞读写。同样的两步法适用于外键。

改列类型 / 删列

操作	代价
DROP COLUMN	✅ 瞬间（只标记删除，空间等 VACUUM 回收）
ALTER COLUMN TYPE varchar(50) → varchar(100)	✅ 不重写（只放宽长度）
ALTER COLUMN TYPE int → bigint	❌ 重写整张表 + 全程 ACCESS EXCLUSIVE
ALTER COLUMN SET NOT NULL	⚠️ 全表扫描校验，但 PG 12 起若已有等价的 CHECK ... NOT NULL 约束则可跳过

int → bigint 是最典型的「主键快用完了」场景。大表上正确的做法是加新列 + 双写 + 回填 + 切换，而不是一条 ALTER：

ALTER TABLE t ADD COLUMN id_new bigint;              -- 瞬间
-- 应用层双写，或者加触发器同步
UPDATE t SET id_new = id WHERE id_new IS NULL        -- 分批回填，每批几千行
  AND id BETWEEN $1 AND $2;
-- 建唯一索引 CONCURRENTLY，然后在一个短事务里切换主键

pg_squeeze、pgroll 这类工具把这套流程自动化了。

一份 DDL 检查清单

执行任何 schema 变更前：

1. 查长事务 —— pg_stat_activity 里有没有跑了几分钟的事务；
2. 设 lock_timeout —— 3~5 秒，拿不到就退出重试；
3. 一个事务只改一张表 —— 避免跨表死锁；
4. 索引用 CONCURRENTLY，约束用 NOT VALID + VALIDATE 两步；
5. 变更后检查 indisvalid，清理失败残留；
6. 低峰期执行，并在变更期间盯住锁等待。

本节验收

先自己回答，再点开对照。

为什么「SELECT 一个 pending 任务再 UPDATE 它」是错的？加 FOR UPDATE 之后又出现了什么新问题？

错在竞态：两个 worker 几乎同时执行 SELECT ... WHERE status = 'pending' ORDER BY id LIMIT 1，会拿到同一个 id，然后各自 UPDATE 并各自执行一遍——同一个任务被处理两次。

加 FOR UPDATE 消除了重复，但引入了新问题：B 同样想要「第一个 pending 任务」，发现 1 号被 A 锁住，于是等待。队列里明明还空着 9 个任务，B 却干等 A 处理完那 30 秒。并发度退化成 1——加了多少个 worker 都一样。

常见错误：以为把这两条语句包进一个事务就解决了竞态。事务提供的是原子性和快照可见性，不提供排他性——Read Committed 下两个 worker 的 SELECT 都能正常读到同一行 pending，谁也没被挡住。要独占一行只能显式加锁；而一加锁就撞上排队问题。SKIP LOCKED 就是从这个两难里逼出来的。

SKIP LOCKED 的语义是什么？它怎么同时解决重复处理和 worker 排队？

语义：遇到已被别人锁住的行，直接跳过，继续往下找。

• 不重复：能拿到的行一定是自己独占持锁的，别的 worker 不可能同时拿到；
• 不排队：跳过就跳过，绝不等待。A 拿 1 号，B 直接拿 2 号，两个 worker 全程无阻塞。

生产里通常写成一条语句完成「取出并标记」：

UPDATE jobs SET status = 'running', locked_at = now()
WHERE id IN (
  SELECT id FROM jobs WHERE status = 'pending'
  ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 2
)
RETURNING id, payload;

常见错误：以为「跳过」意味着任务会被漏掉。跳过的只是当前正被别人持锁的那些行——锁一释放（worker 提交，或者崩溃时随连接断开而释放），这些行对下一次查询立刻又是可见可锁的。真正会漏掉任务的不是 SKIP LOCKED，是 worker 崩在中途：那时行锁释放了，但 status 已经是 running，这一行再也不会被任何查询选中。所以队列表必须配一个按 locked_at 超时把行改回 pending 的清扫任务。

队列表为什么膨胀特别快？该怎么配置？

因为每个任务至少经历 pending → running → done 两次更新，而每次更新都写一个新版本、留一个死元组。队列表的写入量和任务吞吐成正比，全是垃圾。

配置：单独调低这张表的 autovacuum 阈值。

ALTER TABLE jobs SET (autovacuum_vacuum_scale_factor = 0.01);

更彻底的做法是处理完就删除，把已完成的任务归档到另一张表。

常见错误：看到「表里常年只有几十行 pending」就断定它不会膨胀，于是让它跟着全局默认配置走。膨胀量取决于更新次数，不是当前可见行数——一张稳态只有几十行的队列表，每天流过百万个任务，就是几百万个死元组。行数和物理大小在队列表上是彻底脱钩的，这正是它必须单独配置而不能吃全局默认的原因。

（详见表膨胀与 VACUUM）

CREATE INDEX CONCURRENTLY 的三个代价是什么？失败后必须做什么？

它只拿 SHARE UPDATE EXCLUSIVE，不阻塞读写。换来的三个代价：

1. 要扫两遍表，慢得多；
2. 不能在事务块里执行；
3. 可能失败，留下一个 indisvalid = false 的废索引。

失败后必须先 DROP INDEX CONCURRENTLY 清掉残留，再重来。

常见错误：以为废索引「反正查询不会用它，放着无害」。它确实不会被查询使用，但每一次 INSERT / UPDATE 都照样要维护它——纯亏损，只花成本不产生收益，而且完全没有症状。所以每次上线后要主动查一遍：

SELECT indexrelid::regclass::text FROM pg_index WHERE NOT indisvalid;

ADD CONSTRAINT ... NOT VALID 加上 VALIDATE CONSTRAINT 为什么比直接加约束安全？

直接 ADD CONSTRAINT 会全表扫描校验历史数据，而且全程持有强锁——表越大停摆越久。拆成两步，把「拿强锁」和「扫全表」分开：

• NOT VALID：跳过历史数据的校验，只需要一个瞬间的锁；
• VALIDATE CONSTRAINT：慢，要扫全表，但只拿 SHARE UPDATE EXCLUSIVE——不阻塞读写。

同样的两步法适用于外键。

常见错误一：以为 NOT VALID 意味着约束「还没生效」，所以这一步意义不大。恰恰相反——NOT VALID 的约束立即对新数据生效，跳过的只是历史数据的校验。做完第一步，脏数据就不会再增加了，VALIDATE 只是在补历史的账。

常见错误二：因为「只需要一个瞬间的锁」就省掉 lock_timeout。那个瞬间的锁仍然是 ACCESS EXCLUSIVE——瞬间完成 ≠ 安全，它照样会排在长事务后面，照样把后面所有查询堵在队列里。

（详见锁：为什么行锁不占内存，而 DDL 会阻塞读）

大表上把 int 主键改成 bigint，正确的流程是什么？

不能用一条 ALTER COLUMN TYPE——int → bigint 要重写整张表，全程 ACCESS EXCLUSIVE。正确的流程是加新列 + 双写 + 回填 + 切换：

ALTER TABLE t ADD COLUMN id_new bigint;              -- 瞬间
-- 应用层双写，或者加触发器同步
UPDATE t SET id_new = id WHERE id_new IS NULL        -- 分批回填，每批几千行
  AND id BETWEEN $1 AND $2;
-- 建唯一索引 CONCURRENTLY，然后在一个短事务里切换主键

关键在于把一次长时间的独占，换成一串各自都很短的操作。pg_squeeze、pgroll 这类工具把整套流程自动化了。

常见错误：以为「加个 lock_timeout 就能安全地跑那条 ALTER」。lock_timeout 管的是拿到锁之前的排队时间，管不了拿到锁之后。一旦锁到手，重写整张表的过程会全程持有 ACCESS EXCLUSIVE，时长由表的大小决定——十亿行就是几十分钟的全表停摆，lock_timeout 早已功成身退，一点忙都帮不上。区分「排队久」和「持锁久」是评估 DDL 风险的第一步：前者靠 lock_timeout 兜底，后者只能靠换方案。

注意并非所有类型变更都要重写：varchar(50) → varchar(100) 这种只放宽长度的不重写，DROP COLUMN 也是瞬间的（只标记删除，空间等 VACUUM 回收）。