Queue, log or table: four questions for the hand-off between pipeline stages

What sits between two pipeline stages decides who can re-read old work, whether order holds, how failures retry and who sees stuck items. For most internal jobs, a table wins.

Producer Hand-off Work queue Jobs table Partitioned log Consumer Dead-letter store Stuck-work view four questions 5 or more attempts a count on a dashboard
Producer to consumer by way of a work queue, a jobs table or a partitioned log, then on to a dead-letter store and a stuck-work view.
On this page
  1. Four questions under every hand-off
  2. Six hours behind one bad PDF
  3. The same hand-off as a Postgres table
  4. The clickstream stays on Kafka
  5. Table first, log for replay, queue for the rest

A broker will tell you how many messages are waiting. Ask which one has failed eleven times since midnight and there’s no way to put the question, because you can’t run a WHERE clause against a queue.

I think that gap deserves more weight than it gets when a team picks what goes between two pipeline stages. The pick usually gets made on throughput, which most internal pipelines have to spare. What they run short of is a way to notice one stuck item before somebody downstream asks where their file went.

Four questions under every hand-off

Whatever sits between a producer stage and a consumer stage settles four things, whether or not anyone chose them.

Who may go back and re-read work that’s already been handled. Whether items must arrive in the order they were sent. How a failed item gets retried, and where it goes when the tries run out. Who can see what’s stuck, in enough detail to act on it.

A work queue, a partitioned log and a database table answer those differently. The matrix adds two rows for what each one costs.

Work queue (SQS, RabbitMQ) Partitioned log (Kafka) Jobs table (Postgres)
Re-reading old work No. An acknowledged message is gone Yes, within retention, per consumer group Yes, while you keep finished rows
Ordering Loose under redelivery or parallel consumers Strict within a partition, none across Claimed in query order, finished in any order
Retry control Redelivery, then a dead-letter queue if you set one up Yours to write Columns: attempts, next run time, last error
Seeing stuck work Depth and age of the oldest message Lag per partition A SELECT, joinable to your other tables
Throughput ceiling High. Rarely the limit for internal work Highest. Add partitions A few hundred claims a second
Operating cost Low if managed, a cluster if not A cluster or a managed bill Near zero where Postgres already runs, plus pruning

The log’s retry cell needs a longer note. A Kafka consumer group commits one position per partition, with no way to mark a single record as failed and step around it. A record that keeps failing either holds up everything behind it or gets copied to a side topic by code you write yourself.

Six hours behind one bad PDF

Juniper Print exists only in this post, and so do its numbers. It renders 40,000 PDF jobs a day, under one a second. The order service published each job to a RabbitMQ queue, and a single consumer rendered them.

One night a job came in with a font the renderer couldn’t load. The consumer threw an error and rejected the message with requeue. The broker put it back at the head of the queue, and the consumer took it again. That went on for six hours, with about 10,000 jobs waiting behind it.

Nobody saw it. Queue depth was on a graph, and nobody opens a graph at 2 a.m. without a reason.

A retry limit and a dead-letter queue would have broken the loop, and Juniper never set them up. I’d blame the team for that before the broker. Even so, the morning after would have meant pulling messages back out of the dead-letter queue to learn which orders were in it.

The same hand-off as a Postgres table

Juniper already keeps its orders in Postgres, so the jobs moved in next to them.

CREATE TABLE render_jobs (
  job_id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id    bigint      NOT NULL,
  payload     jsonb       NOT NULL,
  status      text        NOT NULL DEFAULT 'queued'
              CHECK (status IN ('queued', 'running', 'done', 'failed')),
  attempts    integer     NOT NULL DEFAULT 0,
  run_after   timestamptz NOT NULL DEFAULT now(),
  locked_by   text,
  locked_at   timestamptz,
  last_error  text,
  created_at  timestamptz NOT NULL DEFAULT now(),
  finished_at timestamptz
);

-- Leaves out running and done rows, so it stays small.
CREATE INDEX render_jobs_ready_idx
  ON render_jobs (run_after)
  WHERE status IN ('queued', 'failed');

The order service inserts the job in the same transaction that saves the order. An order can’t exist without its job, which a publish to a broker never promised.

Each worker claims one job at a time:

-- $1 is the worker's name. Runs in its own short transaction.
WITH next_job AS (
  SELECT job_id
  FROM render_jobs
  WHERE status IN ('queued', 'failed')
    AND attempts < 5
    AND run_after <= now()
  ORDER BY run_after
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
UPDATE render_jobs AS j
SET status    = 'running',
    attempts  = j.attempts + 1,
    locked_by = $1,
    locked_at = now()
FROM next_job
WHERE j.job_id = next_job.job_id
RETURNING j.job_id, j.order_id, j.payload, j.attempts;

The inner query locks the row it picks and passes over any row another transaction has locked, so Juniper’s two workers, asking at the same instant, get different jobs and neither waits. An idle worker polls once a second, which a print proof’s latency budget of minutes can absorb.

Retry control is two statements:

-- Success. $1 is the job.
UPDATE render_jobs
SET status = 'done', finished_at = now()
WHERE job_id = $1;

-- Failure. $2 is the error text. Wait one minute per attempt so far.
UPDATE render_jobs
SET status     = 'failed',
    run_after  = now() + attempts * interval '1 minute',
    last_error = $2
WHERE job_id = $1;

So the bad-font job fails, waits a minute, fails again and waits two. About ten minutes after its first failure it has used its five attempts, and the claim query stops picking it. Every other job renders around it.

A worker that dies mid-render leaves a row in running. A sweep marks any row that’s sat there 15 minutes as failed, and it retries like the rest.

Then the stuck-work view:

SELECT count(*) AS stuck_jobs
FROM render_jobs
WHERE status = 'failed'
  AND attempts >= 5;

That count goes on the team’s dashboard. Swap the count for order_id and last_error and it’s the list. The dead-letter store in the diagram is those same rows, and once the font is fixed, one UPDATE sets them back to queued with attempts at zero.

The table has upkeep. Finished rows need pruning, and every claim is an update that leaves a dead row for autovacuum. At 40,000 jobs a day, autovacuum won’t notice.

The clickstream stays on Kafka

Juniper has a second hand-off, and I’d leave it alone. Storefront clicks arrive at 9,000 events a second and three consumers read them: a sessionizer, the warehouse loader and a recommendations job. Each keeps its own offset. When one ships a bug, it rewinds a day and reads the events again.

Re-reading is required, and a queue can’t do it. Order matters within one visitor’s session, which a partition key covers. And 9,000 a second is about 780 million rows a day, far past anything I’d ask a jobs table to hold. For a feed like this, lag per partition is a fair picture of what’s stuck.

Table first, log for replay, queue for the rest

  • When somebody will ask about one item by name, an order or a file, start with a table.
  • When several consumers read the same stream, or anyone needs a replay, use a log.
  • A queue fits what’s left: independent work, wanted once, with no shared database or more volume than a table likes.
  • Past a few hundred claims a second, measure dead rows on the jobs table before trusting my ceiling.

Whichever one it is, I’d build the stuck-work view before the consumer. For a table it’s the count above. For a queue it’s alarms on oldest-message age and dead-letter depth, and for a log it’s lag per partition. At Juniper, the stuck-jobs count would go on the dashboard the same afternoon the table is created, before the first worker claims anything.