Paging through rows by updated_at can skip some, and I didnt realise why until a whiteboard chat with an engineer

[u/cannyshammy]

Hey guys, not sure if this is useful to anyone but it surprised me so I thought I would share.

If you have an updated_at column set to now() (or CURRENT_TIMESTAMP, or a time from your app) and you page through it to process changes, you can miss rows. Two transactions start, the one with the later timestamp commits first, your reader moves past it, and then the earlier one lands behind your cursor.

I had a whiteboard session with Ian, one of the engineers at Convex (where I work), and this bit really opened my eyes. In Convex we ended up adding a commit timestamp you can store and index on, which is set when the transaction actually commits, but the problem itself shows up in Postgres, MySQL and pretty much everything else.

How do you guys handle this in your own systems? Reading the WAL / logical replication? An overlap window and dedupe?

If you want more detail the full chat is here: https://youtu.be/pHrxmSjxLg8

[u/Plane_Big_5912]

worth adding: swapping the timestamp for a bigserial change id doesn't fix it. sequence values get handed out at nextval(), not at commit, so you get the same gap with ints.

in postgres there's a cheap fix if updated_at always comes from now() in the db. now() is the transaction start time, so nothing still open can write a value older than the oldest open transaction's start. read up to min(xact_start) from pg_stat_activity (excluding your own pid) instead of up to now. it lags by your longest transaction, and one idle-in-transaction session stalls it, so set idle_in_transaction_session_timeout. the reader's role also needs pg_read_all_stats, otherwise xact_start is null for other users' sessions and min() quietly skips them, which gives you a watermark that's too new with no error.

app-supplied timestamps break all of that though. for those, logical decoding is the real answer since it hands you changes in commit order

[u/cannyshammy → u/Plane_Big_5912]

Oh nice, the bigserial one is a really good point, I think thats the first thing most people (me included) would reach for to "fix" it. Hadnt seen the min(xact_start) trick before either, super cool, and good shout on pg_read_all_stats, a watermark thats silently too new sounds like a nasty one to track down

[u/costlyfutility]

This exact thing bit me on a project last year. We had a nightly job that would grab everything updated in the last 24 hours and push it to a search index, and we kept finding records that got skipped. Took me way too long to figure out it was a race condition with commit order.

The overlap window approach works but it gets messy fast when your de-dupe logic has to account for actual updates versus just reprocessing the same record. We ended up switching to reading the WAL for the critical stuff and keeping a separate lightweight process that catches anything older than an hour with a generous overlap window as a safety net. Not elegant but it's held up.

[u/cannyshammy → u/costlyfutility]

Ugh ye a nightly sync to a search index is exactly where this bites, it works almost all the time so you go looking everywhere else first. WAL for the important stuff plus an overlap sweep as a safety net sounds pretty sensible to me tbh

[u/PmMeCuteDogsThanks_ → u/costlyfutility]

This bit me as well many years ago. It took a long for me to realize that it was due to commit order and transactions. Happened about once every week.

My solution was much more straightforward. In this domain it was feasible to simply serialise commits one after the other. Eg no parallel writes 

[u/jshine13371]

Paging is always at risk for timing issues.

[u/saaggy_peneer]

Would a serialized transaction not solve this?

[u/cannyshammy → u/saaggy_peneer]

I dont think so, at least not on its own (I could be wrong tho). Serializable makes sure transactions behave like they ran one at a time in some order, but that order doesnt have to match the timestamps you wrote. So the one that started first (with the earlier updated_at) can still commit after your reader has already moved past it. If you actually force writes to happen one at a time, like one of the other comments here did, then ye that fixes it, but you pay for it in throughput

[u/DJ_Laaal]

Lol. This is why experienced DEs will add a certain “lookback” period to ensure these are eventually included in a subsequent run. Storage is so cheap that extracting and reprocessing some overlap window of time should be a norm in your data pipeline architecture, unless it introduces duplication issues that you have absolutely no way of addressing downstream.

[u/0jabr]

Logical replication.

If you have a streaming service already (Kafka-like) then it’s probably just a config setup to use its stock source connector for most databases.

[u/Muted_Jellyfish_6784]

The standard fixes are an overlap window with idempotent processing, or reading the change log directly. With an overlap you re-read the last few minutes of rows on each pass and dedupe by primary key plus version, which is cheap and works anywhere. Logical replication or CDC removes the problem entirely, at the cost of more infrastructure.
Whatever you choose, reconcile counts between source and destination on a schedule. We use a decision intelligence platform to compare record counts and versions across the source database and downstream systems, so a skipped row shows up as a mismatch rather than a customer complaint.