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

31–40 of 328 posts

Re: Postgres LISTEN/NOTIFY does not scale

#31

Post by franckpachot »

shivasaxena wrote: post #22

Out of curiosity: Would appreciate if others can share what other things like AccessExclusiveLock should postgres users beware of? What I already know - Unique indexes slow inserts since db has to acquire a full table lock - Case statements in Where break query planner/optimizer and require full table scans - Read only postgres functions should be marked as `STABLE PARALLEL SAFE`

Can you provide more details? Inserting with unique indexes do not lock the table. Case statements are ok in where clause, use expression indexes to index it

Re: Postgres LISTEN/NOTIFY does not scale

#32

Post by RedShift1 »

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 use polling with back off up to one minute. So when a workload is done, it immediately polls for more work. If nothing found, wait for 5 seconds, still nothing 10 seconds, ... until one minute and from then on it polls every minute until it finds work again and the back off timer resets to 0 again.

Re: Postgres LISTEN/NOTIFY does not scale

#33

Post by parthdesai »

cpursley wrote: post #19
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…

How did you resolve this? Did you consider listening to the WAL?

Distributed Erlang if application is clustered, redis if it is not.

Source: Dev at one of the companies that hit this issue with Oban

Re: Postgres LISTEN/NOTIFY does not scale

#34

Post by singron »

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

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 while it runs, and I believe vacuum also holds an open transaction part of the time too. It's also nice if your database doesn't melt whenever this happens on accident.

Re: Postgres LISTEN/NOTIFY does not scale

#37

Post by incoming1211 »

dumbfounder wrote: post #35

Transactional databases are not really the best tool for writing tons of (presumably) immutable records. Why are you using it for this? Why not Elastic?

Because transactional databases are perfectly fine for this type of thing when you have 0 to 100k users.

Re: Postgres LISTEN/NOTIFY does not scale

#40

Post by andrewstuart »

0xCMP wrote: post #23
andrewstuart wrote: post #13

There’s lots of ways to invoke NOTIFY without doing it from with the transaction doing the work. The post author is too focused on using NOTIFY in only one way. This post fails to explain WHY they are sending a NOTIFY. Not much use telling us what doesn’t work without telling us the actual business goal. It’s crazy to send a notify for every transaction, they should be debounced/grouped. The point of a NOTIFY is to l…

Agreed, I am struggling to understand why "it does not scale" is not "we used it wrong and hit the point where it's a problem" here. Like if it needs to be very consistent I would use an unlogged table (since we're worried about "scale" here) and then `FOR UPDATE SKIP LOCKED` like others have mentioned. Otherwise what exactly is notify doing that can't be done after the first transaction? Edit: in-fact, how can they…

Agreed.

They’re using it wrong and blaming Postgres.

Instead they should use Postgres properly and architect their system to match how Postgres works.

There’s correct ways to notify external systems of events via NOTIFY, they should use them.

Post reply on HN