Durable Work in PostgresPart 0
A practical guide to durable work in Postgres
Choose and build Postgres-backed work queues with leases, idempotent handlers, competing consumers, outbox delivery, and dead letters.
A confirmation page is not a receipt. Checkout for order:9182 can succeed, the customer can see the thank-you screen, and the next deploy can still kill the process that was supposed to send the email.
The customer does not care whether the missing receipt was caused by a thread, a queue, or a container restart. Support still has to answer a simple question: we accepted the order, so why did the promised work vanish?
A durable work queue gives that promise a record in the same database that accepted the order. The order change and a row meaning “send this receipt” commit in one transaction, so either both become durable or neither does. A worker later claims the row, attempts the email, and records the outcome. If the worker crashes, another worker can recover the unfinished row rather than relying on an in-memory callback that disappeared with the process.
I do not add Kafka, Redis, SQS, or a workflow engine to solve service-local durable work by default. If an application already depends on Postgres, I first consider storing work as rows. This series builds that design from the first claim query through crash recovery, scaling, cross-service handoffs, and operations, so you can stop when its guarantees match your system.
Choose the shortest path that matches your problem:
- One reliable background action: read the claim loop and crash recovery.
- A production worker pool: continue through many workers, partitioning, and production controls.
- Work that crosses services: add the pattern guide and cross-service handoff.
- A process with waits and several steps: treat the workflow article as an optional extension.
- Implementation or incident support: use the schema and operations reference when you need it.
Build it in this order
| Step | Ship when | What you add | Stop if |
|---|---|---|---|
| Durable row + claim loop | You need work to survive deploys | inbox, pending, processing, SKIP LOCKED | One worker is enough |
| Recovery + idempotency | A worker can die mid-handler | Leases, lease cleanup, retry, dead letter, handler dedupe | You can tolerate at-least-once delivery |
| Multiple workers | One process cannot keep up | Competing consumers, per-key ordering guard when needed | Worker count is stable |
| Stable ownership | Autoscale or deploy churn moves too much work | Worker heartbeats, hash ring, bucket-filtered claims | Rebalances are rare |
| Production controls | Long handlers, deploy drains, hot keys, idle polling show up | Renewal, fencing, drain, housekeeping, key sharding, notify wakeups | The queue has runbooks and tests |
| Boundaries and transport | Work leaves this database | Transactional outbox, broker/webhook transport, receiver inbox, contracts | You have measurable eventual consistency |
| Durable workflows | One business process has multiple durable steps | Workflow instances, step rows, durable sleeps, signals, cancellation | Workflow orchestration becomes platform infrastructure |
What problem this solves
If work only lives in memory, there is nothing to claim, retry, inspect, or recover after the process dies.
The durable version is standard queue design implemented in Postgres. A claim gives a worker temporary responsibility for one row. A lease puts a deadline on that responsibility so a crash cannot strand the row forever. Because a worker may finish the external action and crash before it records completion, the handler must be idempotent: repeating it with the same logical key must not create a second customer-visible effect.
Use this when
- Your service already uses Postgres and the producer can write the work row in the same transaction as the business change.
- You need durable background work with clear operational state: pending, processing, completed, failed, dead letter.
- You want SQL visibility into backlog depth, stuck rows, retry counts, and oldest pending age.
- A representative load test shows the database and workers can meet your throughput, latency, and recovery targets.
Keep Postgres in the design while its guarantees and measured capacity still match the problem.
Where Postgres stops being enough
- Use Kafka, Pulsar, or a managed stream when you need a shared event log, long retention, many independent replay consumers, or platform-level fan-out.
- Use a queue service when cross-service transport is the main problem and same-transaction enqueue is not required.
- Use a workflow engine when workflow history, timers, signals, replay, and cross-service orchestration become shared platform infrastructure rather than service-local control flow.
- Use pg-boss, Graphile Worker, or River when a library covers the durable work behavior you need.
- Use an outbox plus transport when work leaves your database boundary.
The core implementation
| Need | Established pattern | Postgres implementation |
|---|---|---|
| Multiple workers process one backlog | Competing Consumers | inbox rows claimed with SKIP LOCKED |
| Retry without duplicate logical jobs | Idempotent Receiver | idempotency_key UNIQUE plus handler dedupe |
| Recover crashed workers | Lease / timeout ownership | claimed_by, lease_expires_at, lease cleanup SQL |
| Keep related work ordered | Partitioned consumption | partition_key, optional ordering guard, hash ring when needed |
| Publish after a domain write | Transactional Outbox | outbox row in the same transaction as the business update |
| Handle permanent failure | Dead Letter Channel | dead_letter status and support queries |
How to read the series
Ship the smallest claim loop that keeps accepted work from vanishing after a deploy, then stop. Add the next mechanism only when a named failure demands it: crashes need lease cleanup, retries need idempotency, related work needs partition keys, cross-service delivery needs an outbox, and high volume needs claim-path tuning.