Diagnose a slow Postgres work queue

The oldest receipt in the queue is now ten minutes late, so the team proposes moving everything to a broker. Then the traces show workers spending most of their time waiting on a slow partner API. Changing the queue would add a migration and leave the actual bottleneck untouched.

Queue lag is a symptom, not a diagnosis.

It can come from claim SQL scanning too much data, handlers doing slow work, completed rows bloating indexes, one hot key serializing traffic, or a transport boundary that the local database should not own.

I measure those paths before changing architecture. This post shows how to separate database claim time from handler capacity and cross-service transport, then tune the part that is actually limiting throughput. A broker may be the right answer for replay or fan-out, but it will not make a slow handler faster or remove the need for leases and idempotency.

Find the bottleneck before changing tools

Slow claims are usually a SQL-path problem: workers may poll too often, scan rows they do not own, or search an index bloated with completed history. If claim latency stays low while pending age grows, the database is handing out work promptly and the handlers or downstream dependency are the bottleneck. Cross-service fan-out and replay are different again: they are transport requirements, not evidence that the local claim query is slow. Measuring these stages separately prevents one symptom, queue lag, from sending the team toward the wrong architecture change.

There is no honest universal throughput target. Row width, indexes, handler time, key distribution, storage, connection limits, and durability settings all change the result. Measure the claim path and handler path separately on a dataset that resembles your queue.

Fix the part that is slow

Filter ownership in SQL

Precompute partition_bucket when the producer enqueues the row, then include WHERE partition_bucket = ANY($owned) in the claim query. Filtering in Postgres lets the index find rows the worker may actually process. Claiming 100 rows and discarding 95 in application code has already paid for their locks, reads, and updates, so it creates database work without increasing throughput.

Cache the ring

Rebuild the ring and owned-bucket list periodically, not on every poll loop. In the example below, the cache lasts 8 seconds while the heartbeat TTL lasts 30 seconds. Measure how quickly your worker pool changes before choosing either value.

Wake, don’t hammer

Use LISTEN inbox_new to wake idle workers when a producer enqueues work, while retaining polling as a recovery path because notifications are not a durable queue. When no backlog exists, increase the poll delay and add random jitter so every worker does not wake and query at the same instant. Poll quickly again while work is available, and measure the resulting query and connection load for your actual worker count.

Right-size claim batches

Start with a small batch such as 25. Increase it while watching claim latency, lock time, and the time needed to finish the batch. Huge batches hold more work than the worker can finish promptly. Tiny batches can make claim overhead dominate.

Keep the inbox table hot

Archive or delete completed rows on a schedule. A large history makes vacuum and index maintenance more expensive even when the claim path uses partial indexes.

Small payloads

Keep large documents outside the queue row when the worker can load them by id. Large JSONB values increase table, index, and write-ahead-log traffic. Measure the tradeoff before adding another network read to every handler.

Use a controlled load test instead of copying queue settings from another system:

1. Record the input rate, handler-time distribution, payload size, key distribution, row counts, Postgres version, instance shape, and durability settings.
2. Hold the handler constant while changing one claim-path variable, such as the index or batch size.
3. Hold the claim path constant while changing handler concurrency.
4. Compare queue age, claim latency, handler latency, database load, downstream errors, and lease expirations.

Choose the heartbeat TTL from the failure-detection delay the service can tolerate. Choose the lease from measured handler time plus scheduling and network margin. Choose batch size from how quickly a worker can finish a claim. Those are workload decisions, not reusable constants.

When the first pass is not enough

Parallel handlers per worker

Claim a batch and process rows with a concurrency limit such as a semaphore. The claim stays serial while handlers run in parallel. Increase concurrency until CPU, connection limits, or the downstream system becomes the constraint.

Index for ordering guard

If using per-key FIFO, add partial index on (partition_key) WHERE status = 'processing' so the NOT EXISTS check stays cheap.

Horizontal workers

Add processes while end-to-end queue age improves and the database and downstream dependency remain inside their tested limits. A consistent hash changes affinity, not capacity.

Postgres hygiene

Monitor vacuum progress, dead tuples, table growth, and index growth. Change autovacuum or storage settings only after the measurements show which maintenance work is falling behind.

An illustrative worker configuration

