pg_durable: Durable, resumable SQL workflow execution inside PostgreSQL
pg_durable embeds a durable, checkpointed workflow runtime into Postgres as an extension, letting teams define and persist step state in SQL — ideal for teams that want workflows next to their data and to remove external orchestrators and state tables.
GitHub microsoft/pg_durable Updated 2026-06-08 Branch main Stars 1.5K Forks 33
PostgreSQL extension durable workflows SQL-native orchestration compute-close-to-data

💡 Deep Analysis

5
Why implement pg_durable as a PostgreSQL extension (background worker + Rust/pgrx)? What advantages does that architecture bring?

Core Analysis

Architectural Choice: pg_durable is implemented as a PostgreSQL extension driven by background workers and developed in Rust using pgrx. This embeds the execution engine into the DB runtime so workflows can directly leverage DB transactions, persistence, and permission semantics.

Technical Features & Advantages

  • Execution Close to Data: Runs at the DB edge, reducing network hops and data copying — beneficial for high-volume row/batch processing.
  • Transaction & Backup Consistency: Checkpoints are ordinary transactions written to tables, integrating with backups and audit trails.
  • No Additional Infrastructure: Eliminates the need to run Redis, separate schedulers, or queue systems, lowering operational overhead.
  • Memory Safety & Stability: Rust + pgrx reduces memory-corruption risks versus raw C extensions, lowering the chance of extension-induced DB crashes.

Practical Recommendations

  1. Operational evaluation: Ensure you can install extensions and set shared_preload_libraries, and plan extension upgrade steps.
  2. Capacity planning: Monitor background worker usage, lock contention, and WAL growth to avoid resource exhaustion under concurrency.
  3. Security & audit: Use DB-native permission controls (RLS, roles) to manage access to df.* tables and df.start().

Important Notice: This architecture is not feasible in managed environments that disallow installing extensions.

Summary: The extension + background-worker approach enables durable in-DB execution while leveraging Rust’s safety, eliminating external service dependencies and centralizing workflow management with data.

88.0%
How to design idempotent steps in pg_durable and safely interact with external APIs to avoid duplicate side effects?

Core Analysis

Key Issue: pg_durable may re-execute steps during recovery or retry, so external side effects must be protected by idempotency or deduplication mechanisms.

Technical Analysis (Practical Patterns)

  • Transaction-scoped idempotency records: Insert a record (e.g. external_calls(call_id, status)) before issuing an external request. On recovery, check this table; if marked successful, skip the call. The write and status changes should be coordinated to avoid races.
  • Idempotent external endpoints: Prefer external APIs that accept an idempotency key (e.g. Idempotency-Key) so duplicate requests have no adverse effects.
  • Deduplication middle layer: If the external service is not idempotent, add a small deduplication proxy/service that enforces idempotency for you.
  • Persist responses: Persist external success/failure results back into df.* or business tables so recovery can observe completed work.

Practical Recommendations

  1. Preferred pattern: Use “write-dedup-record then call” or “call idempotent endpoint then write success marker” so that recovery can determine if the operation was already done.
  2. Timeouts & concurrency: Configure sensible timeouts, retry policies, and concurrency caps to avoid blocking background workers.
  3. Monitoring & alerts: Track duplicate-calls, failure rates, and alert for abnormal patterns for manual inspection.

Important Notice: Avoid embedding complex non-idempotent logic directly as a single SQL step; encapsulate it in a service or UDF and coordinate idempotency with the DB.

Summary: Ensure idempotency via DB-side deduplication records or idempotent external endpoints. The “write dedupe record + call + persist result” pattern plus monitoring is the most robust approach in pg_durable.

88.0%
When evaluating alternatives, how should pg_durable be compared to external orchestrators (Temporal/Airflow)? When should you pick which?

Core Analysis

Key Question: Decide between pg_durable and external orchestrators (Temporal/Airflow) based on the coupling of workflows to data, deployment constraints, and the need for cross-service coordination or SDK support.

Comparison Points

  • Infrastructure & ops: pg_durable runs as an extension and removes separate scheduler/queue infrastructure; Temporal/Airflow require independent services to deploy and operate.
  • Data coupling & audit: If workflows primarily operate on DB-resident data and require auditability & backup consistency within the DB, pg_durable is advantageous; external orchestrators keep state outside the DB.
  • Capabilities & ecosystem: Temporal offers rich multi-language SDKs, complex compensation/retry, and cross-service semantics; Airflow is mature for batch scheduling and has many plugins. pg_durable is SQL-shaped and more limited but simpler.
  • Deployment constraints: If you cannot install DB extensions, external orchestrators are required.

