Database-backed job queues have a bad reputation that is about ten years out of date. Before SKIP LOCKED, a table of pending jobs plus multiple workers meant lock contention: every worker's SELECT ... FOR UPDATE piled up behind the first one, and throughput collapsed exactly when the queue got busy. PostgreSQL added SKIP LOCKED in 9.5, MySQL added it in 8.0, and with it the pattern became genuinely good: transactional job claiming, no extra infrastructure, and jobs that commit or roll back atomically with the work that enqueued them.
I have run queue tables on both engines. The core pattern is nearly identical, which is a gift if you are migrating from MySQL to PostgreSQL. The differences live in the locking internals, the maintenance costs, and one PostgreSQL feature that changes the polling story entirely. This article covers both sides honestly, because a well-built InnoDB queue is a perfectly respectable thing.
The shape of the pattern
The idea is simple. Jobs are rows. A worker opens a transaction, selects a batch of pending rows with FOR UPDATE SKIP LOCKED, and the database hands it only rows no other worker currently holds. Locked rows are skipped instead of waited on, so ten workers polling the same table claim ten disjoint batches concurrently. The row locks are released at commit, so a worker that crashes mid-job automatically returns its rows to the pool when its connection dies and the transaction rolls back.
SKIP LOCKED deliberately returns an inconsistent view of the table: you see a subset of rows depending on who else holds locks. For a queue that is exactly what you want. For almost anything else it is a bug, which is why both engines gate it behind explicit syntax.
Batch size is the main tuning knob on both engines. Claim one row at a time and you pay a round trip per job; claim five hundred and a slow worker strands a large batch behind its transaction until it commits or dies. Ten to fifty rows per claim is a sane starting range for most workloads, adjusted by job duration. If jobs run for minutes rather than milliseconds, do not hold the claiming transaction open for the work itself: mark the rows as running, commit, and rely on a claimed_at timestamp plus a reaper query to recover jobs from workers that vanished.
The MySQL 8.0 claim pattern
In MySQL the claim is typically a transaction that selects, then updates, then does the work or hands it off:
START TRANSACTION;
SELECT id, payload
FROM jobs
WHERE status = 'pending'
ORDER BY id
LIMIT 10
FOR UPDATE SKIP LOCKED;
UPDATE jobs
SET status = 'running', claimed_at = NOW(), worker = 'worker-3'
WHERE id IN (1, 2, 3); -- ids from the SELECT
COMMIT;Two InnoDB details matter here. First, InnoDB locks index records, and it locks what it scans. Without an index that matches the WHERE and ORDER BY, the locking read walks far more of the table than it returns, and workers start colliding on rows they never intended to claim. A composite index on (status, id) keeps the scan and the locks tight. Second, at the default REPEATABLE READ isolation, locking reads take next-key locks; SKIP LOCKED sidesteps most of the resulting contention because conflicting locks are skipped rather than waited on, but keeping queue transactions short is still the main defense. Claim quickly, commit the claim, and do the slow work outside the claiming transaction if jobs run long.
The PostgreSQL claim pattern
PostgreSQL lets you compress claim-and-mark into a single statement, which is my preferred shape because there is no window between selecting and updating:
UPDATE jobs
SET status = 'running', claimed_at = now(), worker = 'worker-3'
WHERE id IN (
SELECT id FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 10
FOR UPDATE SKIP LOCKED
)
RETURNING id, payload;The subquery locks the candidate rows, the UPDATE marks them, and RETURNING hands the worker its batch in one round trip. Row locks in PostgreSQL are written into the tuples themselves rather than held in a shared lock table, so locking ten thousand rows does not exhaust lock memory. A partial index such as CREATE INDEX ON jobs (created_at) WHERE status = 'pending' keeps the claim query fast even when the table holds millions of completed rows, because the index only contains the rows workers actually look for.
The maintenance bill: purge on one side, vacuum on the other
Queue tables are update-heavy by design: every job row is written at least twice and often deleted. On InnoDB the old row versions go to undo logs and the purge threads clean them up; a busy queue with long-running transactions elsewhere on the server can contribute to purge lag, but the table itself stays compact. On PostgreSQL every UPDATE creates a new tuple version in the heap and leaves a dead one behind, so a hot queue table is a bloat generator. This is the real operational difference between the two implementations, and it is manageable if you respect it.
Practical PostgreSQL queue hygiene: set aggressive per-table autovacuum thresholds so vacuum visits the table frequently; consider a lower fillfactor so updates can stay on the same page as HOT updates and skip index maintenance; delete or archive completed jobs promptly rather than letting the table grow; and keep an eye on n_dead_tup for the queue table specifically. A queue table that looks fine at low volume can degrade sharply when a long transaction elsewhere blocks vacuum from reclaiming its dead tuples.
LISTEN/NOTIFY: the PostgreSQL bonus
The weak point of any polling queue is the poll interval. Poll every five seconds and your median job latency includes half of that; poll every 100 ms and you burn connections and CPU on empty checks. PostgreSQL has a native answer: LISTEN/NOTIFY. Workers execute LISTEN job_ready and block on the connection; the enqueuing transaction calls NOTIFY job_ready (or pg_notify) and the notification is delivered when that transaction commits, so workers never wake up for a job that rolled back.
Two honest caveats. Notifications are not durable: a worker that is disconnected when the NOTIFY fires never receives it, so you keep a slow polling loop as a safety net and treat NOTIFY as a latency optimization. And the payload is limited to a few kilobytes, so send a wake-up signal, not the job itself; the worker still claims through the SKIP LOCKED query, which remains the single source of truth. MySQL has no server-side equivalent, so MySQL queues either poll or bolt on an external signaling channel. This is one of the few places in a migration where PostgreSQL hands you a genuinely new capability rather than a different spelling of the same one.
Monitoring a queue table in production
Queues fail in characteristic ways: depth grows because workers died or a poison job is being retried forever; claim latency grows because the partial index bloated or the plan flipped; lock waits appear because someone ran a long transaction that touches job rows outside the claim path. Watch queue depth by status, the age of the oldest pending job, the latency of the claim query family, dead tuples and autovacuum activity on the table, and lock waits involving it. Claim queries are exactly the kind of hot, small, high-frequency statements where a regression is invisible in averages and obvious in per-query history, and vacuum behavior on the queue table deserves the scrutiny described in the PostgreSQL operations material.
How MonPG helps once your queue runs on PostgreSQL
MonPG is PostgreSQL-only, so it enters the picture when your queue does. A SKIP LOCKED queue is a small system with sharp failure modes, and MonPG covers the signals that matter: pg_stat_statements history for the claim and completion query families so you catch a plan or latency regression the day it happens, dead tuple and autovacuum tracking so queue-table bloat is visible before claim latency tells you about it, lock and blocking-chain evidence for the day a migration or backfill collides with the workers, and connection-state visibility for the LISTEN connections your workers hold. The PostgreSQL monitoring guide walks through the full setup. A queue you can see is a queue you can trust with real work.