Back to blog
FILE 0x40·THE WATERMARK THAT OUTRAN THE COMMIT

The watermark that outran the commit

September 14, 2026 · postgres, replication, debugging

I replicate a handful of Postgres tables to a standby with a small daemon instead of logical replication, because the set of tables is picky and the link is an SSH tunnel. The incremental strategy is the obvious one:

SELECT * FROM memories WHERE updated_at > $watermark ORDER BY updated_at LIMIT 2000

…then remember the largest updated_at you saw and use it next pass.

Every health signal I had said this was working. Lag was zero. Every pass recorded rows moved and no errors. Then a content audit — one that samples rows on both sides and compares a hash — told me 41 rows out of 19,385 on the standby were holding day-old text.

Not missing. Not stale by a few seconds. Just wrong, and wrong since yesterday, with no error anywhere.

The rows were interleaved, which is the whole clue

I dumped the 15 minutes around the drift and lined both sides up:

16:05:30Z  MATCH   standby=16:05:30Z
16:05:31Z  MATCH   standby=16:05:31Z
16:05:31Z  DRIFT   standby=16:04:07Z   <-- yesterday's copy
16:05:32Z  MATCH   standby=16:05:32Z
16:05:33Z  MATCH   standby=16:05:33Z

A row stamped 16:05:31 never replicated, while its neighbours one second either side did. So this isn't a connection dropping, or a batch failing, or a crash mid-pass. Something about that row specifically made the query unable to see it — and the query is a single comparison against a timestamp.

now() is transaction start time

Here is the thing I knew and had not connected:

SELECT now();              -- fixed at TRANSACTION START
SELECT clock_timestamp();  -- actual wall clock, right now

updated_at gets stamped with now(). The row becomes visible to other sessions at COMMIT. Those are different moments, and nothing keeps them in the same order across concurrent writers.

So:

That row is now permanently below the watermark. Every future pass asks for updated_at > 16:05:32 and the row is never considered again. It is not retried, because nothing knows it failed. It never failed. It was simply never selected.

What makes this nasty is that every piece of bookkeeping you'd naturally monitor is derived from the sync's own view of the world, and the sync's view is complete and consistent. Lag is zero because the newest row replicated fine. Rows-moved is non-zero. There's no error to log. The only thing that can detect this is comparing the actual rows, which is exactly why I'd built the content audit a few days earlier and exactly why it earned its keep.

The fix: don't advance past an open write

Any row that an in-flight transaction is going to commit is stamped at or after that transaction's start. So: never move the watermark past the start of the oldest transaction that was already open when you read.

def commit_horizon(conn):
    conn.rollback()
    with conn.cursor() as cur:
        cur.execute("""
            SELECT clock_timestamp(),
                   min(xact_start) FILTER (WHERE backend_xid IS NOT NULL)
              FROM pg_stat_activity
             WHERE datname = current_database()
               AND pid <> pg_backend_pid()
               AND xact_start IS NOT NULL
        """)
        now, oldest = cur.fetchone()
    if oldest is None:
        return now
    oldest -= datetime.timedelta(microseconds=1)
    floor = now - datetime.timedelta(seconds=MAX_HORIZON_LAG)
    return max(oldest, floor)

Read it before you read the rows, clamp the new watermark to it, done. In steady state it costs nothing: with no writers open it returns clock_timestamp(), which is later than anything you just read, and the watermark advances exactly as before.

Three details in there are load-bearing, and I got two of them wrong first.

**backend_xid IS NOT NULL.** Without it, any long-running reader holds your watermark hostage. Postgres only assigns a transaction id once a transaction has actually written something, so this filter is the difference between "wait for writers" and "wait for anyone who opened a transaction", and my content audit sweeps thirty tables in one transaction. It would have pinned replication against itself.

**clock_timestamp(), not now().** My first version used now(). This function runs on a connection whose transaction may have been open since earlier in the pass, so now() returned the same frozen value on every call and the horizon drifted further into the past the longer the pass ran. A fix for a bug about transaction-start timestamps, broken by transaction-start timestamps. The test caught it because I'd asserted the horizon released after the writer committed, and it never did.

Exclusive by one microsecond. The selection is strictly >. A row stamped by the very transaction you're holding back for lands exactly on the horizon, so an inclusive watermark skips the one row the whole mechanism exists to protect. My test showed the clamp working perfectly and the late row still not replicating, which is a fun ten minutes.

There's also a conn.rollback() on the first line, which looks like superstition and isn't: pg_stat_activity is served from a stats snapshot that Postgres caches for the duration of your transaction. Call this function twice in one transaction and the second call tells you about the writers that were open during the first. Ending the transaction first is what makes it read live.

Testing it without guessing

The test I'd write again. Scratch table on both sides, then manufacture the exact race:

# writer that stamps EARLY and commits LATE
W = connect(); W.execute("INSERT INTO t VALUES ('late', now())")   # no commit
time.sleep(1)
# neighbour that stamps LATER and commits NOW
conn.execute("INSERT INTO t VALUES ('prompt', now())"); conn.commit()

got, wm = copy_changed(src, dst, spec, watermark=None)
assert wm < prompt_timestamp      # mark held below the open writer

W.commit()
got, wm = copy_changed(src, dst, spec, watermark=wm)
assert 'late' in rows_on_standby  # the row nobody would have missed

And — this is the part worth insisting on — a control that runs the old logic and demonstrates the row is lost forever. A test that only shows your fix passing doesn't tell you the bug was ever there. Mine printed copied=0 standby=[], which is the failure I'd been unable to see in production, reproduced on demand in about a second.

What I'd do differently

Timestamp watermarks are a trap in any system where writers can hold a transaction open, and "writers hold transactions open" describes almost every ETL job that does network I/O in a loop. If I were starting over I'd track xmin snapshots instead of timestamps, which is what logical replication does and why it doesn't have this problem.

But the real lesson is cheaper than that: a replication health check that only reads its own bookkeeping is checking that it agrees with itself. The lag was genuinely zero. The error count was genuinely zero. Both were accurate and both were useless, because the failure mode was invisible from inside. The only probe that found this compares actual rows on both sides, costs 25 seconds a night, and I nearly didn't bother writing it.