Hacker News

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".

+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.

I like pgmq https://github.com/pgmq/pgmq

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.

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.

> 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.

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.

Do you thank the OSS community or Claude?

[flagged]

[flagged]

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

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/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'
    );

I guess it depends on whether you want to write application code with the Temporal SDK or use this new SQL soup. I’d rather stay out of messy SQL land for something like this if I can avoid it, but I can see the value if you already have Postgres and don’t want to introduce another component.

ParadeDB is AGPL so not generally available on the hyperscalars. However, you can use https://github.com/timescale/pg_textsearch on Azure HorizonDB (and likely soon Flex). Disclosure: I'm the pg_textsearch maintainer and now at Azure.

I didn't quite follow your comment about vector support, are you asking for something beyond what pgvector + diskann provide (both available on Azure)?

Wouldn't Azure Cosmos DB be better suited for vector searches?

I'm sorry, I'm sure you've considered this, but why couldn't you create a bare VM with Postgres vCurrent installed?

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-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.

> 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.

Good databases like Oracle and SQL Server have great IDEs for stored procedures development.

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.

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/...

For one, Airflow (or anything external, for that matter) has no insight into DB load, so when devs slam 200 concurrent workers at the DB, other workloads may be impacted. In contrast, this could (I don’t think it does at this time) get near realtime feedback on performance without the RTT cost, and adjust itself accordingly.

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.

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.

Contributor here. At Microsoft, our Postgres customers seem to split pretty evenly into 2 camps, those that want to do as much as they can in the database, and those that agree with your take - want to keep apps and compute outside the DB.

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.

Because you likely already have a database and likely don't need to bring on an entire new distributed system to orchestrate your workflows.

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 (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.

[dead]

The database is exactly the hardcore piece of engineering that's been designed to scale and be fault tolerant for decades

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.

Gotta set up the fall to rake in the dough later with consulting fees

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.

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?

Please see https://learn.microsoft.com/en-us/azure/horizondb/ai/ai-pipe...

(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!

There are 100+ popular extensions around Postgres. They have dependencies on the internal data structures of Postgres. If someone spends the time to rewrite Postgres on Rust and it doesn't support these extensions off the bat, then its DOA.

> Postgres as it's built now lacks any semblence of extensibility

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 am afraid you don't understand Postgres very well.

you might be happy to note there is such a thing.

pgrust.