主題: PostgreSQL

PostgreSQL SKIP LOCKED 能做 Job Queue，但不是魔法 Broker

SKIP LOCKED 能避免 workers 爭搶同一筆 job，可靠 delivery 仍需要短 transaction、lease、retry、idempotency 與明確退場條件。

「Just use Postgres」拿來做 job queue，通常是很好的建議——直到它悄悄不再是好建議。

吸引人的理由很實際：application 可以在同一個 transaction 寫入 business record 與 background job，不必多部署一套 service，不必收拾 dual-write 中間狀態，也不用再學另一種 durability model。PostgreSQL 官方甚至直接寫明，SKIP LOCKED 可用來降低多個 consumers 存取 queue-like table 時的 lock contention。

陷阱是把這個 clause 當成整套 queue。

SKIP LOCKED 只解決一個很窄的 concurrency 問題：某個 worker 鎖住候選 row 時，其他 worker 可以略過它，不必原地等待。它不會替你決定 worker crash 怎麼辦、HTTP request 成功兩次怎麼辦、遺失的 job 如何救回，也不會阻止高頻寫入把 autovacuum 壓垮。

我的判斷原則是：

當 transactional enqueue 與維運簡單度比 broker features 更重要時，用 PostgreSQL 做 job queue 很合理；但 delivery 要按 at-least-once 設計，side effect 必須 idempotent，而且流量替你決定以前，就要先寫好退場訊號。

SKIP LOCKED 到底保證什麼？

一般的 SELECT ... FOR UPDATE 遇到已被其他 transaction 取得的 row lock 會等待。加上 SKIP LOCKED 後，PostgreSQL 會略過當下無法立即鎖定的 rows，因此多個 workers 能各自 claim 不同 jobs，不用再寫 application-level 協調機制。

這個行為也直接帶出兩條界線：

• 結果刻意不是一致的 data view。PostgreSQL 說它不適合 general-purpose query，但適合 queue-like table。
• 它不是 strict FIFO。較舊的 job 若被鎖住，較新的可用 job 可能先執行。

這兩點都不是 bug。Work queue 通常重視持續前進，不需要完美 snapshot；但產品若承諾 global ordering，這個 primitive 從一開始就不對，除非先把工作切成各自有序的 streams。

Claim 要 atomic，transaction 要立刻結束

可靠 pattern 是短暫的 claim transaction，不是讓 transaction 陪 job 跑完整段流程：

WITH next_job AS (
  SELECT id
  FROM jobs
  WHERE status = 'pending'
    AND run_at <= now()
  ORDER BY priority DESC, run_at, id
  FOR UPDATE SKIP LOCKED
  LIMIT 1
)
UPDATE jobs AS j
SET status = 'running',
    locked_at = clock_timestamp(),
    locked_by = $1,
    attempts = attempts + 1
FROM next_job
WHERE j.id = next_job.id
RETURNING j.*;

CTE 會選出並 lock 一筆符合條件的 row；UPDATE ... RETURNING 在同一個 statement 記錄 ownership。拿到 row 就立刻 commit。寄信、上傳、model call 或產生報表放到 transaction 外執行，完成後再用另一個短 transaction 標記完成。

讓 row lock 一路陪著外部工作，看起來很像「確保 ownership」，其實只是把 database lock duration 綁在 network latency 與第三方 failure 上。PostgreSQL 的 locking guidance 也直接警告，不該讓 transaction 長時間保持開啟。

真正的轉折在這裡：只 claim 一次，不等於 side effect 只發生一次。

假設 worker 已經扣款，卻在寫回 status = 'completed' 以前 crash。Rescue process 最後會 retry 這筆 job；payment provider 若沒有收到穩定的 idempotency key，客戶可能被扣款兩次。

務實的 contract 是 at-least-once delivery。設計至少要包含：

1. 對外可見的 operation 使用固定 idempotency key，通常由 job ID 或 business operation ID 衍生。
2. 若過期 worker 不得蓋掉已被救援的 job，完成更新要檢查目前 owner。
3. 儲存 attempts、last_error 與 run_at，讓 retry 有上限、有 exponential backoff，不會變成 hot loop。
4. 超過上限的 job 移到可見的 failed state。沒有 inspection 與 replay 工具的 dead-letter state，只是一座比較安靜的資料墳場。
5. 由 reaper 找出 locked_at lease 已過期的 running jobs，送回 pending 或標記失敗。

Lease 必須長過一般 job duration，而且超時要能觀察。真正的長任務應加明確 heartbeat，或拆成更小、可 resume 的 units；別用一個六小時 database transaction 解決不確定性。

Hot path 越小越好

Queue table 的更新頻率遠高於普通 business table，physical design 也該反映這件事。

先建立與 claim query 對齊的 partial index：

CREATE INDEX jobs_claim_idx
ON jobs (priority DESC, run_at, id)
WHERE status = 'pending';

