Postgres as queue
leontrolski.github.io
leontrolski.github.io
For me, the best features are:
* use LISTEN to be notified of rows that have changed that the backend needs to take action on (so you're not actively polling for new work)
* use NOTIFY from a trigger so all you need to do is INSERT/UPDATE a table to send an event to listeners
* you can select using SKIP LOCKED (as the article points out)
* you can use partial indexes to efficiently select rows in a particular state
* LISTEN for changes to the active working set it cares about
* "select all things in status 'X'" (using a partial index predicate, so it's not churning through low cardinality 'active' statuses)
* atomically update the status to 'processing' (using SKIP LOCKED to avoid contention/lock escalation)
* do the work
* update to a new status (which another worker may trigger on)
This works for millions of units of work an hour with a moderately spec'd database server, and if the alternative is setting up SQS/SNS/ActiveMQ/etc and then _still_ having to track status in the database/manage a dead-letter-queue, etc -- it's not a hard choice at all.
If I have say 50 workers polling the db, either it’s quiet and there's no tasks to do - in which case I don't particularly care about the polling load. Or, it's busy and when they query for work, there's always a task ready to process - in this case the LISTEN is constantly pinging, which is equivalent to constantly polling and finding work.
Regardless, is there a resource (blog or otherwise) you'd reccomend for integrating LISTEN with the backend?
Another factor is polling frequency and processing latency. All things equal, the delay from when a new task lands in a table to the time a backend is working on it should be as small as possible. Single digit milliseconds, ideally.
A NOTIFY event is sent from the server-side as the transaction commits, and you can have a thread blocking waiting on that message to process it as soon as it arrives on the worker side.
So with NOTIFY you reduce polling load and also reduce latency. The only time you need to actually query for tasks is to take over any expired leases, and since there is a 'lease_expire' column you know when that's going to happen so you don't have to continually check in.
As far as documentation, I got a simple java LISTEN/NOTIFY implementation working initially (2013?-ish) just from the excellent postgres docs: https://www.postgresql.org/docs/current/sql-notify.html
Say you have a `thing` table, and backend workers that know how to process a `thing` in status 'new', put it in status 'pending' while it's being worked on, and when it's done put it in status 'active'.
The only thing the backend needs to know is "thing id:7 is now in status:'new'", and it knows what to do from there.
The way I generally build the backends, the first thing they do is LISTEN to the relevant channels they care about, then they can query/build whatever understanding they need for the current state. If the connection drops for whatever reason, you have to start from scratch with the new connection (LISTEN, rebuild state, etc).
JSONB fields in Postgres are pretty awesome for this. You can query the JSON fields, index them, and all that.
Is that what you mean?
> * use NOTIFY from a trigger so all you need to do is INSERT/UPDATE a table to send an event to listeners
Could you explain how that is better than just setting up Event Notifications inside a trigger in SQL Server? Or for that matter just using the Event Notifications system as a queue.
https://learn.microsoft.com/en-us/sql/relational-databases/s...
> * you can select using SKIP LOCKED (as the article points out)
SQL Server can do that as well, using the READPAST table hint.
https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-tr...
> * you can use partial indexes to efficiently select rows in a particular state
SQL Server has filtered indexes, are those not the same?
https://learn.microsoft.com/en-us/sql/relational-databases/i...
Agree on READPAST being similar to SKIP_LOCKED, and filtered indexes are equivalent to partial indexes (I remember filtered indexes being in SQL Server 2008 when I used it).
Reading through the docs on Event Notifications they seem to be a little heavier and have different deliver semantics. Correct me if I'm wrong, but Event Notifications seem to be more similar to a consumable queue (where a consumer calling RECEIVE removes events in the queue), whereas LISTEN/NOTIFY is more pubsub, where every client LISTENing to a channel gets every NOTIFY message.
Why? The article wasn't about that. Hear me out here, but there's value in having multiple implementations for the same idea.
https://learn.microsoft.com/en-us/sql/database-engine/config...
I haven’t had the opportunity to use it in production yet - but it’s worth keeping in mind.
I’ve helped fix poor attempts of “table as queue” before - once you get the locking hints right, polling performs well enough for small volumes - from your list above, the only thing I can’t recall there being in sql server is a LISTEN - but I’m not really an expert on it.
The learning curve is steep and there are some easy anti-patterns you can fall into. Once you grok it, though, it really is very good.
The LISTEN functionality is absolutely there. Your activation procedure is invoked by the server upon receipt of records into the queue. It's very slick. No polling at all.
https://learn.microsoft.com/en-us/azure/azure-functions/func...
I have a visible_at field which indicates when the "message" will show up in checkout commands. When checked out or during a heartbeat from the worker this gets bumped up by a certain amount of time.
When a message is checked out, or re-checked out, a key(GUID) is generated and assigned. To delete the message this key must match.
A message can be checked out if it exists and the visible_at field is older or equal to NOW.
That's about it for semantics. Any further complexity, such as workflows and states, are modeled in higher level services.
If I felt it mattered for perf and was worth the effort I might model this in a more append-only fashion taking advantage of HOT updates and etc. Maybe partition the table by day and drop partitions older than longest supported process. Use the sparse index to indicate deleted.. Hard to say though with SSDs, HOT, and the new btree anti-split features..
If I get millions of users I’ll swap it out, in the meantime it took like a day to implement and I haven’t looked back.
Anyhow, in my case, PostgreSQL is there just there to persist task state, and the database does very little magic. In fact, I also have a SQLite-backed implementation that allows service unit tests to be fast. Most of the work around managing such queue is in-process (and in Rust) because I wanted to keep deployments and operations simple. If interested, I wrote this thing a few months ago on how it works: https://jmmv.dev/2023/06/iii-iv-task-queue.html
“Postgres Is Enough”: https://news.ycombinator.com/item?id=39273954
Queue section in the gist: https://gist.github.com/cpursley/c8fb81fe8a7e5df038158bdfe0f...
There's no 'magic' to it, it uses existing Postgres features so all the performance and consistency guarantees of Postgres are to be expected. Easily gets to 10k+ concurrent reads and writes even on smaller sized Postgres instances, which is more than most applications need.
ehhhhhhhh, i don’t know about that. different strokes and all of that, but TF describing infrequently changing resources requires next to no maintenance. i’m not sure i under what they mean by “risk” in this context though.
if you don’t know $technology well then any thing you do with it is likely to be worse/riskier than using the thing that you do know.
> The need for expertise in anything beyond Python + Postgres.
this should be the first point because it explains 99% of the motivation for all of this.
Changing a source file line and shipping a new version of the app is much easier.
They are not really comparable things at all. Even though we moved to infrastructure as code, that does not mean that the infra code is like functional application code.
Come to think of it, I guess it was a damn good lie to sell infrastructure as code as so easy it's like shipping a new app version when it's everything but..
I feel so bad for the poor engineers that will believe this crap and later regret it. If you're going to advocate doing something like this, you should be forced to be honest and explain all the reasons it's bad idea. The fact is that people are lying about the problems of using Postgres as a queue, because they just don't want to believe that the world is really more complex than that.
The idea (for me anyway) is that if you have a postgres db with data, and need a queue for tasks dealing with that data, building it into the existing db can make more sense than adding additional infrastructure and complexity. Especially if you are familiar with postgres already.
If all you need is a queue, then I agree that dedicated queue software will be better.
If you want a very simple way of looking at it, think about how code becomes modular. You don't put all your code in one function, or one class, or one file, or even one application. Not just because it's easier to read, but because specific components should have clearly defined delineations from others, and forceably be kept separate at the borders of their functionality. Doing that leads to benefits that outweigh them sitting next to each other, tightly tied into each other.
At the very least, you should not be using one giant database for all your data in all your applications. Everybody should know that by now. And just like you shouldn't conflate different applications' data containers/interfaces/internals, you shouldn't conflate a queueing system with a completely separate data container. They need to be separate to avoid a bunch of problems. Again, not because you can't do it - like keeping all your code in a single function, or class - but because it's going to lead to a bad time.
I guess this Postgres queue is a very handy tool for cases like background jobs, but will it work with event sourcing?
Other things just as good at being a queue:
SQLite
SQL server
MySQL
Plain old files on the file system
I am happy that my queue inherit ACID properties.
SQLite simply doesn't allow concurrent write so it is a no go for a queue.
I don't know much about SQL Server and MySQL but I wouldn't favor a lockin closed source software or anything remotely connected to Oracle.
At the end, only Postgresql remains I guess. Also, Postgresql is super solid and the closest to SQL standard.
Are you certain about that?
If that's a concern, then MariaDB is another alternative. Postgres isn't the only option in town.
EDIT: I'm correcting myself here because as far as I can tell, LISTEN NOTIFY equivalent capabilities are only available in MariaDB's SkyQL fully managed services offering, and not in the base MariaDB Community Server.
…and accessed over SFTP.
I worked for a company in the health industry and one of the labs we integrated refused to call a HTTPS endpoint whenever a result was ready, so we had to poll every _n_ mins to fetch results. That worked well until covid happened and there were so many test results causing all sorts of issues like reading empty files (because they were about to be written to disk) and things of that nature.
Row update occurs as normal express api, but at the end I call pg_notify on some channel name. I pickup the message in a new thread via polling, and perform aggregate queries to a agg table. It’s like materialized views, but with no wasteful refreshes. Then I push the updates aggs back to the frontend via websockets.
The less separate pieces of infrastructure you can run, the less likely something will break that you don't know how to easily fix.
The article touched on this in the list of things to avoid when it said "I estimate each line of terraform to be an order of magnitude more risk/maintenance/faff than each line of Python" and "The need for expertise in anything beyond Python + Postgres"
It allows you to wrap it all in a transaction. If you separate a database update and insertion into a queue, whichever happens second may fail, while the first succeeds.
Or actual XA, but that is cursed.
The scale at which you would outgrow using postgres for the queue, you would also outgrow using postgres for the data.
It is not a mystery why all webscale companies endup designing their own DB technology.
That being said, most of the DB in the wild are not remotely at those scale. I have seen my share of Postgresql/ElasticSearch combo to handle below TB data and just collapsing because of the overeng of administrating two DB in one app.
Not hard to see path ahead of us similar to Linux's gradual step towards Rust in the kernel.
Is your code handling different varieties of event states stored in your database?
Why implement this as a monolithic data source? Was it not possible to separate different states by additional queues?
Curious to hear the decision making process here. Maybe I can see it for low volume?
If there is a two phase process then you just need idempotence and can safely transact with the db.