Only slightly related, but I have been using Oban as a Postgres native message queue in the elixir ecosystem and loving it.
For my use case, it’s so much simpler than spinning up another piece of infrastructure like Kafka or rabbitmq
Using PostgreSQL as a Dead Letter Queue for Event-Driven Systems
61–70 of 82 posts
Re: Using PostgreSQL as a Dead Letter Queue for Event-Driven Systems
#62We did this at Chargify, but with MySQL. If Redis was unavailable, it would dump the job as a JSON blob to a mysql table. A cron job would periodically clean it out by re-enqueuing jobs, and it worked well.
Re: Using PostgreSQL as a Dead Letter Queue for Event-Driven Systems
#63https://github.com/pgmq/pgmq
Indeed, pgmq is exactly the Postgres queueing system that you would build from scratch (for update skip locked and all that), except it's already built. Cloud providers should install this extension by default - it's in a really sweet spot for when you don't want or need a separate queue
Re: Using PostgreSQL as a Dead Letter Queue for Event-Driven Systems
#64Hmm that raises a question for me.
I haven't done a project that uses a database (be it sql or no sql) where the amount of deletes is comparable to the amount of inserts (and far larger than like tens per day, of course).
How does your average db server work with that, performance wise? Intuitively I'd think it's optimized more for inserts than for deletes, but of course I may be wrong.
Re: Using PostgreSQL as a Dead Letter Queue for Event-Driven Systems
#65Why use string as status, instead of a boolean? That just wastes space for no discernable benefit, especially since the status is indexed. Also, consider turning event_type into an integer if possible, for similar reasons. Furthermore, why have two indexes with the same leading field (status)?
Boolean is rarely enough for real production workloads. You need a 'processing' state to handle visibility timeouts and prevent double-execution, especially if tasks take more than a few milliseconds. I also find it crucial to distinguish between 'retrying' for transient errors and 'failed' for dead letters. Saving a few bytes on the index isn't worth losing that observability.
Re: Using PostgreSQL as a Dead Letter Queue for Event-Driven Systems
#66Biggest 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.
This is the same risk with any DLQ. The idea behind a DLQ is it will retry (with some backoff) eventually, and if it fails enough, it will stay there. You need monitoring to observe the messages that can't escape DLQ. Ideally, nothing should ever stay in DLQ, and if it does, it's something that should be fixed.
What do you use for the monitoring of DLQs?
Re: Using PostgreSQL as a Dead Letter Queue for Event-Driven Systems
#67Great application of first principles. I think it's totally reasonable also, at even most production loads. (Example: My last workplace had a service that constantly roared at 30k events per second, and our DLQs would at most have orders of hundreds of messages in them). We would get paged if a message's age was older than an hour in the queue. The idea is that if your DLQ has consistently high volume, there is somet…
What did you use for the DLQ monitoring? And how did you fix the issues?
Re: Using PostgreSQL as a Dead Letter Queue for Event-Driven Systems
#68Ofc 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…
Very few things dna start at an extremely high scale event processing. 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.
So perhaps don’t use kafka at all? E.g. Adyen used postgresql [1] as a queue until the outgrew.
In this case it seems there are a lot of things that can go south in case of major issue on the event pipeline. Unless the throughput is low.. but then why kafka?
[1] https://www.adyen.com/knowledge-hub/design-to-duty-adyen-arc...
Re: Using PostgreSQL as a Dead Letter Queue for Event-Driven Systems
#69I maintain a small Postgres-native job queue for Python called PGQueuer: https://github.com/janbjorge/pgqueuer
It uses the same core primitives people are discussing here (FOR UPDATE SKIP LOCKED for claiming work; LISTEN/NOTIFY to wake workers), plus priorities, scheduled jobs, retries, heartbeats/visibility timeouts, and SQL-friendly observability. If you’re already on Postgres and want a pragmatic “just use Postgres” queue, it might be a useful reference / drop-in.
Re: Using PostgreSQL as a Dead Letter Queue for Event-Driven Systems
#70Earlier quoted context omitted.
Which system is immune to all downtime except the DC getting nuked?
Properly designed distributed systems. Challenge: Design a fault tolerant event-driven architecture. Only rule, you aren’t allowed to use a database. At all. This is actually an interview question for a top employer. Answer this right and you get a salary that will change your life.
No, those go down all the time. AWS had three nines last year. Bitcoin had the value overflow incident.