Implementing Durable Workflows with Postgres as an Orchestrator

Durable execution is a critical requirement for many modern automation systems, ensuring that workflows survive failures and resume from the last completed step. Traditionally, this is achieved using dedicated external orchestrators, which manage workflow state, dispatch tasks, and handle retries. However, this introduces another stateful system to deploy, secure, monitor, and scale.

Postgres as a Workflow Orchestrator

The article proposes using an existing relational database, specifically PostgreSQL, as the central orchestrator for durable workflows. This approach eliminates an external dependency by storing all workflow state directly in Postgres, allowing stateless application servers to coordinate workflow execution through standard database operations. This simplifies the architecture, improves observability through SQL queries, and consolidates reliability and security management.

Building a Concurrent Work Queue with SKIP LOCKED

A core component of the system is a concurrent work queue built using a simple `workflow_executions` table and the `SELECT ... FOR UPDATE SKIP LOCKED` clause. This allows multiple workers to simultaneously poll the table and safely claim unique workflow executions without blocking, effectively guaranteeing exactly-once processing for each task. The claiming occurs within a short transaction that updates the workflow status and sets a lease.

BEGIN;
WITH claimed AS (
  SELECT id FROM workflow_executions
  WHERE status = 'enqueued'
  ORDER BY created_at LIMIT 1
  FOR UPDATE SKIP LOCKED
)
UPDATE workflow_executions e
SET status = 'running', owner_id = $1, lease_expires = now() + INTERVAL '30 seconds', updated_at = now()
FROM claimed WHERE e.id = claimed.id
RETURNING e.id, e.input;
COMMIT;

Idempotency and Crash Recovery

Idempotency is enforced by storing step outputs in an `operation_outputs` table with a composite primary key on `(execution_id, step_id)`. An `INSERT ... ON CONFLICT DO NOTHING` statement ensures that if a step reruns after a crash, the database prevents duplicate results, and the worker simply reads the previously stored output. Crash recovery is handled by a lease-and-sweeper pattern: workers periodically heartbeat their owned tasks by extending a `lease_expires` timestamp, and a background sweeper re-enqueues any tasks whose leases have expired, making them available for other workers to pick up.