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

Postgres LISTEN/NOTIFY does not scale

recall.ai

Post reply on HN
Print view View original

141–150 of 328 posts

Re: Postgres LISTEN/NOTIFY does not scale

#141

Post by physix »

const_cast wrote: post #96
kristianc wrote: post #84

Earlier quoted context omitted.

Sounds like a deliberate attempt to avoid spinning up Redis, Kafka, or an outbox system early on.. and then underestimated how quickly their scale would make it blow up. Story as old as time.

I find the opposite story more true: additional complexity in the form of caching early, for a scale that never comes. I've worked on one too many sprawling, distributed systems with too little users to justify it.

"Sprawling distributed systems".

I like that. Sounds like a synonym for "Platform Engineering". :-)

I remember being amazed that lambda architecture was considered a kind of reference, when it looked to me more like a workaround.

We like to build IT cathedrals, until we have to run them.

Re: Postgres LISTEN/NOTIFY does not scale

#142

Post by broken_broken_ »

leontrolski wrote: post #17

I'd be interested as to how dumb-ol' polling would compare here (the FOR UPDATE SKIP LOCKED method https://leontrolski.github.io/postgres-as-queue.html ). One day I will set up some benchmarks as this is the kind of thing people argue about a lot without much evidence either way. Wasn't aware of this AccessExclusiveLock behaviour - a reminder (and shameless plug 2) of how Postgres locks interact: https://leontrolski.…

I have implemented polling against a cluster of mixed mariadb/mysql databases which do not offer listen/notify. It was a pain in the neck to get right.

- The batch size needs to be adaptative for performance, latency, and recovering smoothly after downtime.

- The polling timeouts, frequency etc the same.

- You need to avoid hysteresis.

- You want to be super careful about not disturbing the main application by placing heavy load on the database or accidentally locking tables/rows

- You likely want multiple distributed workers in case of a network partition to keep handling events

It’s hard to get right especially when the databases at the time did not support SKIP LOCKED.

In retrospect I wish I had listened to the WAL. Much easier.

Re: Postgres LISTEN/NOTIFY does not scale

#143

Post by Footkerchief »

panzi wrote: post #129
fathomdeez wrote: post #125

Earlier quoted context omitted.

This kind of issue always comes up when people put business logic inside the database. Databases are for data. The data goes in and the data goes out, but the data does not get to decide what happens next based on itself. That's what application code is for.

So what are your thoughts on constraints then? Foreign keys? Should that only be handled by the application, like Rails does (or did, haven't used in a long time).

You still use constraints even if you put all your business logic in stored procedures.

Re: Postgres LISTEN/NOTIFY does not scale

#144

Post by whatevaa »

doc_manhat wrote: post #47

Got up to the TL;DR paragraph. This was a major red flag given the initial presentation of the discovery of a bottleneck: ''' When a NOTIFY query is issued during a transaction, it acquires a global lock on the entire database (ref) during the commit phase of the transaction, effectively serializing all commits. ''' Am I missing something - this seems like something the original authors of the system should have done…

You don't know how heavy it will be in new systems. As another commenter mentioned, you might never reach that point. Simplier is always better.

Re: Postgres LISTEN/NOTIFY does not scale

#145

Post by IgorPartola »

platzhirsch wrote: post #130
fathomdeez wrote: post #125

Earlier quoted context omitted.

This kind of issue always comes up when people put business logic inside the database. Databases are for data. The data goes in and the data goes out, but the data does not get to decide what happens next based on itself. That's what application code is for.

If you want your database to just store bytes, use a key-value store. But SQL gives you schemas and constraints for a reason; they're guardrails for your business logic. Just don’t ask your tables to run the business for you.

If only different ORMs had more support for triggers and stored procedures. Things would be so much easier if I could do things like denormalize certain frequently accessed fields across tables but with proper ability to update them automatically without having to do them in application code.

Re: Postgres LISTEN/NOTIFY does not scale

#146

Post by IgorPartola »

Jailbird wrote: post #127
bevr1337 wrote: post #126

Earlier quoted context omitted.

> the data does not get to decide what happens next based on itself. Then why bother with a relational database? Relations and schemas are business logic, and I'll take all the data integrity I can get.

I've seen both of these philosophies. I liken them to religions, the believers are devout. Code is King vs the DB is King. I'm personally Code is King, and I have my reasons (like everyone else)

I am mostly on the side of business logic should live in applications and relationships between data types are not business logic so much as just the layout of the data. But I typically access data via an ORM and they typically don’t have support for triggers and stored procedures. If they did, I would certainly use it because projects I work on might have multiple people writing application code but everyone uses a single set of database models. This would mean that critical constraints on the shape of the data could be defined and respected at all times vs some developer on my team forgetting to include some critical check in their data update routine.

Re: Postgres LISTEN/NOTIFY does not scale

#147

Post by osigurdson »

goodkiwi wrote: post #124
osigurdson wrote: post #110

I like this article. Lots of comments are stating that they are "using it wrong" and I'm sure they are. However, it does help to contrast the much more common, "use Postgres for everything" type sentiment. It is pretty hard to use Postgres wrong for relational things in the sense that everyone knows about indexes and so on. But using something like L/N comes with a separate learning curve anyway - evidenced in this c…

I’ve been meaning to check out NATS - I’ve tended to default to Redis for pubsub. What are the main advantages? I use clickhouse and Postgres extensively

NATS gives you regular pub/sub but also streams as well (similar to Kafka along with strong durability guarantees, etc).

Re: Postgres LISTEN/NOTIFY does not scale

#148

Post by chatmasta »

fathomdeez wrote: post #125
osigurdson wrote: post #110

I like this article. Lots of comments are stating that they are "using it wrong" and I'm sure they are. However, it does help to contrast the much more common, "use Postgres for everything" type sentiment. It is pretty hard to use Postgres wrong for relational things in the sense that everyone knows about indexes and so on. But using something like L/N comes with a separate learning curve anyway - evidenced in this c…

This kind of issue always comes up when people put business logic inside the database. Databases are for data. The data goes in and the data goes out, but the data does not get to decide what happens next based on itself. That's what application code is for.

The first thing I did when I saw this article was to check the Postgres docs, because I thought "heh, surely they just didn't read the fine print," but the LISTEN/NOTIFY page has zero mentions of "lock" in the entire content.

Re: Postgres LISTEN/NOTIFY does not scale

#149

Post by chatmasta »

sbstp wrote: post #133
goodkiwi wrote: post #124

Earlier quoted context omitted.

I’ve been meaning to check out NATS - I’ve tended to default to Redis for pubsub. What are the main advantages? I use clickhouse and Postgres extensively

I've been disappointed by Nats. Core Nats is good and works well, but if you need stronger delivery guarantees you need to use Jetstream which has a lot of quirks, for instance it does not integrate well with the permission system in Core Nats. Their client SDKs are very buggy and unreliable. I've used the Python, Rust and Go ones, only the Go one worked as expected. I would recommend using rabbitmq, Kafka or redpand…

Are those recommendations based on using them all in the same context? Curious why you chose Kafka (or Redpanda which is effectively the same) over NATS.
Post reply on HN