This excerpt shows where the chosen values enter the worker. It omits type definitions and helper implementations. The linked source below contains the type-checked ring-cache example.

const BATCH_SIZE = 25;
const HANDLER_CONCURRENCY = 8;
const RING_TTL_MS = 8_000;
const IDLE_POLL_MS = 1_500;
const ACTIVE_POLL_MS = 75;

let ringCache = { ring: null, ownedBuckets: [], expiresAt: 0 };

async function getRing(db) {
  if (Date.now() < ringCache.expiresAt) return ringCache;
  const live = await db.query(`SELECT id FROM workers
    WHERE status = 'alive'
      AND last_seen_at > now() - interval '30 seconds'
    ORDER BY id`);
  const ring = buildRing(live, 128);
  ringCache = {
    ring,
    ownedBuckets: ownedBucketsFor(WORKER_ID, ring, 360),
    expiresAt: Date.now() + RING_TTL_MS,
  };
  return ringCache;
}

// LISTEN inbox_new → wake workers and refresh ownership soon
db.on("notification", () => {
  wakeFlag = true;
  ringCache.expiresAt = 0;
});

while (true) {
  await maybeRunHousekeeping(db);
  const { ownedBuckets } = await getRing(db);
  const batch = await db.claimInboxBatch({
    workerId: WORKER_ID,
    buckets: ownedBuckets,
    limit: BATCH_SIZE,
  });

  if (batch.length === 0) {
    await sleep(wakeFlag ? ACTIVE_POLL_MS : IDLE_POLL_MS + Math.random() * 500);
    wakeFlag = false;
    continue;
  }

  await mapWithConcurrency(batch, HANDLER_CONCURRENCY, async (row) => {
    await processWithLease(row, WORKER_ID);
  });
}

Reproduce the claim-path measurement

Record the Postgres version, instance shape, durability settings, row count, pending-to-completed ratio, payload size, and partition-key distribution. Then capture the plan instead of reporting only wall-clock time:

EXPLAIN (ANALYZE, BUFFERS, WAL)
WITH picked AS (
  SELECT id
  FROM inbox
  WHERE status = 'pending'
    AND available_at <= now()
    AND partition_bucket = ANY($1)
  ORDER BY available_at, created_at, id
  LIMIT 25
  FOR UPDATE SKIP LOCKED
)
SELECT * FROM picked;

Run the same workload long enough to include vacuum and steady-state writes. Publish p50 and p99 claim time, rows scanned per row claimed, buffer reads, WAL volume, queue age, and handler throughput. Without that context, a jobs-per-second number teaches readers almost nothing.

Keep completed rows out of the hot table

The inbox should mostly hold pending and processing. Move history out on a schedule (housekeeping can do this too).

-- Run daily or when completed count > 1M
WITH moved AS (
  DELETE FROM inbox
  WHERE status = 'completed'
    AND completed_at < now() - interval '7 days'
  RETURNING *
)
INSERT INTO inbox_archive
SELECT * FROM moved;

-- Batch deletes (10k at a time) if table is huge: avoid one massive lock

Add indexes only for measured pressure

-- Speeds per-key ordering guard (NOT EXISTS on processing siblings)
CREATE INDEX idx_inbox_partition_processing
  ON inbox (partition_key)
  WHERE status = 'processing';

CREATE INDEX idx_inbox_partition_pending_order
  ON inbox (partition_key, created_at, id)
  WHERE status = 'pending';

-- Already in schema.sql: claim path depends on these partial indexes:
-- idx_inbox_bucket_claim (partition_bucket, available_at, created_at, id) WHERE status = 'pending'
-- idx_inbox_claim (available_at, created_at, id) WHERE status = 'pending'

Move transport only when transport is the bottleneck

Separate where you coordinate claims from what carries bytes between services. The table uses product names only as familiar anchors.

Multi-DC, replicas, and cost signals

Move transport only when transport is the measured bottleneck.

Multi-DC and replicas

Enqueue and claim must hit the primary. Read replicas work for dashboards and lag metrics, not for claiming work. Cross-DC inbox requires a single write leader, or you accept split-brain risk.

Cost signals

When claim queries peg CPU and archiving cannot keep pace, I tune coordination first. I move high-volume transport to a broker only if that does not solve the bottleneck.