When to choose which

  1. Choose pg_durable when:
    - Workflows are tightly coupled to DB data and need in-DB audit/recovery.
    - You want to reduce operational infrastructure.
    - Logic maps well to SQL or small services/UDFs.
  2. Choose Temporal/Airflow when:
    - You need multi-language SDKs, complex cross-system orchestration, or tenant isolation.
    - You cannot install DB extensions.

Hybrid strategy: Use pg_durable for data-local subflows (performance/audit benefits) and an external orchestrator to coordinate cross-service long-running flows or non-SQL-heavy logic.

Summary: Treat pg_durable as a data-first, operationally light option; treat Temporal/Airflow as a general-purpose orchestration platform. Choose or combine based on workflow boundaries and hosting constraints.

88.0%
What are the learning costs and common pitfalls when migrating logic into pg_durable? What are the best practices?

Core Analysis

Key Issue: Migrating workflows into pg_durable requires both a shift to SQL-centered design and learning extension deployment/operation details. Common pitfalls relate to permissions, idempotency, and resource management.

Technical Analysis (Learning Costs & Pitfalls)

  • Deployment & ops: You must modify shared_preload_libraries, install packages or build from source with pgrx. Managed DB restrictions can block deployment.
  • SQL-shaped implementation: Converting complex business logic into SQL-shaped steps (or wrapping it accessible via df.http()) requires deliberate design.
  • Idempotency & side effects: Non-idempotent steps can cause duplicated external side effects on retries/recovery (e.g. duplicate API calls or billing).
  • Permissions & upgrades: Forgetting df.grant_usage or not reapplying permissions after upgrades causes runtime failures.

Best Practices

  1. Design for idempotency: Ensure external-effect steps are idempotent or have deduplication keys.
  2. Encapsulate complex logic: Move complex code or non-HTTP SDK logic into small services or SQL UDFs and integrate via df.http() or function calls.
  3. Checkpoint by step: Split large work into manageable steps to reduce retry costs.
  4. Local testing & fault injection: Use Dev Container/Codespace and inject restarts/failures to validate recovery semantics.
  5. Monitoring & quotas: Monitor df.instances, worker usage, set concurrency caps, and apply least-privilege access.

Important Notice: If you cannot install extensions or adjust DB startup, pg_durable is not an option.

Summary: The migration learning curve is moderate: focus on extension ops, idempotent step design, and thorough testing to minimize production risk.

87.0%
How do pg_durable's step-level checkpoints work? What are the performance and recoverability impacts and limitations?

Core Analysis

Key Issue: pg_durable persists a checkpoint after each step so completed subtasks need not be redone. This improves recoverability but impacts performance (notably I/O and WAL generation).

Technical Analysis

  • How it works: Each step, when finished, is persisted via a normal DB transaction into df.* tables. Background workers consult these tables to decide the next step; after crashes the runtime resumes from the last committed checkpoint.
  • Recovery semantics: Recovery granularity is determined by your checkpoint granularity. Finer-grained steps reduce rework on recovery.
  • Performance/resource impact: More checkpoints produce more short transactions and WAL writes, increasing I/O, fsync frequency, and WAL consumption — effects magnified under high concurrency.

Practical Recommendations

  1. Step granularity: Break tasks into idempotent, low-cost steps, avoiding excessive fragmentation into many microscopic transactions.
  2. Capacity & monitoring: Load-test before production and monitor WAL generation, IOPS, background worker usage, and df.instances table growth.
  3. Batching & parallelism: Use fan-out/aggregation constructs (e.g. |=>) for large batches and throttle concurrent instances to protect DB resources.

Important Notice: If your environment is very sensitive to WAL growth or I/O, carefully evaluate and capacity-plan before adoption.

Summary: Step-level checkpoints greatly improve recoverability but require balancing checkpoint frequency with DB I/O and concurrency constraints, backed by idempotent step design and monitoring.

86.0%

✨ Highlights

  • Brings resumable workflows natively into Postgres
  • Runs as an extension with no additional infrastructure required
  • Requires installing an extension and configuring a background worker
  • Not suitable where extensions can't be installed or workflow logic is non-SQL/complex

🔧 Engineering

  • Define checkpointed steps in SQL; runtime persists state step-by-step and automatically resumes
  • Provides DB-level primitives for scheduling, parallelism, conditional logic, and HTTP calls

⚠️ Risks

  • Depends on installing an extension and a background worker on the target Postgres instance; may be restricted in managed environments
  • SQL-shaped model cannot directly host complex in-memory logic or non-HTTP external integrations

👥 For who?

  • Backend/data engineers, DBAs, and SREs familiar with Postgres and SQL operations
  • Suitable for teams that want workflows colocated with data and to reduce external orchestrators