Inside pg_durable: How Microsoft's In-Database Durable Execution Redefines Workflow Resiliency

The Fallacy of Network Reliability and the State Management Nightmare

In distributed systems architecture, the network is assumed to be unreliable. Connections drop, API calls timeout, container instances restart arbitrarily, and downstream services fail under load. For backend engineers, writing code that safely handles these partial failures is one of the most intellectually taxing and error-prone aspects of application development.

Traditionally, solving this problem required implementing complex software patterns such as the Saga Pattern, Outbox Pattern, or integrating heavy external orchestrators like Temporal, Cadence, or AWS Step Functions. While highly effective, these external orchestrators introduce significant operational complexity, serialization overhead, and the notorious "dual-write problem"—where ensuring that both your database and your workflow orchestrator agree on the current state of a transaction becomes a major distributed consensus headache.

With Microsoft's open-sourcing of pg_durable, a paradigm shift is underway. By bringing durable execution directly into the PostgreSQL relational engine, developers can now write fault-tolerant, stateful workflows that inherit the transactional atomicity, consistency, isolation, and durability (ACID) guarantees of Postgres itself.

Let's take a deep dive into the architecture of pg_durable, analyze how in-database durable execution works, and evaluate how it compares to traditional external workflow engines.

What is Durable Execution?

Before analyzing pg_durable, it is essential to define durable execution. Normal application code is transient; if the process running your code crashes mid-execution, all local state, variables, and call stacks are lost. You must write defensive code to determine where the execution left off and how to safely resume or roll back.

Durable execution guarantees that your code will execute to completion, regardless of infrastructure failures, process restarts, or network disruptions. It achieves this by transparently persisting the execution state (local variables, call stacks, and execution history) at every step. If the server running the code dies, another worker can instantly resume the execution from the exact point of failure, with all local state perfectly reconstructed.

Historically, this required a dedicated orchestration cluster and a client SDK that intercepted execution points. pg_durable changes this by leveraging PostgreSQL as both the runtime scheduler and the state transition engine.

Under the Hood: The Architecture of pg_durable

pg_durable is designed as a native PostgreSQL extension, written in C and Rust, that integrates directly with Postgres's background worker processes and transactional engine.

Instead of treating the database as a passive data store where an external engine writes state updates, pg_durable turns PostgreSQL into an active runtime. Here is how the core architecture operates:

1. Unified Transactional State Transitions

In a traditional microservices architecture, if you update a user's balance in Postgres and then dispatch a task to an external orchestrator, you face a split-brain risk. If the orchestrator fails before receiving the task, your database and your workflow are out of sync.

With pg_durable, the state of the workflow and the business data reside in the same physical database engine. Spawning a new workflow step, updating business tables, and committing the transaction happen within a single, atomic PostgreSQL transaction. If the transaction commits, the workflow step is guaranteed to execute; if it rolls back, the workflow step never happened.

2. Event Sourcing and Replay Mechanics

pg_durable utilizes event sourcing to reconstruct workflow state. Every time a workflow makes progress (such as completing an external API call or finishing a computational block), a record is written to a highly optimized, append-only history table inside PostgreSQL.

When a workflow needs to resume after a crash, pg_durable re-runs the workflow code. However, instead of executing the side effects (like charging a credit card) again, the engine intercepts these calls and returns the saved results directly from the Postgres history table. This ensures the workflow is deterministic and safe from duplicate executions.

3. Integrated Background Workers

PostgreSQL allows extensions to register Background Workers (BGWs)—isolated processes that run alongside the main query execution engines. pg_durable provisions a pool of background workers directly within the Postgres process space. These workers monitor the workflow state queues, execute workflow steps, handle exponential backoff retries, and manage concurrency limits, completely bypassing the need for external daemon processes.

A Conceptual Implementation: Writing Resilient Workflows

To understand how this simplifies development, let's look at a typical e-commerce order fulfillment workflow. We must charge a customer, update inventory, and send a confirmation email. If any step fails, we need structured retries and compensation logic.

Without pg_durable, this involves complex queueing, state tables, and retry cron jobs. With pg_durable, the workflow can be represented as a deterministic state machine managed directly via SQL and PL/pgSQL or native language bindings.

-- Create a new durable workflow instance
SELECT pg_durable.create_workflow(
    workflow_id := 'order_98321_flow',
    workflow_type := 'order_fulfillment',
    input_data := '{"order_id": 98321, "amount": 150.00}'::jsonb
);

-- Define steps using transactional operations
CREATE OR REPLACE FUNCTION process_order_fulfillment(w_id UUID, input JSONB) 
RETURNS VOID AS $$
BEGIN
    -- Step 1: Reserve Inventory (Atomic database update)
    PERFORM pg_durable.execute_step(w_id, 'reserve_inventory', function_name := 'db_reserve_items', input);

    -- Step 2: Charge Customer (External API call, tracked for durability)
    PERFORM pg_durable.execute_step(w_id, 'charge_card', function_name := 'api_charge_stripe', input);

    -- Step 3: Dispatch Shipping (External system update)
    PERFORM pg_durable.execute_step(w_id, 'schedule_delivery', function_name := 'api_schedule_fedex', input);
END;
$$ LANGUAGE plpgsql;

If the database server loses power exactly between Step 2 and Step 3, Postgres will recover, and the pg_durable background worker will detect that order_98321_flow was interrupted at the charge_card step. Because the result of Step 2 is already committed to the database's durable history table, the engine will resume the workflow, skip re-charging the card, and proceed directly to scheduling the delivery.

Comparing Architectures: pg_durable vs. External Orchestrators

Trade-offs and Architectural Limits

While pg_durable is a massive leap forward for simplicity, it is not a silver bullet. Software architects must analyze the following trade-offs before migrating their entire orchestration layer into PostgreSQL:

Database CPU and Memory Saturation: Running complex workflow logic, JSON serialization, and state replays inside Postgres consumes CPU cycles that would otherwise be allocated to servicing raw read/write queries. For high-throughput applications, this can lead to database starvation.
Write Amplification: Every state transition in pg_durable translates to a write to the PostgreSQL Write-Ahead Log (WAL) and the underlying tables. If your workflows feature hundreds of micro-steps, this can generate substantial WAL bloat, placing pressure on replication lag and disk I/O.
Long-Running Compute Workflows: Postgres background workers are best suited for short, I/O-bound tasks (like coordinating API calls or updating tables). If your workflows involve heavy CPU calculations or run for hours without yielding, they can exhaust the PostgreSQL background worker pool.

The Verdict: When to Use pg_durable

pg_durable represents a significant democratization of durable execution. It is an exceptional fit for:

Monoliths and Minimalist Architectures: Teams that want the reliability of durable execution without the massive overhead of managing a Temporal or AWS Step Functions cluster.
Highly Transactional Workflows: Systems where the workflow state is deeply intertwined with relational database tables (such as ledger updates, inventory management, and billing systems).
Edge and Local Environments: Applications running on the edge or in local environments where resource optimization is paramount and spinning up multiple microservice containers is impossible.

By open-sourcing pg_durable, Microsoft has acknowledged a growing industry trend: the consolidation of the backend stack around PostgreSQL. By turning the world's most trusted relational database into a native, ACID-compliant workflow engine, the boundaries of what can be built reliably with a single database have been expanded once again.