Reading your own Postgres writes

When a Postgres primary starts struggling, the obvious move is to send reads to replicas. Then the bug reports start. A user updates their profile, gets a 200 back, reloads the page, and sees the old name.

Nothing is broken. The write committed on the primary, the read went to a replica, and that replica was two hundred milliseconds behind. Every layer did its job and the user still watched their change disappear. The database promises eventual consistency. Users assume they can read what they just wrote, and nobody ever asks them.

The usual fix is to pin a session to the primary for a few seconds after it writes. That mostly works. The problem is the number. Pick five seconds and you’ve guessed: too long for almost every request, still too short for the unlucky one. And every pinned session gives back the read capacity you added replicas to get.

Postgres already tracks the number you actually want. Every commit has a log sequence number, and every replica reports how far through the WAL it has replayed. So record the LSN a write committed at, then compare it against replay positions:

// After a write, remember how far this session needs replicas to be.
var lsn pglsn.LSN
if err := tx.QueryRow(ctx, "SELECT pg_current_wal_lsn()").Scan(&lsn); err != nil {
    return err
}
session.Fence(lsn)

// A replica is only eligible once it has replayed past that point.
func (r *Replica) CaughtUpTo(fence pglsn.LSN) bool {
    return r.ReplayLSN() >= fence
}

Routing follows from that. A read from a fenced session goes to any replica that has replayed past the fence, and to the primary if none has. No timer anywhere. A session that just wrote gets correctness. Every other session keeps using the replicas, and the fence clears the moment replication catches up.

There’s also no constant to tune, which is the part I like. The database was already publishing the number the timeout was trying to approximate.