microsoft/pg_durable

What is pg_durable?

pg_durable is a PostgreSQL extension that lets you write long‑running, fault‑tolerant workflows directly in SQL. A durable function is a graph of SQL steps; the extension checkpoints progress after each step, stores the state inside the database, and automatically resumes after crashes, restarts, or failed steps. No external orchestrator, queue, or cron service is required – everything runs inside the PostgreSQL server as a background worker written in Rust.

Who should consider it?

Backend / data engineers who already keep their data in Postgres and want the orchestration logic to live next to that data.
DBAs and SREs who need reliable runbooks that survive restarts and can be audited via SQL.
Teams building data or AI pipelines (e.g., vector‑embedding, ingestion, enrichment) that need per‑row or batch‑level durability.

Core idea & how it works

Define a workflow using a small SQL DSL (|=>, ~>, df.if(), df.join(), etc.).
Start it with df.start(...); the call returns an instance ID.
A background worker (the durable runtime) executes each step, persisting checkpoint data in a duroxide.* schema.
If the server crashes or a step fails, execution resumes from the last checkpoint automatically.
Query status from tables like df.instances and df.nodes while the workflow runs or after it finishes.

Quick example

SELECT df.start(
    'SELECT id FROM documents WHERE processed = false LIMIT 100' |=> 'batch'
    ~> 'UPDATE documents SET processed = true WHERE id IN (SELECT id FROM $batch.*)'
);

The example creates a durable function that selects a batch of unprocessed rows, then updates them. If the database restarts halfway through, the update step is not re‑run for rows that were already processed.

Typical use cases

Vector‑embedding pipelines – chunk text, call an external embedding API, upsert results into pgvector.
Ingest pipelines – stage, deduplicate, transform, and publish large batches.
Scheduled maintenance – detect bloat, notify, wait for approval, then run cleanup.
Fan‑out aggregation – run independent queries in parallel and join the results.
External API workflows – enrichment, classification, or webhook‑style calls via df.http().

When NOT to use it

The job is a single simple INSERT … SELECT or other one‑shot statement.
You need sub‑millisecond, synchronous request handling (pg_durable is for background work).
Your environment does not allow installing extensions or background workers.
The workflow spans many heterogeneous systems and cannot be expressed cleanly in SQL.
You need arbitrary in‑process code that cannot be wrapped in a SQL function or HTTP call.

Installation & getting started

Prerequisites – PostgreSQL 17 or 18, development headers, a Rust toolchain, and cargo‑pgrx (v0.16.1).
Choose an install method:
- Debian package – download the .deb from a GitHub release and install with dpkg.
- PGXN – pgxn install --sudo -- pg_durable (builds from source).
- Docker image – ghcr.io/microsoft/pg_durable:pg17 (or pg18). The image pre‑installs the extension on top of the official Postgres image.
Add pg_durable to shared_preload_libraries in postgresql.conf and restart.
In the target database (default postgres): CREATE EXTENSION pg_durable;
Grant usage to application roles, e.g. SELECT df.grant_usage('app_role');.

Full step‑by‑step guides are in the User Guide and the Quick Example section of the repo.

Limitations & considerations

The model is SQL‑shaped; arbitrary code must be wrapped in a SQL function or exposed via an HTTP endpoint (df.http()).
The background worker role (pg_durable.worker_role) must be a superuser because it bypasses row‑level security to manage all users’ instances.
The published Docker image is for evaluation only – it runs with a superuser durable instance and limited HTTP security.
No arm64 Docker images yet; they will be added once Debian packages are available.
Windows source builds are not supported.

Where to learn more

Website & docs – https://microsoft.github.io/pg_durable/
User Guide – USER_GUIDE.md
Examples – examples/README.md
Architecture overview – the README’s diagram and the linked duroxide libraries.

In short: pg_durable brings the pattern of durable, checkpointed workflows inside PostgreSQL, letting teams replace external cron/worker setups with a single extension that persists state, retries automatically, and remains fully observable via SQL.