# 客户不许用消息中间件？在 Postgres 里自建任务队列

> 一条 SKIP LOCKED 查询就能让多个 worker 分摊任务，难点在于处理卡住的任务和表膨胀。

原文: https://fdetimes.net/zh/guides/postgres-job-queue-skip-locked/

Postgres 里的一张队列表可能膨胀到几十 GB，而其中的有效数据只有几 MB。Microsoft 的 Postgres 博客记录过这种情况。它往往发生在那些刚建好时人人都以为“不过是一张小表加一条 SELECT”的系统里。

作为前线部署工程师（FDE），你一定会遇到这种场景。假设客户是一家银行或医院。运维团队不允许把 RabbitMQ 或 Kafka 引入已经审批过的基础设施，而你需要把几千份文档送进一条抽取数据管道。唯一允许你用的，就是已经在运行的那套 Postgres。

本指南就在这套 Postgres 上搭建一个够用的队列。你只需要一个可以做实验的 Postgres（本地或云上均可）和两个 `psql` 窗口。代码为了便于学习做过精简，还不能直接用于生产环境。

## 第 1 步：一张表、一个索引，就够了

先建一张带状态列的 jobs 表。部分索引只覆盖处于等待状态的行，因为 worker 只需要找到这些行。

```sql
CREATE TABLE jobs (
  id          bigserial PRIMARY KEY,
  status      text NOT NULL DEFAULT 'pending',
  payload     jsonb NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now(),
  started_at  timestamptz
);

CREATE INDEX jobs_pending_idx ON jobs (created_at)
  WHERE status = 'pending';
```

建好之后，运行 `\d jobs`，确认索引带有 `WHERE status = 'pending'` 条件。记住这个索引的名字：到第 6 步，它会成为最让人头疼的地方。

## 第 2 步：领取任务，互不干扰

根据 Postgres 官方文档，SKIP LOCKED 会跳过无法立即加锁的行。这个选项本来就是为多消费者的类队列表设计的。

Neon 的指南给出了基本模式：按时间排序取最早的待处理任务，`LIMIT 1`，再加上 `FOR UPDATE SKIP LOCKED`。哪个进程先到达某一行，就锁住它；其他进程跳过这一行，去取下一行。

从这里开始，领取任务有两种方式。方式 A 在整个处理过程中保持事务打开：

```sql
BEGIN;
SELECT id, payload FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;
-- 应用在这里处理任务
UPDATE jobs SET status = 'done' WHERE id = 1;  -- 把 1 换成 SELECT 刚返回的 id
COMMIT;
```

在 `psql` 里，复制 SELECT 返回的 id，粘贴到 UPDATE 中。在真实代码里，由应用把这个 id 作为参数传进去。

方式 A 非常干净。据 dbpro 博客介绍，如果 worker 中途崩溃，事务会回滚，任务自动回到队列。代价是每个运行中的任务都占着一个打开的事务，而调用模型或跑 OCR 可能要好几分钟。

方式 B 领取任务后立即提交：

```sql
UPDATE jobs
SET status = 'in_progress', started_at = now()
WHERE id = (
  SELECT id FROM jobs
  WHERE status = 'pending'
  ORDER BY created_at
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
RETURNING id, payload;
```

这样事务非常短，但你得自己处理挂掉的 worker。第 4 步会讲到。

## 第 3 步：证明两个 worker 不会领到同一个任务

先插入几个任务用于测试：

```sql
INSERT INTO jobs (payload) VALUES
  ('{"doc": 1}'), ('{"doc": 2}'), ('{"doc": 3}');
```

打开两个 `psql` 窗口。在第一个窗口输入 `BEGIN;`，运行方式 A 的 SELECT，但**先不要提交**。在第二个窗口做完全相同的操作。

如果一切正确，第二个窗口会立即返回另一个 id，而不是卡住。如果它一直在等，多半是忘了写 `SKIP LOCKED`。测试结束后，在两个窗口里分别运行 `ROLLBACK;`。

把这个测试带到客户的机器上，你就有了可以当场展示给运维团队的证据。他们往往不太相信纸面上的论证，但两个并排运行的终端窗口要有说服力得多。

## 第 4 步：卡住的任务谁来清理？

对于方式 B，Neon 的指南提醒：worker 出故障时，任务可能永远卡在 `in_progress`。它建议用一条查询找出运行时间过长的任务。换句话说，你需要一个 reaper。

```sql
UPDATE jobs
SET status = 'pending', started_at = NULL
WHERE status = 'in_progress'
  AND started_at < now() - interval '15 minutes';
```

15 分钟只是示例。阈值要比你在客户真实数据上测到的最慢处理时间更长。阈值太短，reaper 会把仍在运行的任务放回队列，这个任务就会被处理两次。

所以 worker 应当是幂等的：同一份文档再处理一次，不能生成两条结果记录。

测试方法：让一个 worker 用方式 B 领取任务，杀掉这个进程，等过了阈值再运行 reaper。任务应当回到 `pending`。

**要点:** 加锁查询是最简单的部分；队列是否可靠，取决于你如何清理卡住的任务。

## 第 5 步：唤醒 worker，但不要只依赖通知

如果 worker 每隔几秒查一次表，大多数查询都找不到新任务。于是你会想用 LISTEN/NOTIFY。根据 Postgres 文档，NOTIFY 是事务性的：只有事务提交时，事件才会送达。因此，在同一个事务里写入任务并发送通知是安全的。

