The Skipped Outbox Row

An order service writes the business row and its event in one transaction, which is the outbox pattern done right:

BEGIN;
INSERT INTO orders (id, user_id, total_cents) VALUES ($1, $2, $3);
INSERT INTO outbox (payload) VALUES ($4);   -- id BIGSERIAL PRIMARY KEY
COMMIT;

The relay polls and remembers a high-water mark:

-- every 200ms, with :last held in the relay's memory
SELECT id, payload FROM outbox WHERE id > :last ORDER BY id LIMIT 500;
-- then: last = max(id) of the returned rows

Under load, a small fraction of events are never published. Nothing errors and no row is missing from outbox. Diagnose it.

The relay reads from a streaming replica, so replication lag hides the newest rows. Pointing the poll at the primary removes the window.

nextval hands out ids before commit, so commit order need not match id order. A slow transaction holding id 104 can commit after one holding 107; if the relay polled in between it has already moved :last to 107 and will never look at 104 again. Track a per-row sent_at instead of one cursor.

The outbox insert is trapped inside the business transaction, so the relay cannot see it until commit. Writing the event on its own connection makes it visible immediately and the skipping stops.

Rolled back transactions burn sequence values, so outbox has gaps. The relay stalls at each missing id waiting for it to appear and eventually gives up on the rows behind it.