# How pg_durable Persists State Across PostgreSQL Crashes Using Runtime State Tables

> Learn how pg_durable persists state across PostgreSQL crashes using runtime state tables. It stores metadata in WAL-protected tables for exact replay after restarts.

- Repository: [Microsoft/pg_durable](https://github.com/microsoft/pg_durable)
- Tags: internals
- Published: 2026-06-08

---

**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`](https://github.com/microsoft/pg_durable/blob/main/src/dsl.rs), lines 994-1004 show the DSL using `Spi::run` to execute these inserts inside the user's transaction.

```rust
// 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.

```sql
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`](https://github.com/microsoft/pg_durable/blob/main/src/worker.rs) so it can detect if the extension has been dropped.

```rust
// 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.

```rust
// 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`](https://github.com/microsoft/pg_durable/blob/main/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.

```rust
// 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`](https://github.com/microsoft/pg_durable/blob/main/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.

```sql
-- 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`](https://github.com/microsoft/pg_durable/blob/main/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.