Keeping Postgres Queues Healthy

The core problem when running a job queue on Postgres is dead tuple accumulation, the resulting table bloat, and increasingly slow index scans.

Most rows in a queue table cycle quickly through insert-read-delete, but because cumulative throughput is large, even a slight delay in vacuum causes performance degradation to build up rapidly. Even with FOR UPDATE SKIP LOCKED, the structure of repeatedly scanning the same B-tree index doesn't change.

The essence of the problem is the MVCC horizon. If even one long-running transaction or overlapping analytical query remains, autovacuum cannot clean up dead tuples from that point onward. In other words, even if the queue worker is fast, a slow workload on the same Postgres instance can block vacuum and degrade overall queue performance.

The cleanup mechanism is simple.

• Row metadata like ctid, xmin, xmax determines visibility.
• Dead tuples don't appear in SELECT results, but they create additional I/O in both sequential scans and index scans.
• Autovacuum is affected by autovacuum_naptime, autovacuum_vacuum_threshold, and autovacuum_vacuum_scale_factor.

The issue isn't "queries taking too long" — it's that different workloads running concurrently keep pinning the horizon. Not just a single long-running transaction, but a pattern where multiple 40-second queries run staggered 20 seconds apart can also block vacuum.

Existing mechanisms like statement_timeout, idle_in_transaction_session_timeout, and transaction_timeout only target individual sessions or single execution durations, so they're insufficient to prevent this kind of concurrency-driven degradation.

PlanetScale's Traffic Control is presented as a means to apply resource limits per query class here. It classifies target queries using metadata such as SQLCommenter tags, and uses controls like Maximum concurrent workers to limit the concurrent execution count of slow analytical queries, securing time for autovacuum to keep up. The premise is that blocked queries are retried, not permanently rejected.

In the reproduction experiment, an existing recursive CTE-based queue was compared with a SKIP LOCKED + batch processing version, and both approaches showed a similar curve of performance collapsing due to increasing dead tuples over time. However, while SKIP LOCKED and batch improved initial lock time and queue behavior, they did not eliminate the root cause: dead tuple accumulation.

In conclusion, Postgres queues remain powerful, but they can silently degrade when coexisting with slow analytical queries in the same DB. The most important response is not making the queue smarter, but limiting workload concurrency so that VACUUM can keep up.