這能把 completed 與 failed rows 排除在 hot index 外。Query predicate 必須與 partial-index predicate 足夠一致，PostgreSQL planner 才認得；partial index 不是會自行猜測語意的 pattern matcher。

接著讓 table 保持無聊：

• Worker 用不到的 indexes 就不要建，每次 state transition 都要維護它們。
• 用 bounded batches archive 或刪除 completed jobs，別讓歷史資料永遠和 hot rows 住在一起。
• 監控 dead tuples、table/index size、autovacuum progress、claim latency、queue age、retry rate 與 stuck leases。
• 從 measurement 調 autovacuum。UPDATE 與 DELETE 會留下必須由 VACUUM 回收的舊 row versions；queue 邏輯正確，不代表 maintenance cost 很小。

近期一篇 daily.dev 熱門文章整理了 high concurrency 下更深的 failure mode：MultiXact SLRU contention、WAL volume、snapshot overhead 與 bloat，可能讓看似簡單的 query 突然掉下懸崖。原始文章是很好的警告，但其中約略的 worker threshold 不是通用 capacity limit。Row width、indexes、transaction length、connection count、storage、job duration 與 cleanup policy 都會移動界線；請 benchmark 真實 workload。

LISTEN/NOTIFY 是門鈴，不是 queue

每幾秒 polling 一次很簡單，很多場合也已經足夠。若 idle latency 很重要，NOTIFY 可以在 enqueue transaction commit 後叫醒 listeners。

Durable job 仍要放在 table，只把 notification 當成「請檢查 table」。PostgreSQL 把 NOTIFY 定位為簡單的 interprocess signal；同一 transaction 內、channel 與 payload 相同的重複通知會被合併，而且 payload 有大小限制。Worker 重新連線後，仍必須透過 query 找回 pending jobs。

這樣分工最乾淨：

• Table：durable state、retries、ownership、inspection。
• NOTIFY：低延遲提示。
• Periodic poll：提示或 connection 遺失時的 recovery。

什麼時候 PostgreSQL 是對的 queue？

這些條件成立時，它是很好的 default：

• Enqueue 必須與相鄰 relational data 在同一 transaction commit。
• Jobs 只有單一路徑、fan-out 不高，也能接受 polling latency。
• 團隊維運一套 PostgreSQL，明顯比兩套 distributed systems 更可靠。
• Queue traffic 相對於 database 的 primary workload 很小。
• 能接受 at-least-once processing 與 application-level idempotency。

當需求變成多個獨立 consumer groups、replay、partitioned ordering、極高的持續 dispatch throughput、長期 retention，或 queue traffic 絕不能干擾 transactional queries，就該選 purpose-built broker。

最好的 migration trigger 不是一個流行的 jobs-per-second 數字，而是 queue 已吃掉 database reliability budget 的證據：claim latency 上升、autovacuum 追不上、lock/SLRU waits、replica lag、WAL pressure，或 business queries 開始和 workers 搶資源。

小型 production checklist

在把一張 table 稱為 queue 以前，先回答：

• Enqueue 能否與 business change 放在同一 transaction？
• Claim 是否為單一 atomic statement，而且立刻 commit？
• 誰負責回收 expired lease？
• 哪些 side effects 已 idempotent？由哪個 key 強制？
• Retry 如何 delay、封頂、inspection 與 replay？
• Partial index 是否完全對齊 claim predicate 與 ordering？
• Completed rows 如何移除，才不會留下無限增長的 maintenance problem？
• 哪些 metrics 代表 workload 已超過 PostgreSQL 的界線？

如果答案一頁內寫得完，PostgreSQL 很可能就是你能維運的最簡單可靠 queue。如果答案已經在重造 consumer groups、replay logs、partition ownership 與 flow control，database 正在請你別再叫它假裝 broker。

結論：一個 clause 消除 contention，沒有消除責任

FOR UPDATE SKIP LOCKED 的價值正是它夠小。它讓 PostgreSQL workers 用很少的 machinery 分配可用 rows，而 transactional enqueue 又能消除一整類 dual-write failures。

要保住這份優勢，就得誠實補齊其餘部分：短 claim transaction、at-least-once delivery、idempotent effects、leases、bounded retries、精準 index、理解 vacuum 的 cleanup，以及可觀察的退場條件。

PostgreSQL 可以是很好的 job queue。真正讓它變差的，是拿「我們已經有 database」代替 delivery design。

外部參考資料

• PostgreSQL：SELECT locking clause 與 SKIP LOCKED
• PostgreSQL：Explicit locking
• PostgreSQL：Partial indexes
• PostgreSQL：Routine vacuuming
• PostgreSQL：NOTIFY
• daily.dev：Potential Consequences of Using Postgres as a Job Queue
• Richard Yen：Potential Consequences of Using Postgres as a Job Queue
• Reddit：Postgres message queue 討論