361–369 of 369 posts
I've done the Postgres skip locked thing at least three times and I'm currently doing it, but IMO it is actually more maintenance and overhead, not less -- at least when compared with the queues made available by the major cloud providers. Compared with Pubsub or SQS you need to handle, * Metrics, monitoring, alarming on depth, message age * Autoscaling on your custom metrics * Managing retries, dead lettering, backo…
Earlier quoted context omitted.
> Multiple times, I've pushed an SQL-based queue a couple orders of magnitude past the scale where others say SQL craps out and a distributed solution is an absolute requirement. What about availability, though? The distributed solution is also useful to avoid downtime in case of single node failure. Is there an off-the-shelf solution that lets me do that with Postgres? I know the newest version (16) just added activ…
But if you're using Postgres as your queuing system because you're already using it as your core database technology for your app, you've got the same issue. If your single Postgres instance is down then your app is, too, and won't be enqueuing more jobs. And unless your jobs are trivial then it's highly likely that they interact with your app in some way so it doesn't really matter if your workers are distributed an…
If background jobs need to be available while some other core application is down, that needs to be designed for, and that design can be achieved with any queue technology. Simply separate the queue system stack from the core application system stack.
> But if you're using Postgres as your queuing system because you're already using it as your core database technology
Note your own use of "database technology" and not "database server". It's common to have separate application and queue database servers when such an architectural constraint is present. Of course, this sacrifices the benefit of transactional guarantees when the application and background jobs run on the same server.
Like I said in the post, technology (and architectural) choices are tradeoffs all the way down :)
Earlier quoted context omitted.
you're confusing between "i don't care about order", and "there is no order". Name ONE queue implementation that doesn't have order.
Here you go. One of the first tutorials explaining how SKIP LOCKED works in Postgres implants a job “queue” that doesn’t have an order by clause. https://www.pgcasts.com/episodes/the-skip-locked-feature-in-... I’m not confusing anything. I’ve seen random selection “job queues” implemented many times. As long as you truly don’t care about start order, it’s fine to trade it for increased throughout.
Does that mean it doesn't have any order or that whoever writes the query doesn't care about order?
Also we are arguing over whether pg suffices as a queue implementation, and you use itself as an example?
Earlier quoted context omitted.
Queue can clearly mean "work that needs to be completed" not necessarily 'work completed in order'. Your definition is much stricter than it needs to be for most use cases.
From the dictionary. Queue: a list of data items, commands, etc., stored so as to be retrievable in a definite order, usually the order of insertion. note the term "Usually", not "always".
always has an oder, which is usually of insertion.
I'm in the market for a Postgres-backed queue system with client libraries in NodeJS _and_ Python. Clients in both languages need to be able to read and write from the queue. Can anybody suggest one?
PGMQ does not require a client library, https://github.com/tembo-io/pgmq so long as your language of choice can run SQL. All the functions live in Postgres, and you just call them with SQL statement. Very similar feel and semantics to SQS.
Earlier quoted context omitted.
Here you go. One of the first tutorials explaining how SKIP LOCKED works in Postgres implants a job “queue” that doesn’t have an order by clause. https://www.pgcasts.com/episodes/the-skip-locked-feature-in-... I’m not confusing anything. I’ve seen random selection “job queues” implemented many times. As long as you truly don’t care about start order, it’s fine to trade it for increased throughout.
> doesn’t have an “order by” clause Does that mean it doesn't have any order or that whoever writes the query doesn't care about order? Also we are arguing over whether pg suffices as a queue implementation, and you use itself as an example?
I’m not using pg itself as an example. I’m using a specific implementation of a “job queue” built with pg.
I’ve seen and you can search for and find many implementations of “job queues” using relational databases where job start order guarantees are traded away for throughput.
For several projects I’ve opted for the even dumber approach, that works out of the box with every ORM/Query DSL framework in every language: using a normal table with SELECT FOR UPDATE SKIP LOCKED https://www.pgcasts.com/episodes/the-skip-locked-feature-in-... It’s not “web scale” but it easily extends to several thousand background jobs in my experience
skip lock works well on many Ks/sec message queues.
Earlier quoted context omitted.
Similar experience here. Multiple times, I've pushed an SQL-based queue a couple orders of magnitude past the scale where others say SQL craps out and a distributed solution is an absolute requirement. And the SQL solution is typically simpler, requires fewer compute resources, and easier to support in production. But, to make it work, you've got to know the database well enough to know that things like SELECT FOR UP…
> Multiple times, I've pushed an SQL-based queue a couple orders of magnitude past the scale where others say SQL craps out and a distributed solution is an absolute requirement. What about availability, though? The distributed solution is also useful to avoid downtime in case of single node failure. Is there an off-the-shelf solution that lets me do that with Postgres? I know the newest version (16) just added activ…
And, frankly, even for the less-critical stuff that was only running on a single node, I still dealt with fewer availability problems back in the day than I do now that everything's gone distributed. I think that a thing that's been forgotten over the years is that a lot of this stuff that distributed systems do to be reliable was more about digging oneself out of the hole that was created by running on lots of cheap hardware instead of using a single server with redundancy built-in. I acknowledge that, past a certain scale, that's the only option that makes sense. But if you're not operating at that scale then there's a good chance it's all just verschlimmbessern.
Earlier quoted context omitted.
you can even built a advisory lock queue in Postgres, which is way slower but has some benefits.
slower to build or slower to run?