Keeping a Postgres Queue Healthy

Keeping a Postgres queue healthy

By Simeon Griggs |

A healthy digestive system is one that efficiently eliminates waste. Fiber is a key part of a healthy diet, not because it is nutritious, but because it keeps everything you consume moving.

Databases are not so different. If you want a healthy queue table, you'll need to monitor the systems that are designed to perform cleanup well before they're backed up.

Postgres has been a popular choice for queue-based workloads long before it was a good fit for the job. Over many years and multiple major versions, Postgres has only become an even stronger choice for this type of workload.

But what makes job queues uniquely problematic? And in spite of all these advancements, what traps remain?

It's worth knowing, since they could bring down not only your job queue but also your mixed-workloads database and your entire application.

The "just use Postgres" meme lends credence to the notion that every workload belongs in a Postgres database. It's not the worst idea. You really can throw just about anything at a Postgres database and make it stick. The rich extensions ecosystem fills any functionality gaps in "vanilla" Postgres.

As a result, you may have multiple distinct workload types running in the same database at the same time. Your OLTP, OLAP, Time Series, Event Sourcing, Full Text, Geospatial, and/or Queue workloads may all be running at the same time in the same database cluster with different needs, challenges, and priorities—while competing for the same resources.

There are dedicated services for each of these workload types that you can use in isolation. If you're reading this blog post, however, you're likely looking to optimize how they can all work in harmony.

At PlanetScale, we're always in favor of choosing the right tool for each job—Postgres or otherwise. But if you're curious about maintaining healthy queues alongside mixed workloads in Postgres, keep reading.

What makes a queue table unique is that most rows are transient. Inserted, read once, and deleted. So the table's size stays roughly constant while its cumulative throughput is enormous.

Your application may use a job queue to track asynchronous actions like sending an email, creating an invoice, or generating a report. The major benefit of doing this in Postgres is that you can keep the job state and any other logic running in your database in sync with the transaction.

If the job fails, the entire transaction fails and rolls back. If the transaction fails, the job may retry or get deleted. Using an external vendor requires careful coordination to keep in sync with your application's transactional state.

Consider this simple queue table one might use to create individual jobs that need doing. The payload column contains all the information your application needs to complete the operation.

CREATE TABLE jobs (
  id BIGSERIAL PRIMARY KEY,
  run_at TIMESTAMPTZ DEFAULT now(),
  status TEXT DEFAULT 'pending',
  payload JSONB
);
CREATE INDEX idx_jobs_fetch ON jobs (run_at) WHERE status = 'pending';

As your application regularly performs queries to check for jobs to be done, it searches for the oldest job that is still in a pending state, performs whatever work is necessary, and then deletes that job.

The worker opens a transaction and claims the next pending job:

BEGIN;
SELECT * FROM jobs
WHERE status = 'pending'
ORDER BY run_at
LIMIT 1
FOR UPDATE SKIP LOCKED;

In practice, keeping this transaction as short as possible is critical — the longer it stays open, the longer it holds back vacuum. The examples in this post assume sub-millisecond worker operations.

The worker performs whatever work the job requires. If the work fails, the transaction rolls back — the row was never modified, the lock is released, and the job becomes visible to other workers again.

If the work succeeds, the worker deletes the job and commits:

DELETE FROM jobs WHERE id = $1;
COMMIT;

For concurrency and faster job processing, you may want multiple workers executing individual jobs simultaneously. With the example query above, each worker is protected against performing duplicate work by the line FOR UPDATE SKIP LOCKED as the same query will "lock" the row it is working with until the transaction is committed.

As we can see, the nature of a job queue workload is quite simple. A row is fetched and then deleted. Beneath the surface, however, there's more to it than that. There's cleanup to be done.

The common issue that degrades job queues and the database they operate in is when the database cannot clean up after these transactions faster than new work accumulates.

Postgres is documented by others to handle this workload at massive scale. So Postgres' capability to support job queues is not in question.

Keeping your job queue in harmony with the other competing workloads of your database is typically the challenge.

The health of your queue table depends not only on its own configuration, but also on the behavior of every other transaction running on the same Postgres instance. While replicas and replication slots can also work against queue tables, this post focuses on competing query traffic on the primary.

When rows mutate, Postgres can maintain multiple versions of the same row, so that different transactions can see row values as of the time they were queried. This is Postgres' implementation of "Multi-Version Concurrency Control" (MVCC) and a core principle of its design.

This means in our job queue a row in a Postgres database targeted by a DELETE operation is not immediately removed. Instead, it is marked for deletion, made invisible to new transactions, and remains in the database until cleaned up. These not-yet-deleted, invisible rows are referred to as "dead tuples."

Dead tuples are cleaned up by a "vacuum" operation, which can be performed manually or occurs regularly in a healthy Postgres database. While dead tuples are not returned in a SELECT query, they still incur a cost.

For a sequential scan, the executor reads dead tuples from heap pages and checks their visibility before discarding them.

For an index scan — the kind our job queue relies on with ORDER BY run_at LIMIT 1 — the cost is more insidious: the B-tree index itself accumulates references to dead tuples, forcing the scan to traverse entries that point to rows no longer visible.

Each dead index entry means additional I/O to check a heap page only to discard it. This overhead is invisible to the application but can grow substantially with the number of dead tuples.

As for how frequently cleanup is attempted, autovacuum_naptime controls how long the launcher sleeps between checking each database for tables that need vacuuming, usually 1 minute by default. When a table is vacuumed depends on the dead tuple thresholds autovacuum_vacuum_threshold and autovacuum_vacuum_scale_factor.

Let's envision the scenario where we have a jobs table in which tasks of different types are regularly created and processed. Another application accesses the same database to perform large analytical queries and generate reports. These are lower-priority and slower to complete.