An outbox poller that reads WHERE id > last_seen_id loses events in Postgres, and it takes two transactions to prove it. The first took id = 1 and committed three seconds late. The second took id = 2 and committed at once, so the poller advanced its cursor to 2 and never published the payment.captured event in row 1.
Most pages ranking for "transactional outbox pattern" explain the dual-write problem and stop there. Of the seven top results I checked, one mentions this skip, via a link. None warns that the CDC alternative can fill your disk, and none is written for payment events, where a lost message is a merchant who never hears a refund cleared. So I ran the failure modes in a throwaway Postgres 18.6 container. Unless I say otherwise, the output below is pasted from those runs.
What Is the Transactional Outbox Pattern?
When a capture succeeds, a payment service posts ledger entries and tells everything downstream: webhooks, reconciliation, the warehouse. Commit to Postgres and then publish to Kafka, and a crash between the two leaves money moved with no event. Publish first, and a rollback announces money that never moved. Kafka transactions are atomic across Kafka partitions only, so two-phase commit is off the table.
The outbox puts the event inside the same database transaction:
BEGIN;
INSERT INTO ledger_entries (account, amount) VALUES ('acc_123', 1000), ('clearing', -1000);
INSERT INTO outbox (aggregate_id, type, payload)
VALUES ('pay_8f2c', 'payment.captured', '{"amount":1000,"currency":"GBP"}');
COMMIT;
A relay then publishes committed rows. That gives at-least-once delivery, because the relay can crash after publishing and before recording progress. Every interesting decision sits in how the relay reads the table.
Why Does an Outbox Poller Skip Events in Postgres?
A sequence hands out its value when the row is inserted, but the row only becomes visible when its transaction commits. BIGSERIAL is a sequence, so id order is insert order, not commit order:
B got id=2 txid=755
--- t~1s naive poller (id > 0):
2|pay_B
A got id=1 txid=754
--- t~4s naive poller resumes from high-water mark id > 2:
(empty = pay_A skipped forever)
A transaction that does more work after its outbox insert, such as a fraud-score call before the final ledger write, can commit after a later one. The poller sees a gap at id = 1 that looks exactly like a rolled-back insert, so it cannot tell a lost event from a harmless hole. A created_at cursor has the same flaw, plus clock skew between pods.
Read the full article on tomcn.uk →
About the Author
I'm Tom Wang, an AI Developer & Fintech Developer — building AI agents, crypto payment infrastructure, and cross-border payout systems with Rust, Go, and TypeScript. Based in London, UK.
Currently open to new opportunities in fintech, crypto payments, and AI agent engineering.
Top comments (0)