Comment by williamdclt
2 days ago
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
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-...
Ha, your [2] is how I learnt about it! Thanks :)
You know, this would be a great talk at the 2026 Carolina Code Conference...
Ha, that's interesting :) Do you have any more details to that one?
`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.
1 reply →
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
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....
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.