Have you considered using LISTEN/NOTIFY for this?
Closed this issue · 3 comments
Hi, I understand you set out with CDC in mind, but on the other hand you mentioned simplicity as something desirable. I'd be interested in learning what disadvantages you see in this alternative.
Hey, I considered, but LISTEN/NOTIFY is a second-class citizen in Postgres. It's not reliable enough to use it for delivering outbox messages. They can for a pull-based approach to notify about the new records to make dynamic polling, but from my experience notify mechanism is not reliable.
The logical replication is built-in into the Postgres Write-Ahead Log. It's durable and part of the core mechanism. Postgres team is also actively working on expanding it. Each version brings new improvements.
Read more in my blog article: https://event-driven.io/en/push_based_outbox_pattern_with_postgres_logical_replication/.
It's news to me that it's unreliable, I always thought it was battle tested by these guys: https://github.com/johto/allas (I always forget which company uses it in production).
I think that if you're really good at Postgres operations, then you may make it stable enough for certain needs. Still, I wasn't able to make it reliable; when I asked around my colleagues, all of them had the same experience. Don't get me wrong; it's good tooling if asked as notifications that are not critical if they get lost. Logical replication from the design basis should be better. It also covers really well scenarios when DB or subscriber is down, as all messages are stored in the write-ahead log.