Postgres LISTEN/NOTIFY does not scale • Hacker News Board

Re: Postgres LISTEN/NOTIFY does not scale

#11

Post by GuinansEyebrows » Thu, Jul 10, 2025, 8:52 PM UTC

h1fra wrote: post #8

You had one problem with listen notify which was a fair one, but now you have a problem with http latency, network issues, DNS, retries, self-DDoS, etc.

it sounds like the impact of LISTEN/NOTIFY scaling issues was much greater on the overall DB performance than the actual load/scope of the task being performed (based on the end of the article), and they're aware that if they needed something more performant for that offloaded task, they have options (pub/sub via redis or w/e).

Re: Postgres LISTEN/NOTIFY does not scale

#12

Post by supportengineer » Thu, Jul 10, 2025, 8:52 PM UTC

LISTEN/NOTIFY isn’t just a lock-free trigger. It can jeopardize concurrency under load.

Features that seem harmless at small scale can break everything at large scale.

Re: Postgres LISTEN/NOTIFY does not scale

#13

Post by andrewstuart » Thu, Jul 10, 2025, 8:54 PM UTC

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 let some other system know something has changed. Don’t do it every transaction.

Re: Postgres LISTEN/NOTIFY does not scale

#14

Post by sorentwo » Thu, Jul 10, 2025, 8:55 PM UTC

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 apps didn’t weren’t clustered. Thanks to the rise of platforms like Fly that made it easier, and the decline of Heroku that made it nearly impossible.

Re: Postgres LISTEN/NOTIFY does not scale

#15

Post by remram » Thu, Jul 10, 2025, 8:57 PM UTC

soursoup wrote: post #4

hombre_fatal wrote: post #2

Interesting. What if you just execute `NOTIFY` in its own connection outside of / after the transaction?

Isn’t it standard practice to have a separate TCP stream for NOTIFY or am I mistaken

You mean for LISTEN?

Re: Postgres LISTEN/NOTIFY does not scale

#16

Post by cshimmin » Thu, Jul 10, 2025, 9:00 PM UTC

If I understood correctly, the global lock is so that notify events are emitted in order. Would it make sense to have a variant that doesn't make this ordering guarantee if you don't care about it, so that you can "notify" within transactions without locking the whole thing?

Re: Postgres LISTEN/NOTIFY does not scale

#17

Post by leontrolski » Thu, Jul 10, 2025, 9:00 PM UTC

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.github.io/pglockpy.html

Re: Postgres LISTEN/NOTIFY does not scale

#18

Post by aurumque » Thu, Jul 10, 2025, 9:03 PM UTC

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.

Re: Postgres LISTEN/NOTIFY does not scale

#19

Post by cpursley » Thu, Jul 10, 2025, 9:09 PM UTC

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?

Re: Postgres LISTEN/NOTIFY does not scale

#20

Post by cpursley » Thu, Jul 10, 2025, 9:10 PM UTC

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

Have you played with pgmq? It's pretty neat: https://github.com/pgmq/pgmq