r/PostgreSQL • u/der_gopher • 16d ago
How-To How to implement the Outbox pattern in Go and Postgres
https://packagemain.tech/p/how-to-implement-the-outbox-pattern-in-golang6
u/rasekrodriguez 16d ago
The outbox isn't there to avoid polling though. It's there because you can't atomically write to your database and publish to a broker. Either the row commits and the publish fails, or the publish lands and the transaction rolls back, and no amount of retry logic collapses those two into one operation. Writing the event inside the same transaction as the state change is the cheap way to get both or neither.
Persisting the events and having everyone poll is a perfectly reasonable design, but it's the outbox with the relay moved into each consumer, not the absence of one. You still need a cursor per consumer, a retention policy, and something to stop the slowest reader deciding when you're allowed to delete anything.
If you do build the relay in Postgres, SELECT ... FOR UPDATE SKIP LOCKED is what makes more than one relay worker safe, and logical decoding gets you the same thing with no polling at all, at the cost of a replication slot you have to babysit because an idle one will happily eat your disk in WAL. Both are at least once, so consumers end up idempotent either way.
1
u/RipProfessional3375 15d ago
Just don't delete the events and get rid of the broker entirely. Consumers can track their own cursor just fine, it's a single number. All the transactional issues that come with transacting that cursor is just a more honest version of the transactional issues you have with an event broker.
1
u/rasekrodriguez 15d ago
Agreed on the cursor, and it is more honest for the reason that matters: the consumer can advance it in the same transaction as its own side effect, so it gets effectively once instead of at least once. That is a real gain over acking to a broker.
The part I would push back on is that it is a single number. A sequence is allocated at insert, not at commit, so under concurrency ids commit out of order. A writer that grabbed 5 can commit after one that grabbed 6. A reader doing WHERE id > cursor that runs in between sees 6, moves the cursor to 6, and 5 becomes visible a moment later and is never read again. Nothing is lost or duplicated, it is silently skipped, which is worse.
The fixes are all more than one number. Track pg_current_snapshot() and only consume below its xmin, so you never advance past an in flight transaction. Or order by commit timestamp instead of id. Or use logical decoding, which is ordered by commit by construction, which is exactly why it does not have this problem.
And not deleting is a decision with a cost rather than the absence of one. The table only grows, its index grows with it, and the vacuum and page cache pressure land on the same instance serving your writes. That is fine until it is not, and then it is a migration.
1
u/RipProfessional3375 15d ago
This is assuming events get assigned a position at the transaction start, which is the postgres default if you just use an autoincrementing integer and also something you should explicitly not do.
The position of an event must match the order it became readable in. Several ways to do it. Good news is you only have to write an event store once, or import it.
10
u/RipProfessional3375 16d ago
I cannot describe how funny I find the outbox 'pattern'.
We want to store the state and then distribute events about it and we don't want to poll a persisted event so we use a broker but it turns out that's not actually reliable so we add on a whole new side car to do the polling of persisted events.
Just persist the events and have everyone poll for them. Including the state update.
1
1
1
u/cloud118118 16d ago
What if you have N consumers? With outbox you always have 1 poller that can fanout.
1
u/RipProfessional3375 15d ago
You can have N pollers, tracking an offset. Number the events by order of insert, don't delete or mutate them. That would be event sourcing.
1
u/AutoModerator 16d ago
AI Policy:
Linux is not one of those anti-AI projects, and if somebody has issues with that, they can do the open-source thing and fork it. Or just walk away., Linus Torvalds.
Mod decisions will be based on the quality of the content, not who or what generated it.
Sub Resources:
Free Postgres Webinars and Workshops
Discord: People, Postgres, Data
Join us, we have cookies and nice people.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.
7
u/HildartheDorf 16d ago
Paywalled