5 ms·
Push-based outbox pattern with Postgres logical replication
- GordonS 4y agoThought this was an interesting approach, rather than the more well-known `LISTEN`/`NOTIFY` pub/sub mechanism!
- jerrysievert 4y agomuch more robust, with easier replay-ability.
- GordonS 4y agoWhich way do you mean, using WAL or Publish/Notify?
- MapleWalnut 4y agoWAL is reliable, but Publish/Notify isn't. You'll lose messages with Publish/Notify if you aren't listening for them.
- jerrysievert 4y agothis is exactly what I meant - WAL is your friend.
- thom 4y agoIf you just use notify to tell listeners “there is work in the queue to grab” can you still lose messages? I’ve always seen it implemented where workers have to go and claim items (transactionally), not just wait to be pushed them.
- SahAssar 4y agoNo, that way is safe. Just make sure that your workers also check for items in the queue when they start.
- cpursley 4y agoLISTEN/NOTIFY also has an 8000 character limitation.
- GordonS 4y agoThe way I've ways seen LISTEN/NOTIFY used is just to send a "hey, there are items available in the queue!" signal to consumers. I take your point though, that WAL is less limiting in this regard.
- maxpert 4y agoInteresting approach, I use a very similar methodology in Marmot (https://github.com/maxpert/marmot https://github.com/maxpert/marmot) to do CDC for SQLite via triggers, and then these changes are published over NATS, replicated over to other nodes. So row-level replication powered by similar outbox except it uses NATS and JetStreams's quorum property to ensure reliable recovery.
- agentultra 4y agoI wrote a Haskell library to implement this as well [0]. It's a very useful pattern. [0] https://hackage.haskell.org/package/postgresql-replicant https://hackage.haskell.org/package/postgresql-replicant
- ananthakumaran 4y agoDebezium supports outbox[1] pattern out of the box. All your application needs to do is to write to the outbox table and debezium will do the rest mentioned in the post and publish your events to kafka. 1: https://debezium.io/documentation/reference/stable/transformations/outbox-event-router.html https://debezium.io/documentation/reference/stable/transform...
- lopatin 4y agoI’m working on a db migration project from MySQL to kv database and Debezium saved the project. Don’t know how we would have done it without it. Really cool tech.
- mj2718 4y agoAlways found the first method of keeping bookmarks to the positions in a table to be the best method. I don’t think the 3 problems given to be an issue personally. - just loop at the interval that your users can accept the delay - querying a database 10 times a second is nothing, even 100 times, this wouldn’t cause resource limitations - the scaling concern made no sense to me, seems a bit arbitrary. Cool to know this is an option though, but I much prefer not relying on database internals like wal logs, this hurts scaling more IMO. The only thing you need to worry about with the table method is out of order increment IDs, which is always possible in a transactional database. But there are many solutions for this.
- uneven88 4y agoDo you have a few pointers to the solutions regarding out of order IDs? I'm thinking of keeping track of gaps (yet unseen IDs) in another column and retrieving them in the next poll.
- dagss 4y agoNot OP, but there is an approach here of using a dedicated loop worker to assign post-commit ID sequence. I.e. using the outbox pattern once, simply to assign a post-commit ID. https://github.com/vippsas/mssql-changefeed/blob/main/MOTIVATION.md https://github.com/vippsas/mssql-changefeed/blob/main/MOTIVA... I wish DBs had this more built in, it seems a critical feature of a DB these days and the commit log already have very similar sequence numbers internally...
- Redsquare 4y agoor just use a proper eventstore
- dagss 4y ago1) Well assuming data is stored in the SQL as the transactional store, how do you move data safely from SQL to the eventstore? You at least need the postbox pattern. It is not clear to me that the postbox pattern is less hacky than listening to a SQL change feed. E.g., with Azure Cosmos DB -- you would not use the postbox pattern there. You listen to the DB change feed -- since that is provided and is easily accessible. 2) In our case the schemas in SQL are mostly event based already (we prefer insert over update ...even if we do business logic/flexible querying on the data). So using an event store is mainly a duplication of the data. An event store is a database too. What exactly is it about a database that makes it a "proper event store"? I honestly think the focus on duplicating data you have in your DB in a separate event store DB too may be something of a fad that will pass in a while -- similar to NoSQL. It's needed for volumes larger than what a SQL DB can handle, but if you don't need such large volumes why introduce the extra component. Event sourcing architecture is great; but such thinking on an architecture level is really orthogonal to the storage and transport chosen.
- pweissbrod 4y agoHow would this work from a fault tolerance perspective? For example the listening application happens to be offline but the database is inserting records. How would the application catch up?
- GordonS 4y agoIt would catch up by reading the WAL files when it restarted - using this method, the WAL files will remain on disk until they've been read.
- codaphiliac 4y agobeware of subscribers being down. wal file will fill-up and then you’ll loose messages.
- GordonS 4y agoWon't it just... write more WAL files?
- jerrysievert 4y agoyes, until you run out of disk - it's happy to write as much as you can handle. but, disk isn't usually infinite.
- GordonS 4y agoWell of course, but I don't feel like this is a noteworthy limitation here; it applies to any form of queue that persists messages. I had imagined the GP might have been insinuating something like: a configurable number of WAL files would be written, then they'll be overwritten once all full.
- skrtskrt 4y agoI mean running out of disk is a danger for any persistence, that's not specific to using WAL
- dagss 4y agoThe author says the outbox pattern should "always" be used. I agree with the goal -- one should definitely never publish an event externally before it is committed to DB! But I think using a "post-commit sequence number" is even more powerful than the outbox pattern. Sadly few DBs seems to supoort this well with low latency. CosmosDB has great support for a post commit low latency change feed A hack for mssql is here: https://github.com/vippsas/mssql-changefeed/blob/main/MOTIVATION.md https://github.com/vippsas/mssql-changefeed/blob/main/MOTIVA... More about this way of publishing events: https://github.com/vippsas/zeroeventhub https://github.com/vippsas/zeroeventhub
- cpursley 4y agoI've been using this pattern with Elixir (which is well suited for this type of workload) in production. I put together a little library (which is mostly ripped off from Supabase's realtime but with some helpers): https://github.com/cpursley/walex https://github.com/cpursley/walex
- avtar 4y agoAnyone know of any Rust libraries that implement this pattern?
- seddonm1 4y agoThe Materialize team manage a fork of https://github.com/sfackler/rust-postgres https://github.com/sfackler/rust-postgres with the changes required to consume from the Postgres WAL: https://github.com/materializeInc/rust-postgres https://github.com/materializeInc/rust-postgres. Here is a comment and link to some code which seems to work: https://github.com/sfackler/rust-postgres/issues/116#issuecomment-1018486358 https://github.com/sfackler/rust-postgres/issues/116#issueco...
- avtar 4y agoThank you!
- phyzome 4y agoWhat happens if the message processing fails, e.g. your message bus broker is down -- is there a way of signalling to Postgres "hey, try me again later"?
- groodt 4y agoDoes using this approach prevent direct DML changes or migrations outside application logic?
- VincentEvans 4y agoWhat will I lose if instead of this or debezium I simply have a column representing the status of the message as “pending” or “complete”, and a job that periodically processes pending messages and marks them complete upon success, and another job that archives complete ones?
- ntqz 4y agoWhat advantage would this offer over a durable, transactional queue, like, say Pulsar? I don't see much of an advantage, and depending on how much you're pushing through your queue, I could see the outbox table causing issues with your production system if you're using that DB to hold the data.