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.

That is what pgpilot does, and both halves sit in internal/proxy/session.go. When a session’s transaction commits on the primary, its fence advances to the primary’s current WAL position:

if heldAddr == primary && wroteThisTx {
    if lsn, lerr := s.primaryLSN(ctx, held); lerr == nil && lsn > fence {
        fence = lsn
        s.log.Debug("advanced write fence", "lsn", lsn)
    }
}

primaryLSN is a SELECT pg_current_wal_lsn() on the connection that just did the writing. A replica then gets the session’s next read only once its replayed position has reached that fence:

func (s *session) replicaEligible(st registry.Status, fence uint64) bool {
    switch s.cfg.Fencing.Mode {
    case config.FenceRelaxed:
        return true
    case config.FenceBounded:
        return st.LagSeconds*1000 <= float64(s.cfg.Fencing.BoundedMs)
    default: // strict
        return st.LSN >= fence
    }
}

Strict is the default. The other two modes are escape hatches: relaxed sends reads to any healthy replica, and bounded accepts a staleness window in milliseconds, which is the guess this post started with, kept for people who would rather have the throughput.

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, with no timer involved at any point. 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.