The Setup: How We Fool Ourselves Into Missing Data

The pattern is everywhere. You have a table with an updated_at timestamp, and you process changes by paging through rows ordered by that timestamp:

-- Process everything updated after our last checkpoint
SELECT * FROM orders
WHERE updated_at > :last_processed
ORDER BY updated_at ASC
LIMIT 1000;

Seems innocent enough. The application sets updated_at at write time, the cursor advances past what you’ve processed, and new updates fall naturally after your checkpoint. What could possibly go wrong?

The Race You Never See Coming

Two transactions start at nearly the same moment. Transaction A begins first, ticking updated_at = 10:00:01 (let’s just say a transaction start time). Transaction B starts a millisecond later, setting updated_at = 10:00:02.

But here’s the twist: transaction B commits first. Both transactions call their COMMIT, and the database makes B’s changes visible before A’s.

Your paging job runs at 10:00:03. It’s already processed everything up to updated_at = 10:00:02 (including B’s data, which was visible). A hasn’t committed yet, so its record isn’t visible to your reader. The cursor advances past 10:00:02, and A’s row, still sitting at 10:00:01 in the database, is now behind your cursor.

When A finally commits at 10:00:04, its record lands with a timestamp that your job considers “already processed.” The record is silently skipped.

This isn’t a hypothetical edge case. It’s a race condition that bites real systems with real consequences. The folks over at r/Database discussed this exact issue, with one engineer sharing how a nightly job pushing updates to a search index kept skipping records, took them way too long to figure out it was a race condition with commit order.

The problem shows up in PostgreSQL, MySQL, and pretty much any relational database where the application controls the timestamp. It’s not a vendor bug, it’s a fundamental mismatch between when you stamp a transaction and when it becomes visible.

It’s Not Just updated_at: The Sequence Trap

You might be thinking: “Fine, I’ll switch to a monotonic integer. Problem solved.”

Not so fast. Swapping updated_at for a bigserial ID doesn’t actually fix anything. Sequence values are handed out at nextval(), which happens when the statement executes, not when the transaction commits. You get the exact same gap, just with integers instead of timestamps.

Transaction A pulls sequence value 100. Transaction B pulls 101. B commits first, your reader processes 101, then A commits with its value of 100 sitting behind your cursor. Same race, different type.

The Postgres-Specific Workaround (and Its Pitfalls)

There’s a clever trick in PostgreSQL that exploits a specific behavior: now() returns the transaction start time, not the statement time. That means nothing still open can write a value older than the oldest open transaction’s start.

So instead of reading up to now(), you read up to the minimum transaction start time from pg_stat_activity:

SELECT min(xact_start) 
FROM pg_stat_activity 
WHERE pid <> pg_backend_pid();

This gives you a watermark that lags behind by the duration of your longest-running transaction. Any transaction that commits with a timestamp before that watermark is guaranteed to already be visible to your reader.

But there’s a catch: one idle-in-transaction session stalls your entire pipeline. One developer who opens a transaction, forgets to commit, and walks away for lunch means your paging job churns through nothing for hours. You need idle_in_transaction_session_timeout configured aggressively.

And there’s a sneakier gotcha: the reader’s role needs pg_read_all_stats, otherwise xact_start comes back null for other users’ sessions, and min() silently ignores them. Your watermark becomes too new, with no error, no warning, just silently broken pagination.

Why App-Supplied Timestamps Are the Real Villain

