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

271–280 of 328 posts

Re: Postgres LISTEN/NOTIFY does not scale

#271

Post by notarobot123 »

aurumque wrote: post #18
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'll take the shameless plug. Thank you for putting this together! Very helpful overview of pg locks.

It's funny how "shameless plug" actually means "excuse the self-promotion" and implies at least a little bit of shame even when the reference is appropriate and on-topic.

Re: Postgres LISTEN/NOTIFY does not scale

#272

Post by osigurdson »

dathinab wrote: post #235
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…

if you need transaction across a queue into a normal SQL DB or similar I believe you are doing something very wrong. Sure you need transaction about processing things in a queue (mark as "taken out", but not yet remove then remove or "place back in (or into a failed messages inbox)" on timeout or similar can be _very_ important for queue systems. But the moment the "fail save if something dies while processing a mess…

>> To be fair that might still be the best solution for some situations.

It is arguable. Let's say your team knows Postgres well from a relational standpoint. Now they need to do something messages and require some type of notification / messaging. There is a learning curve here anyway. I'd argue they should spend it on more standard approaches which are not harder to start with. Of course, if you know that your site / service will only be used by yourself and your grandmother do whatever you want (just use a text file or better yet just call her instead).

Re: Postgres LISTEN/NOTIFY does not scale

#273

Post by nightpool »

sorentwo wrote: post #14

Postgres LISTEN/NOTIFY was a consistent pain point for Oban (background job processing framework for Elixir) for a while. The payload size limitations and connection pooler issues alone would cause subtle breakage. It was particularly ironic because Elixir has a fantastic distribution and pubsub story thanks to distributed Erlang. That’s much more commonly used in apps now compared to 5 or so years ago when 40-50% of…

What about Heroku made Erlang clustering difficult? It's had the same DNS clustering feature that Fly has, and they've had it since 2017: https://devcenter.heroku.com/articles/dyno-dns-service-disco....

Re: Postgres LISTEN/NOTIFY does not scale

#274

Post by sorentwo »

nightpool wrote: post #273
sorentwo wrote: post #14

Postgres LISTEN/NOTIFY was a consistent pain point for Oban (background job processing framework for Elixir) for a while. The payload size limitations and connection pooler issues alone would cause subtle breakage. It was particularly ironic because Elixir has a fantastic distribution and pubsub story thanks to distributed Erlang. That’s much more commonly used in apps now compared to 5 or so years ago when 40-50% of…

What about Heroku made Erlang clustering difficult? It's had the same DNS clustering feature that Fly has, and they've had it since 2017: https://devcenter.heroku.com/articles/dyno-dns-service-disco... .

The problem was with restrictive connections, not DNS based discovery for clustering. It wasn't possible (as far as I'm aware) to connect directly from one dyno to another through tcp/udp.

Re: Postgres LISTEN/NOTIFY does not scale

#275

Post by singron »

atombender wrote: post #190
singron wrote: post #34

Earlier quoted context omitted.

Polling is the way to go, but it's also very tricky to get right. In particular, it's non-trivial to make a reliable queue that's also fast when transactions are held open and vacuum isn't able to clean tuples. E.g. "get the first available tuple" might have to skip over 1000s of dead tuples. Holding transactions open is an anti-pattern for sure, but it's occasionally useful. E.g. pg_repack keeps a transaction open w…

Dead tuples is a real and significant problem, not just because it has to skip the tuples, but because the statistics that drive the planner don't account for them. I found this out the hard way when I had a simple query that suddenly got very, very slow on a table where the application would constantly do a `SELECT ... FOR UPDATE SKIP LOCKED` and then immediately delete the rows after a tiny bit of processing. It tu…

Unfortunately, updating the row also creates dead tuples. It's very tricky!

Re: Postgres LISTEN/NOTIFY does not scale

#276

Post by cryptonector »

williamdclt wrote: post #255
cryptonector wrote: post #254

Earlier quoted context omitted.

`pg_logical_emit_message()` is great and better than `NOTIFY` in terms of how it works, but... `pg_logical_emit_message()` perpetuates/continues the lack of authz around `NOTIFY`.

What do you mean by this? What authz would you expect/like?

I'd like to say that only some roles can NOTIFY to some channels. Similarly for alternatives to LISTEN/NOTIFY.

Re: Postgres LISTEN/NOTIFY does not scale

#277

Post by atombender »

singron wrote: post #275
atombender wrote: post #190

Earlier quoted context omitted.

Dead tuples is a real and significant problem, not just because it has to skip the tuples, but because the statistics that drive the planner don't account for them. I found this out the hard way when I had a simple query that suddenly got very, very slow on a table where the application would constantly do a `SELECT ... FOR UPDATE SKIP LOCKED` and then immediately delete the rows after a tiny bit of processing. It tu…

Unfortunately, updating the row also creates dead tuples. It's very tricky!

It does, but because of how indexes work, I believe it won't be skewed by the presence of dead tuples (though the bloat can cause the live dat to be spread across a lot more blocks and therefore generate more I/O) as long as you run autoanalyze semi-regularly.

Re: Postgres LISTEN/NOTIFY does not scale

#278

Post by nightpool »

sorentwo wrote: post #274
nightpool wrote: post #273

Earlier quoted context omitted.

What about Heroku made Erlang clustering difficult? It's had the same DNS clustering feature that Fly has, and they've had it since 2017: https://devcenter.heroku.com/articles/dyno-dns-service-disco... .

The problem was with restrictive connections, not DNS based discovery for clustering. It wasn't possible (as far as I'm aware) to connect directly from one dyno to another through tcp/udp.

That is not an issue when using Private Spaces, which have been available since 2015

Re: Postgres LISTEN/NOTIFY does not scale

#279

Post by panzi »

Lio wrote: post #161
panzi wrote: post #129

Earlier quoted context omitted.

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

Rails fully supports constraints and encourages you to use them. You can either execute SQL in your migration or use add_check_constraint.

Back when I used Rails the sentiment was: You don't need foreign keys, this is all handled by ActiveRecord.

Re: Postgres LISTEN/NOTIFY does not scale

#280

Post by panzi »

fathomdeez wrote: post #131
panzi wrote: post #129

Earlier quoted context omitted.

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

I don't think of those as business logic, per se. They're just validity checks on what the data should look like before it's written to disk - they're not actionable in the way L/N is. That being said, constraints usually end up being duplicated outside the db anyway, but having them where the data rests (so you don't have to assume every client is using the correct constraint code) makes sense.

I see. Further I have used triggers to automatically populate log tables or aggregate statistics on write. Why do I need fast statistics? For API limits. Customers have N free operations per months and such, so I have to query that on every operation. Do you consider these things as business logic that don't belong in the database?
Post reply on HN