• Book: Database Playbook: Choosing the Right Store for Every System You Build
• Also by me: Thinking in Go (2-book series) — Complete Guide to Go Programming + Hexagonal Architecture in Go
• My project: Hermes IDE | GitHub — an IDE for developers who ship with Claude Code and other AI coding tools
• Me: xgabriel.com | GitHub

Your read replica is lying to you, and "eventual consistency" is the lie's polite name. The user clicks Pay, the API returns 200, the next page reads the order from a replica, and the row isn't there yet. Support gets the ticket. You blame "replication lag" like it's weather.

It isn't weather. It's a config choice, and most Postgres clusters are running the default.

The lie, in one paragraph

A read-after-write that returns stale data is a contract violation. The user gave you a write, you accepted it, you confirmed it, and then you served them old data on the next request. There's no philosophical defence of that. "We're eventually consistent" is what you say to the postmortem doc when you didn't pick a stronger guarantee at config time.

Postgres gives you five settings on a single knob called synchronous_commit, and they buy completely different guarantees at completely different costs. Only one of them prevents a read replica from serving stale rows. Most teams run a global default of on and have never typed the other four values into a session.

The 4 (well, 5) sync modes, ranked by guarantee

synchronous_commit has five legal values. Ranked from "fire and forget" to "wait until the replica has the row visible to queries":

1. off: primary returns commit before fsync to its own WAL. A primary crash inside the next few hundred milliseconds loses the transaction. Don't ship this unless you really mean it.
2. local: primary returns commit after its own WAL fsync. Replica state is irrelevant. This is the value when no synchronous_standby_names is set.
3. on (the default) / remote_write: primary waits for the synchronous standby to receive the WAL into its kernel. Not flushed to disk. Not replayed. A replica OS crash before flush can still lose it.
4. remote_flush: primary waits for the synchronous standby to fsync the WAL to its disk. The replica can survive a crash and still have the record. But it hasn't been replayed yet, so a SELECT on that replica won't see the row.
5. remote_apply: primary waits for the synchronous standby to apply the WAL, actually replaying it into the database. A SELECT on that replica will see the row immediately after the COMMIT returns.

The first four are about durability. Only the last one is about read-after-write consistency on the replica. That's the whole point of this post: if you want the replica to never serve stale data, remote_apply is the only setting that gives you that.

Everything else is theatre.

What each mode guarantees vs costs

A rough latency table from a 3-node Postgres 16 cluster in one AWS region (us-east-1, gp3, c6i.large), measured with pgbench -c 16 -j 4 -T 60 -N. Treat numbers as order-of-magnitude, not benchmarks:

The spread between off and remote_apply is roughly 15x. Across a region (say a standby in Frankfurt and a primary in Dublin, ~25 ms RTT) the same table shifts: remote_apply becomes 50-60 ms per commit. That's not a footnote. That's a product decision.

Most teams who default to on and assume "we're durable" are running with the replica can crash and lose the record setting. That's fine for a lot of workloads. It is not fine for payment intent records or permission grants.

Synchronous standbys and the quorum trick

synchronous_commit interacts with one other setting: synchronous_standby_names. Without that one set, none of the remote_* modes do anything. There's nobody to wait for, so the primary degrades silently to local.

The naive form names a single standby:

synchronous_standby_names = 'replica-fra-1'

The problem with naming one standby is obvious. If replica-fra-1 is down or partitioned, your primary blocks on every write until it comes back. You traded read-staleness for write-availability, and you didn't get a discount.

The fix is quorum commit. Postgres 10+ supports:

synchronous_standby_names = 'ANY 2 (replica-fra-1, replica-fra-2, replica-fra-3)'

Read this as: "wait until any 2 of these 3 standbys acknowledge." If replica-fra-1 is down, the other two ack and the commit goes through. You stay durable across two replicas, you stay available as long as two are healthy, and you pay the latency of the second-fastest standby instead of one specific one.

There's a FIRST n (...) form too (strict priority order), but ANY n is the one to reach for. It's the cluster equivalent of a Raft quorum without the consensus protocol.

Per-transaction synchronous_commit: the feature nobody uses

Most teams miss this: synchronous_commit is a session/transaction-scoped GUC. You don't have to run the cluster on one global setting. You can pick the right guarantee per write.

Run the cluster at on (cheap default, ~2 ms commits, replicas may lag a beat). Then, for the specific writes that need read-after-write consistency, escalate inside the transaction:

# checkout flow: must be visible on read replica
# before the redirect to the confirmation page
async def confirm_payment(order_id: str, intent_id: str) -> Order:
    async with db.transaction() as tx:
        # this transaction only, not the whole connection
        await tx.execute(
            "SET LOCAL synchronous_commit = 'remote_apply'"
        )

        await tx.execute(
            """
            UPDATE orders
               SET status = 'paid',
                   stripe_intent_id = $1,
                   paid_at = NOW()
             WHERE id = $2
               AND status = 'awaiting_payment'
            """,
            intent_id,
            order_id,
        )

        # the row visible on every replica before COMMIT returns
        order = await tx.fetchrow(
            "SELECT * FROM orders WHERE id = $1",
            order_id,
        )

    # safe to redirect. confirmation page reads
    # from a replica and will see status='paid'
    return Order.from_row(order)

