This New Postgres Extension Let's You Write 90% Less Code

Englishالعربية Deutsch Español Français हिन्दी Bahasa Indonesia 日本語 한국어 Português Русский 中文

Computing/SoftwareInternet Technology

Transcript

00:00:00everyone's favorite database just got a huge upgrade durable crash proof functions inside

00:00:05postgres you can now manage scheduled jobs automatic retries or long running processes

00:00:11and workflows like human in the loop approval all with no external services this is pg durable

00:00:17an open source postgres extension developed by microsoft today we'll cover exactly what pg

00:00:22durable is and even run through some examples like setting up scheduling directly into postgres

00:00:28so you can ditch whatever external service you are using and we cover a huge amount

00:00:33of developer content on this channel so if you want to stay up to date then subscribe

00:00:42a durable function in pg durable is persisted to disk every step of the way that gives you a specific

00:00:48set of guarantees you don't get from a plain begin commit block or a cron job and this means operations

00:00:53like database crashes and restarts long waits and failures can now be managed directly inside

00:00:59postgres so let's dive into exactly how all of this works pg durable provides durable functions which are

00:01:05just a graph of steps you submit with sql dsl and then submit with the df start function so here's a

00:01:11super simple example just to explain the concepts if we take sql like select hello world this would just

00:01:17return to us the string hello world but now we can run it as select df start select hello world obviously

00:01:23this would benefit from long running processes but what we get back from this is an eight character id

00:01:28we can pass to the df status function this would just give us the operation status or we can use df

00:01:34results to give us the final output from the operation once it's complete so you can see straight

00:01:39away how this would be valuable for long running tasks or for tasks that require an approval step pg durable

00:01:45also comes with a bunch of built-in operations like running multiple operations in sequence running

00:01:50multiple operations in parallel or racing operations to return the fastest we also have a bunch of built-in

00:01:56functions including df dot wait for schedule to wait on cron expressions using this we could run

00:02:01scheduled jobs without any third-party systems so let's jump straight into a demo to show you how it's done

00:02:07so in this example here we're setting up a schedule we're calling select with the df start function and

00:02:13then inside this we're using the loop operation so that means this will just loop around forever on a

00:02:18minute schedule so every single minute and then here we're using the sequence operation so we're saying

00:02:23first we'll wait for one minute then after that task is completed we're going to call our procedure

00:02:29which is called refresh materialized views you could just call any sql here but in our case we'll call

00:02:34call in a procedure this in our case will insert into a table called refresh log and it's just going

00:02:40to insert the value weekday refresh so if we run this you can see first the original df start operation

00:02:47runs and then we get an id coming back from that so then we can just watch the logs fill up inside

00:02:52that table by running this sql here select id run out from refresh log and then we'll order in by the id

00:02:58so you can see now that's fired a couple of times and if we head into our database client and refresh this

00:03:03you can see now we've got three logs inside the database because this is now running on a permanent

00:03:08loop so as long as this script is running this will just loop around forever and insert data into

00:03:13our table on a one minute schedule in the next example we'll run through a workflow that can approve

00:03:18orders with human in the loop approval so again we're using select df start so the first thing that we're

00:03:23going to do is just to run this sql here which is just going to select the top order in our database

00:03:28and then this is the variable assignment command so the result of this will be stored inside the

00:03:34variable order then this is the sequence command we'll say once we've got the order we're going to

00:03:39then wait for the signal of approval from a human this will then be stored in the sig variable and it

00:03:44will wait up to 24 hours again we use the sequence operation and then we're using the if function and

00:03:50we're basically saying if the approval didn't time out and the user approved it then we can update the

00:03:55order status as approved otherwise we update it as rejected so if we run this script in the first

00:04:01terminal npm run workflow you can see that we execute the sql we get an id back and now we're waiting

00:04:07on approval from our second script so if we look inside the database at this point you can see we have an

00:04:11order and the status is set to pending and then if we run our approval script the status now has

00:04:16switched to approved the first script then exits because the approval has now happened and you can

00:04:21see inside this sql when we actually made the approval we're using the df signal function which

00:04:27wakes the parked workflow to then approve the record in the database now if you want to run all of this

00:04:32yourself i'll just quickly go over the docker compose file so you can run it all locally so here inside

00:04:37my docker compose file i'm using the pg durable image from microsoft which is running postgres 17

00:04:43because this of course includes the pg durable extension you can see further down as well inside

00:04:48commands we're using the shared preload libraries set to pg durable but other than that the rest of the

00:04:54config can just be customized as you like it this will then run a postgres database with the pg

00:04:59durable extension running so you can then run any of these examples locally yourself there are also a bunch of

00:05:04call examples on the pg durable site like this example showing you how to replace 300 lines of sql with a

00:05:11single seven line sql statement so you can see here on the left without pg durable we have 300 lines of

00:05:17boilerplate which do things like queue setup and configuration worker management and polling message

00:05:22handling and state tracking error handling and retries and it carries on this is 300 lines of boilerplate

00:05:30which can then be reduced down to just seven lines with pg durable this will first run three operations

00:05:36in parallel using the ampersand character and then we're going to use the sequence character to then

00:05:41refresh the dashboard now this is so concise not just because of the new operations we've got access to

00:05:46but also because things like error handling and retries are handled automatically inside pg durable and if

00:05:53you want to dive deeper into pg durable check out the relevant links in the video description i hope you

00:05:58enjoyed that one guys i've been warren from better stack thanks for watching and i'll see you next time

Key Takeaway

Pg Durable reduces complex backend workflow code by up to 90% by enabling crash-proof, scheduled, and stateful functions directly within PostgreSQL.

Highlights

Pg Durable is an open-source PostgreSQL extension developed by Microsoft that manages scheduled jobs, automatic retries, and long-running workflows natively.
Functions defined with Pg Durable persist to disk at every step, providing crash-proof guarantees for database operations.
Complex logic requiring 300 lines of boilerplate code for queue setup, worker management, and state tracking can be reduced to seven lines of SQL.
The extension supports sequences, parallel execution, race conditions, and cron-based scheduling directly within the database.
Human-in-the-loop workflows, such as order approvals with 24-hour timeouts, can be implemented and signaled directly through PostgreSQL functions.

Timeline

Pg Durable Functionality and Core Guarantees

Pg Durable functions are persisted to disk at every step to ensure resilience during crashes.
The system uses a SQL DSL and the df_start function to initialize workflow operations.
Operations return a unique eight-character ID used to track status or retrieve final output.

This extension manages long-running processes, automatic retries, and scheduled jobs without requiring external services. By shifting workflow management inside the database, operations survive restarts and failures that typically break standard Begin-Commit blocks.

Implementing Schedules and Workflows

Built-in operations include sequence, parallel, and race execution patterns.
The df_wait_for_schedule function enables cron-based job execution without third-party tools.
Human-in-the-loop approval workflows use df_signal to wake parked processes upon user input.

Scheduled jobs execute in continuous loops to trigger procedures like refreshing materialized views at defined intervals. Approval workflows pause execution while waiting for external signals, allowing developers to set timeouts and conditional logic based on human interaction.

Local Deployment and Code Reduction

Local deployment is achieved using the Microsoft pg_durable Docker image running PostgreSQL 17.
Boilerplate code for tasks like polling, message handling, and error management is replaced by native SQL operations.
Operations can execute in parallel using the ampersand operator within the DSL.

Integration requires adding the extension to shared_preload_libraries within the database configuration. By replacing external worker management with native DSL commands, developers consolidate complex infrastructure requirements into significantly shorter SQL scripts.

Community Posts

Write about this video