POSTGRES CHANGE FEED · 2026
pg-sync
A change-feed cursor for Postgres that does not lose rows — and a test that proves the usual one does.
- YEAR
- 2026
- ROLE
- Solo — design, implementation, tests
- STACK
- Postgres 13+ · Node.js · node:test
- LICENCE
- MIT, open source
The bug nobody sees
Almost every hand-rolled sync engine paginates its changelog on a bigserial: pull everything above the last sequence number you saw, store the new high-water mark, repeat. It works perfectly in development, where one transaction runs at a time.
Under concurrency it drops rows — with no error, no retry and no log line. Nobody finds out until a field operator reports that a job they filed is simply gone.
The interleaving
Three connections on a timeline. Connection A opens a transaction and inserts sequence 100, then stays open. Connection B inserts sequence 101 and commits. The reader pulls everything above sequence 0, sees 101, and stores 101 as its cursor. Only then does connection A commit, making sequence 100 visible. The next pull asks for everything above 101, so sequence 100 is never returned. The row is lost.
Why the sequence lies
A bigserial hands out its number the moment the INSERT runs, inside the transaction. The row becomes visible to everyone else only when that transaction commits. Between those two events any amount of time can pass — and other transactions can take later numbers and commit first.
So the changelog is ordered by allocation while the reader consumes it in order of visibility. A cursor built on the sequence assumes those are the same ordering. They are not, and the gap between them is exactly where rows fall through.
What the cursor has to be instead
The cursor becomes the pair (xid, seq), compared lexicographically — and the pull refuses to read anything at or above the oldest transaction still in flight.
select seq, xid, entity, entity_id, op, payload
from sync_changelog
where (xid, seq) > ($1::xid8, $2::bigint)
and xid < pg_snapshot_xmin(pg_current_snapshot())
order by xid, seq
limit $3;Transaction ids increase from left to right, split by the snapshot xmin marker. Everything below the marker has already committed or aborted and can never change, so it is safe to read. Everything at or above the marker is still in flight and may commit in any order, so the pull excludes it entirely.
Why the pair, and why xid8
xid alone is not enough. Every row written by one transaction shares its xid, so a cursor that tracks only xid cannot resume in the middle of a large transaction without either skipping rows or replaying them. seq is the tiebreaker inside a single xid — which is the one job a bigserial is actually good at.
It is xid8 rather than xid because xid is 32 bits and wraps around. xid8 is 64 bits and, in practice, never does. Both halves are carried as strings and compared as BigInt, because past 2^53 a JavaScript Number quietly stops being able to tell two cursors apart.
And the snapshot is read with pg_current_snapshot() rather than pg_current_xact_id(). The latter assigns a real transaction id to its caller — which would turn this read-only pull into a writing transaction, one that then holds the watermark down for everybody else.
How it is proven
Both strategies run the same interleaving. The first test passes by proving the loss — it asserts the row is gone. The second asserts that same row arrives, on the same interleaving, through the correct pull.
The ordering is decided rather than hoped for: three explicit connections, never a pool, which would take the ordering decision away. No sleeps, no retries, no polling. That is what makes it a regression test instead of a demo that reproduces when it feels like it.
✔ seq cursor drops the row from the long-running transaction (63.8ms)
✔ (xid, seq) cursor with the xmin watermark loses nothing (38.3ms)
✔ paging with a limit neither skips nor replays across transaction boundaries (38.2ms)Where the boundaries are
A pipeline in four stages. Postgres holds the changelog table. src/changelog.js is the only module that speaks SQL. src/cursor.js is pure arithmetic with no database and no domain knowledge. The consumer receives a single opaque cursor string to persist.
Two boundaries hold. Everything else is negotiable.
- 01
The core does no I/O
No
pg, no network.test/cursor-unit.test.jsruns with no database at all, which is what keeps this true instead of aspirational. - 02
The core knows no domain
Entities are strings, payloads are opaque. Nothing in
cursor.jshas heard of the application sitting on top of it.
Get those two right and the same core runs on Node, in a browser, and on a device. Get them wrong and no directory layout saves you.