31–40 of 82 posts
https://web.archive.org/web/20240309030618/https://www.2ndqu...
corresponding HN discussion thread from 2016 https://news.ycombinator.com/item?id=14676859
[†] it seems that all the old 2ndquadrant.com blog post links have been broken after their acquisition by enterprisedb
Another day, another “Using PostgreSQL for…” thing it wasn’t designed for. This isn’t a good idea. What happens when the queue goes down and all messages are dead lettered? What happens when you end up with competing messages? This is not the way.
Ofc I wouldn't us it for extremely high scale event processing, but it's great default for a message/task queue for 90% of business apps. If you're processing under a few 100m events/tasks per day with less than ~10k concurrent processes dequeuing from it it's what I'd default to. I work on apps that use such a PG based queue system and it provides indispensable features for us we couldn't achieve easily/cleanly with…
There’s also an order of magnitude higher events when doing event based work in processing.
This seems like a perfectly reasonable starting and gateway points that can have things organized for when the time comes.
Most things don’t scale that big.
Biggest thing to watch out with this approach is that you will inevitably have some failure or bug that will 10x, 100x, or 1000x the rate of dead messages and that will overload your DLQ database. You need a circuit breaker or rate limit on it.
No need to look down on PG because it makes it more approachable and is more longer a specialized skill.
re: SKIP LOCKED, introduced in postgres 9.5, here's an an archived copy [†] of the excellent 2016 2ndquadrant post discussing it https://web.archive.org/web/20240309030618/https://www.2ndqu... corresponding HN discussion thread from 2016 https://news.ycombinator.com/item?id=14676859 [†] it seems that all the old 2ndquadrant.com blog post links have been broken after their acquisition by enterprisedb
PostgreSQL FOR UPDATE SKIP LOCKED: The One-Liner Job Queue https://www.dbpro.app/blog/postgresql-skip-locked
It covers the race condition, the atomic claim behaviour, worker crashes, and how priorities and retries are usually layered on top. Very much the same approach described in the old 2ndQuadrant post, but with a modern end-to-end example.
Earlier quoted context omitted.
Not necessarily. If you can't deliver the message somewhere you don't ACK it, and the sender can choose what to do (retry, backoff, etc.) Sure, it's unavailability of course, but it's not data loss.
If you are reading from Kafka (for example) and you can't do anything with a message (broken json as an example) and you can't put it into a DLQ - you have not other option but to skip it or stop on it, no?
If the problem is that the consumers themselves cannot write to the DLQ, then that feels like either Kafka is dying (no more writes allowed) or the consumers have been misconfigured.
Edit: In fact there seems to be a self inflicted problem being created here - having the DLQ on a different system, whether it be another instance of Kafka, or Postgres, or what have you, is really just creating another point of failure.
Another day, another “Using PostgreSQL for…” thing it wasn’t designed for. This isn’t a good idea. What happens when the queue goes down and all messages are dead lettered? What happens when you end up with competing messages? This is not the way.
There are a ton of job/queue systems out there that are based on SQL DBs. GoodJob and SupaBase Queues are two examples. It’s not usable for high scale processing but most applications just need a simple queue with low depth and low complexity. If you’re already managing PSQL and don’t want to add more management to your stack (and managed services aren’t an option), this pattern works just fine. Go back 10-15yrs and…
Another day, another “Using PostgreSQL for…” thing it wasn’t designed for. This isn’t a good idea. What happens when the queue goes down and all messages are dead lettered? What happens when you end up with competing messages? This is not the way.
Criticism without a better solution is only so valuable. How would you do this instead, and why?
Another day, another “Using PostgreSQL for…” thing it wasn’t designed for. This isn’t a good idea. What happens when the queue goes down and all messages are dead lettered? What happens when you end up with competing messages? This is not the way.
The other system you're using that isn't Postgres can also go down. Many developers overcomplicate systems. In the pursuit of 100% uptime, if you're not extremely careful, you removed more 9s with complexity than you added with redundancy. And although hyperscalers pride themselves on their uptime (Amazon even achieved three nines last year!) in reality most customers of most businesses are fine if your system is dow…
Only if DC gets nuked.
Many developers overcomplicate systems and throw a database at the problem.
re: SKIP LOCKED, introduced in postgres 9.5, here's an an archived copy [†] of the excellent 2016 2ndquadrant post discussing it https://web.archive.org/web/20240309030618/https://www.2ndqu... corresponding HN discussion thread from 2016 https://news.ycombinator.com/item?id=14676859 [†] it seems that all the old 2ndquadrant.com blog post links have been broken after their acquisition by enterprisedb
We just published a detailed walkthrough of this exact pattern with concrete examples and failure modes: PostgreSQL FOR UPDATE SKIP LOCKED: The One-Liner Job Queue https://www.dbpro.app/blog/postgresql-skip-locked It covers the race condition, the atomic claim behaviour, worker crashes, and how priorities and retries are usually layered on top. Very much the same approach described in the old 2ndQuadrant post, but wi…