Database replication and failover: what a standby promises

The primary database died at 02:14. The standby was promoted at 02:16 and the status page went back to green. By 09:00 the support queue holds a dozen tickets from customers whose 02:13 orders do not exist. Nobody lost a disk. Nobody dropped a table. The system did exactly what it was configured to do, and what it was configured to do was quietly weaker than what everyone assumed.

The assumption has a shape. "We have a replica" gets heard as "we cannot lose data," and "we have failover" gets heard as "we cannot go down." A streaming replica promises neither. It promises a second copy of the write-ahead log that is usually a few milliseconds behind, and a node that can be promoted if someone, or something, decides to. Everything past that is a setting you chose, or a default you did not know you chose.

Streaming replication

Acknowledged on the primary, visible on the standby later

asynchronous by default

Primary

accepts writes, flushes WAL

pg_current_wal_lsn()

WAL stream

replay lag 200 ms

at 500 writes/s: about 100 acknowledged writes

Standby

receives, flushes, replays

pg_last_wal_replay_lsn()

1. t+0 ms primary: INSERT row 42; commit acknowledged to the client
2. t+40 ms replica read: SELECT row 42 returns 0 rows
3. t+40 ms primary read: SELECT row 42 returns 1 row
4. t+200 ms standby: replays the record; row 42 is now visible there

synchronous_commit decides what "acknowledged" means

local

acknowledged when the primary flushed the record

standby lags behind

on

acknowledged when a standby flushed it too

standby read may still miss it

remote_apply

acknowledged when a standby applied it

standby read returns it

What the WAL stream actually carries

PostgreSQL is the concrete example here; the mechanics are the same in every log-shipping database. Every change the primary makes is first written to the write-ahead log. A streaming standby holds a connection to the primary, receives WAL records as they are produced, writes them to its own disk, and replays them into its own data files. Three separate moments: sent, flushed, applied. The distance between the primary's current position and the standby's applied position is replication lag, and the primary exposes both so you can measure it instead of guessing:

SELECT application_name,
       state,
       sync_state,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag_bytes,
       replay_lag
FROM pg_stat_replication;

sync_state is the column that tells you what "committed" means on this cluster. With the default async, the primary acknowledges a commit as soon as the record is on its own disk, and the standby learns about it whenever the stream delivers it. The standby server documentation describes streaming replication as asynchronous by default, with a delay between the commit on the primary and the change becoming visible on the standby, and offers synchronous replication as the way to confirm a transaction reached a standby before the commit returns.

Synchronous commit buys durability with latency

You can make the primary wait. Name the standbys that count, then pick how far the record must travel before the client hears "committed":

ALTER SYSTEM SET synchronous_standby_names = 'ANY 1 (standby_a, standby_b)';
ALTER SYSTEM SET synchronous_commit = 'remote_apply';
SELECT pg_reload_conf();

ANY 1 means one of the two named standbys must confirm before the commit returns; if one is down, the other keeps commits flowing. synchronous_commit picks the moment of confirmation, and the WAL configuration reference lists the ladder: local waits only for the primary's disk, on waits for a standby to flush the record, and remote_apply waits for a standby to apply it, which is the only level at which a read on that standby is guaranteed to return the row.

The price is one network round trip per commit. If the standby sits in the same rack and answers in 0.5 ms, a commit that took 2 ms now takes 2.5 ms and nobody notices. Put it in a region 30 ms away and every commit takes 32 ms; a request that made five sequential writes went from 10 ms to 160 ms. That is the durability-versus-latency trade in one number, and it is why teams so often run synchronous within a region and asynchronous across regions. The other price is availability: name a single standby instead of ANY 1 of several, and that standby's outage stalls every commit on the primary until someone intervenes.

A replica promises a copy that is usually a few milliseconds old. Whether "usually" is good enough is a decision you make with synchronous_commit, not one the replica makes for you.

Reading your own writes from a replica

Read replicas are where replication lag stops being an operations concern and becomes a product bug. A user submits a comment, the API commits it on the primary, redirects to the thread, and the thread page reads from the replica. The replica is 40 ms behind. The comment is not there. The user posts it again.

Two fixes work, and both are cheap. The first routes any read that follows a write in the same request to the primary. A scoped flag is enough:

public sealed class RequestWriteState
{
    public bool WroteThisRequest { get; private set; }

    public void MarkWrote() => WroteThisRequest = true;
}

