Introducing Durable Functions in PostgreSQL
By Abe Omorogbe, Senior PM | Pino De Candia, Principal Software Engineer | TJ Green, Principal Software Engineer Postgres will happily store your data, run your queries, and scale with you for years. But the moment you need to do more with that data, such as running multi-step transformation, scheduling nightly rollups, generating embeddings or waiting on an approval, you hit a wall. Postgres has no built-in way to run long-lived, fault-tolerant work. That's why we built pg_durable, a new open-source PostgreSQL extension that brings durable execution directly into the database. With pg_durable, Postgres doesn’t just store your data, it runs long-lived, fault-tolerant workflows on it, with built-in retries, parallelism, scheduling, and recovery. Instead of stitching together PL/pgSQL functions or building external orchestration systems, you can now define and run resilient workflows entirely in your database, backed by Postgres' durability and high availability. And on Azure HorizonDB, pg_durable also powers AI pipelines, enabling production-ready data and AI workflows, end-to-end, right inside the database. In this post, we'll cover: The hidden trap: blocking background work What pg_durable is and the DSL that drives it How this engine powers AI pipelines on HorizonDB Sample patterns worth exploring Getting started on HorizonDB, on your laptop, and in VS Code 🚀 Want to try it out? pg_durable ships in Azure HorizonDB, Microsoft's new PostgreSQL cloud service. The HorizonDB Preview is the fastest way to try pg_durable and AI pipelines together. Get started in HorizonDB → pg_durable visualization The hidden trap: blocking background work Most Postgres teams eventually reach a point where they need to run critical tasks on their data: transformations, nightly aggregations, database maintenance workflows, embedding jobs, or multi-step business processes. So, they do the natural thing and try to keep that work inside Postgres. They end up on a journey of increasing complexity and maintenance burden. First, just run the task as a function in your database You cram the whole workflow into one PL/pgSQL function: loop, transform, call APIs, write results, return. It looks simple until you have to run it in production. One connection stays tied up the whole time. Everything runs inside one big transaction, with long locks and no visibility into partial progress. If the connection drops or the database restarts, the whole run is gone. No per-step retries. No parallelism. No scheduling. No clean way to pause for human input. When it fails, you move it outside You push the workflow into an external service: a job queue, polling workers, state tables, step coordination, retry logic, crash-recovery sweeps, and cleanup jobs. What started as a few background tasks turns into a full distributed system. Before you’ve even touched the business logic, you’re building and operating infrastructure just to coordinate work that’s still fundamentally tied to your data. Both paths are workarounds for the same missing primitive: durable, asynchronous background work that lives where your data lives. That's the gap pg_durable fills. What pg_durable actually is pg_durable is a Postgres extension that consists of a DSL (Domain specific language) and the duroxide runtime hosted in a Postgres background worker. You describe a workflow as a small SQL expression, call df.start(...), and get an instance ID back immediately. The work runs off to the side in a background worker, so it never blocks your connection or transaction, and you can check progress later with df.status() and df.result(). The execution state lives in Postgres, which means it benefits from the database’s durability, HA, backups, and recovery. Additionally, the workflow definition does not have to live in the database: your application can send it to df.start(...) over a regular Postgres connection. 2: pg_durable orchestration of worker and schema Because execution is asynchronous, pg_durable automatically breaks a workflow into discrete steps. Each step runs in its own session and transaction, commits its progress, and hands off to the next instead of keeping one giant transaction open. Steps are checkpointed in Postgres and recovered by deterministic replay, so workflows survive crashes, restarts, and failovers and resume where they left off. If a step fails, only that step retries. The whole thing is expressed through a tiny DSL of composable operators: Operator Meaning ~> Sequential. run this, then that & Parallel. fan out, wait for all | Race. fan out, take the first to finish ?> / !> Conditional. if / else @> Loop. repeat durably, survive restarts |=> Capture a step's result into a variable (reuse with $) Advanced Functions df.if() Conditional branch df.loop() Repeat statements df.join() Execute in parallel, wait for all df.http() To call an allowlisted endpoint df.wait_for_schedule() For cron-style timing df.wait_for_signal() Pause for an external event Read more about all operators and functions in pg_durable Without pg_durable vs. with pg_durable The hand-rolled version of "run three aggregations in parallel, then refresh a dashboard with retries and crash recovery" usually means 300+ lines of queue tables, polling workers, state-machine rows, per-step retry logic, crash-recovery sweeps, and cleanup jobs. Plus, the runbook to operate it. The pg_durable version: SELECT df.start( 'SELECT count(*) FROM users' & 'SELECT count(*) FROM orders' & 'SELECT sum(amount) FROM orders' ~> 'REFRESH MATERIALIZED VIEW metrics', 'refresh-dashboard' ); You write the SQL. pg_durable owns the queue, the state, the coordination, the retries, and the crash recovery. Two ways to use pg_durable 1: Use pg_durable directly (works on Azure HorizonDB or any Postgres 17) Enable it and start orchestrating: CREATE EXTENSION pg_durable; SELECT df.start($$ SELECT 'Hello, durable world!' AS message $$); -- returns an instance ID immediately; the worker runs it asynchronously From there you compose: sequential pipelines, conditional branches, races for timeout-or-result, variable passing between steps, human-in-the-loop approvals, scheduled maintenance all in SQL, close to the data, with no new infrastructure. This is the "just use Postgres" answer to a problem teams usually solve by leaving Postgres. Because it's open source under the permissive PostgreSQL License, you can clone the repo and run it on your laptop, your server, or any cloud. 2: AI pipelines (HorizonDB capability) On HorizonDB, pg_durable becomes the foundation for something even more approachable: a managed, declarative AI pipeline surface in the azure_ai extension. pg_durable gives you the durable execution engine, while the ai.* API gives you an AI-shaped model of sources, steps, sinks, and triggers that compile into a durable graph. Traditional app-tier embedding pipelines fail in predictable ways: a transient API error mid-batch with no shared checkpoint, a worker that crashes after writing chunks but before marking the parent row processed, no clean way to re-embed just the rows that changed. Move that logic into HorizonDB and the source, the steps, the sink, and the run history are all SQL, protected by the same transactions, backups, and PITR (point-in-time recovery) your data already has. A complete chunk → embed AI pipeline is one definition: SELECT ai.create_pipeline( name => 'ai_pipeline', source => ai.table_source(table_name => 'documents_ai_pipeline'), steps => ARRAY[ ai.chunk(input => 'content'), ai.embed(model => 'default-embedding', input => 'chunk_text', dimensions => 1536) ], trigger => 'on_change', sink => ai.table_sink('documents_ai_pipeline_output') ); SELECT ai.run('ai_pipeline'); Each AI step becomes a durable node, so if ai.embed() fails, ai.chunk() doesn’t run again. And with trigger => 'on_change', the pipeline runs automatically as rows change, embedding only what’s new. Add a DiskANN index on the resulting table, and you have production-ready vector search end to end, entirely inside the database. Where pg_durable fits and where it doesn't If you've used external orchestrators such as Temporal or Airflow, your first reaction is probably: why would I put control flow in my database? Fair question. pg_durable isn't trying to be a universal orchestrator. Reach for pg_durable when the workflow is tightly coupled to Postgres state. The rows it reads and writes live in the same database, it benefits from the database's own durability, backups, and PITR, and you'd rather not stand up a separate system to coordinate work that never leaves the data tier. Think: embedding pipelines, ETL jobs, scheduled maintenance, and queue-style background jobs. Reach for a dedicated orchestrator when the workflow's center of gravity is outside Postgres, fanning across heterogeneous services, or running arbitrary application logic that does not map cleanly to SQL steps, branching, loops, or HTTP calls. Get started On Azure HorizonDB CREATE EXTENSION IF NOT EXISTS pg_durable; -- Execute a simple SQL query as a durable function SELECT df.start($$ SELECT 'Hello, durable world!' AS message $$); -- Returns: a1b2c3d4 (8-character instance ID) -- Get result of a specific instance SELECT df.result(<ID>); That's it: submit, walk away, inspect. Read the documentation for more details. In VS Code, with the PostgreSQL extension A dense one-liner of ~>, &, and |=> is precise once it clicks, but the learning curve is real so flatten it with tooling. Install the PostgreSQL extension for VS Code from the Marketplace: Connect to HorizonDB or your local Postgres directly from the extension Let Copilot write the SQL. The pg-durable-sql skill turns a plain-English description ("every night, archive orders older than 90 days") into correct pg_durable syntax. Run it and watch it. The extension renders pg_durable workflows and azure_ai pipelines as live graphs, definition and each run, so you can see every step, its timing, and exactly where a failure happened. Authoring, execution, run visualization, and inspection in one window and the same tooling works against any Postgres, not just HorizonDB. On your laptop Prefer to run it yourself? Clone microsoft/pg_durable, use the Codespace prebuild or VS Code Dev Container, and add the extension on any Postgres 17. Sample patterns worth exploring The scenario guide has a full catalog of scenarios; however, these are the three I would start with. ETL Pipeline: a multi-step data transformation where each step must be completed before the next begins. Failures should stop the pipeline. SELECT df.start( 'DELETE FROM target WHERE loaded_at < now() - interval ''7 days''' -- Step 1: Cleanup old ~> 'UPDATE staging SET processed_at = now() WHERE processed_at IS NULL' -- Step 2: Mark staging ~> 'INSERT INTO target (data, source_id) SELECT data, source_id FROM staging WHERE processed_at IS NOT NULL', -- Step 3: Load 'etl-pipeline' -- Label for easy identification ); If the database restarts mid-backfill, it picks up from the last checkpointed batch, not row zero. See full example Scheduled Data Sync: poll an external API or run a job on a schedule (hourly, daily, every 30 minutes). The job should run forever and survive restarts. (See full example): -- Scheduled sync: fetch data every 30 minutes (runs forever) SELECT df.start( @> ( -- @> creates an eternal loop -- Fetch from external API (df.http( 'https://httpbingo.org/json', 'GET' ) |=> 'response') -- Store the response ~> 'INSERT INTO external_data_sync (data) VALUES ($response::jsonb)' -- Wait for next scheduled run ~> df.wait_for_schedule('*/30 * * * *') -- Cron: every 30 minutes ), 'scheduled-data-sync' ); Human-in-the-loop approval: auto-apply routine changes, pause the risky ones until a person signals approval (See full example): SELECT df.start( 'SELECT amount > 10000 AS needs_review FROM invoices WHERE id = 42' |=> 'risky' ?> ( df.wait_for_signal('invoice-42') ~> 'UPDATE invoices SET status = ''paid'' WHERE id = 42' ) !> 'UPDATE invoices SET status = ''paid'' WHERE id = 42', 'invoice-approval' ); The workflow simply waits minutes or days until a reviewer releases it with the matching signal, then resumes. The community is already running with it pg_durable launched as open source and the community is already kicking the tires. The project was a top article on Hacker News on launch day and 1.7K stars on GitHub within its first few days of initial launch. Also Franck Pachot (PostgreSQL community veteran) published an independent walkthrough, Getting Started with pg_durable: durable workflows inside PostgreSQL within days of release. The repo is actively developed, and the maintainers are reading every issue and PR. If you want improvements in our DSL ergonomics, say so. If you want an operator that doesn't exist yet, open an issue. If you've got a scenario we haven't covered, send a PR. The syntax, the docs, and the rough edges all get better when people who run Postgres in production push back. So, clone it, and build something real. If you find rough edges, open an issue or send a PR at microsoft/pg_durable. We think you'll be surprised by how much it can take. Learn more pg_durable on GitHub Durable Functions on HorizonDB AI pipelines on HorizonDB
