Database Replication Explained: Primary-Replica Setups and Read Scaling

Quick Answer

Database replication means keeping automatic, continuously-updated copies of a database on separate servers: one primary that accepts writes, and one or more replicas that receive a stream of every change and apply it locally. The main reasons to do it are spreading out read traffic across multiple machines and surviving a server failure without losing data — but the replication stream is never instant, and that lag is the detail behind most outages involving replicas.

Why Add Replicas At All

Database replication means keeping automatic, continuously-updated copies of a database on separate servers: one primary that accepts writes, and one or more replicas that receive a stream of every change and apply it locally. Teams reach for it for three distinct reasons, and it's worth naming them separately because they lead to different design decisions.

The first is read scaling. Most web applications are read-heavy — often 80-95% of queries are reads — and a single primary's read capacity eventually becomes the ceiling on total throughput even though writes are a small fraction of the traffic. Spreading reads across N replicas multiplies available read capacity roughly by N, without touching the write path at all. The second is high availability: a replica that's ready to be promoted means a hardware failure or a data-center outage doesn't automatically mean total downtime or data loss. The third is workload isolation — expensive analytics queries, nightly reports, and backup jobs that scan huge amounts of data can run against a replica instead of competing for the same buffer pool and lock table as the live application traffic hitting the primary.

The caveat that has to come first, before any of the mechanics below: replication multiplies read capacity, not write capacity. Every replica still has to apply every single write eventually, in order. A write-heavy workload does not get faster by adding replicas — if anything, keeping more replicas in sync consumes more of the primary's resources and can make things slower.

The Basic Shape: One Writer, a Stream of Changes, Many Readers

Strip away vendor-specific detail and the mechanism is the same everywhere: the primary is the single source of truth for writes, and it also generates a continuous, ordered log of every change it makes. A replica is simply a process that reads that log starting from wherever it last left off, and replays each change locally, in the same order it happened on the primary.

That framing matters because it means replication is fundamentally a log-shipping problem, not a "copy the whole database over and over" problem. After an initial full copy (a snapshot, taken once when a replica is first set up), only the ongoing log stream needs to keep moving — which is why replication can keep up with a busy primary using a small fraction of the bandwidth a repeated full copy would require.

The most common topology is one primary with several replicas hanging directly off it. A variant worth knowing is cascading replication, where a replica itself becomes the log source for further replicas downstream — useful for fanning replication out to a distant region without adding more direct connection load to the primary itself. Multi-primary setups, where more than one server accepts writes, exist too, but they introduce a materially harder problem: what happens when two primaries accept conflicting writes to the same row at nearly the same time. That conflict-resolution problem is a different, harder architecture that most teams should avoid taking on unless they have a specific, well-understood reason to need it.

How MySQL Does It: Binlog, Formats, and GTIDs

MySQL's change log is called the binary log (binlog), and it can record changes in one of three formats. Statement-based logging records the literal SQL statement that ran — compact, but risky for anything non-deterministic, since a statement like an UPDATE ... LIMIT 5 with no deterministic ordering, or one referencing NOW(), can legitimately produce a different result when replayed on a replica than it did on the primary. Row-based logging instead records the actual before-and-after row images — larger binlog volume, but deterministic and safe regardless of what produced the change, which is why it's the modern default. Mixed mode uses statement-based logging by default and automatically switches to row-based for any statement MySQL detects as unsafe to replay as a raw statement.

Before MySQL 5.6 (released 2013), replicas tracked their position in the replication stream using a binlog filename plus a byte offset inside it — a coordinate system that made failover and repointing a replica to a new primary genuinely error-prone, since whoever performed the failover had to know the exact file and position the new primary should be read from. MySQL 5.6 introduced GTIDs (Global Transaction Identifiers): every committed transaction gets a unique ID that's consistent across the entire replication topology, and a replica can find its own correct starting point automatically with SOURCE_AUTO_POSITION=1 rather than a human supplying a file-and-offset pair by hand.

One naming detail worth knowing if you're reading older tutorials: MySQL 8.0.22 and 8.0.23 renamed the old SLAVE/MASTER terminology throughout the replication commands. What used to be CHANGE MASTER TO and SHOW SLAVE STATUS are now CHANGE REPLICATION SOURCE TO and SHOW REPLICA STATUS — the old spellings still work as deprecated aliases, but new documentation and new scripts use the SOURCE/REPLICA naming.

By default, MySQL replication is fully asynchronous: the primary commits a transaction and returns success to the client without waiting for any replica to receive it at all. Semi-synchronous replication is the middle ground — the primary waits for at least one replica to acknowledge it has received (not necessarily applied) the transaction before returning success to the client. That reduces, but does not eliminate, the window of data that could be lost if the primary died the instant after committing, and it adds real latency to every single commit in exchange.

How PostgreSQL Does It: WAL Shipping and Synchronous Commit Levels

PostgreSQL's equivalent change log is the write-ahead log (WAL). Modern Postgres uses streaming replication, where a replica connects directly to the primary and receives WAL records nearly as soon as they're generated, rather than waiting for whole log files to be shipped in batches the way older log-shipping-only setups worked. A replication slot is the primary's own bookkeeping entry that reserves and retains WAL specifically for one named replica, so that WAL isn't recycled or deleted before that replica has actually consumed it.

