Crash recovery

The worker sending the receipt for order:9182 is terminated in the middle of the SMTP call. The database row still says processing, but no process owns it now. Worse, the email provider may have accepted the message just before the connection disappeared, so a replacement worker cannot know whether sending again will create a duplicate.

This is the failure a claim loop must be designed around, not an unusual edge case. I assume every worker will eventually die at the least convenient point.

A lease makes ownership temporary. If the worker stops renewing its deadline, another process may recover the row. Lease cleanup moves expired work back to pending. Idempotency makes the repeated handler safe when the first attempt may already have produced its effect. This post connects those two ideas so a crash becomes recoverable work instead of a permanently stuck row or a second customer email.

What a lease buys you

When a worker claims a row, it sets status = 'processing', stamps claimed_by, and sets lease_expires_at in the future. That deadline is the crash-recovery window.

Each claim also increments lease_generation, a number that identifies this particular period of ownership. Suppose Worker A pauses long enough for its lease to expire and Worker B claims the row. When A resumes, its completion update still carries the older generation, so the guarded UPDATE matches no rows. Used this way, the generation is a fencing token: it prevents an old owner from overwriting the result of a newer claim. A downstream store that supports conditional writes can apply the same check to effects outside the queue table.

Lease fields and handler idempotency

Open in Studio

WITH picked AS (
  SELECT id
  FROM inbox
  WHERE status = 'pending'
    AND available_at <= now()
  ORDER BY created_at, id
  LIMIT 25
  FOR UPDATE SKIP LOCKED
)
UPDATE inbox i
SET
  status = 'processing',
  claimed_by = $1,
  lease_expires_at = now() + interval '90 seconds',
  lease_generation = i.lease_generation + 1,
  attempts = i.attempts + 1
FROM picked
WHERE i.id = picked.id
RETURNING i.*;

When the worker dies: lease cleanup

If a process crashes mid-handler, the row stays processing with a stale claimed_by. Without lease cleanup, that receipt never sends. Manual SQL during an incident is a fallback, not the recovery design.

The cleanup query runs at the top of every worker loop. It finds rows whose lease has expired and puts them back on the queue:

status back to pending
claimed_by and lease_expires_at cleared
attempts left as-is because it already incremented at claim time

At-least-once delivery means the handler may run twice, even when the queue is working correctly.

Another claim cycle can now pick the row up, which is why recovery produces at-least-once rather than exactly-once execution. For a remote email provider, send a stable idempotency key such as receipt:order:9182:v1. A local “already sent” flag cannot close the uncertainty: writing it before the remote call can suppress an email that was never sent, while writing it afterward leaves a gap when the provider accepted the email but the response was lost. The provider must recognize the stable key, or the effect must be recorded in a local transaction you control.

UPDATE inbox
SET
  status = 'pending',
  claimed_by = NULL,
  claimed_at = NULL,
  lease_expires_at = NULL,
  available_at = now()
WHERE status = 'processing'
  AND lease_expires_at < now()
  AND attempts < max_attempts;

UPDATE inbox
SET status = 'dead_letter',
    claimed_by = NULL,
    claimed_at = NULL,
    lease_expires_at = NULL
WHERE status = 'processing'
  AND lease_expires_at < now()
  AND attempts >= max_attempts;

With one worker, the same while (true) loop calls sweepExpiredLeases() before each claim. With many workers, each process may attempt housekeeping, but only one should run cleanup per interval. Use a Postgres advisory lock so workers elect one housekeeper without a separate scheduler.

async function run() {
  while (true) {
    await db.sweepExpiredLeases();
    const batch = await db.claim(WORKER_ID);

    for (const row of batch) {
      await handleRow(row);
    }

    if (batch.length === 0) {
      await sleep(500);
    }
  }
}

Two kinds of idempotency

A lease recovers ownership. Idempotency protects the effect when ownership repeats.

Producer idempotency and handler idempotency solve different failures.

Layer	Guard	Failure it handles
Enqueue	`idempotency_key UNIQUE` on insert	The API retries after a timeout and would otherwise create duplicate rows
Handler	Transactional local write or downstream idempotency key	Lease cleanup retries the same effect

Simulation: crash and dedupe

Where this breaks down

I treat these as design boundaries, not surprising edge cases:

Missing handler idempotency: lease cleanup can duplicate side effects without a transactional local write or a downstream idempotency key.
Long handlers without lease renewal: a 10-minute job on a 90-second lease loses ownership mid-flight unless you renew.
Exactly-once side effects: put the dedupe key in the store that performs the effect. If a remote provider offers neither idempotency nor a way to query the result, an ambiguous timeout cannot be made exactly once by the local queue.

Source

These companion files collect the SQL and TypeScript used in this article. The link labels distinguish full implementations from focused examples and worker skeletons.