You Might Not Need Kafka: Building a Job Queue with PostgreSQL

The Criteria for a Job Queue

A job needs a few things to execute properly within a system. It needs to be persistent, surviving unforeseen crashes. Jobs must not be processed more than once by workers - one job should be processed once by one worker. Also, a job's state has to be tracked through every step. A job state must show when it's pending, completed, or failed. That's the bar any solution must clear.

With the Postgres approach, persistence comes free. Jobs live in a table so when a worker dies mid-job, the job still exists in a row in the DB. Whereas with in-memory queues, a crash loses everything still in memory. A broker like RabbitMQ has to be configured for persistence and if configured wrongly, jobs get lost. A database however is fundamentally built for durability.

Avoiding Collisions with FOR UPDATE SKIP LOCKED

Now, let's say three workers poll the queue at the same instant and run the same query. They'll see the same pending job at the top and nothing stops them all from grabbing it. If that job is a payment, the customer gets charged three times for one service. All the workers successfully process the job with no indication of an error or alerts. This is the requirement that seems to demand a real message broker, and it's exactly where people assume a database can't compete.

It can. Postgres has a specific tool for exactly this. The SQL clause FOR UPDATE is used to lock rows. This can be called on a job when a worker picks it up to process. By default other workers will get blocked during this process - they'll wait for the lock to release before processing available rows. This kills concurrency. The SKIP LOCKED modifier changes that. It allows workers to step over locked rows and take the next available one.

An important note is to start a database transaction before you run your query. FOR UPDATE only holds the lock for the life of the transaction. Outside a transaction, the lock releases immediately and the whole guarantee evaporates.

Here's the query inside its transaction:

BEGIN;
SELECT * FROM jobs
WHERE status = 'pending' AND schedule_at <= NOW()
ORDER BY priority DESC, created_at ASC
LIMIT 1
FOR UPDATE SKIP LOCKED;
UPDATE jobs SET status = 'processing' WHERE id = $1;
COMMIT;

Now when worker 1 locks job 5, workers 2 and 3 hit the same query, see job 5 is locked, skip it and take jobs 6 and 7. Three workers, three different jobs, no collision.

Test Results

After running 100 jobs across 3 workers, here's what each worker picked up:

Worker 1 processing job 543
Worker 2 processing job 545
Worker 3 processing job 546
Worker 1 processing job 550
Worker 2 processing job 556
Worker 3 processing job 558
....

Total jobs processed: 100
Unique job IDs: 100
Duplicates: 0
Worker 1: 34
Worker 2: 33
Worker 3: 33

Every job ID appears exactly once across all three workers - no job was ever picked up twice.

SELECT status, COUNT(*) FROM jobs GROUP BY status;

 status   | count
----------+-------
 completed |   100

One row, all completed - nothing stuck in processing, which is the signature of a worker that died holding a job.

Evidently, PostgreSQL can be used to simplify the daunting task real message queuing systems can bring. But every solution still has trade-offs.

Trade-offs

Throughput Ceiling from Polling

Workers poll the database at a specified interval for jobs to run even when the queue is completely empty. All workers keep asking forever. This results in wasted work where workers run pointless queries at specified intervals. Also it can cause a delay in job processing. If a job gets added one millisecond after a worker checks, that job sits untouched until the next poll.

Although a weakness, Postgres has a feature called LISTEN / NOTIFY that can push notifications, however it's more complex to wire up and has its own limits.

No Native Pub/Sub Fanout

The use of SKIP LOCKED ensures that one job goes to one worker, but some situations may need one event to reach several different systems. For example: an order is placed and email, inventory, and analytics each need their own copy of that event.

Long-Held Connections

Postgres pool holds 10-20 connections and workers borrow connections to communicate with the database, then release afterwards. If a job calls a slow external API and takes 30 seconds, that database connection sits reserved and idle for 30 seconds doing nothing, helping nobody, and unavailable to anyone else.

An easy fix is to commit the transaction immediately after marking the job status to processing - this releases the lock and frees the connection back to the pool. Let the worker run the job with no transaction open, then open a second short transaction to update the job to completed. A job with a processing status won't be touched by other workers so the current worker won't be competing with any other to process it.

Conclusion

None of these are dealbreakers for most systems. For most applications operating at normal scale the infrastructure you already run is enough, and reaching for Kafka before you need it is solving a problem you don't have yet. Thanks for reading to this point. If you enjoyed this article you can find a related job queue post I made on exponential backoff here.