4 ms·

pg_durable: Microsoft open sources in-database durable execution

474 points by coffeemug 4mo ago

levkk 4mo ago
2026 is the year of the Postgres queue! (DBOS[0], pgQue[1]) It's awesome that the community is contributing this and giving us the option to use it. As an ex-app engineer though, I kind of prefer my queue logic to be in code, in Git, but maybe with the right tooling, you can change my mind. :) [0]: https://www.dbos.dev/ https://www.dbos.dev/ [1]: https://github.com/NikolayS/pgque https://github.com/NikolayS/pgque
- dietr1ch 4mo ago
  Yeah, it's harder to work on, or maybe just different, but I guess the docs, info(searchable docs, posts, experience), and tooling are lacking. What's the story for version control, debugging, testing, releasing? It'd be cool to have everything together for data locality and simplifying the stack, but it feels you'd lose a lot of useful knowledge about how to do stuff "properly".
  - gdecandia 4mo ago
    Contributor here. Good points, we do need to develop some best practices around managing function versioning and lifecycle for pg_durable. https://github.com/microsoft/duroxide https://github.com/microsoft/duroxide - also OSS, the durable execution framework pg_durable is built on itself supports function versions. We can leverage that to get similar support in pg_durable.
    - vault 4mo ago
      > Access to this repository has been disabled by GitHub Staff due to a violation of GitHub's terms of service why is Azure/durabletask disabled? ':)
- babhishek21 4mo ago
  +1 on "prefer my queue logic to be in code". The <shape> of my data doesn't change nearly as much as the actions I need to take on it; it doesn't make sense to me why I'd want to do a migration (which is an all or nothing op btw) every time I want to change how I behave with my data. This is also why I absolutely abhorred having to make postgres functions to do anything remotely non-trivial on Supabase. That said, we did hand-build a simple job queue (just lock, poll, reserve on a column, poll and update reservation to mark job done) on top of postgres at my previous startup. Something like pgque would have made that much more polished.
- moomoo11 4mo ago
  same but this could be useful for db level things that are not business logic related. i have always had maintenance packages for this type of stuff. if i could deploy them alongside the database itself that could be kind of cool. but yeah i agree with you that i do prefer having this in the code layer.
- giancarlostoro 4mo ago
  > As an ex-app engineer though, I kind of prefer my queue logic to be in code, in Git, but maybe with the right tooling, you can change my mind. :) I mean, we used to keep our SQL code in git too for projects where we had DB triggers. I think some were even shoved in there via Django migrations just to let someone setup locally and have the triggers available in their local database.
  - jrumbut 4mo ago
    If you have triggers I don't see why you wouldn't put them in a migration. That addresses one of the most problematic aspects of triggers (invisibility, no version tracking, etc) without reducing their usefulness. With some cleverness you could even introduce some testing that way. Not perfect but better than nothing.
  - SoftTalker 4mo ago
    I've never heard of not source controlling stored procedures, functions, triggers, etc. I source-control all my schema objects, never imagined this isn't normal.
- CuriouslyC 4mo ago
  You're not wrong, I've been a big postgres fanboy since version 7, and have tried to build stuff in PG to the greatest degree possible in experiments, and my experience is that at a minimum, the DX/observability isn't there. The multi-master scaling story isn't turnkey or bulletproof either, so I'm hesitant to do any fancy write-bound things that hasten the need to scale the database.
- tmpz22 4mo ago
  Do you thank the OSS community or Claude?
- gitscope_ai 4mo ago
  [flagged]
- nextaccountic 4mo ago
  I like pgmq https://github.com/pgmq/pgmq https://github.com/pgmq/pgmq
- hmaxdml 4mo ago
  A PG-backed queue is in code right after being in PG, and the beauty of a neat durable queue framework is in exposing it conveniently and efficiently.
- hanzeweiasa 4mo ago
  [flagged]
- KaiShips 4mo ago
  [flagged]
faxmeyourcode 4mo ago
This feels like the wrong solution to an age old problem solved by the DAG schedulers like Apache Airflow for a while now. Why would I want to store my control flow in the database and not in code? It feels strange. Not trying to dismiss the project, I'm just not getting it yet I think.
- daxfohl 4mo ago
  Microsoft has their own Durable Task framewor[1] for that kind of stuff, and it supports both running as a self-hosted standalone service like temporal, and running serverless on Azure Functions. It actually predated airflow, temporal, etc., IIRC. This one seems to be more database-specific use case. The advantage is probably that you can track the exact state of the job in the database itself, rather than having to cross-reference the workflow log with the codebase and trace through it line by line to figure out what the state is. Plus I assume it's less overhead and latency, and operationally one less thing to spin up. [1] https://learn.microsoft.com/en-us/azure/durable-task/common/what-is-durable-task https://learn.microsoft.com/en-us/azure/durable-task/common/...
  - affandar 4mo ago
    (Author of both durable task framework and pg_durable/duroxide here) Indeed Durable tasks is an exceptional project and was a unique innovation at the time. pg_durable brings the same reliability and durablity semantics to long running operations within the database. We have tons of interesting scenarios on the roadmap. Stay tuned! :)
    - alex_hirner 4mo ago
      Does ai.backfill() fill incomplete/dirty rows or does pg_durable have some notion of partial completion?
      - abeomor 4mo ago
        Hi there! PM from the PG AI team, working on both pg_durable and the AI pipeline layer. ai.backfill() ignores that row-level state entirely and reprocesses everything from scratch. https://learn.microsoft.com/en-us/azure/horizondb/ai/ai-pipelines#cost-controls https://learn.microsoft.com/en-us/azure/horizondb/ai/ai-pipe... pg_durable answers "did this workflow instance finish, and if it crashed, where do I resume?", completed/running/pending/failed per node + checkpoint replay. https://github.com/microsoft/pg_durable/blob/main/USER_GUIDE.md#durability https://github.com/microsoft/pg_durable/blob/main/USER_GUIDE... If you want this problem addressed better, please add an issue to the open-source repo, we would love to dig in. https://github.com/microsoft/pg_durable/issues https://github.com/microsoft/pg_durable/issues
