Rls and triggers dont scale either
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.
Postgres LISTEN/NOTIFY does not scale
21–30 of 328 posts
Re: Postgres LISTEN/NOTIFY does not scale
#22Out 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`
Re: Postgres LISTEN/NOTIFY does not scale
#23There’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 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?
Re: Postgres LISTEN/NOTIFY does not scale
#24If 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?
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).
Re: Postgres LISTEN/NOTIFY does not scale
#25It does scale. Just not to recall levels of traffic. Come on guys let's not rewrite everything in cassandra and rust now.
Re: Postgres LISTEN/NOTIFY does not scale
#26Rls and triggers dont scale either
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.
that, and keeping your business logic in the database makes everything more opaque!
Re: Postgres LISTEN/NOTIFY does not scale
#27Right, 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)
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
Re: Postgres LISTEN/NOTIFY does not scale
#28Postgres 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?
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.
Re: Postgres LISTEN/NOTIFY does not scale
#29There’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…
Assuming you skip select transaction, or require logging on it because your regulated industry had bad auditors, then every transaction changes something.
Re: Postgres LISTEN/NOTIFY does not scale
#30Rls and triggers dont scale either
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.