Microsoft pg_durable: In-Database Durable Execution for PostgreSQL

Microsoft has open sourced pg_durable, a PostgreSQL extension designed to bring durable execution directly into the database. This allows developers to define long-running, fault-tolerant workflows using a SQL-based Domain Specific Language (DSL), eliminating the need to stitch together external cron jobs, message queues, and status tables to manage background work.

Durable Execution in the Database

pg_durable enables the creation of SQL functions that act as graphs of steps. The core value proposition is that PostgreSQL checkpoints each step of the workflow; if the database crashes, restarts, or a specific step fails, execution resumes from the last durable checkpoint rather than requiring a full restart or manual state reconstruction.

Core Architecture

pg_durable is built using pgrx and operates as a PostgreSQL extension with no external service dependencies. Its architecture relies on two underlying Rust libraries:

  • duroxide: A durable task framework that provides the orchestration runtime, including deterministic replay, timers, and checkpoints.
  • duroxide-pg: A PostgreSQL-backed state provider that persists runtime state—such as instances, history, and work queues—within a dedicated duroxide.* schema.

Workflows are defined using composable operators (such as ~> and |=>) and are triggered via df.start(). A background worker, which must be configured as a superuser to bypass Row-Level Security (RLS) for instance management, handles the execution.

Target Workloads and Use Cases

pg_durable is specifically designed for backend and data engineers who want their compute to live close to their data. Ideal workloads include:

  • Vector Embedding Pipelines: Chunking data, calling embedding APIs, and upserting results into pgvector.
  • Ingest Pipelines: Staging, deduplicating, and transforming large batches of data.
  • Scheduled Maintenance: Detecting database bloat and executing corrective actions following an approval signal.
  • Fan-out Aggregation: Running multiple independent queries in parallel and joining the results into a final step.
  • External API Workflows: Performing data enrichment or classification via HTTP calls directly from SQL.

Architectural Shifts and Trade-offs

Implementing pg_durable changes how background work is managed by moving the workflow definition and retry state from the application tier into the database. This can remove the need for external orchestrators like Apache Airflow, Temporal, or AWS Step Functions.

When to Use pg_durable

It is most effective when the workflow is "SQL-shaped" and the primary state resides in PostgreSQL. It reduces the fragility of long-running transactions that hold locks and grow the Write-Ahead Log (WAL).

When to Avoid pg_durable

Microsoft advises against using the extension in the following scenarios:

  • The task is a single SQL statement (e.g., a simple INSERT ... SELECT).
  • The requirement is sub-millisecond synchronous request handling.
  • The environment prohibits the installation of extensions or background workers.
  • The workflow spans many heterogeneous systems outside of PostgreSQL.
  • The logic requires complex application code that does not map to SQL steps, loops, or HTTP calls.

Community Perspectives and Critique

While the project is praised for its use of pgrx and the trend toward "Postgres-as-a-platform," some developers expressed concerns regarding the placement of business logic.

Concerns over "Logic in the DB"

Several contributors argued that moving control flow into the database mirrors the problems of stored procedures, citing difficulties in unit testing, versioning, and observability. One user noted:

"Business logic in the database... [is] harder to isolate noisy workloads, no observability, scaling pressure lands solely in Postgres."

Comparison to External Orchestrators

Some users questioned the necessity of pg_durable compared to established DAG (Directed Acyclic Graph) schedulers like Apache Airflow. The debate centers on whether the convenience of "zero infrastructure" outweighs the benefits of having control flow managed in version-controlled application code rather than in the database.

Installation and Requirements

pg_durable is currently in Preview. It requires PostgreSQL 17 or 18 and is distributed as Debian packages for amd64. To enable the extension, users must add pg_durable to shared_preload_libraries, restart the server, and execute CREATE EXTENSION pg_durable;.

Sources