kilobaud 4mo ago
> When not to use it > … > The workflow mostly lives outside Postgres and spans many heterogeneous systems. How is this project at all comparable to something like Temporal? Am I misunderstanding the limitation implied by this particular recommendation?
- faxmeyourcode 4mo ago
  I aggree - I'm not understanding the value of the project either if you look at the example here https://github.com/microsoft/pg_durable/blob/main/examples/invoice-approval/without-df.md https://github.com/microsoft/pg_durable/blob/main/examples/i... It's an interesting technical achievement I guess, but it's very bizarre to try and read this SELECT df.start( @> ( ($$SELECT ... FROM demo.invoices WHERE status = 'pending'$$ |=> 'inv') ~> df.if_rows('inv', $$UPDATE ... SET status = 'processing'$$ ~> (df.http(...) |=> 'resp') ~> df.if($$SELECT $r.ok$$, -- classify, branch, wait for signal ... ), df.sleep(5) ) ), 'invoice-approval-pipeline' );
  - gdecandia 4mo ago
    Contributor here - at Microsoft we've built AI workflows on pg_durable and seen it substantially reduce code and increase reliability. Agree that the DSL ergonomics can be improved. Our pipelines use a higher level language and therefore simplified, but pg_durable is meant to solve a wider array of problems. We're happy to take suggestions for improvements.
    - faxmeyourcode 4mo ago
      Somebody else in the thread brought up the benefit of snapshotting a database at a point in time stores not only the state of execution but also the code, etc. That is a unique benefit I'd be interested in exploring over storing your orchestration outside of the database. Not trying to dismiss the project - it looks like a lot of hard work has gone in and somebody has a use for it. I just come from an airflow style external orchestrator frame of mind that manages durability state in postgres but keeps the control flow out. Sorry if I came off as a bit snarky
cpursley 4mo ago
Looks pretty good but I wonder why they didn’t build it on pgmq? If you’re on elixir I maintain a DAG package around this (based on and compatible with pgflow.dev which is TS/Deno). https://github.com/agoodway/pgflow https://github.com/agoodway/pgflow
- affandar 4mo ago
  (pg_durable committer here) The provider is an extensibility point. We just shipped the simplest version of it. Happy to take contribs if someone sends a pgmq based provider!
  - cpursley 4mo ago
    Cool! I maintain https://postgresisenough.dev https://postgresisenough.dev, I'd love to get a PR for pg_durable up to include it: https://github.com/agoodway/postgresisenough https://github.com/agoodway/postgresisenough
  - evntdrvn 4mo ago
    When do we get mssql_durable :)
jraedisch 4mo ago
If understanding correctly, Absurd (by the Pi LLM harness devs) minimizes the pure db approach as much as possible. I only just started getting into the topic myself, though. https://github.com/earendil-works/absurd https://github.com/earendil-works/absurd
- snqb 4mo ago
  a nitpick: absurd seems to be an original earendil project they started before Mario Zechner joined earendil, I don't see him in the commits too but I might not know all the details, I'm genuinely curious
  - CuriouslyC 4mo ago
    You could call Armin a Pi dev in all honesty. He has a fair number of commits.
oa335 4mo ago
Can anyone explain why I would want to use this over an orchestration tool that lives outside the DB? Read through the Readme and some of the examples, I still don't get it.
- jpalomaki 4mo ago
  It’s sometimes convenient if database is the only ”stateful” component in architecture. Also if all the "state" is in one database, then you have better chance of getting consistent backups.
- thibaut_barrere 4mo ago
  You can have well-integrated applicative workflows (eg: progress report on a permalink in your front end app), app-restart-proof resumable workflows, and it avoids adding an extra piece of infrastructure. We use Postgres for that on https://transport.data.gouv.fr https://transport.data.gouv.fr (Elixir app which does a fair bit of processing), and it helps. Not familiar yet with pg_durable though, but I have used or implemented similar solutions and can relate.
- rswail 4mo ago
  Snapshot PITR of your database means everything restores including the durable jobs at the PIT. Don't need to synchronize the backups with anything else that is part of the same data store, good for ETL pipelines and other state machine type jobs. If your ETL is mostly SQL anyway, then having the actual job being run on the same server helps as well.
  - regularfry 4mo ago
    Yes, but that doesn't have to imply that the compute part of the durable jobs framework also needs to be part of the database snapshot. You almost certainly want that defined in code anyway, if only to have a sane versioning story. So then by having it also be part of the snapshot, you've now got the problem that there are apparently two sources of truth for that bit of the code.
- gdecandia 4mo ago
rastignack 4mo ago
I hope it could be used in the future to export pg_dump formated exports to s3. One would be able to trigger maintenance jobs via simple lambda functions whose duration is capped.
- gdecandia 4mo ago
  Committer here. I would love to hear more about this scenario. Is the proposal to be able to export pg_dump formatted data on some schedule or trigger, entirely hosted in PostgreSQL and with timeouts? There are already extension that can export to blob/file storage and can be combined with pg_durable or pg_cron, so I assume the challenge is pg_dump compatible data export from SQL running in the database?
