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

It's easy to reach for the popular tool before asking what your system actually needs. For job queueing, the usual advice is to use RabbitMQ or Kafka. The underlying burden of using these tools will be additional processes to deploy, monitor and reason about. But what if using your existing database is possible? I built a job queueing system with a PostgreSQL database that cleared the bar without adding infrastructure. The next question will be, does this solution meet the criteria of 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.

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 to make 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;

Enter fullscreen mode Exit fullscreen mode

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.

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
....

Enter fullscreen mode Exit fullscreen mode

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

Enter fullscreen mode Exit fullscreen mode

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

Enter fullscreen mode Exit fullscreen mode

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. First of the trade-offs is the 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.

Also there's 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.

Another trade-off worth mentioning is one I encountered while building my job queue. 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.

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