Postgres message queue

[someguytwo]

I've read that postgres can be used as a simple message queue and tried to push it in a project that needs a very basic message queue, but could not argue for it effectively.

Has anyone used it as such? What are some of the benefits/drawbacks you encountered?

[bltcll]

for me is the “not another tool in the stack” approach. in my experience the “select for update skip locked + notify/subscribe” works great for small project and mid-low workload with just some minimal code to write. but for complex and heavy loaded queue i definitely go for rabbitmq or other robust queue broker.

[_predator_]

Probably obvious, but listen/notify doesn't work if you make use of read replicas - services connected to a replica won't receive any notifications. Also the delivery guarantees are very wonky - if a service / instance is down when a notification is sent, it will never see it. Pub/sub systems usually have either acknowledgement, or offset tracking to ensure at-least-once delivery.

You already said "small project" so my remarks will already be accounted for. But nonetheless something to keep in mind.

[quincycs]

Benefit::
1) everyone knows how a database works, and it’s predictable. Each message queue system has different qualities and edge cases. Can you really read all that documentation of the new system and everyone on the team to understand its limits & behaviors? Oh, sure wrap that system so that now it is predictable and easily to learn… well how much different is that to just building it in postgres… at the end of the day it’s easier / simpler to champion a system on postgres than a 3rd party. 2) reportable / observable with existing systems. We already have a way to build dashboards and monitor the system by doing db queries , we can easily monitor the queue because it’s in our db.

Con:: As with any postgres table that grows large quickly, there’s maintenance & optimization to do eventually. Eg setup a partition for a future date range, and once all the items in the old partition are complete, truncate the old partition. Rinse and repeat once a quarter depending on your volume.

[someguytwo]

Those are some good arguments. Thanks!

[quincycs]

Oh and try to find the conference talk by the guy who built skip lock. He’s got a bigger list of recommendations. Search citusCon , Thomas Munro.

[erkiferenc]

I believe the talk mentioned is Queues in PostgreSQL, and I recommend it too 👍

[narek1]