mikey_p 4mo ago
Is this an open sourcing of something they use internally? My first thought on durable jobs was GHA aka Azure Devops.
- gdecandia 4mo ago
  Please see https://learn.microsoft.com/en-us/azure/horizondb/ai/ai-pipelines https://learn.microsoft.com/en-us/azure/horizondb/ai/ai-pipe...
  - mikey_p 4mo ago
    Thanks for answering, this makes tons of sense
  - jiggawatts 4mo ago
    These approaches always suffer from the same issues such as synchronous single threaded code that would be trivial to parallelise in a “proper” programming language such as C#. What has Microsoft done to work around this?
redmonduser 4mo ago
Seems like an interesting idea to add durability and resumability to lengthy cron jobs.
TuringNYC 4mo ago
I'm trapped on Azure at work and we're constantly waiting for Azure pg to catch up with modernity. For example, you cant use this: https://www.paradedb.com/blog/hybrid-search-in-postgresql-the-missing-manual https://www.paradedb.com/blog/hybrid-search-in-postgresql-th... Also for example, you dont get ultra-wide high dimensionality vectors. It is nice they are open sourcing pg_durable, but how about adopting table stakes I'd get with AWS?
- abeomor 4mo ago
  Hey! I'm a PM on the Azure PG team and work on AI features on Postgres. Wanted to address your points directly because we actually ship the capabilities you're asking about, we have made ALOT of progress in the last 3-6 months: Hybrid search (BM25 + vector): Worth noting that ParadeDB's pg_search isn't an AWS-native feature either, you'd need to self-host it on EC2. On Azure PostgreSQL, we built pg_textsearch which provides the same BM25 ranking model (term frequency saturation, document-length normalization, IDF) natively. Fun fact, the main contributor of pg_textsearch is now on the Azure Postgres team :) Docs: https://learn.microsoft.com/en-us/azure/horizondb/ai/full-text-search https://learn.microsoft.com/en-us/azure/horizondb/ai/full-te... High-dimensional vectors: This is actually an area where we're ahead. pgvector with HNSW caps at 2,000 dimensions. We support pgvector for vector storage and search, and for high-dimensional / large-scale workloads we ship pg_diskann — Microsoft's graph-based vector index that supports up to 16,000 dimensions and also does advanced in-index filtering (your WHERE clauses get evaluated during graph traversal, so you don't lose recall on selective predicates). pgvector: https://learn.microsoft.com/en-us/azure/horizondb/ai/vector-search-pgvector https://learn.microsoft.com/en-us/azure/horizondb/ai/vector-... DiskANN high-dimension support: https://learn.microsoft.com/en-us/azure/horizondb/ai/vector-index-diskann#support-for-high-dimension-embeddings https://learn.microsoft.com/en-us/azure/horizondb/ai/vector-... These are available today on Azure PostgreSQL, specifically Azure HorizonDB (Preview). Happy to dig into specifics if you have a particular workload in mind.
  - jbonatakis 4mo ago
    > we built pg_textsearch Maybe you meant to word this differently and I’m nitpicking, but didn’t TJ Green build this while he was still at Tiger Data?
joelthelion 4mo ago
Isn't the database already one of the hardest piece of infras to scale? Why would you want to load it with additional long-running jobs?
- greenavocado 4mo ago
  Gotta set up the fall to rake in the dough later with consulting fees
- gdecandia 4mo ago
  Long-running jobs on Postgres are not new at all. See pg_cron for one example. At the end of the day, these workloads would be running anyway against the database, whether triggered by an external component. HTTP queries from the database have also become more popular to avoid round-trips and failure points from additional components in data or AI pipelines. But yes, whether to bring the compute to the data or vice-versa is a design choice that has a lot of contention.
- hmaxdml 4mo ago
  The database is exactly the hardcore piece of engineering that's been designed to scale and be fault tolerant for decades
