Postgres LISTEN/NOTIFY does not scale

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

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.

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

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.

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

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

Ha, your [2] is how I learnt about it! Thanks :)

You know, this would be a great talk at the 2026 Carolina Code Conference...

`pg_logical_emit_message()` is great and better than `NOTIFY` in terms of how it works, but...

`pg_logical_emit_message()` perpetuates/continues the lack of authz around `NOTIFY`.

What do you mean by this? What authz would you expect/like?

I'd like to say that only some roles can NOTIFY to some channels. Similarly for alternatives to LISTEN/NOTIFY.

Right. It’s not something I’ve had to handle, I’ve always worked in environments where db clients are well behaved and under my control, what’s your use case out of interest?

Ha, that's interesting :) Do you have any more details to that one?

Security in depth. If I have to give someone login access, I should be able to control what they do.