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. :)
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.
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".
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.
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.
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.
With some cleverness you could even introduce some testing that way. Not perfect but better than nothing.
For example, you cant use this: 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?
I didn't quite follow your comment about vector support, are you asking for something beyond what pgvector + diskann provide (both available on Azure)?
Fyi, we are in discussion with some hyperscalers on making this possible.
You dont support ultra-wide vectors from the largest embeddings models. We have to wierd stuff like chop up vectors across fields.
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-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-...
DiskANN high-dimension support: 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.
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?
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.
Also if all the "state" is in one database, then you have better chance of getting consistent backups.
We use Postgres for that on 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.
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.
PostGIS. pgvector. TimescaleDB. Citus. pg_cron. pgmq. Apache AGE. ParadeDB. hstore. plv8. postgres_fdw. pg_partman. pg_stat_statements...
The extension API is the thing making your thesis possible. Rewriting it away would mean deleting the exact feature you're asking for.
I won't be running that, though.
pgrust.
How is this project at all comparable to something like Temporal? Am I misunderstanding the limitation implied by this particular recommendation?
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'
);The steps are:
1. Get all the pending invoices
2. Set their state to "processing"
3. Call out to an external service/process to do the actual processing, wait for a response.
4. If the response is OK, do something
5. Wait 5 seconds and then start again.
Not sure I love the syntax and the way SQL is embedded between the $$
But it is in the database, can be updated and modified in the same way as all the other stored procedures/functions, allows job control, I assume other control structures for parallel steps etc.
Gonna go read the doco now.
Good for local database only jobs though. Niche use cases.
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.
You can certainly unit test them, good databases have telemetry and metrics.
Version control is no different from using containers instead of VMs.
Any database change goes through CI/CD pipelines and regular devs cannot edit code directly on the DB.
In fact the biggest issue with databases is like debugging, some devs rather not learn how to use them properly.
In one they never go beyond printf, in the other, they only know what an ORM looks like, and the command line applications for basic SELECTs.
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.
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/...
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! :)
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!
What has Microsoft done to work around this?
but I might not know all the details, I'm genuinely curious
df.wait_for_schedule()
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$$,
Also not immediately clear: how to handle errors/exceptions?
[0] https://github.com/microsoft/pg_durable/blob/main/examples/i...
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.
For better or worse, they “understand” and have seen a lot of message queuing code and read lots of message queue support discussions.
One would be able to trigger maintenance jobs via simple lambda functions whose duration is capped.
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?