Recently, Microsoft has unveiled pg_durable, an innovative PostgreSQL extension that empowers developers to execute durable workflows directly within the database environment. This advancement significantly reduces reliance on external orchestration systems, streamlining the development process.
Seamless Workflow Management
With pg_durable, the complexities of integrating cron jobs, background workers, message queues, and external orchestrators are alleviated. Developers can now express long-running, fault-tolerant SQL functions in a more straightforward manner. The extension adeptly manages execution concerns, including retries, fan-out, and recovery, allowing for a more focused approach to workflow design.
Workflows are articulated in SQL, with the extension taking charge of retry states, progress tracking, and checkpointing, all within PostgreSQL. This encapsulation means that many app-tier workers, queue consumers, or scheduling mechanisms can be entirely eliminated from the architecture.
A
pg_durablefunction operates as a graph of SQL steps that PostgreSQL executes and checkpoints throughout the process. In the event of a database crash, restart, or failure of a step, execution can resume seamlessly from the last durable checkpoint, negating the need for manual state reconstruction.
The extension ensures that function execution states are preserved within PostgreSQL tables, allowing workflows to withstand crashes, restarts, and failovers. It introduces a domain-specific language (DSL) that offers first-class primitives for scheduling, conditions, and parallel execution.
Example of Durable Functions
Here is a concise illustration of a durable function that processes data in distinct steps:
SELECT df.start(
'SELECT id FROM documents WHERE processed = false LIMIT 100' |=> 'batch'
~> 'UPDATE documents SET processed = true WHERE id = ANY($batch)'
);
The above snippet highlights the use of ~> and |=>, two special operators introduced by pg_durable that facilitate the sequential execution of nodes and the binding of results to variables. The df.start function initiates the execution of a durable function. Additionally, the following example demonstrates how to execute two nodes in parallel and wait for their completion using the df.join function or its equivalent & operator:
df.join('SELECT count(*) FROM a', 'SELECT count(*) FROM b')
'SELECT 1' & 'SELECT 2'
Microsoft envisions pg_durable as particularly beneficial for vector embedding pipelines, where data is segmented, sent to an embedding API, and subsequently upserted into pgvector. It is also well-suited for scheduled maintenance tasks, such as detecting bloat, triggering notifications, awaiting approvals, and executing follow-up actions. Moreover, it accommodates workflows that rely on external APIs.
Architectural Insights
Architecturally, pg_durable is designed with minimalism in mind, comprising solely a PostgreSQL extension and a background worker, devoid of any external control plane. The worker responsible for executing durable functions is built upon two Rust libraries: duroxide, which provides the orchestration runtime, including deterministic replay, checkpoints, sub-orchestrations, and timers; and duroxide-pg, which maintains instances, history, work queues, and other runtime states within a dedicated schema owned by duroxide.
Durable execution represents a software paradigm that allows long-running workflows to automatically resume from failure points, thereby eliminating the complexities associated with manual state recovery. This model simplifies the architecture of distributed systems, fostering robust agent architectures and cloud control planes. Previous explorations of durable execution have highlighted its support in platforms such as Temporal and Cloudflare.
About the Author
Sergio De Simone
Show moreShow less
