How pg_durable Persists State Across PostgreSQL Crashes Using Runtime State Tables

pg_durable stores all orchestration metadata in ordinary WAL-protected PostgreSQL tables so the background worker can replay execution state exactly after a crash or restart.

The microsoft/pg_durable extension implements durable workflows inside PostgreSQL by treating database tables as its sole source of truth for both user-defined function graphs and internal runtime progress. To persist state across PostgreSQL crashes, the extension writes every node definition, instance record, and orchestration checkpoint to standard tables protected by the Write-Ahead Log (WAL). This design allows the duroxide background worker to resume orchestration seamlessly after any unplanned restart.

Graph Construction Writes Durable Rows to df.nodes

Every call to df.start(...) triggers an INSERT into df.nodes for each node in the function graph, plus a corresponding row in df.instances. In src/dsl.rs, lines 994-1004 show the DSL using Spi::run to execute these inserts inside the user's transaction.

// src/dsl.rs – part of df.start()
Spi::run(&format!(
    "INSERT INTO df.nodes
     (id, instance_id, node_type, query, result_name, left_node, right_node)
     VALUES ('{}','{}','{}',{}, {}, {}, {})",
    node_id, instance_id, node.node_type,
    query_escaped,
    escape_option(&node.result_name),
    escape_option(&left_id),
    escape_option(&right_id)
));

The df.nodes schema stores the node type, SQL query, result name, parent references, and a default pending status.

CREATE TABLE df.nodes (
    id VARCHAR(8) PRIMARY KEY,
    instance_id VARCHAR(8),
    node_type TEXT NOT NULL,
    query TEXT,
    result_name TEXT,
    left_node VARCHAR(8),
    right_node VARCHAR(8),
    status TEXT DEFAULT 'pending',
    result JSONB,
    created_at TIMESTAMPTZ DEFAULT now()
);

At this point the entire graph definition is already protected by PostgreSQL WAL.

Runtime State Tables Survive a Crash

The duroxide background worker persists its own orchestration state in tables under the duroxide schema, plus sentinel rows in df._worker_epoch and duroxide._worker_ready. When the worker starts, it writes an epoch sentinel in src/worker.rs so it can detect if the extension has been dropped.

// src/worker.rs – write epoch sentinel (lines 94-101)
let epoch_id = uuid::Uuid::new_v4().to_string();
sqlx::query("INSERT INTO df._worker_epoch (epoch_id, started_at, last_seen_at) VALUES ($1::uuid, now(), now())")
    .bind(&epoch_id)
    .execute(pool)
    .await?;

The worker also writes a ready flag with the current schema version, using an ON CONFLICT upsert to handle restarts gracefully.

// src/worker.rs – write worker-ready flag (lines 514-525)
sqlx::query(
    "INSERT INTO duroxide._worker_ready (sentinel, schema_version, initialized_at) \
     VALUES (TRUE, $1, now()) \
     ON CONFLICT (sentinel) DO UPDATE SET \
         schema_version = EXCLUDED.schema_version, \
         initialized_at = EXCLUDED.initialized_at \
     WHERE duroxide._worker_ready.schema_version != EXCLUDED.schema_version"
)
    .bind(crate::WORKER_SCHEMA_VERSION)
    .execute(pool)
    .await?;

All checkpointed results are stored in the result JSONB column of df.nodes. When the runtime revisits a node after a crash, it inspects the status column; if the value is already completed, execution is skipped. This guarantees exact-once semantics without external coordinators.

Crash Recovery Flow in duroxide_worker_main

When PostgreSQL restarts, shared_preload_libraries re-launches the background worker. The duroxide_worker_main function in src/worker.rs reconnects and validates the epoch sentinel through check_epoch_sentinel. If the sentinel is present, the extension is still installed and the runtime replays any queued orchestration.

// src/worker.rs – keep running until the sentinel disappears (lines 889-894)
let still_valid = match epoch_id {
    Some(eid) => check_epoch_sentinel(poll_pool, eid).await,
    None => check_extension_exists(poll_pool).await,
};
if !still_valid { break; }

During replay, the Duroxide runtime loads the function graph from df.nodes through the load_function_graph activity and walks the graph in src/orchestrations/execute_function_graph.rs. Because no critical state lives in memory, the new worker simply continues where the crashed instance left off.

Inspecting Persisted State After a Crash

You can query the exact tables the runtime consults during recovery.

-- See the graph stored for instance 'xyz123ab'
SELECT * FROM df.nodes WHERE instance_id = 'xyz123ab';
SELECT * FROM df.instances WHERE id = 'xyz123ab';

-- Snapshot of variables captured at start
SELECT * FROM df.vars;

-- Worker liveness and epoch sentinels
SELECT * FROM duroxide._worker_ready;
SELECT * FROM df._worker_epoch;

These queries return the same state the duroxide runtime replays on startup.

Summary

• Ordinary PostgreSQL tables provide the durability layer; all state is written to the df and duroxide schemas.
• Graph definitions are persisted during df.start(...) into df.nodes and df.instances.
• Worker liveness is tracked in df._worker_epoch and duroxide._worker_ready.
• Checkpoint data including node results is stored in df.nodes.result as JSONB, with status enforcing exact-once execution.
• Crash recovery is handled entirely by replaying WAL-protected tables in a fresh duroxide_worker_main process.

Frequently Asked Questions

What happens to running orchestrations when PostgreSQL crashes?

All orchestration state resides in PostgreSQL tables protected by the Write-Ahead Log. When the database restarts, the duroxide_worker_main background worker reconnects, validates the epoch sentinel, and replays queued work from duroxide.* and df.* tables exactly where it left off.

How does pg_durable prevent duplicate node execution after recovery?

The runtime writes each node's output to the result JSONB column in df.nodes and sets status to completed. During replay, the orchestrator checks this status and skips any node that is already finished, ensuring exact-once semantics without external locking.

What is the purpose of the df._worker_epoch sentinel?

Created on lines 94-101 of src/worker.rs, the epoch sentinel acts as a heartbeat record. The worker periodically calls check_epoch_sentinel; if the row disappears, the worker determines the extension has been dropped and exits cleanly instead of processing stale orchestrations.

Where does pg_durable store function graph definitions?

User-level graph definitions are stored in df.nodes and df.instances during the transaction that calls df.start(). Each df.nodes row records a node's type, query, result name, and edge relationships, while df.instances tracks the root node and overall status. This allows the load_function_graph activity to rebuild the full in-memory graph after a restart.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}