←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.725s | 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. cyberax ◴[10 Jul 25 21:50 UTC] No.44526012[source]▶
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.

replies(1): >>44526264 #
2. williamdclt ◴[10 Jul 25 22:13 UTC] No.44526264[source]▶
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

replies(2): >>44527457 #>>44528991 #
3. cyberax ◴[11 Jul 25 01:10 UTC] No.44527457[source]▶
Sure, but the replication protocol requires a separate connection. And the annoying part is that it requires a separate `pg_hba.conf` entry to be allowed. So it's not enabled for IAM-based connections on AWS, for example.

pg_logical_slot_get_binary_changes returns the same entries as the replication connection. It just has no support for long-polling.

4. gunnarmorling ◴[11 Jul 25 06:36 UTC] No.44528991[source]▶
Debezium handles logical decoding messages OOTB. There's also an SMT (single message transform) for decoding the binary payload: https://debezium.io/documentation/reference/stable/transform....