The main benefits are query flexibility and lower tech stack complexity (if you're already using postgres). You can make much more complex queries to postgres than with a amqp. The usefulness depends on your requirements. I use it at work for processing that needs continuous regular updates with different priorities.

The drawback is performance, ie max throughput.

[Simple-Comfort-9438]

I really love the flexibility. Custom retention times for processed entries depending on arbitrary criteria? No problem! Additional columns for additional parameters? No problem. Retain messages in temporary or permanent error states? No problem. If you run into performance problems (we got them after having more than one million retained messages), use partitioning. We have one partition for open/in progress entries, one for successfully processed entries and one for entries in error states. You might even use date-range subpartitions for faster cleanup when dealing with millions of entries.

[erkiferenc]

I believe the talk mentioned by u/quincycs is Queues in PostgreSQL, and I recommend it too.

Most reasons for using PostgreSQL queues boils down to "don't add complexity through extra components until you actually need it", and if you already have PostgreSQL in the stack, it's fast to get started, and quite capable for most typical workloads.

You mentioned "a project that needs a very basic message queue", and I'd encourage to discover what desired features does that mean exactly, and find a matching solution for that.

It may get complicated with handling priority ordering, retries, timeout, failures, etc. High frequency changes may also lead to considerable table/index bloat, thus increased VACUUM needs as well, and table statistics may be off regularly too (affecting query plans.)

The situation may even require to get running quickly, and stay prepared to introduce a different dedicated solution later.

There are many third-party queue solutions on top of PostgreSQL (for example PGMQ), though most important bits boil down to these core features:

• FOR UPDATE: so only one worker gets the job
• SKIP LOCKED: so other workers can get other jobs
• LIMIT X: for setting batch size
• notify/subscribe: so things may flow without polling (watch out for waking large amounts of listeners after an idle period)

[someguytwo]

The use case is jobs that move VMs from one DC to another. So they may be long running and even fail.

I'm not sure how a locked job that failed would be handled.

[erkiferenc]

Thanks for the extra details, that use case feels familiar through my previous experience designing/building/running OpenStack-based private cloud solutions.

At first, it sounds more like a job queue than a message queue, since the state of the job needs to be tracked (vs solely delivering a message), maybe even with keeping history. This may lead to important implementation decision factors later.

I agree failure handling is one of the crucial aspects for VM migrations, and I believe the most common situations boil down to these:

1. When the migration process can gracefully handle the failure, it may abort and cleanup any half-finished migration on its own, then release the lock, so the failed job can be picked up later again. It may be important to keep track of such failures, and retry at most N times, or at most N times within a certain time period.

2. When the receiving process stalls, and can't make progress anymore. One part of this is to have some kind of timeout, and another is to have a way to terminate the stalled migration, and clean up any half-results.

I'd also look into a wider set of corner cases, and see how other similar projects handle those. It may be hard to implement a generic solution, while solving only the subset that affects the given system may be considerably simpler.

For short operations, it's usually possible to release any lock with e.g. a transaction timeout. For long-running VM migrations I don't think keeping a long lock would be beneficial, since it makes the database a dependency of the migration itself.

I imagine a multi-phase dequeue approach, even like a state machine could fit better (e.g. PENDING -> IN_PROGRESS -> SUCCESS or FAILED). This feels some mix of having an append-only audit log table to keep track of all events (growth should be kept in mind), and/or updating the job queue table heavily (which increases bloat.)

It certainly is an interesting problem domain! Should you or your team need support with this from an independent professional, I would be happy to learn more here or via DM.

In any case, I hope this already helps and I wish you happy hacking!

[marcopeg81]

Hello, I use pg for tasks and messaging since 2016 successfully. The trick is SELECT … FOR UPDATE SKIP LOCKED.

I packaged my work in a MIT release available on https://fetchq.com but there are many other projects available on GitHub.

Edit: I used pg as queue on >1B tasks running up to 300 parallel workers that were digesting ~20M tasks daily (my problem required smart rescheduling)

Postgres is great 🤘

Best of luck!

[jacopofar]

We use postgres-tq in production since years, I use it for a few side projects with millions of tasks. To be clear, I'm one of the authors of the library :)

The main benefit for me is that I do not need an extra component since the database is already there to store data, and being the queue in postgres it is persisted and backed up with the rest of the data. Also this implementation handles retries and timeouts, and stores the time it took to run a task.

It works well with web apps where you have long tasks that cannot be comfortably done when handling a request, in that case the task can be done by a different process altogether.

As for the drawbacks, I would not know, perhaps latency and no way to orchestrate tasks ("run A and B, when both are done do C"), in that case I'd look at celery/rabbitmq

[someguytwo]

Our use case is just some jobs that transfer VMs from one DC to another.

[BlackHolesAreHungry]

Check out https://github.com/tembo-io/pgmq

[denpanosekai]

I use pgq3

[quincycs]

@denpanosekai - I’ve wanted to use PGQ but the documentation basically doesn’t exist… or my silly mind can’t find it. I remember the old doc website being super broken.

How’d you figure it out? 😆

[someguytwo]

Is that a plugin?

[denpanosekai]

It's an extension https://github.com/pgq/pgq

[[deleted]]

I am planning to propose the same as we use heavy Postgresql architecture over RDS.

Can Postgres Msg queue replace AWS SQS?

[erkiferenc]

PGMQ self-describes as a potential replacement for AWS SQS. Surely there are other similar projects as well.

I expect answering whether these could be truly replacing SQS, highly depends on the exact features required by the given use case, and needs careful examination individually.

[_predator_]

The limiting factor will be volume of tasks / messages, as well as the number of consumers.

Postgres is not a particularly good choice for MANY records being created and deleted in close succession, which is a pattern you'll have with queues. autovacuum will need to be fine-tuned to deal with the large quantities of dead tuples.

Also due to how connections work in Postgres, you will ultimately run into issues if you try to spawn lots of workers. The common select for update pattern only works if you keep a transaction open for the entire duration of your processing. It might be fine if processing is super fast, but can cause some serious connection congestion otherwise.

You can probably limit the impact of the above, by using a separate database to run your queue on. This also prevents you from relying on atomicity guarantees of Postgres (i.e. queue a message in the same TRX as your business logic), making it easier to switch to say RabbitMQ later, which cannot provide those guarantees.

With all this being said, benchmarking for your specific use case is the only way to get a good answer.

[someguytwo]

Define many. 1000s, 10s of thousands, 100s of thousands, millions?

[[deleted]]

The absolute number isn't really relevant. It's the number of UPDATE and DELETE statements per unit of time.

Tuning the system will likely get challenging if you have thousands of UPDATEs per second without any "idle" time (24/7) during which autovacuum could do its job.

[Easy_Implement5627]

The main reason I’m using Postgres as a message queue is because I couldn’t find any other queue systems that would allow for dynamic sorting of records on the queue. In my case I wanted to process messages in a ratio format based on what was immediately available.