The Message Queue You Built Is a Table With Anxiety

Every backend I’ve worked on eventually grows a table called something like jobs or tasks, with a status column that cycles through pending, processing, done and failed. A cron job or worker loop polls it every few seconds, grabs whatever’s pending, and marks it processing while it works. Nobody sat down and decided to build a message queue. It accreted, one small feature at a time, because “just add a status column” always looks cheaper than “stand up RabbitMQ.” The trouble is that a jobs table without careful locking isn’t a simpler queue. It’s a queue that’s forgotten to guarantee the one thing queues exist to guarantee: that a message gets processed once, not zero times and not twice.

I’ve debugged this exact failure at more than one place, and it’s always the same story. Two worker processes poll the table at the same moment, both see the same row marked pending, both update it to processing, and both start executing the job. Nobody designed for that race condition because nobody thought of the table as a queue with concurrency semantics — they thought of it as a table, full stop. Understanding why the naive version breaks, and what a proper queue actually does underneath its API, is the difference between a jobs table that works by luck and one that works by design.

Why polling a status column races

The naive poll loop looks roughly like this, and it’s the version I’ve seen shipped more times than I can count:

1
2
3
4
5
6

-- worker A and worker B run this at nearly the same instant
SELECT id, payload FROM jobs WHERE status = 'pending' LIMIT 1;
-- both get row 42

UPDATE jobs SET status = 'processing' WHERE id = 42;
-- both succeed, because neither transaction has committed yet

Without an explicit lock, the SELECT and the UPDATE are two separate round trips, and nothing stops a second worker from reading the same row between them. Under light load this might run for months without anyone noticing, because the window is small. Under real load — several workers, larger batches, slower jobs — it becomes routine, and the symptom is duplicate emails, duplicate charges, or duplicate webhook deliveries, which is precisely the kind of bug that only shows up in production because staging never has enough concurrent workers to expose it.

The fix that a lot of teams reach for first is a full table lock or a global mutex around the poll-and-update step, which works but throttles every worker down to strictly serial processing — you’ve built a queue with a single lane regardless of how many workers you’re running. The fix that actually scales is row-level locking scoped to exactly the rows being claimed, which is precisely what SELECT ... FOR UPDATE SKIP LOCKED gives you in Postgres.

What FOR UPDATE SKIP LOCKED actually does

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

BEGIN;

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

-- this worker now holds row locks on the 10 rows it selected;
-- any other worker running the same query at the same instant
-- skips those rows entirely and locks a different 10

UPDATE jobs SET status = 'processing' WHERE id = ANY(:selected_ids);

COMMIT;

FOR UPDATE takes a row lock on every row the select returns, inside the current transaction. SKIP LOCKED tells Postgres that if a row is already locked by another transaction, don’t wait for it and don’t error — just skip it and return the next unlocked row instead. Put those together and you get exactly the behaviour a message queue promises: any number of workers can poll concurrently, each one gets a distinct batch of rows, and nobody blocks waiting for anybody else. This is not a workaround bolted onto Postgres for queue use cases specifically — it’s a fifteen-year-old feature originally built for exactly this pattern, and it’s fast enough that Postgres itself is a completely reasonable queue backend for workloads under a few thousand jobs a second, which is the overwhelming majority of internal job queues most teams will ever run.

The parts a jobs table still has to earn

Locking solves the double-processing race, but a real queue makes several other guarantees that a naive table doesn’t get by accident. Visibility timeout is the first: if a worker claims a job, crashes mid-processing, and never marks it done, a real queue eventually makes that message visible again for another worker to pick up. A jobs table needs this built explicitly — typically a claimed_at timestamp and a periodic sweep that resets any row that’s been processing for longer than the slowest job should reasonably take, back to pending. Skip this and a single worker crash leaves jobs stuck in processing forever, silently, because nothing is watching for jobs that never finished.

Retry and backoff is the second. A real queue distinguishes “this message failed and should be retried” from “this message has failed enough times that retrying it is pointless and it should go to a dead-letter queue for a human to look at.” A jobs table needs an attempts counter and a policy: increment on failure, retry with exponential backoff up to some ceiling, then flip to a dead status once the ceiling’s hit. Without that ceiling, one systematically bad message — a malformed payload, a downstream API that’s permanently down — retries forever and quietly burns through your worker capacity on a job that will never succeed.

Ordering is the third, and the one people most often assume for free and don’t get. ORDER BY created_at in the select gives you approximate FIFO ordering under low concurrency, but the moment you have more than one worker pulling batches, two jobs created a millisecond apart can finish in either order, because they’re processed by different workers running at different speeds. If your application logic actually depends on strict ordering — updating the same customer record in the sequence the events arrived — a jobs table needs a partition key (customer ID, say) and logic to ensure only one worker processes jobs for a given key at a time, which is the same problem Postgres tuning for a homelab touches on when discussing connection and lock contention under concurrent access, and it’s not a problem SKIP LOCKED solves on its own.