The workaround above only works when updated_at comes from now() inside the database. If your application sets the timestamp (e.g., DateTime.Now in C# or new Date() in JavaScript) and passes it as a parameter, the entire premise breaks down.

App-supplied timestamps introduce a whole dimension of chaos. The front-office project on GitHub discovered this the hard way: a transaction’s date depended on the session’s time zone, so a New York build dated 28 transactions a day early. The DuckDB session’s time zone was leaking into the data, shifting to_timestamp(ms/1000)::timestamp semantics.

The numbers tell the story: 28 of 737 real transactions landed on the wrong date. Every one was exactly one day early, at Eastern times between 00:15 and 03:09. CI ran in UTC where the cast happened to work, so no test caught it.

This isn’t just about pagination ordering, it’s about the fundamental semantics of what a timestamp means. The distinction between “when the event happened” and “when the database made it visible” isn’t a trivia question, it’s the difference between correct data and silently corrupted analytics.

What Actually Works: Commit-Time Tracking

At Convex, where this whiteboard session originated from, the engineering team added a commit timestamp that’s set when the transaction actually commits, not when it starts, not when the application stamps it, but at the moment the database finalizes the write. This gives you:

1. An ordering that matches visibility: If your cursor is at commit timestamp X, everything before X is guaranteed visible.
2. Indexability: You can index on it and paginate efficiently.
3. No race window: The value doesn’t exist until the record is actually visible.

The MongoDB approach is comparable: the $clusterTime and per-document timestamp tracking ensure that a cursor’s position corresponds to what’s observable.

Alternatives Worth Considering

Logical Replication / WAL Reading

If you’re on Postgres, logical replication hands you changes in commit order. That’s the gold standard for change capture, the WAL already encodes the commit sequence, and you’re reading it in the same order the database made it visible. This is what real-time event-driven pipelines using Change Data Capture for accurate change tracking rely on, and it scales to high-throughput workloads.

The trade-off is operational complexity. You need a replication slot, a consumer that handles the stream, and the infrastructure to manage it. Some teams find this acceptable, others prefer simpler approaches.

The Overlap Window + Dedupe (The Pragmatic Hack)

One approach that emerged from the trenches: run your paging job with a generous overlap window, say, an hour before your last checkpoint, and deduplicate on the consumer side. It works, but it gets messy fast when the deduplication logic has to distinguish between “we already processed an update to this record” and “this is a genuinely new update to the same record.”

The Serialization Gambit

There’s an approach one engineer shared: simply forbid concurrent writes for the affected tables and serialize commits one after another. Crude, but effective. If your domain can tolerate it (e.g., low write volume on the critical table), it eliminates the race entirely. It’s like solving a distributed systems problem by removing the “distributed” part, laughable in general, brilliant when it works.

The Deeper Issue: Timestamp Provenance

The TransactionProcessing project on GitHub frames this more broadly: you need to distinguish between when the event happened and when the host received it. Their proposed fix, preserve POS-reported timestamps with offset and provenance, store a canonical UTC instant for ordering, and keep host receipt time separate, is the right architectural instinct.

In a distributed system, a timestamp is not just a point in time. It’s a statement about who observed something, when they observed it, and what their clock said. Collapsing all of that into a single DateTime column is asking for trouble.

This connects to event-driven architectures and projection-based read models for reliable data processing, when you build projections on top of unreliable change tracking, you’re architecting for eventual inconsistency.

Practical Recommendations

1. If you control the database: Use the now()-based watermark approach with pg_stat_activity, and enforce idle_in_transaction_session_timeout. Set pg_read_all_stats on your reader role. Add a test that actually exercises the race condition by holding transactions open.
2. If you control the application: Switch from DateTime.Now to DateTimeOffset.Now. Send timestamps with explicit offsets, reject offset-free timestamps at the API boundary. Normalize to UTC inside the database, but keep the provenance.
3. If you’re building for the long term: Invest in logical replication or CDC infrastructure. It’s the only approach that gives you commit order without the caveats of timestamp math.
4. If you’re stuck with the current design: Implement an overlap-and-dedupe window as a safety net, and accept that the incremental processing logic may need periodic reconciliation.

The Takeaway

The uncomfortable reality is that most systems running on updated_at pagination have been silently dropping records since day one. The race condition is probabilistic, it might bite once a week or once a year, but it always shows up eventually.

The fix isn’t elegant, but it’s necessary: commit-time tracking, logical replication, or a serialization strategy that acknowledges the fundamental mismatch between “when a transaction started” and “when a transaction became visible.”

Your database knows when things became visible. Your application’s clock doesn’t. The question is whether you’re willing to listen to the one that has the answer.