That last detail creates a real, well-known operational trap: if a replica with an active slot goes offline and stays offline, the primary keeps retaining WAL on its behalf indefinitely, because it doesn't know the replica isn't coming back. Disk usage on the primary climbs continuously until either the replica reconnects and catches up, or someone notices and manually drops the slot. Running a primary out of disk space because of one forgotten, permanently-dead replica slot is a genuinely common incident, not a rare edge case.

Postgres exposes the durability/latency tradeoff directly through the synchronous_commit setting, and the official documentation lists its five levels from strongest to weakest guarantee as: remote_apply, on, remote_write, local, and off. Read from weakest to strongest instead: off waits for nothing, so a crash immediately after "success" was reported can lose that commit. local waits for the primary's own disk flush but doesn't involve any replica. remote_write waits until the synchronous replica has received the record and written it to its own operating system, but not necessarily fsynced to its disk yet. on — the default once synchronous_standby_names is configured — waits until the replica has actually flushed the record to its own disk. remote_apply, the strongest, waits until the replica has replayed the transaction, so a read against that replica immediately after commit is guaranteed to see it.

Every step up that ladder adds real wall-clock time to every single commit, in exchange for a stronger guarantee about what survives a crash. There is no setting that gives you the strongest guarantee for free.

Replication Lag: The Assumption That Breaks Production

Replication lag is the gap between when a transaction commits on the primary and when a specific replica has actually applied it. Under asynchronous replication — the default in most setups — that gap is never exactly zero, even when it's usually a harmless handful of milliseconds.

The concrete failure mode this causes is familiar to anyone who's debugged it once: a user submits a form, the application immediately reads back the result (often routed to whichever replica a load balancer happened to pick) to confirm or display it, and that particular replica hasn't caught up yet. The user sees their own just-created data appear to be missing. This is usually described as a database bug, but it's really a routing bug — reading from a replica for data you just wrote yourself, without accounting for lag, is called violating "read-your-writes" consistency.

Lag has causes beyond plain network delay. A single large or long-running transaction on the primary has to be applied by a replica in full, in order, before anything queued behind it can proceed — one slow transaction can back up everything after it. Older MySQL versions applied replication changes single-threaded, which made a replica structurally unable to keep up with a sufficiently write-heavy primary regardless of hardware (modern MySQL and Postgres logical replication both support parallel apply now, with their own separate limits). And sometimes it's simpler than any of that: the replica is just under-provisioned relative to the primary's actual write rate.

Both major engines expose lag directly rather than leaving you to guess. MySQL's SHOW REPLICA STATUS reports a Seconds_Behind_Source field. PostgreSQL exposes the same information through the pg_stat_replication view — specifically the gap between the primary's current WAL position and a replica's replay_lsn, or by comparing pg_last_wal_replay_lsn() on the replica against the primary's current position. Either way, lag is something to actively monitor, not something to assume away.

Read/Write Splitting in Practice

The standard pattern is straightforward to state: writes always go to the primary, and reads get distributed across replicas. Where that routing decision actually happens varies — some teams build it directly into application code as two separate connection pools (one "write" pool pointed at the primary, one "read" pool pointed at a set of replicas), others push it down to a dedicated proxy layer, like ProxySQL for MySQL or PgBouncer/pgpool-II plus routing rules for Postgres, or rely on a managed reader endpoint a cloud provider already load-balances for you, such as an RDS or Aurora reader endpoint.

The trap directly tied to the lag discussion above: naively round-robining every single read across all available replicas, with no awareness of what was just written, reliably reintroduces the read-your-writes bug described earlier. Two mitigations are common in practice. One is sticky routing — for a short window right after a given user writes something, route that specific user's subsequent reads back to the primary (or to whichever replica already acknowledged that write) instead of to an arbitrary replica. The other is simpler: reserve replicas specifically for reads that can tolerate a little staleness — dashboards, public listings, search results — and always hit the primary for anything transactional immediately following a write, rather than trying to make every single read replica-safe.

Failover: Promoting a Replica Is Not a Free Action

Failover means telling a replica to stop replicating and start accepting writes itself, then repointing application traffic — and any other replicas that were following the old primary — at the newly-promoted server. It sounds like a single clean step; in practice it has two sharp edges worth understanding before you need it during an actual incident.

The first is data loss. Under asynchronous replication, whatever hadn't yet been shipped to the replica being promoted at the exact moment the old primary died is gone — it was never anywhere else. Failover, under async replication, trades some durability for availability by design. Semi-synchronous or fully synchronous replication is the direct lever for shrinking that loss window, at the latency cost on every write covered earlier — there is no way to get both zero data loss risk and zero commit latency overhead simultaneously.

The second is split-brain: if the old primary comes back online after a failover without being explicitly told it's no longer the primary, and anything is still routing writes to it, two servers are now independently accepting writes to what used to be one dataset. That's a genuinely dangerous state, and it has caused real, severe data-loss incidents in production systems that handled failover with a human running commands by hand mid-outage. This is exactly why dedicated tooling exists for this specific problem: Patroni manages Postgres failover by holding a single, unambiguous "who is currently primary" lock in a separate consensus store like etcd, so there is one authoritative answer to "who is primary" that every node can check; Orchestrator does a comparable job for MySQL topologies; and managed offerings like RDS/Aurora Multi-AZ or Cloud SQL handle promotion and traffic redirection as one coordinated, tested operation rather than leaving it to manual intervention under pressure.

Frequently Asked Questions