A dashboard I built once showed a user’s order as “processing” for about four seconds after they had just paid for it. Not broken, exactly. The write went to the primary database just fine. The dashboard just happened to read from a replica that had not caught up yet.
I spent an embarrassing amount of time assuming I had a caching bug before I remembered I did not have a cache anywhere near that code path. I had a replica, and replicas are not the same thing as the database, even though they mostly behave like it.
What a replica actually is
Take a single Postgres instance handling both your app’s writes and every analytics query your data team runs. At some point the analytics queries get heavy enough that they start slowing down the checkout flow, because both are fighting for the same CPU and the same disk.
The fix is not usually a bigger server. It is a second copy of the database that receives every change the primary makes, in close to real time, and exists specifically so you can point read traffic at it. That copy is a replica. The primary keeps handling writes. The replica keeps handling reads that do not need to be perfectly current, and if the primary catches fire, a replica is often the thing you promote to take its place.
The three databases I use most, Postgres, MySQL, and MongoDB, all implement this idea, and all three implement it differently enough that “just add a replica” means three different sets of commands.
PostgreSQL: streaming the write-ahead log
Postgres does not literally copy each INSERT statement to the replica. Every change first gets written to the write-ahead log (WAL), a file Postgres already keeps so it can recover from a crash. Streaming replication just ships that same log to another server, which replays it.
On the replica, you point it at the primary:
# postgresql.auto.conf on the standby
primary_conninfo = 'host=primary-db port=5432 user=replication password=secret'And on the primary, wal_level has to be replica or higher, with max_wal_senders raised enough to cover however many standbys and backup tools connect (the default of 10 is usually fine unless you have a lot of replicas).
By default this is asynchronous. The primary commits a transaction and tells the client “done” without waiting to hear back from any replica. That is why my dashboard was four seconds stale, that gap between commit and replay is exactly the window where a replica can be behind.
If four seconds of staleness is not acceptable, and for things like financial balances it usually is not, Postgres lets you flip specific replicas to synchronous:
# on the primary
synchronous_standby_names = 'FIRST 1 (replica_a, replica_b)'Now the primary waits for at least one of replica_a or replica_b to confirm it has written the WAL record before the client’s COMMIT returns. Zero data loss if the primary dies, at the cost of every write now waiting on a network round trip to another machine. Most setups run a mix, one synchronous replica for the guarantee, a handful of async ones purely for read scaling.
flowchart LR
A[Client writes] --> B[Primary]
B --> C[WAL record]
C -->|async| D[Read replica 1]
C -->|async| E[Read replica 2]
C -->|sync, waits for ack| F[Standby for failover]
MySQL: binlog and GTIDs
MySQL’s version of the WAL is the binary log, and for a long time replicas tracked their position in it using a filename and a byte offset. It worked, but it was fragile. If a replica got promoted to primary during a failover, every other replica had to be manually told the new file and offset to resume from, and getting that wrong meant silently replaying the wrong transactions or missing some entirely.
GTIDs (global transaction identifiers) fixed this. Every transaction gets a unique ID when it commits on the source, and replicas track which IDs they have already applied instead of a raw file position. Turn it on with:
# my.cnf, both source and replica
gtid_mode = ON
enforce_gtid_consistency = ONThen point the replica at the source and let it figure out its own position:
CHANGE REPLICATION SOURCE TO
SOURCE_HOST = 'primary-db',
SOURCE_PORT = 3306,
SOURCE_USER = 'repl_user',
SOURCE_PASSWORD = 'secret',
SOURCE_AUTO_POSITION = 1;
START REPLICA;SOURCE_AUTO_POSITION = 1 is the whole point here. The replica compares the set of GTIDs it already has against what the source has and asks only for what it is missing. Check on it with:
SHOW REPLICA STATUS\GSeconds_Behind_Source in that output is the number I actually watch in production. Anything creeping past a few seconds under normal load usually means the replica’s single-threaded apply process cannot keep up with however many parallel writers the primary has, which is a real limitation worth knowing about before you lean on a MySQL replica for anything time-sensitive.
MongoDB: replica sets and elections
MongoDB skips the primary/replica naming split and calls the whole group a replica set. One member is primary and takes all writes. The rest are secondaries, and they catch up by tailing the primary’s oplog, a capped collection that records every write as it happens, conceptually the same idea as a WAL or binlog, just modeled as a MongoDB collection instead of a flat file.
rs.initiate({
_id: "rs0",
members: [
{ _id: 0, host: "mongo-a:27017", priority: 2 },
{ _id: 1, host: "mongo-b:27017", priority: 1 },
{ _id: 2, host: "mongo-c:27017", priority: 1 },
],
});priority is the interesting field. A higher priority member calls for an election sooner and is more likely to win it, which is how you tell MongoDB “if the primary goes down, I’d rather this specific server take over, not just whichever one notices first.”
What actually happens when the primary disappears: secondaries that have not heard from it in electionTimeoutMillis (10 seconds by default) start an election. Whoever gets a majority of votes and has replayed the most recent oplog entries becomes the new primary. This is also why MongoDB recommends an odd number of voting members, an even number risks a tie with no majority, and nobody gets elected until that resolves.
flowchart TD
A[Primary stops responding] --> B{Secondary waits electionTimeoutMillis}
B --> C[Secondary calls for election]
C --> D{Gets majority of votes?}
D -->|Yes| E[Becomes new primary]
D -->|No| F[Stays secondary, retries]
One detail that caught me off guard the first time: a member with priority: 0 can still hold a full copy of the data and serve reads, it just can never become primary and never triggers an election. Useful for a replica in a secondary region you want for read traffic or disaster recovery, but never want taking over as the main write target.
The lag problem, across all three
Every one of these systems has the same underlying tradeoff, whatever they call it. Async replication is faster and cheaper, and it means there is always some window, milliseconds most of the time, occasionally longer under load, where a replica does not yet have the latest write.
The dashboard bug I opened with is the most common way this bites you: write to the primary, immediately read from a replica, and the read loses the race. The fix I landed on was boring but effective, route the read that happens right after a write back to the primary for a few seconds, then let it fall back to replicas after that. Some ORMs and drivers have “read your own writes” modes that do this automatically. Worth checking before you write it yourself.
What a replica does not give you
A replica is not a backup. If someone runs a bad DELETE with no WHERE clause on the primary, that delete replicates to every standby just as faithfully as any other write, usually within milliseconds. You still need actual point-in-time backups, separate from replication entirely.
A replica also does not scale writes. Every write still goes through the primary no matter how many read replicas you add, so if your bottleneck is write throughput rather than read throughput, more replicas will not touch it. That is a sharding problem, which is a different post.
Four seconds of lag does not sound like much until it is your own order status flickering in front of you. Now when something reads stale, checking whether it is hitting a replica is the first thing I check, not the last.
References
- PostgreSQL Replication Configuration Docs —
synchronous_standby_names,primary_conninfo, and every replication parameter - MySQL GTID-Based Replication — full setup steps for
gtid_modeandCHANGE REPLICATION SOURCE TO - MongoDB Replica Set Elections — priority, voting, and
electionTimeoutMillisbehaviour