[HN Gopher] Postgres LISTEN/NOTIFY does not scale
___________________________________________________________________
Postgres LISTEN/NOTIFY does not scale
Author : davidgu
Score : 210 points
Date : 2025-07-07 14:05 UTC (3 days ago)
(HTM) web link (www.recall.ai)
(TXT) w3m dump (www.recall.ai)
| hombre_fatal wrote:
| Interesting. What if you just execute `NOTIFY` in its own
| connection outside of / after the transaction?
| soursoup wrote:
| Isn't it standard practice to have a separate TCP stream for
| NOTIFY or am I mistaken
| remram wrote:
| You mean for LISTEN?
| nick_ wrote:
| My thought as well. You could add notify commands to a temp
| table during the transaction, then run NOTIFY on each row in
| that temp table after the transaction commits successfully?
| foota wrote:
| Wouldn't you need to then commit to remove the entries from
| the temp table?
| zbentley wrote:
| No, so long as the rows in there are transactionally
| guaranteed to be present or not, a sweeper script can
| handle removing failed "publishes" (notifys that didn't
| delete their row) later.
|
| This does sacrifice ordering and increases the risk of
| duplicates in the message stream, though.
| zbentley wrote:
| This is roughly the "transactional outbox" pattern--and an
| elegant use of it, since the only service invoked during the
| "publish" RPC is also the database, reducing distributed
| reliability concerns.
|
| ...of course, you need dedup/support for duplicate messages
| on the notify stream if you do this, but that's table stakes
| in a lot of messaging scenarios anyway.
| parthdesai wrote:
| You lose transactional guarantees if you notify outside of the
| transaction though
| hombre_fatal wrote:
| Yeah, but pub/sub systems already need to be robust to missed
| messages. And, sending the notify after the transaction
| succeeds usually accomplishes everything you really care
| about (no false positives).
| parthdesai wrote:
| What happens when transaction succeeds but the execution of
| NOTIFY fails if it's outside of transaction, in it's own
| separate connection?
| polote wrote:
| Rls and triggers dont scale either
| shivasaxena wrote:
| Yeah, I'm going to remove triggers in next deploy of a POS
| system since they are adding 10-50ms to each insert.
|
| Becomes a problem if you are inserting 40 items to order_items
| table.
| GuinansEyebrows wrote:
| that, and keeping your business logic in the database makes
| everything more opaque!
| brikym wrote:
| Have you tried deferring them?
| candiddevmike wrote:
| How do you handle trigger logic that compares old/new without
| having a round trip back to the application?
| Spivak wrote:
| Neither do foreign keys the moment you need to shard. Turns out
| that there's no free lunch when you ask your database to do
| "secret extra work" that's supposed to be transparent-ish to
| the user.
| mulmen wrote:
| [delayed]
| cpursley wrote:
| Right, plus there's character limitations (column size). This is
| why I prefer listening to the Postgres WAL for database changes:
|
| https://github.com/cpursley/walex?tab=readme-ov-file#walex
| (there's a few useful links in here)
| williamdclt wrote:
| I found recently that you can write directly to the WAL with
| transactional guarantees, without writing to an actual table.
| This sounds like it would be amazing for queue/outbox purposes,
| as the normal approaches of actually inserting data in a table
| cause a lot of resource usage (autovacuum is a major concern
| for these use cases).
|
| Can't find the function that does that, and I've not seen it
| used in the wild yet, idk if there's gotchas
|
| Edit: found it, it's pg_logical_emit_message
| cyberax wrote:
| One annoying thing is that there is no counterpart for an
| operation to wait and read data from WAL. You can poll it
| using pg_logical_slot_get_binary_changes, but it returns
| immediately.
|
| It'd be nice to have a method that would block for N seconds
| waiting for a new entry.
|
| You can also use a streaming replication connection, but it
| often is not enabled by default.
| williamdclt wrote:
| I think replication is the way to go, it's kinda what it's
| for.
|
| Might be a bit tricky to get debezium to decode the logical
| event, not sure
| denysonique wrote:
| For node.js users there is postgres.js that can listen to the
| Postgres WAL and emit node events that can be handled by
| application code.
| meesles wrote:
| Yeah until vendors butcher Postgres replication behaviors and
| prevent common paths of integrating these capabilities into
| other tools. Looking at you AWS
| CaliforniaKarl wrote:
| I appreciate this post for two reasons:
|
| * It gives an indication of how much you need to grow before this
| Postgres functionality starts being a blocker.
|
| * Folks encountering this issue--and its confusing log line--in
| the future will be able to find this post and quickly understand
| the issue.
| h1fra wrote:
| 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.
| GuinansEyebrows wrote:
| 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).
| NightMKoder wrote:
| Facebook's wormhole seems like a better approach here - just
| tailing the MySQL bin log gets you commit safety for messages
| without running into this kind of locking behavior.
| mulmen wrote:
| Sounds like one centralized Postgres instance, am I understanding
| that correctly? Wouldn't meeting bots be very easy to parallelize
| across single-tenant instances?
| supportengineer wrote:
| 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.
| andrewstuart wrote:
| 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.
| 0xCMP wrote:
| 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 send an HTTP call for something and
| not be able to do a `NOTIFY` after as well?
|
| One possible way I could understand what they wrote is that
| somewhere in their code, within the same transaction, there are
| notifies which conditionally trigger and it would be difficult
| to know which ones to notify again in another transaction after
| the fact. But they must know enough to make the HTTP call, so
| why not NOTIFY?
| andrewstuart wrote:
| 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.
| tomrod wrote:
| Assuming you skip select transaction, or require logging on it
| because your regulated industry had bad auditors, then every
| transaction changes something.
| thom wrote:
| Yeah, the way I've always used LISTEN/NOTIFY is just to tell
| some pool of workers that they should wake up and check some
| transactional outbox for new work. False positives are
| basically harmless and therefore don't need to be
| transactional. If you're sending sophisticated messages with
| NOTIFY (which is a reasonable thing to think you can do) you're
| probably headed for pain at some point.
| sorentwo wrote:
| 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.
| cpursley wrote:
| How did you resolve this? Did you consider listening to the
| WAL?
| sorentwo wrote:
| We have Postgres based pubsub, but encourage people to use a
| distributed Erlang based notifier instead whenever possible.
| Another important change was removing insert triggers,
| partially for the exact reasons mentioned in this post.
| parthdesai wrote:
| Distributed Erlang if application is clustered, redis if it
| is not.
|
| Source: Dev at one of the companies that hit this issue with
| Oban
| alberth wrote:
| I didn't realize Oban didn't use Mnesia (Erlang built-in).
| sorentwo wrote:
| Very very few applications use mnsesia. There's absolutely no
| way I would recommend it over Postgres.
| cshimmin wrote:
| 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?
| GuinansEyebrows wrote:
| possibly, but i think at that point it would make more sense to
| move the business logic outside of the database (you can wait
| for a successful commit before triggering an external process
| via the originating app, or monitor the WAL with an external
| pub/sub system, or something else more clever than i can think
| of).
| leontrolski wrote:
| 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
| aurumque wrote:
| I'll take the shameless plug. Thank you for putting this
| together! Very helpful overview of pg locks.
| cpursley wrote:
| Have you played with pgmq? It's pretty neat:
| https://github.com/pgmq/pgmq
| edoceo wrote:
| Another thing for @leontrolski to add to the benchmarks -
| which I cannot wait to read.
| RedShift1 wrote:
| 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.
| singron wrote:
| 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.
| shivasaxena wrote:
| 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`
| franckpachot wrote:
| 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
| cellis wrote:
| It does scale. Just not to recall levels of traffic. Come on guys
| let's not rewrite everything in cassandra and rust now.
| dumbfounder wrote:
| 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?
| incoming1211 wrote:
| Because transactional databases are perfectly fine for this
| type of thing when you have 0 to 100k users.
| Kwpolska wrote:
| [citaiton needed]
| anonu wrote:
| was hoping the solution was: we forked postgres.
|
| cool writeup!
| threecheese wrote:
| I had a similar thought, as I was clicking through to TFA;
| "NOTIFY does not scale, but our new Widget can! Just five
| bucks"
| randall wrote:
| wow thanks for the heads up! no idea this was a thing.
| wordofx wrote:
| It's not a thing.
| 0xbadcafebee wrote:
| RBDMS are not designed for write-heavy applications, they are
| designed for read-heavy analysis. Also, an RDBMS is not a message
| queue or an RPC transport.
|
| I feel like somebody needs to write a book on system architecture
| for Gen Z that's just filled with memes. A funny cat pic telling
| people not to use the wrong tool will probably make more of an
| impact than an old fogey in a comment section wagging his finger.
| hombre_fatal wrote:
| But those rules of thumb aren't true. People use Postgres for
| job queues and write-heavy applications.
|
| You'd have to at least accompany your memes with empirics. What
| is write-heavy? A number you might hit if your startup succeeds
| with thousands of concurrent users on your v1 naive
| implementation?
|
| Else you just get another repeat of everyone cargo-culting
| Mongo because they heard that Postgres wasn't web scale for
| their app with 0 users.
| kccqzy wrote:
| There are OLTP and OLAP RDBMSes. Only OLAP ones are designed
| for read-heavy analyses.
| doc_manhat wrote:
| 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 due diligence on before
| implementing a write heavy work load.
| kccqzy wrote:
| I think it's just difficult to predict how heavy is heavy
| enough to make this a problem. FWIW I had worked at a startup
| with a much more primitive data storage system where serialized
| commits were actually totally fine. The startup never outgrew
| that bottleneck.
___________________________________________________________________
(page generated 2025-07-10 23:00 UTC)