CharlieDigital 4mo ago
A few things are not clear to me from reading through docs and examples: df.wait_for_schedule() How does this call work? Is it idempotent if I call it from an application? If I run it 2x with the same parameters, does it double tick? Am I invoking this manually from a query console to only do this one time? Am I running this as part of a migration script? For this[0]: -- Wait for human signal (5 minute timeout) ~> (df.wait_for_signal('approval', 300) |=> 'sig') ~> df.if( $$SELECT NOT ($sig::jsonb->>'timed_out')::boolean AND ($sig::jsonb->'data'->>'approved')::boolean$$, Is the `timed_out` a fixed constant that is returned on timeout? Also not immediately clear: how to handle errors/exceptions? [0] https://github.com/microsoft/pg_durable/blob/main/examples/invoice-approval/sql/05_start_workflow.sql https://github.com/microsoft/pg_durable/blob/main/examples/i...
- affandar 4mo ago
  You are creating a durable function and starting its execution at the same time by calling df.start(<durable function definition>). This will you give you back an instance id which represents this durable function execution. You can use this to refer to this execution from this point onwards. Within this durable function you are calling df.wait_for_signal(<signal_name>). This call is exactly once within this function instance. There are no duplicates possible. Your df.start() call might get duplicated if it times out and you re-run it, but in this case it would end up creating a different function instance. Any 'unhandled' errors in executing SQL will fail the function instance. Its status would bubble up the exact error being raised.
steno132 4mo ago
I would argue that for all but the largest tech companies you only need a single data system which is Postgres. Message brokers, analytical databases all can be built on Postgres. Unfortunately, Postgres as it's built now lacks any semblence of extensibility which makes this impossible in practice. I would propose a rewrite of Postgres in another language like Rust, introducing a pluggable application layer on top. While ambitious in scope I think it would be helpful and even necessary.
- reactordev 4mo ago
  you might be happy to note there is such a thing. pgrust.
  - steno132 4mo ago
    This is a great initiative. Postgres was written in the 1980s and we can't afford to have our most utilized workloads running on a software written before most of us even existed. LLMs make it possible to rewrite Postgres and we should take that chance.
    - reactordev 4mo ago
      Not broke don’t fix? Or write your own? Not everything needs a rewrite to rust.
    - sgarland 4mo ago
      It’s always been possible to rewrite it; LLMs just made it possible for people who don’t understand it to do so. I personally don’t see that as a benefit, but you do you. As to the age statement, why on earth does that matter? HAProxy launched in 2001, and despite it being 25 years old, you’ll struggle to find anything faster or more stable in its field. Then there’s, you know, Linux - 1991. I suppose you want to see it rewritten?
    - sbuttgereit 4mo ago
      > Postgres was written in the 1980s This is a pretty poor take. Sure the software that we call "PostgreSQL" started to be developed in the 80's... but they didn't stop there. PostgreSQL has been in continuous development, including improvements, changes, and additions, and by some very smart people at that. It's not static and as long as I've been a professional user of the database, decades, it has continually evolved and in some cases even led the way. If we were to survey the software, wouldn't you at least be interested to know how much of code base actually dates back to those long ago decades and how much is more modern before making such statement? It would be a mistake to take what PostgreSQL actually offers: an excellent database that has be continuously developed and updated over many years (i.e. "maturity"), for some arbitrary idea and evidently baseless idea that somehow "new" must be better. If new is better, say why; and do so with more actually true statements than it's not extensible. Want it in rust? Well, OK, sure you can give hand-wavy reasons about security and such for why that might be beneficial; but if you want to be convincing you need to be much more specific about the problem in PostgreSQL and the specific way in which your recommendation actually and convincingly moves the needle. If you can't do that, you're simply giving us an emotional outpouring rather than a rational one.
linuxhiker 4mo ago
Hopefully they will start sponsoring PGRX now that they are so publicly using it.
7373737373 4mo ago
Feels like perhaps yet another https://en.wikipedia.org/wiki/Inner-platform_effect https://en.wikipedia.org/wiki/Inner-platform_effect that would be unnecessary if popular programming languages/virtual machines already supported determinism, metered and controllable stepwise execution and runtime state suspension, (de)serialization and resumption?
ijustlovemath 4mo ago
We made a very functional job queue in Postgres with PostgREST. highly recommend, as the automatic REST API makes building new clients a breeze
junto 4mo ago
This smells like stored procedures. You can’t unit test it. You can’t version it. Business logic in the database, (hidden brain problem), harder to isolate noisy workloads, no observability, scaling pressure lands solely in Postgres, lack of IO, especially API calls. Good for local database only jobs though. Niche use cases.
- dpark 4mo ago
  > This smells like stored procedures. You can’t unit test it. You can’t version it Say what? Stored procedures are awesome when used correctly. Versioning is straightforward. You stick any sort of monotonically increasing id at the end of the name. Whenever you need a breaking change, you bump the id. You also leave the old version with the old id, retiring it only after it’s no longer used. You do need a real story for DB upgrades for this to work well. If your story is that someone on the team executes some random SQL migration as root, you’re gonna have a bad time. You can unit test stored procedures in exactly the same way you could test any other SQL. You have to spin up a DB to do it. But if you can’t test your stored procedures, you’re admitting you have no way to test your SQL which is your real problem. > Business logic in the database, (hidden brain problem) Ok? How much you shove into your stored procedures is up to you. In my experience the real alternative to stored procedures is not zero business logic in the DB. It’s SQL code sprinkled throughout the codebase, where it’s harder to test, poorly versioned, and poorly encapsulated. And also often needlessly slow. > harder to isolate noisy workloads Dunno what this means > no observability Maybe some truth here. It is more work to inspect issues in SQL than most programming languages. > scaling pressure lands solely in Postgres, lack of IO, especially API calls. If stored procedures are causing IO problems and scaling issues then you are using them wrong. Stored procedures often drastically reduce IO when used correctly and thereby improve scalability.
  - otterley 4mo ago
    Why would using a stored procedure reduce I/O? I can see it reducing network round trips, but not storage reads and writes.
    - 4mo ago
fragmede 4mo ago
What's the one GitHub uses? Because I may not be GitHub scale, but it seems to be having problems.
sathyayoshi 4mo ago
[flagged]
efitz 4mo ago
My only concern is that AI agents won’t be good at this. For better or worse, they “understand” and have seen a lot of message queuing code and read lots of message queue support discussions.
- advertum 4mo ago
  Agreed, but I think the hard part is the syntax, not the idea. The concept is simple. The way the SQL is written here is unusual, and since there is little training data on it, a model will likely fall back on a more common approach it has seen before.
lxdlam 4mo ago
Durable execution was one of my most worth investing techniques 2024, and glad to see it's blooming in 2026. The idea behind it is pretty simple: persistent state machines, auto or semi-auto context capture, and a run engine, but it actually solves many common headaches like exactly once execution with retry, signal based workflow and so on.
eddysir 4mo ago
[flagged]
viveknathani_ 4mo ago
adoption of pgrx, durable execution becoming popular - good things! but not a fan of keeping complex flows inside the DB
sathyayoshi 4mo ago
[flagged]
ryanshrott 4mo ago
[dead]
alik75 4mo ago
[flagged]

474 points by coffeemug 4mo ago

levkk 4mo ago

2026 is the year of the Postgres queue! (DBOS[0], pgQue[1]) It's awesome that the community is contributing this and giving us the option to use it. As an ex-app engineer though, I kind of prefer my queue logic to be in code, in Git, but maybe with the right tooling, you can change my mind. :) [0]: https://www.dbos.dev/ https://www.dbos.dev/ [1]: https://github.com/NikolayS/pgque https://github.com/NikolayS/pgque

dietr1ch 4mo ago
Yeah, it's harder to work on, or maybe just different, but I guess the docs, info(searchable docs, posts, experience), and tooling are lacking. What's the story for version control, debugging, testing, releasing? It'd be cool to have everything together for data locality and simplifying the stack, but it feels you'd lose a lot of useful knowledge about how to do stuff "properly".
- gdecandia 4mo ago
  Contributor here. Good points, we do need to develop some best practices around managing function versioning and lifecycle for pg_durable. https://github.com/microsoft/duroxide https://github.com/microsoft/duroxide - also OSS, the durable execution framework pg_durable is built on itself supports function versions. We can leverage that to get similar support in pg_durable.
  - vault 4mo ago
    > Access to this repository has been disabled by GitHub Staff due to a violation of GitHub's terms of service why is Azure/durabletask disabled? ':)
