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

201–210 of 328 posts

Re: Postgres LISTEN/NOTIFY does not scale

#201

Post by whstl »

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.

This is one of those absolute statements that cause the kind of problem stated by grandparent. There are lots of those: "Use Postgres for everything", "No business data on the DB", "No methods bigger than 10 lines", "Abstractions only after 3 usages".

Back to the topic: Lots of potential bugs and data corruption issues are solved by moving part of the business logic to the database. Other people already covered two things: data validation and queue atomicity.

On the other hand, lots of potential issues can also arise by putting other parts of business logic to the database, for example, calling HTTPS endpoints from inside the DB itself is highly problematic.

The reality is that the world is not black and white, and being an engineer is about navigating this grey area.

Re: Postgres LISTEN/NOTIFY does not scale

#202

Post by whstl »

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)

And both of those philosophies will lead to bad engineering.

There are things that work better, are safer and simpler to do on the database, and things that work better, are safer and simpler in code. And those things might change depending on context, technology, requirements, size of project, experience of contributors, etc.

Forcing round pegs into square holes will always lead to brittle code and brittle products, often for more cost (mental and financial!) than actually using each tool correctly.

Re: Postgres LISTEN/NOTIFY does not scale

#203

Post by daitangio »

I wrapped together a simple yet powerful queue system:

https://github.com/daitangio/pque

I evaluated Listen/notify but it seems to loose messages if no one is listening, so its use case seems pretty limited to me (my 2 cents).

Anyway, If you need to scale, I suggest an ad hoc queue server like rabbitmq.

Re: Postgres LISTEN/NOTIFY does not scale

#204

Post by jumski »

mike_hearn wrote: post #177
ownagefool wrote: post #166

Earlier quoted context omitted.

Largely agree. Functionality wise if you don't have many jobs, using the database as the queue is fine. However, I've been in several situations where scaling the queue brings down the database, and therefore the app, and am thus of the opinion you probably shouldn't couple these systems too tightly. There are pros and cons, of course.

Using the database for queues is more than fine, it's often essential to correctness. In many use cases for queues you need to atomically update the database with respect to popping from the queue, and if they're separate systems you end up needing either XA or brittle and unreliable custom idempotency logic. I've seen this go wrong before and it's not nice, the common outcome is business-visible data corruption that…

Using queues in atomic, transactional way was a core principle for building https://pgflow.dev - having whole workflow state transactionally updated alongside the work on the in db queue really simplifies a lot of things: debugging is easier, audit log is easy, reporting, stats etc are one SQL query away.

Re: Postgres LISTEN/NOTIFY does not scale

#205

Post by calderwoodra »

bjornsing wrote: post #156

If I’m not mistaken LISTEN/NOTIFY doesn’t work with connection poolers, and you can’t have tens of thousands of connections to a Postgres database. Not sure you need a more elaborate analysis than that to reach the same conclusion.

Why doesn't LISTEN/NOTIFY work with connection poolers?

Re: Postgres LISTEN/NOTIFY does not scale

#206

Post by ahoka »

the_duke wrote: post #191
v5v3 wrote: post #173

Earlier quoted context omitted.

Isn't Kafka the Postgresql of pub/sub I.e. use Kafka unless you have a explicit reason not to? So why Nats?

Kafka is far from trivial to operate, for one thing, even post zookeeper.

And it's kinda wrong to use as a queue (in most cases), being a log stream you can seek in.

Re: Postgres LISTEN/NOTIFY does not scale

#207

Post by ahoka »

asg0451 wrote: post #102
sorentwo wrote: post #51

Earlier quoted context omitted.

Very very few applications use mnsesia. There’s absolutely no way I would recommend it over Postgres.

can you explain why?

I have only worked with a product that used it, so no direct experience, but one problem that was often mentioned is split-brains happening very frequently.

Re: Postgres LISTEN/NOTIFY does not scale

#208

Post by j45 »

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…

There’s no reason this article and start with Postgres for everything can’t be true.

In the beginning having fewer parts to connect and maintain lets the needs and bottlenecks of the actual application emerge.

If it was listen/notify in such a scenario at some volume where optimizing it isn’t in the cards… so be it. It would be some time down the road before sharding a function into a specific subsystem like what you described.

Appreciate learning about the Postgres/Clickhouse/nats combo. If there might be an article if the three together that you liked would be happy to read and learn.

Re: Postgres LISTEN/NOTIFY does not scale

#209

Post by 0xFEE1DEAD »

ako wrote: post #192
Jailbird wrote: post #127

Earlier quoted context omitted.

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)

It’s really not about code is better or database it better, it’s mostly about locality: if you want to update thousands of records, you can’t pull those records into a separate process, update them there and then write back. So you put your code next to the data in the database. Stored procedures are just code deployed to a database container…

Sure you can, I've done it plenty of times. I'm genuinely curious why you think it's not possible.

The only reasons I can think of:

- you're rewriting a legacy system and migrate parts incrementally

- data compliance

- you're running a dangerous database setup

I try my best to avoid putting any business logic inside databases and see stored procedures only as a temporary solution.

Re: Postgres LISTEN/NOTIFY does not scale

#210

Post by mike_hearn »

jumski wrote: post #204
mike_hearn wrote: post #177

Earlier quoted context omitted.

Using the database for queues is more than fine, it's often essential to correctness. In many use cases for queues you need to atomically update the database with respect to popping from the queue, and if they're separate systems you end up needing either XA or brittle and unreliable custom idempotency logic. I've seen this go wrong before and it's not nice, the common outcome is business-visible data corruption that…

Using queues in atomic, transactional way was a core principle for building https://pgflow.dev - having whole workflow state transactionally updated alongside the work on the in db queue really simplifies a lot of things: debugging is easier, audit log is easy, reporting, stats etc are one SQL query away.

Nice! I'm also using queues as part of a workflow engine.
Post reply on HN