public sealed class RoutedConnections(
    [FromKeyedServices("primary")] NpgsqlDataSource primary,
    [FromKeyedServices("replica")] NpgsqlDataSource replica,
    RequestWriteState state)
{
    public ValueTask<NpgsqlConnection> OpenForWriteAsync(CancellationToken cancellationToken)
    {
        state.MarkWrote();
        return primary.OpenConnectionAsync(cancellationToken);
    }

    public ValueTask<NpgsqlConnection> OpenForReadAsync(CancellationToken cancellationToken)
    {
        // A request that has already written must see its own row.
        var source = state.WroteThisRequest ? primary : replica;
        return source.OpenConnectionAsync(cancellationToken);
    }
}

Register RequestWriteState as scoped and the two data sources as keyed singletons, and every read after a write in the same request lands on the primary. It does not help the next request, the one the redirect sends a hundred milliseconds later. For that, capture the primary's position after the commit with SELECT pg_current_wal_lsn(), hand it to the client in a cookie, and have the replica wait until pg_last_wal_replay_lsn() reaches it before running the read. Both keep the replica doing the bulk of the reads while the one user who just wrote sees their row. Both also depend on two pools with two limits, and connection pooling has its own failure modes on the day the primary changes address.

Failover: who decides, and what the old primary is still doing

Promotion is a command, pg_ctl promote or a call to pg_promote(), and the interesting question is who issues it. A human at 02:14 is slow. A script that promotes on the first failed health check is fast and dangerous, because the check can fail while the primary is fine and merely partitioned from the checker. Then two nodes accept writes, the WAL histories diverge, and reconciling them afterwards is manual work with guesswork in it.

Fencing is the answer, and it happens before promotion, not after. The old primary must be made unable to accept writes: powered off, its virtual IP withdrawn, or its connections killed and the instance stopped. Cluster managers built for this job fence as an explicit step, with a quorum of observers agreeing that the primary is gone. If your runbook says "promote the standby" and does not say what happens to the old primary, it is not a runbook yet.

Then the clients need to find the new primary. A DNS record with a 60 second TTL means some clients keep resolving the old address for up to a minute. A connection string change needs a redeploy or a config reload. A proxy or virtual IP in front of the cluster moves fastest, because clients never learned a host name that changed. Whichever you use, every pooled connection to the old host is now a dead socket; the pool discovers that one borrowed connection at a time, and the first requests after the switch each pay a timeout.

Now the arithmetic the tickets were about. Asynchronous replication with 200 ms of replay lag on a primary taking 500 writes per second means the standby is, at any instant, roughly 100 acknowledged writes behind. Promote it and those 100 commits are gone: the clients got "committed," and the surviving database never saw them. Two seconds of lag under a heavy batch is 1,000 writes. Synchronous commit at on or above makes that number zero, at the latency cost above. You pick.

A replica is not a backup

Replication copies everything, faithfully and fast. A DROP TABLE orders reaches the standby a few milliseconds after it reaches the primary, and a DELETE with a missing WHERE clause replicates row by row. Failing over to the standby after either gives you a second database with the same hole in it.

Backups exist for the mistakes replication reproduces. Base backups plus continuous WAL archiving let you restore to a point in time, such as 02:13:59, the second before the statement. A delayed standby, one configured to replay WAL an hour behind on purpose, is the cheap middle ground: the mistake reaches it an hour later, which is usually long enough for someone to notice. Neither is optional because you have a replica. The replica is for hardware and process failure; backups are for human failure.

And test the failover on purpose, on a schedule, during business hours, with the people who would run it at 02:14 watching. Kill the primary. Time the promotion. Count the lost writes against the lag you measured. Watch what the connection pools and the DNS cache actually do. The first time you run a failover should not also be the first time you learn what your standby promised.

Where this gets practiced

The decisions above are the content of the "Survive a Database Failure" fundamentals challenge in Katabench's System Design Studio. You build the architecture on a canvas from provider-neutral components, including a Database and a DB Replica, and deterministic rules check required paths and redundancy, so a design that claims to survive the primary's loss has to show the path that does. The capacity simulation reports p99 latency and the component that saturates first under a versioned traffic scenario, where a replica that quietly absorbed every read shows its real load. It is a reproducible model built for learning, not a provisioned load test, and there is no single correct diagram.

The same habit, asking what "committed" means before trusting it, runs through the platform's database work. Optimistic concurrency in EF Core is the same question at row scale; when the limit is the primary's write rate rather than its durability, sharding versus partitioning is the next decision. The track overview shows how the Database track builds these instincts against real PostgreSQL query plans.