SET LOCAL is the keyword that matters. It scopes the change to the current transaction. The connection goes back to the cluster default after COMMIT. You don't have to remember to reset it. You don't poison the next query on the same pooled connection.

Now the rest of your traffic (view counts, click events, "user toggled dark mode") runs at cheap on. The one write where stale reads break the product runs at remote_apply and pays 6 ms instead of 2. You bought consistency where it mattered. You didn't pay for it everywhere else.

The same trick applies the other direction. If you have a hot write path that you genuinely don't care about durability for (say a last_seen_at timestamp updated on every API call), drop it to local or off for that one statement:

BEGIN;
SET LOCAL synchronous_commit = 'off';
UPDATE users SET last_seen_at = NOW() WHERE id = $1;
COMMIT;

The cluster keeps its strong default, and this one statement gets to be fast and slightly lossy on purpose.

The failure mode you'll hit first

A synchronous primary with no healthy standby hangs every write. This is not a bug. This is the literal definition of synchronous replication: the primary refuses to acknowledge a commit until its synchronous partner agrees, and if there's no synchronous partner, there's no agreement, so there's no acknowledgement.

You see it as:

• New connections succeed.
• SELECT queries succeed.
• INSERT, UPDATE, DELETE, COMMIT block forever.
• Your APM dashboard goes red on write p99.

Operators look at the primary and say "Postgres is up, it's healthy, what's the problem." The problem is that synchronous_standby_names = 'replica-fra-1' is set, replica-fra-1 is the only listed standby, it's down, and Postgres is doing exactly what you asked. The mitigations:

• Use the quorum form (ANY n (...)). Survives any single standby loss.
• Set up monitoring on pg_stat_replication.sync_state so you find out before users do.
• For non-critical workloads, accept the on default and don't promise read-after-write consistency on a replica at all.

Don't fix it by editing synchronous_standby_names to an empty string during the incident, by the way. That degrades the cluster to async behind your back, and the runbook tomorrow won't remember why.

Verifying which mode is actually active

Two queries to know. First, what does the primary think the setting is:

SHOW synchronous_commit;
SHOW synchronous_standby_names;

Second, what's the live replication state. Which standbys exist, which are sync, which are async, where are they in WAL:

SELECT
    application_name,
    client_addr,
    state,
    sync_state,
    sync_priority,
    pg_wal_lsn_diff(sent_lsn, write_lsn)   AS write_lag_bytes,
    pg_wal_lsn_diff(sent_lsn, flush_lsn)   AS flush_lag_bytes,
    pg_wal_lsn_diff(sent_lsn, replay_lsn)  AS replay_lag_bytes,
    write_lag,
    flush_lag,
    replay_lag
FROM pg_stat_replication
ORDER BY sync_priority;

sync_state is the column to stare at:

• sync: a synchronous standby. Primary waits on it.
• quorum: part of an ANY n quorum. Primary waits on enough of the group.
• potential: listed in synchronous_standby_names but not currently chosen as sync.
• async: purely asynchronous. Primary never waits on it.

The three lag columns tell you where each replica is: write_lag is the gap until the standby received the WAL, flush_lag until it fsynced, replay_lag until it applied. replay_lag is the one users feel when they read from a replica.

Alert on replay_lag > 1 second on any standby you serve user reads from. That's not a "warn at 30 minutes" metric. That's a contract.

A decision tree, per write

For each write your service does, ask in order:

1. Can a stale read on a replica break a user-visible contract? Checkout confirmation, permission grant, "your account is now verified", yes. Use remote_apply, per-transaction with SET LOCAL.
2. Must this survive a primary crash plus a replica crash? Audit log, payment intent, idempotency key, yes. At least remote_flush. Probably also remote_apply if it's followed by a replica read.
3. Must this survive a primary crash but a lost replica is acceptable? Most application data. on is fine.
4. Is durability negotiable for throughput? Last-seen timestamps, view counts, ephemeral session bumps. Use local or off, per-statement with SET LOCAL.

The trap is question 1. Teams skip it because the default of on sounds durable, and they conflate "durable on primary" with "visible on replica." Those are different guarantees on different machines, separated by however much WAL is in flight.

If a checkout-flow read-after-write returns stale because your replica hadn't applied yet, no amount of "the write was durable on the primary" gets you off the hook with the user staring at an empty cart. remote_apply is the answer. Use it where it matters, leave it off everywhere else, and stop blaming the weather.

If this was useful

If picking the right durability and consistency settings per write is the kind of thing you want a full chapter on (not just Postgres, but Mongo write concerns, Cassandra tunables, Redis persistence modes, and the same trade-off shape across each), that's exactly what the Database Playbook is built around. Each store gets its own chapter on consistency knobs, what they cost, and which writes deserve the expensive setting.