Database replication

Replication is often pictured as one database copying data to another, and the picture leads to wrong expectations about how current a replica is and what it protects. What actually crosses the wire is not data. It is the same log the primary writes to survive its own crashes, and everything worth knowing about replication follows from that.

The log is what gets shipped#

A relational database already records every change before applying it, which is what makes a commit survive a power cut. A replica is built by taking that record and replaying it. PostgreSQL’s streaming replication connects a standby to the primary, which streams write-ahead log records to the standby as they are generated — without waiting for a log file to be filled, which is what makes streaming more current than shipping completed files.

Replaying the log rather than copying pages has a consequence worth drawing out: the standby is not receiving a snapshot it has to reconcile, it is performing the same sequence of changes in the same order. That is why a replica is consistent at every moment rather than only at checkpoints, and why the mechanism is the same one a change feed uses to tell other systems what happened. One log, several audiences.

The default has a price#

Streaming replication is asynchronous by default, which means a commit returns to the client as soon as the primary has it, and the standby catches up shortly afterwards. The documentation describes the gap as a small delay between committing on the primary and the change becoming visible on the standby. Small is doing a lot of work in that sentence, and what it costs is stated exactly: if the primary crashes then some committed transactions may not have been replicated, and the amount of data loss is proportional to the replication delay at the time of failover.

That is an unusually useful formulation, because it turns an abstract risk into something you already measure. Replication lag is not a performance curiosity — it is the size of the loss you take if the primary fails right now, denominated in seconds of writes. A dashboard showing four seconds of lag is telling you the worst case is four seconds of committed transactions.

Synchronous replication closes that window by making each commit of a write transaction wait until the commit has been written to the write-ahead log on disk of both the primary and the standby. Two settings turn it on together — synchronous_commit at a level that includes the remote, and synchronous_standby_names naming which standbys count and how many must answer. What you pay is a round trip on every commit, so the primary’s write latency now includes the network and the standby’s disk.

Both settings repay unpacking, because between them they decide what the word synchronous actually means here, and whether a standby can take the primary down with it. synchronous_commit is a five-position dial rather than a switch, and the positions are stops along the path a commit record travels. local waits for the local flush and not for replication at all. remote_write waits until the standby has received the record and written it to its file system, which survives PostgreSQL crashing on the standby but not the operating system crashing there, because the data has not necessarily reached durable storage. on, the default, waits until the standby has flushed it to durable storage. And remote_apply waits until the standby has received it, flushed it, and applied it, so that it has become visible to queries on the standby, at the cost, the documentation warns, of much larger commit delays, because it is now waiting for replay rather than for a write.

The second setting names which standbys count, and it takes two forms that mean different things. FIRST 2 (s1, s2, s3) is priority-based: the standbys earlier in the list are the synchronous ones, and later entries stand ready to take that role if an earlier one disconnects. ANY 2 (s1, s2, s3) is quorum-based: a commit waits for replies from at least any two of the three, whichever two answer first.

That distinction stops being academic the moment a synchronous standby dies. The documentation states the consequence plainly: such transaction commits may never be completed if any one of the synchronous standbys should crash. A primary configured with exactly one synchronous standby does not quietly fall back to asynchronous when it loses that standby. It stops acknowledging writes, and goes on not acknowledging them until somebody intervenes. The documented remedy is to keep as many synchronous standbys as you have asked for, by naming more candidates than the number you require; the documented emergency exit is to lower that number, or disable it, and reload the configuration on the primary. Both are worth knowing before the night it happens rather than during it.

A replica is not a backup#

The costly misunderstanding is treating a replica as a backup. A replica applies what the primary did, faithfully and fast. A mistaken delete, a migration that drops the wrong column, an application bug writing nonsense — every one of those replicates perfectly, and the second copy is corrupted moments after the first. Replication answers the question “what if this machine dies”. It has nothing to say about “what if we did the wrong thing”, which needs a copy from before the wrong thing.

The second is reading synchronous as up-to-date for queries. Synchronous replication waits for the record to be durable on the standby, not for it to have been applied there. A standby can therefore be fully caught up on what survives a crash while still being fractionally behind on what a query sees — a distinction that only matters when someone reads their own write from a replica and finds it missing. That is precisely the gap remote_apply exists to close, and the reason it is not the default is the one given above: it waits for replay, so it charges that visibility to commit latency on every transaction, including the overwhelming majority nobody is about to read back.

The third is lag that arrives without any failure. Replay on the standby is narrower work than the parallel writing the primary is doing, so a heavy write period produces lag with nothing in an error log. A long-running query on the standby can produce it too, by holding back the replay that would remove rows it is still reading. What happens next is a fight the standby has to settle, and by default the replay wins: once a conflicting query has run longer than max_standby_streaming_delay allows, it is cancelled. Setting hot_standby_feedback reverses the outcome, because the standby then asks the primary to hold off cleaning up the rows it is still reading, and the documentation names the price for that: it delays cleanup of dead rows on the primary, which may result in undesirable table bloat. That is the same bloat the heap article describes, now produced by a query running on a different machine. Nothing breaks. The delay simply grows, and the loss window from the section above grows with it.