```sql
-- 生产者端
BEGIN;
INSERT INTO jobs (payload) VALUES ('{"doc": 4}');
NOTIFY jobs_channel;
COMMIT;

-- worker 端
LISTEN jobs_channel;
```

有一点常被忽略：通知只会送达已经执行过 LISTEN 的会话。正在重启的 worker 什么也收不到。队列的真实状态始终在 jobs 表里，所以 worker 仍然要定期轮询，确保不漏掉任何任务。

文档还警告，NOTIFY 队列可能被填满，此时调用 NOTIFY 的事务会在提交时失败。原因是某个监听者停留在一个长事务里。如果 worker 既执行 LISTEN、又在处理期间持有事务，这就是方式 A 的一个隐性代价。用于 LISTEN 的连接要和干活的连接分开。

## 第 6 步：在客户的 DBA 问起之前，先测量膨胀

Microsoft 的 Postgres 博客把机制讲得很清楚：死元组的累积速度快于 autovacuum 的清理速度。受影响最严重的是 pending 状态上的部分索引。在持续写入的表上，vacuum 和 worker 还可能相互干扰。

你可以在自己的机器上制造这种压力。下面是一个精简的练习：往表里塞 10,000 个模拟任务，然后像一次集中处理那样把它们全部标记为 done。

```sql
INSERT INTO jobs (payload)
SELECT jsonb_build_object('doc', g)
FROM generate_series(1, 10000) AS g;

UPDATE jobs SET status = 'done' WHERE status = 'pending';
```

然后测量死元组数量，以及表和索引的大小：

```sql
SELECT n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'jobs';

SELECT pg_size_pretty(pg_relation_size('jobs'))             AS table_size,
       pg_size_pretty(pg_relation_size('jobs_pending_idx')) AS pending_index_size;
```

你会看到 `n_dead_tup` 随 UPDATE 次数上升，而 pending 索引并没有缩小，尽管此时已经没有等待中的任务。把这段代码多跑几次，记录每一轮的结果。你会亲眼看到，队列表膨胀的速度远远快于其中真实数据的增长。

在客户现场，从第一周起就让这两条测量查询每天运行，按日期保存结果并设置告警阈值。不要等到领取查询明显变慢了才去找原因。如果膨胀问题由你先提出来，而不是等客户的 DBA 团队自己发现，他们会更信任你。

## 让队列出问题的三个陷阱

最常见的陷阱是觉得 SKIP LOCKED 好用，就把它用到业务查询上。Postgres 文档说得很明确：跳过被锁定的行，意味着你看到的是不一致的数据视图。所以这个选项不适合通用场景，只适合类队列表。

第二个陷阱是以为 SKIP LOCKED 会跳过所有类型的锁。实际上它只作用于行级锁；表级的 ROW SHARE 锁照常获取。最后一个陷阱是写 `FETCH ... WITH TIES` 来一次取走时间戳相同的一组任务：Postgres 不允许 SKIP LOCKED 与这种语法一起使用。

## 什么时候该换方案？

如果客户允许装扩展、但不允许单独部署消息中间件，可以考虑 pgmq。它是一个完全运行在 Postgres 内部的 SQS 风格队列。根据项目仓库的说明，pgmq 保证每条消息在可见性超时时间内恰好投递一次。

可见性超时的作用和你刚写的 reaper 差不多，但它并不能免除第 4 步的两条教训：超时时间仍然要长于真实处理时间，worker 仍然必须是幂等的。

把这段经历写进简历时，要写清楚约束条件以及你如何应对，而不是只写“熟悉 Postgres”。例如：“客户不允许使用消息中间件；在现有 Postgres 上搭建队列，用 reaper 处理卡住的任务，并每日监控表膨胀。”当 FDE 招聘启事提到“on-prem”或“客户基础设施”时，这就是你在面试中该讲的故事。

Postgres 里的队列不是权宜之计。只要你清楚它会在哪里出问题，它就能让你在客户已经信任的基础设施上完成交付。

**本周动手试试:**

- 打开两个 psql 窗口，在两个事务中运行同一条领取查询，确认两个窗口拿到的 id 不同
- 在处理中途杀掉一个 worker，然后写出 reaper 查询，把超时的 in_progress 任务放回 pending
- 往 jobs 表里塞 10,000 个模拟任务，记录前后的 n_dead_tup 以及表和索引的大小，作为交给客户 DBA 团队的运维手册的基线

## 信源

- [SELECT (PostgreSQL Documentation)](https://www.postgresql.org/docs/current/sql-select.html)

- [Queue system using SKIP LOCKED in Lakebase Postgres](https://neon.com/guides/queue-system)

- [PostgreSQL FOR UPDATE SKIP LOCKED: The One-Liner Job Queue](https://www.dbpro.app/blog/postgresql-skip-locked)

- [Potential Consequences of Using Postgres as a Job Queue](https://techcommunity.microsoft.com/blog/adforpostgresql/potential-consequences-of-using-postgres-as-a-job-queue/4514332)

- [NOTIFY (PostgreSQL 18.7 Documentation)](https://www.postgresql.org/docs/current/sql-notify.html)

- [pgmq/pgmq: A lightweight message queue. Like AWS SQS and RSMQ but on Postgres](https://github.com/pgmq/pgmq)
