←back to thread

Postgres LISTEN/NOTIFY does not scale

(www.recall.ai)
570 points davidgu | 4 comments | 07 Jul 25 14:05 UTC | HN request time: 0.615s | source
Show context
cpursley ◴[10 Jul 25 20:42 UTC] No.44525353[source]▶
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)

replies(3): >>44525747 #>>44526330 #>>44526350 #
williamdclt ◴[10 Jul 25 21:20 UTC] No.44525747[source]▶
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

replies(3): >>44526012 #>>44528978 #>>44533458 #
1. gunnarmorling ◴[11 Jul 25 06:34 UTC] No.44528978[source]▶
pg_logical_emit_message() is how I recommend users on Postgres to implement the outbox pattern [1]. No table overhead as you say, no need for housekeeping, etc. It has some other cool applications, e.g. providing application-specific metadata for CDC streams or transactional logging, wrote about it at [2] a while ago. Another one is making sure replication slots can advance also if there's no traffic in the database they monitor [3].

[1] https://speakerdeck.com/gunnarmorling/ins-and-outs-of-the-ou...

[2] https://www.infoq.com/articles/wonders-of-postgres-logical-d...

[3] https://www.morling.dev/blog/mastering-postgres-replication-...

replies(2): >>44529870 #>>44533192 #
2. williamdclt ◴[11 Jul 25 08:58 UTC] No.44529870[source]▶
Ha, your [2] is how I learnt about it! Thanks :)
3. brightball ◴[11 Jul 25 15:22 UTC] No.44533192[source]▶
You know, this would be a great talk at the 2026 Carolina Code Conference...
replies(1): >>44542105 #
4. gunnarmorling ◴[12 Jul 25 13:55 UTC] No.44542105[source]▶
Ha, that's interesting :) Do you have any more details to that one?