babhishek21 4mo ago
+1 on "prefer my queue logic to be in code". The <shape> of my data doesn't change nearly as much as the actions I need to take on it; it doesn't make sense to me why I'd want to do a migration (which is an all or nothing op btw) every time I want to change how I behave with my data. This is also why I absolutely abhorred having to make postgres functions to do anything remotely non-trivial on Supabase. That said, we did hand-build a simple job queue (just lock, poll, reserve on a column, poll and update reservation to mark job done) on top of postgres at my previous startup. Something like pgque would have made that much more polished.
moomoo11 4mo ago
same but this could be useful for db level things that are not business logic related. i have always had maintenance packages for this type of stuff. if i could deploy them alongside the database itself that could be kind of cool. but yeah i agree with you that i do prefer having this in the code layer.
giancarlostoro 4mo ago
> As an ex-app engineer though, I kind of prefer my queue logic to be in code, in Git, but maybe with the right tooling, you can change my mind. :) I mean, we used to keep our SQL code in git too for projects where we had DB triggers. I think some were even shoved in there via Django migrations just to let someone setup locally and have the triggers available in their local database.
- jrumbut 4mo ago
  If you have triggers I don't see why you wouldn't put them in a migration. That addresses one of the most problematic aspects of triggers (invisibility, no version tracking, etc) without reducing their usefulness. With some cleverness you could even introduce some testing that way. Not perfect but better than nothing.
- SoftTalker 4mo ago
  I've never heard of not source controlling stored procedures, functions, triggers, etc. I source-control all my schema objects, never imagined this isn't normal.
CuriouslyC 4mo ago
You're not wrong, I've been a big postgres fanboy since version 7, and have tried to build stuff in PG to the greatest degree possible in experiments, and my experience is that at a minimum, the DX/observability isn't there. The multi-master scaling story isn't turnkey or bulletproof either, so I'm hesitant to do any fancy write-bound things that hasten the need to scale the database.
tmpz22 4mo ago
Do you thank the OSS community or Claude?
gitscope_ai 4mo ago
[flagged]
nextaccountic 4mo ago
I like pgmq https://github.com/pgmq/pgmq https://github.com/pgmq/pgmq
hmaxdml 4mo ago
A PG-backed queue is in code right after being in PG, and the beauty of a neat durable queue framework is in exposing it conveniently and efficiently.
hanzeweiasa 4mo ago
[flagged]
KaiShips 4mo ago
[flagged]

faxmeyourcode 4mo ago

This feels like the wrong solution to an age old problem solved by the DAG schedulers like Apache Airflow for a while now. Why would I want to store my control flow in the database and not in code? It feels strange. Not trying to dismiss the project, I'm just not getting it yet I think.

daxfohl 4mo ago
Microsoft has their own Durable Task framewor[1] for that kind of stuff, and it supports both running as a self-hosted standalone service like temporal, and running serverless on Azure Functions. It actually predated airflow, temporal, etc., IIRC. This one seems to be more database-specific use case. The advantage is probably that you can track the exact state of the job in the database itself, rather than having to cross-reference the workflow log with the codebase and trace through it line by line to figure out what the state is. Plus I assume it's less overhead and latency, and operationally one less thing to spin up. [1] https://learn.microsoft.com/en-us/azure/durable-task/common/what-is-durable-task https://learn.microsoft.com/en-us/azure/durable-task/common/...
- affandar 4mo ago
  (Author of both durable task framework and pg_durable/duroxide here) Indeed Durable tasks is an exceptional project and was a unique innovation at the time. pg_durable brings the same reliability and durablity semantics to long running operations within the database. We have tons of interesting scenarios on the roadmap. Stay tuned! :)
  - alex_hirner 4mo ago
    Does ai.backfill() fill incomplete/dirty rows or does pg_durable have some notion of partial completion?
    - abeomor 4mo ago
      Hi there! PM from the PG AI team, working on both pg_durable and the AI pipeline layer. ai.backfill() ignores that row-level state entirely and reprocesses everything from scratch. https://learn.microsoft.com/en-us/azure/horizondb/ai/ai-pipelines#cost-controls https://learn.microsoft.com/en-us/azure/horizondb/ai/ai-pipe... pg_durable answers "did this workflow instance finish, and if it crashed, where do I resume?", completed/running/pending/failed per node + checkpoint replay. https://github.com/microsoft/pg_durable/blob/main/USER_GUIDE.md#durability https://github.com/microsoft/pg_durable/blob/main/USER_GUIDE... If you want this problem addressed better, please add an issue to the open-source repo, we would love to dig in. https://github.com/microsoft/pg_durable/issues https://github.com/microsoft/pg_durable/issues