At-least-once is the guarantee you actually get

It’s worth being precise about what guarantee any of this buys you, because “exactly once” is a phrase that gets thrown around loosely and it isn’t what SKIP LOCKED plus a retry ceiling actually delivers. What you get is at-least-once delivery: every job will be attempted, possibly more than once, if a worker dies after marking a row processing but before committing whatever side effect the job was supposed to cause — sending an email, charging a card, writing to another system. The row-lock guarantees only one worker is running a given job at any single instant; it does nothing to guarantee that a worker which crashes halfway through a side effect won’t have that job picked up again by the visibility-timeout sweep and re-run from the start.

The practical fix is designing every job handler to be idempotent, so that running it twice produces the same end state as running it once. For a webhook delivery, that usually means the receiving side deduplicates on a message ID rather than trusting the sender never repeats itself. For a database write, it means an upsert keyed on a natural identifier rather than a blind insert, so a duplicate attempt overwrites the same row instead of creating a second one. For anything involving money, it means a client-generated idempotency key attached to the job payload and checked against a table of already-processed keys before the charge goes through — Stripe’s API popularised this pattern precisely because “retry a failed request safely” and “a message queue may redeliver” are the same underlying problem wearing different names. A jobs table that skips this step hasn’t actually built a queue with weaker guarantees than RabbitMQ or SQS; it’s built the same at-least-once guarantee those systems give you, and just hasn’t done the idempotency work that’s supposed to sit on top of it regardless of which broker is underneath.

Troubleshooting a homemade queue

The single most useful diagnostic query on any jobs table is one that groups by status and age:

1
2
3

SELECT status, count(*), min(created_at), max(updated_at)
FROM jobs
GROUP BY status;

If processing has rows with an updated_at far in the past, you’ve found stuck jobs from a crashed worker with no visibility-timeout sweep. If pending is growing faster than done, your workers aren’t keeping up with the queue’s arrival rate, and you need more workers or a look at why each job takes as long as it does. If failed is climbing steadily for a small set of job types, that’s your dead-letter signal, and it usually means a specific downstream dependency is unreliable rather than the queue mechanism itself.

One thing that trips people up once volume grows is the index behind that WHERE status = 'pending' clause. A plain index on status degrades badly once most rows are done — the planner has to skip past thousands of irrelevant rows to find the handful still pending, and the query that used to take milliseconds starts taking seconds. A partial index solves this cleanly:

1
2
3

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

That index only contains rows still waiting to be claimed, stays tiny regardless of how many millions of completed jobs pile up in the table, and keeps the claim query fast indefinitely. Pair it with a periodic archival job that moves done and dead rows out to a history table, since an unbounded jobs table is also an unbounded autovacuum burden even with a good partial index in place.

Lock contention is the second thing to watch. pg_stat_activity and pg_locks will show you whether workers are piling up waiting on the same rows, which usually means either your SKIP LOCKED query isn’t actually using it (check the query plan — a wrapped subquery or a badly placed join can silently defeat the row-lock skip) or your batch size is too large relative to worker count, so most workers spend their time waiting rather than skipping past already-claimed rows. If you’re running enough workers that connection count itself becomes the bottleneck rather than lock contention, that’s the point where connection pooling stops being optional — a jobs table with fifty polling workers each holding an open connection adds up fast on a database that also serves your application’s regular queries.

Is it worth reaching for the real thing

For low-to-moderate volume — a few hundred jobs a minute, no need for multi-datacenter fan-out, no requirement for guaranteed message ordering across the whole queue — a Postgres jobs table with SKIP LOCKED, a visibility-timeout sweep and a retry ceiling is a genuinely solid queue, and it has the advantage of living in the same database and the same backup routine as the rest of your application data, rather than a whole separate piece of infrastructure to operate and monitor. Past a few thousand messages a second, or once you need routing topologies (fan-out to multiple consumers, priority lanes, delayed delivery at scale), reach for Postgres itself before Redis as the first upgrade, and a purpose-built broker only once that stops being enough. Just build the locking and the retry ceiling in from day one — those aren’t advanced features you bolt on later, they’re the actual definition of a queue, and skipping them is how a jobs table earns its anxiety.

The honest test I’d apply before reaching for a dedicated broker is whether the missing feature is genuinely about scale, or whether it’s really about avoiding twenty lines of SQL and a cron job. Priority lanes are a priority column and an ORDER BY priority DESC, created_at. Delayed delivery is a run_after timestamp and a WHERE run_after <= now() clause. Fan-out to multiple consumer types is a second table recording which consumer groups have processed which job. None of that requires new infrastructure; it requires remembering that a jobs table is a queue with a schema you can see, which is precisely the property that makes debugging it easier than debugging an opaque broker’s internal state when a message goes missing at two in the morning.