Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Using PostgreSQL as a Dead Letter Queue for Event-Driven Systems

diljitpr.net

Post reply on HN
Print view View original

71–80 of 82 posts

Re: Using PostgreSQL as a Dead Letter Queue for Event-Driven Systems

#71

Post by hans_castorp »

branko_d wrote: post #58

Why 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)?

Postgres does index de-duplication. So it's likely that even if you change the strings to enums, the index won't be that much smaller.

> Furthermore, why have two indexes with the same leading field (status)?

That indeed is a valid question.

Re: Using PostgreSQL as a Dead Letter Queue for Event-Driven Systems

#72

Post by tlb »

reactordev wrote: post #9

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.

I think the PG designers would be surprised by the claim that it wasn't designed for this. Database designers try very hard to support the widest possible range of uses.

If all queue actions are failing instantly, you probably want a separate throttle to not remove them from the Kafka queue, since you'd rather keep them there and resume processing them normally instead of from the DLQ when queue processing is working again. In fact, the rate limit implicitly enforced by adding failure records to the DLQ helps with this.

Re: Using PostgreSQL as a Dead Letter Queue for Event-Driven Systems

#73

Post by branko_d »

storystarling wrote: post #65
branko_d wrote: post #58

Why 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.

> Boolean is rarely enough for real production workloads. You need a 'processing' ... 'retrying'... 'failed' ...

If you have more than 2 states, then just use integer instead or boolean.

> Saving a few bytes on the index isn't worth losing that observability.

Not sure why having a few well-known string values is more "observable" than having a few well-known integer values.

Also, it might be worth having better write performance. When PostgreSQL updates a row, it actually creates a new physical row version (for MVCC), so the less it has to copy the better.

Re: Using PostgreSQL as a Dead Letter Queue for Event-Driven Systems

#74

Post by j45 »

gytisgreitai wrote: post #68
j45 wrote: post #33

Earlier quoted context omitted.

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...

Probably not worth using a sledgehammer (Kafka) for an ant.

Lots of ppl do resume building only to realize rolls like Kafka at start vs scale can be very different.

It’s best to learn events from the ground up including how, when, and where you may outgrow existing implementation approaches let alone technologies.

Re: Using PostgreSQL as a Dead Letter Queue for Event-Driven Systems

#75

Post by tracker1 »

gytisgreitai wrote: post #68
j45 wrote: post #33

Earlier quoted context omitted.

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...

RDBMS are pretty well understood and very flexible, more still with the likes of JSONB where parts of your schema can be (de)normalized for convenience and reducing joins in practice. Modern hardware is MUCH more powerful today than even a decade and a half ago. You can scale vertically a LOT with an RDBMS like PostgreSQL, so it's a good fit for more use cases as a result.

Personally, at this point, I'm more inclined to reach for a few tools than to try to increase certain types of complexity. That said, I'm probably more inclined to introduce valkey/redis earlier on for some things, which I think may be better suited to MQ type duties without an actual MQ or more complex service bus over PG... but PG works.

Especially for systems that you aren't breaking up queues because of the number of jubs, so much as the benefits of a logical separation of the work from the requestor. Email (for most apps), report generation, etc... all types of work that an RDBMS is more than suitable for.

Re: Using PostgreSQL as a Dead Letter Queue for Event-Driven Systems

#76

Post by reactordev »

direwolf20 wrote: post #70
reactordev wrote: post #51

Earlier quoted context omitted.

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.

Credit cards still worked…

Email still worked…

Again, there are fault tolerant distributed systems out there that don’t rely on a single point of failure.

That’s not to say failure doesn’t happen.

Re: Using PostgreSQL as a Dead Letter Queue for Event-Driven Systems

#77

Post by throw_away_623 »

branko_d wrote: post #73
storystarling wrote: post #65

Earlier quoted context omitted.

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.

> Boolean is rarely enough for real production workloads. You need a 'processing' ... 'retrying'... 'failed' ... If you have more than 2 states, then just use integer instead or boolean. > Saving a few bytes on the index isn't worth losing that observability. Not sure why having a few well-known string values is more "observable" than having a few well-known integer values. Also, it might be worth having better write…

Postgres supports enum that would fit this use case well. You get the readability of text and the storage efficiency of an integer. Adding new values used to require a bit of work, but version 9.1 introduced support for it.

Re: Using PostgreSQL as a Dead Letter Queue for Event-Driven Systems

#78

Post by tonymet »

quibono wrote: post #45
tonymet wrote: post #27

Postgres is essentially a b-tree with a remote interface. Would you use a b-tree to store a dead letter queue? What is big O of insert & delete? what happens when it grows? Postgres has a query interface, replication, backup and many other great utilities. And it’s well supported, so it will work for low-demand applications. Regardless, you’re using the wrong data structure with the wrong performance profile, and at…

What would you use?

for parity functionality and better performance, a Redis list .

Re: Using PostgreSQL as a Dead Letter Queue for Event-Driven Systems

#79

Post by with »

microlatency wrote: post #67
with wrote: post #44

Great 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?

We strictly used AWS for everything and always preferred AWS-managed, so we always used SQS (and their built-in DLQ functionality). They made it easy to configure throttling, alerting, buffering, concurrency, retries etc, and you could easily use the UI to inspect the messages in a pinch.

As far as fixing actual critical issues - usually the message inside the DLQ had a trace that was revealing enough, although not always so trivial.

The philosophy was either: 1. fix the issue 2. swallow the issue (more rare)

but make sure this message never comes back to DLQ again

Re: Using PostgreSQL as a Dead Letter Queue for Event-Driven Systems

#80

Post by with »

microlatency wrote: post #66
with wrote: post #43

Earlier quoted context omitted.

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?

At my last workplace, we used pure AWS Cloudwatch. At my new workplace, we use Grafana+Sentry
Post reply on HN