kilobaud 4mo ago

> When not to use it > … > The workflow mostly lives outside Postgres and spans many heterogeneous systems. How is this project at all comparable to something like Temporal? Am I misunderstanding the limitation implied by this particular recommendation?

faxmeyourcode 4mo ago
I aggree - I'm not understanding the value of the project either if you look at the example here https://github.com/microsoft/pg_durable/blob/main/examples/invoice-approval/without-df.md https://github.com/microsoft/pg_durable/blob/main/examples/i... It's an interesting technical achievement I guess, but it's very bizarre to try and read this SELECT df.start( @> ( ($$SELECT ... FROM demo.invoices WHERE status = 'pending'$$ |=> 'inv') ~> df.if_rows('inv', $$UPDATE ... SET status = 'processing'$$ ~> (df.http(...) |=> 'resp') ~> df.if($$SELECT $r.ok$$, -- classify, branch, wait for signal ... ), df.sleep(5) ) ), 'invoice-approval-pipeline' );
- gdecandia 4mo ago
  Contributor here - at Microsoft we've built AI workflows on pg_durable and seen it substantially reduce code and increase reliability. Agree that the DSL ergonomics can be improved. Our pipelines use a higher level language and therefore simplified, but pg_durable is meant to solve a wider array of problems. We're happy to take suggestions for improvements.
  - faxmeyourcode 4mo ago
    Somebody else in the thread brought up the benefit of snapshotting a database at a point in time stores not only the state of execution but also the code, etc. That is a unique benefit I'd be interested in exploring over storing your orchestration outside of the database. Not trying to dismiss the project - it looks like a lot of hard work has gone in and somebody has a use for it. I just come from an airflow style external orchestrator frame of mind that manages durability state in postgres but keeps the control flow out. Sorry if I came off as a bit snarky

cpursley 4mo ago

Looks pretty good but I wonder why they didn’t build it on pgmq? If you’re on elixir I maintain a DAG package around this (based on and compatible with pgflow.dev which is TS/Deno). https://github.com/agoodway/pgflow https://github.com/agoodway/pgflow

affandar 4mo ago
(pg_durable committer here) The provider is an extensibility point. We just shipped the simplest version of it. Happy to take contribs if someone sends a pgmq based provider!
- cpursley 4mo ago
  Cool! I maintain https://postgresisenough.dev https://postgresisenough.dev, I'd love to get a PR for pg_durable up to include it: https://github.com/agoodway/postgresisenough https://github.com/agoodway/postgresisenough
- evntdrvn 4mo ago
  When do we get mssql_durable :)

jraedisch 4mo ago

If understanding correctly, Absurd (by the Pi LLM harness devs) minimizes the pure db approach as much as possible. I only just started getting into the topic myself, though. https://github.com/earendil-works/absurd https://github.com/earendil-works/absurd

snqb 4mo ago
a nitpick: absurd seems to be an original earendil project they started before Mario Zechner joined earendil, I don't see him in the commits too but I might not know all the details, I'm genuinely curious
- CuriouslyC 4mo ago
  You could call Armin a Pi dev in all honesty. He has a fair number of commits.

oa335 4mo ago

Can anyone explain why I would want to use this over an orchestration tool that lives outside the DB? Read through the Readme and some of the examples, I still don't get it.

jpalomaki 4mo ago
It’s sometimes convenient if database is the only ”stateful” component in architecture. Also if all the "state" is in one database, then you have better chance of getting consistent backups.
thibaut_barrere 4mo ago
You can have well-integrated applicative workflows (eg: progress report on a permalink in your front end app), app-restart-proof resumable workflows, and it avoids adding an extra piece of infrastructure. We use Postgres for that on https://transport.data.gouv.fr https://transport.data.gouv.fr (Elixir app which does a fair bit of processing), and it helps. Not familiar yet with pg_durable though, but I have used or implemented similar solutions and can relate.
rswail 4mo ago
Snapshot PITR of your database means everything restores including the durable jobs at the PIT. Don't need to synchronize the backups with anything else that is part of the same data store, good for ETL pipelines and other state machine type jobs. If your ETL is mostly SQL anyway, then having the actual job being run on the same server helps as well.
- regularfry 4mo ago
  Yes, but that doesn't have to imply that the compute part of the durable jobs framework also needs to be part of the database snapshot. You almost certainly want that defined in code anyway, if only to have a sane versioning story. So then by having it also be part of the snapshot, you've now got the problem that there are apparently two sources of truth for that bit of the code.
gdecandia 4mo ago

rastignack 4mo ago

I hope it could be used in the future to export pg_dump formated exports to s3. One would be able to trigger maintenance jobs via simple lambda functions whose duration is capped.

gdecandia 4mo ago
Committer here. I would love to hear more about this scenario. Is the proposal to be able to export pg_dump formatted data on some schedule or trigger, entirely hosted in PostgreSQL and with timeouts? There are already extension that can export to blob/file storage and can be combined with pg_durable or pg_cron, so I assume the challenge is pg_dump compatible data export from SQL running in the database?

