21 ms·
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
by williamdclt 1y 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
- cyberax 1y agoOne 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.
- williamdclt 1y agoI 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
- cyberax 1y agoSure, 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.
- gunnarmorling 1y agoDebezium handles logical decoding messages OOTB. There's also an SMT (single message transform) for decoding the binary payload: https://debezium.io/documentation/reference/stable/transformations/decode-logical-decoding-message-content.html https://debezium.io/documentation/reference/stable/transform....
- gunnarmorling 1y agopg_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-outbox-pattern?slide=24 https://speakerdeck.com/gunnarmorling/ins-and-outs-of-the-ou... [2] https://www.infoq.com/articles/wonders-of-postgres-logical-decoding-messages/ https://www.infoq.com/articles/wonders-of-postgres-logical-d... [3] https://www.morling.dev/blog/mastering-postgres-replication-slots/ https://www.morling.dev/blog/mastering-postgres-replication-...
- williamdclt 1y agoHa, your [2] is how I learnt about it! Thanks :)
- brightball 1y agoYou know, this would be a great talk at the 2026 Carolina Code Conference...
- gunnarmorling 1y agoHa, that's interesting :) Do you have any more details to that one?
- brightball 1y agoTraveling now, but I’ll connect when I get back
- brightball 1y agoI don't see any contact info in your profile, but we typically open the call for speakers from early January - end of March. https://carolina.codes https://carolina.codes
- cryptonector 1y ago`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`.
- williamdclt 1y agoWhat do you mean by this? What authz would you expect/like?
- cryptonector 1y agoI'd like to say that only some roles can NOTIFY to some channels. Similarly for alternatives to LISTEN/NOTIFY.
- williamdclt 1y agoRight. 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?
- cryptonector 1y agoSecurity in depth. If I have to give someone login access, I should be able to control what they do.