mikey_p 4mo ago

Is this an open sourcing of something they use internally? My first thought on durable jobs was GHA aka Azure Devops.

gdecandia 4mo ago
Please see https://learn.microsoft.com/en-us/azure/horizondb/ai/ai-pipelines https://learn.microsoft.com/en-us/azure/horizondb/ai/ai-pipe...
- mikey_p 4mo ago
  Thanks for answering, this makes tons of sense
- jiggawatts 4mo ago
  These approaches always suffer from the same issues such as synchronous single threaded code that would be trivial to parallelise in a “proper” programming language such as C#. What has Microsoft done to work around this?

redmonduser 4mo ago

Seems like an interesting idea to add durability and resumability to lengthy cron jobs.

TuringNYC 4mo ago

I'm trapped on Azure at work and we're constantly waiting for Azure pg to catch up with modernity. For example, you cant use this: https://www.paradedb.com/blog/hybrid-search-in-postgresql-the-missing-manual https://www.paradedb.com/blog/hybrid-search-in-postgresql-th... Also for example, you dont get ultra-wide high dimensionality vectors. It is nice they are open sourcing pg_durable, but how about adopting table stakes I'd get with AWS?

abeomor 4mo ago
Hey! I'm a PM on the Azure PG team and work on AI features on Postgres. Wanted to address your points directly because we actually ship the capabilities you're asking about, we have made ALOT of progress in the last 3-6 months: Hybrid search (BM25 + vector): Worth noting that ParadeDB's pg_search isn't an AWS-native feature either, you'd need to self-host it on EC2. On Azure PostgreSQL, we built pg_textsearch which provides the same BM25 ranking model (term frequency saturation, document-length normalization, IDF) natively. Fun fact, the main contributor of pg_textsearch is now on the Azure Postgres team :) Docs: https://learn.microsoft.com/en-us/azure/horizondb/ai/full-text-search https://learn.microsoft.com/en-us/azure/horizondb/ai/full-te... High-dimensional vectors: This is actually an area where we're ahead. pgvector with HNSW caps at 2,000 dimensions. We support pgvector for vector storage and search, and for high-dimensional / large-scale workloads we ship pg_diskann — Microsoft's graph-based vector index that supports up to 16,000 dimensions and also does advanced in-index filtering (your WHERE clauses get evaluated during graph traversal, so you don't lose recall on selective predicates). pgvector: https://learn.microsoft.com/en-us/azure/horizondb/ai/vector-search-pgvector https://learn.microsoft.com/en-us/azure/horizondb/ai/vector-... DiskANN high-dimension support: https://learn.microsoft.com/en-us/azure/horizondb/ai/vector-index-diskann#support-for-high-dimension-embeddings https://learn.microsoft.com/en-us/azure/horizondb/ai/vector-... These are available today on Azure PostgreSQL, specifically Azure HorizonDB (Preview). Happy to dig into specifics if you have a particular workload in mind.
- jbonatakis 4mo ago
  > we built pg_textsearch Maybe you meant to word this differently and I’m nitpicking, but didn’t TJ Green build this while he was still at Tiger Data?

joelthelion 4mo ago

Isn't the database already one of the hardest piece of infras to scale? Why would you want to load it with additional long-running jobs?

greenavocado 4mo ago
Gotta set up the fall to rake in the dough later with consulting fees
gdecandia 4mo ago
Long-running jobs on Postgres are not new at all. See pg_cron for one example. At the end of the day, these workloads would be running anyway against the database, whether triggered by an external component. HTTP queries from the database have also become more popular to avoid round-trips and failure points from additional components in data or AI pipelines. But yes, whether to bring the compute to the data or vice-versa is a design choice that has a lot of contention.
hmaxdml 4mo ago
The database is exactly the hardcore piece of engineering that's been designed to scale and be fault tolerant for decades

CharlieDigital 4mo ago

A few things are not clear to me from reading through docs and examples: df.wait_for_schedule() How does this call work? Is it idempotent if I call it from an application? If I run it 2x with the same parameters, does it double tick? Am I invoking this manually from a query console to only do this one time? Am I running this as part of a migration script? For this[0]: -- Wait for human signal (5 minute timeout) ~> (df.wait_for_signal('approval', 300) |=> 'sig') ~> df.if( $$SELECT NOT ($sig::jsonb->>'timed_out')::boolean AND ($sig::jsonb->'data'->>'approved')::boolean$$, Is the `timed_out` a fixed constant that is returned on timeout? Also not immediately clear: how to handle errors/exceptions? [0] https://github.com/microsoft/pg_durable/blob/main/examples/invoice-approval/sql/05_start_workflow.sql https://github.com/microsoft/pg_durable/blob/main/examples/i...

affandar 4mo ago
You are creating a durable function and starting its execution at the same time by calling df.start(<durable function definition>). This will you give you back an instance id which represents this durable function execution. You can use this to refer to this execution from this point onwards. Within this durable function you are calling df.wait_for_signal(<signal_name>). This call is exactly once within this function instance. There are no duplicates possible. Your df.start() call might get duplicated if it times out and you re-run it, but in this case it would end up creating a different function instance. Any 'unhandled' errors in executing SQL will fail the function instance. Its status would bubble up the exact error being raised.

steno132 4mo ago

I would argue that for all but the largest tech companies you only need a single data system which is Postgres. Message brokers, analytical databases all can be built on Postgres. Unfortunately, Postgres as it's built now lacks any semblence of extensibility which makes this impossible in practice. I would propose a rewrite of Postgres in another language like Rust, introducing a pluggable application layer on top. While ambitious in scope I think it would be helpful and even necessary.

reactordev 4mo ago
you might be happy to note there is such a thing. pgrust.
- steno132 4mo ago
  This is a great initiative. Postgres was written in the 1980s and we can't afford to have our most utilized workloads running on a software written before most of us even existed. LLMs make it possible to rewrite Postgres and we should take that chance.
  - reactordev 4mo ago
    Not broke don’t fix? Or write your own? Not everything needs a rewrite to rust.
  - sgarland 4mo ago
    It’s always been possible to rewrite it; LLMs just made it possible for people who don’t understand it to do so. I personally don’t see that as a benefit, but you do you. As to the age statement, why on earth does that matter? HAProxy launched in 2001, and despite it being 25 years old, you’ll struggle to find anything faster or more stable in its field. Then there’s, you know, Linux - 1991. I suppose you want to see it rewritten?
  - sbuttgereit 4mo ago
    > Postgres was written in the 1980s This is a pretty poor take. Sure the software that we call "PostgreSQL" started to be developed in the 80's... but they didn't stop there. PostgreSQL has been in continuous development, including improvements, changes, and additions, and by some very smart people at that. It's not static and as long as I've been a professional user of the database, decades, it has continually evolved and in some cases even led the way. If we were to survey the software, wouldn't you at least be interested to know how much of code base actually dates back to those long ago decades and how much is more modern before making such statement? It would be a mistake to take what PostgreSQL actually offers: an excellent database that has be continuously developed and updated over many years (i.e. "maturity"), for some arbitrary idea and evidently baseless idea that somehow "new" must be better. If new is better, say why; and do so with more actually true statements than it's not extensible. Want it in rust? Well, OK, sure you can give hand-wavy reasons about security and such for why that might be beneficial; but if you want to be convincing you need to be much more specific about the problem in PostgreSQL and the specific way in which your recommendation actually and convincingly moves the needle. If you can't do that, you're simply giving us an emotional outpouring rather than a rational one.

linuxhiker 4mo ago

Hopefully they will start sponsoring PGRX now that they are so publicly using it.

7373737373 4mo ago

Feels like perhaps yet another https://en.wikipedia.org/wiki/Inner-platform_effect https://en.wikipedia.org/wiki/Inner-platform_effect that would be unnecessary if popular programming languages/virtual machines already supported determinism, metered and controllable stepwise execution and runtime state suspension, (de)serialization and resumption?

ijustlovemath 4mo ago

We made a very functional job queue in Postgres with PostgREST. highly recommend, as the automatic REST API makes building new clients a breeze

junto 4mo ago

This smells like stored procedures. You can’t unit test it. You can’t version it. Business logic in the database, (hidden brain problem), harder to isolate noisy workloads, no observability, scaling pressure lands solely in Postgres, lack of IO, especially API calls. Good for local database only jobs though. Niche use cases.

dpark 4mo ago
> This smells like stored procedures. You can’t unit test it. You can’t version it Say what? Stored procedures are awesome when used correctly. Versioning is straightforward. You stick any sort of monotonically increasing id at the end of the name. Whenever you need a breaking change, you bump the id. You also leave the old version with the old id, retiring it only after it’s no longer used. You do need a real story for DB upgrades for this to work well. If your story is that someone on the team executes some random SQL migration as root, you’re gonna have a bad time. You can unit test stored procedures in exactly the same way you could test any other SQL. You have to spin up a DB to do it. But if you can’t test your stored procedures, you’re admitting you have no way to test your SQL which is your real problem. > Business logic in the database, (hidden brain problem) Ok? How much you shove into your stored procedures is up to you. In my experience the real alternative to stored procedures is not zero business logic in the DB. It’s SQL code sprinkled throughout the codebase, where it’s harder to test, poorly versioned, and poorly encapsulated. And also often needlessly slow. > harder to isolate noisy workloads Dunno what this means > no observability Maybe some truth here. It is more work to inspect issues in SQL than most programming languages. > scaling pressure lands solely in Postgres, lack of IO, especially API calls. If stored procedures are causing IO problems and scaling issues then you are using them wrong. Stored procedures often drastically reduce IO when used correctly and thereby improve scalability.
- otterley 4mo ago
  Why would using a stored procedure reduce I/O? I can see it reducing network round trips, but not storage reads and writes.
  - 4mo ago

fragmede 4mo ago

What's the one GitHub uses? Because I may not be GitHub scale, but it seems to be having problems.

sathyayoshi 4mo ago

[flagged]

efitz 4mo ago

My only concern is that AI agents won’t be good at this. For better or worse, they “understand” and have seen a lot of message queuing code and read lots of message queue support discussions.

advertum 4mo ago
Agreed, but I think the hard part is the syntax, not the idea. The concept is simple. The way the SQL is written here is unusual, and since there is little training data on it, a model will likely fall back on a more common approach it has seen before.

lxdlam 4mo ago

Durable execution was one of my most worth investing techniques 2024, and glad to see it's blooming in 2026. The idea behind it is pretty simple: persistent state machines, auto or semi-auto context capture, and a run engine, but it actually solves many common headaches like exactly once execution with retry, signal based workflow and so on.

eddysir 4mo ago

[flagged]

viveknathani_ 4mo ago

adoption of pgrx, durable execution becoming popular - good things! but not a fan of keeping complex flows inside the DB

sathyayoshi 4mo ago

[flagged]

ryanshrott 4mo ago

[dead]

alik75 4mo ago

[flagged]