Everyday storyPhotocopies of the school timetable
The school office keeps the master timetable. Changes are only ever made on the master; then the peon photocopies it and pins a copy on every floor's noticeboard. Students read the noticeboards, so the office isn't flooded with "which period is next?" Five floors, five noticeboards, and the office clerk can get on with her work.
But right after the office moves maths to Friday, the third-floor noticeboard still shows the old copy until the peon gets there. On a quiet day he's quick. On exam week, with forms to photocopy and parents at the gate, the noticeboards can be an hour out of date. And if a student has just asked the office to change their own exam slot, it would be unkind to send them to the noticeboard to check: they should be told by the office itself.
That's the whole story of replication. One master that takes every change, many copies that take the reading, a delay between them that's usually tiny and sometimes not, and a few questions that should always be answered by the master.
In a real productPosting a comment on a social app
You post a comment, the app shows it, then you pull to refresh and for a second it's gone. Refresh again and it's back. Nothing was lost: your comment was saved, but the copy you were reading from hadn't caught up yet. Or you change your profile photo, and a friend sitting next to you still sees the old one for a few seconds.
Most of the time you never notice, because well-built apps hide it. Your own new post appears at once because the app shows it straight from what you typed, or reads your recent changes from the main copy. The like count on a viral video might be a few seconds behind on someone else's phone, and nobody minds. But your wallet balance after a payment is never allowed to show the old amount, and apps go to real trouble to make sure of that.
The ideaHow it works
Why one database runs out of reading power
Most apps read far more than they write. For every comment posted, hundreds of people scroll past it. For every product a seller updates, thousands of shoppers view it. Ratios of 100 reads to 1 write are common, and for feeds and catalogues 1,000 to 1 isn't unusual.
Let's put numbers on it. Say a social app gets 40,000 reads and 400 writes a second in the evening. One well-tuned database server can comfortably answer, say, 10,000 reads a second. So reads alone need the power of four servers. You could buy one enormous server, but there's a limit to how big one machine gets, and it would still be a single point of failure. The other answer is to keep several copies of the same data and spread the reading across them.
- The is the master timetable. Every save goes there, and only there.
- are the photocopies. The primary sends each change to them, and app servers send most reads to the replicas.
For our app: one primary taking all 400 writes a second plus a few important reads, and five replicas sharing the 40,000 reads, about 8,000 each. Why five, not four? So that if one replica fails, the other four can still carry the load at about 10,000 each.
Notice what didn't get divided: the writes. All 400 a second still go to the one primary, and every replica must also apply all 400, because each replica is a full copy. Replicas share the reading, never the writing. When writes outgrow one machine, the answer is , splitting the data itself across several primaries, which is a later lesson.
How the copying works
When the primary saves a change, it first writes a short note of it to a : an ever-growing, numbered list of every change, in order. "Change 48,213,907: set user 1182's name to Ananya Kulkarni." This log is what lets a database recover after a crash, and it's also what replicas follow.
Each replica keeps a connection to the primary and receives the log as it grows. It applies each change in exactly the same order. Same starting point, same changes, same order: same result. A replica only needs to remember one number, the last change it applied, so if it disconnects for a minute it can say "send me everything after 48,213,907" and catch up.
- 1
App serverPrimary
Save: new comment
- 2
PrimaryReplica
Copy the change (a moment later)
- 3
App serverReplica
Load: show the comments
- 4
ReplicaApp server
Here they are (if the copy has arrived)
Replication lag
Copying takes a moment. The change must travel over the network, and the replica must apply it. That delay is . In a healthy system in one city, it's a few milliseconds to a few tens of milliseconds, faster than a person can refresh a page. So a replica is : give it a moment and every copy agrees.
Lag grows when a replica can't keep up. Say a bulk import runs on the primary, making 2,000 changes a second, but a replica can apply only 1,500 a second because it's also busy answering reads. It falls 500 changes further behind every second. After a minute, it's 30,000 changes behind, which at 1,500 a second is about 20 seconds of lag. When the import stops and normal traffic drops back to 400 changes a second, the replica catches up at about 1,100 a second net, so it takes roughly half a minute more to clear the backlog.
Common causes of big lag:
- a burst of writes on the primary, like an import or a mass update;
- a long, heavy query on the replica that slows everything else it's doing;
- a replica that was restarted and is catching up on everything it missed;
- distance: a replica in another city adds the travel time to every change.
The fix starts with measuring. Every replica should report how far behind it is, and the app should stop reading from any replica that falls too far back.
To wait or not to wait
When the primary saves a change, does it wait for the replicas before telling the app "saved"?
- With , it waits for a replica to confirm it has the change. That replica can never be behind on confirmed saves, so if the primary dies, nothing confirmed is lost. The price: every save takes longer (the round trip to the replica), and if that replica is slow or down, saves slow down or stop.
- With replication, the primary confirms at once and sends the change on its way. Saves are fast and don't depend on replicas, but a replica can lag, and if the primary dies, the last few changes it never sent are gone.
Many systems take the middle path: wait for one replica, usually one nearby, and let the rest copy in their own time. Then at least two machines have every confirmed save, without waiting for the slowest of many.
Choosing which copy to read
Lag can't be wished away, so apps decide, request by request, which copy may answer. The common rules:
- Read your own writes. For a short while after you save something, say 10 seconds, your reads go to the primary. Or the app remembers the log number of your save and only uses a replica that has reached it. This promise is called .
- Don't go backwards. If you read from a replica that's up to date and then from one that's behind, a comment can appear and then vanish. Sending each user to the same replica for their whole visit gives : time never runs backwards for you.
- Must-be-exact reads go to the primary. A wallet balance before a payment, the last seat on a train, a stock count at checkout: anything where a stale answer causes real harm.
Everything else, like feeds, profiles, product pages and search results, can read from replicas and accept being a moment behind.
Replicas in other cities, and as spares
Replicas can live close to users. A Delhi user reading from a replica in Delhi waits about 5 ms instead of 25 ms to Mumbai. Their saves still travel to the primary in Mumbai, so writing is no faster, but reading, which is most of what they do, is.
Replicas also make good spares. If the primary dies, a replica that's close to up to date can be promoted to become the new primary, usually within a minute. That's for databases, and it's why even apps with few reads usually run at least one replica.
The big idea: copies let many people read at once and give you a spare, at the price of copies sometimes being a moment behind, so decide which reads can live with that and send the rest to the primary.
For experienced engineers
Physical (WAL or redo-log shipping) replication gives byte-identical replicas and is the default for HA; logical replication (row-change events) allows cross-version, partial and fan-out targets and underpins change data capture. Measure lag in log position and in time (a heartbeat row written by the primary every second is more honest than the replica's own estimate, which reads zero when idle). Read-your-writes is best done with log positions: return the commit LSN or GTID with the write, and route reads to a replica whose replay position is at least that, falling back to the primary; time-window routing is simpler but fragile under lag spikes. Semi-synchronous replication bounds data loss but silently degrades to async on timeout in some systems, so alert on it. Failover with async replication trades RPO for availability: decide explicitly whether to promote a lagging replica or wait, fence the old primary, and make clients reconnect through a stable endpoint. Long-running queries on hot standbys conflict with replay (cancelled queries or held vacuum), so isolate analytics on its own replica. Cascading replicas cut fan-out load on the primary at the cost of extra lag. Multi-leader and leaderless (quorum) designs remove the single write point but bring conflict resolution and read-repair; they're a different trade, not a bigger replica set.
Step by stepOne name change, three readers
Ananya, in Pune, changes her display name on a social app from "ananya_k" to "Ananya Kulkarni". The app has a primary database in Mumbai and three replicas. Most evenings lag is about 20 ms, but tonight Replica 3 is also running a heavy weekly report and is about 1.8 seconds behind. The app follows two rules: for 10 seconds after a user saves, that user's reads go to the primary, and any replica more than 5 seconds behind is taken out of use.
-
9:15:00.000 PM. Ananya taps Save. The app server sends the change to the primary, which writes it to its log as change 48,213,907 and saves it. Confirmed in about 8 ms.
-
9:15:00.010. The app notes the time of her save. For the next 10 seconds, Ananya's own reads will skip the replicas.
-
9:15:00.030. The change reaches Replicas 1 and 2, about 20 ms after the save. Replica 3 receives it too, but it's still working through the changes before it. It will apply this one at about 9:15:01.8.
-
9:15:00.300. Ananya's profile screen reloads. Because she saved under 10 seconds ago, this read goes to the primary. She sees "Ananya Kulkarni" at once. Had it gone to Replica 3, she'd have seen her old name and probably tapped Save again, wondering if the app was broken.
-
9:15:00.500. Her friend Kabir, in Bengaluru, opens her profile. His read is an ordinary one, so it goes to a replica: Replica 3, as it happens. He sees "ananya_k". This is fine: he has no idea she just changed it, and the copy will catch up in about 1.3 seconds.
- 1
AnanyaApp server
Change my name to Ananya Kulkarni
- 2
App serverPrimary
Save it (8 ms)
- 3
PrimaryReplica 3
Copy sent, but this replica is 1.8 s behind
- 4
AnanyaApp server
Reload my profile (300 ms later)
- 5
App serverPrimary
She saved just now, so read from the primary
- 6
App serverAnanya
New name shown
- 7
KabirApp server
Open Ananya's profile
- 8
App serverReplica 3
Normal read, from a replica
- 9
App serverKabir
Old name for about 2 seconds more
-
9:15:01.800. Replica 3 applies the change. All three replicas now agree with the primary. Anyone who opens her profile sees the new name.
-
9:15:12. Ananya scrolls her feed. More than 10 seconds have passed since her save, so her reads go back to the replicas, keeping the primary free for writes.
-
9:16. She buys a ₹299 sticker pack from her wallet balance. This check never uses a replica, whatever the time. A replica a few seconds behind might not yet show a payment she made a moment ago and let her spend the same money twice. Money reads always go to the primary.
-
9:20. The weekly report pushes Replica 3 to 6 seconds behind. It crosses the 5-second limit, so the app stops sending reads to it until it catches up. Replicas 1 and 2 share its reads for the next few minutes, each taking about half as much again as usual. Busy, but within what the team planned for.
Out of thousands of reads, only one person saw something old, for under two seconds, about something that didn't matter to him. That's what good replication looks like.
What happens when…Situations
Real situations this idea runs into, and how well-built systems handle each one.
What happens when a big job runs on the primary
At midnight, a job updates the delivery status of 50 lakh old orders in one go. The primary makes changes far faster than the replicas can apply them while also answering reads. Lag climbs from milliseconds to minutes. Shoppers who just placed an order see "no orders yet", and support gets calls from people convinced they were charged for nothing.
Break big jobs into small batches, say 5,000 rows at a time, with a short pause between them, and slow down further if replica lag rises. Schedule them for the quietest hours. Watch lag on every replica and alert when it passes a few seconds.
What happens when the primary dies
The primary's machine fails at 7 PM. With asynchronous replication, the last two seconds of saves it confirmed hadn't reached any replica. A replica is promoted to be the new primary within a minute, and the app carries on, but those two seconds of saves are gone. Some users posted comments that vanished; worse, one shop's orders from those two seconds have no record.
Make at least one nearby replica synchronous, so every confirmed save exists on two machines before the app is told "saved". Automate the promotion, the old primary so it can't come back taking saves, and point the app at the new primary through a fixed address that moves with it.
What happens when refreshing makes a comment come and go
You post a comment, and your next refresh goes to Replica 1, which has it. The refresh after that goes to Replica 2, which is a second behind, and your comment disappears. The next goes back to Replica 1 and it reappears. Nothing is lost, but to you the app seems broken, and you might post the same comment three times.
Send each user to the same replica for their whole visit, so their view only ever moves forward. Combine it with reading your own recent changes from the primary, and show your own new comment straight from what you typed while the save completes.
What happens when someone runs a heavy report on a replica
The finance team points a spreadsheet tool at a replica and runs a report that scans every order for the past year. It runs for 40 minutes, hogging the replica's disk and memory. That replica slows down, falls minutes behind, and the shoppers whose reads land on it see slow pages and old data.
Give reports and analytics their own replica that serves no users, so nothing a report does can slow down the app. Or copy the data into a separate system built for big reports.
What happens when one replica falls hours behind
A replica's disk gets slow, and it quietly falls further and further behind. Nobody notices, because it still answers every read quickly, just with data from three hours ago. Users whose reads land on it see orders missing and prices from the morning.
Route reads based on lag, not just on whether a replica answers: any replica more than a few seconds behind is taken out of use automatically. If it can't catch up, rebuild it from a fresh copy of the primary instead of waiting.
Watch outCommon mistakes
Deciding about money from a replica
A wallet app checks the balance on a replica before approving a payment. A user pays ₹2,000 and, a second later, taps pay again for another ₹2,000. The second check reads a replica that hasn't seen the first payment yet, sees the full balance and approves it. The user has spent money they didn't have.
Any read that leads to a decision, like approving a payment, booking the last seat or selling the last item, reads from the primary, ideally inside the same database operation that makes the change. Replicas are for showing things, not deciding them.
Adding replicas to fix slow writes
Saves are slow, so the team adds three more replicas. Nothing improves. Writes still all go to the one primary, and now it must send every change to more copies. The real problem, too many writes for one machine, is untouched.
Check first whether the strain is from reads or writes. For too many writes, reduce them (batch small updates, stop saving things nobody reads), make each one cheaper, or split the data across several primaries with sharding.
Counting replicas as backups
An engineer runs a command that deletes a table by mistake. Within milliseconds every replica deletes it too, because they faithfully copy every change, good or bad. The team had five copies of the data and lost all five at once.
Keep real as well: regular full copies plus the change log, stored separately, so you can restore the database to a minute before the mistake. Some teams also keep one replica that deliberately runs an hour behind, as a quick way back.
Testing only where there is no lag
On a developer's laptop, the "replica" and primary are the same database, so lag is zero and every read sees every write. The app ships, and in production users meet missing posts, double submissions and "saved" items that aren't there on the next screen.
Test with real replicas and add artificial lag, say 2 seconds, in testing. Every screen that reads right after a save will show its problem straight away, while it's cheap to fix.
Interview angleIn an interview
Replication comes up whenever a design has far more reads than writes, which is most of them: feeds, catalogues, profiles, URL shorteners. Interviewers listen for whether you split reads from writes, put numbers on it, and know what lag does to the user.
A strong answer covers:
- the read-to-write ratio, and how many replicas that means, with one spare;
- all writes go to the primary; replicas only share reads;
- replication lag, its usual size and what makes it grow;
- synchronous versus asynchronous, and what each costs;
- which reads must go to the primary: the user's own recent changes and anything involving money or limited stock;
- failover by promoting a replica, and what can be lost;
- replicas are not backups.
Follow-up: "How do you make sure a user sees their own new post?" For a short time after a save, send that user's reads to the primary, or only to replicas that have reached the save's position in the log; the app can also show the post straight from what the user typed.
Follow-up: "What happens when the primary fails?" A replica is promoted, after the old primary is fenced off; with asynchronous copying, the last few unsent saves can be lost, so keep one replica synchronous if that matters.
Follow-up: "Your writes are now the bottleneck. What next?" More replicas won't help; batch or reduce writes, move some to a , and when that isn't enough, shard the data across several primaries.
Try it in the simulator
Goal: Take the read load off the primary database.
95% of these requests are reads, and the single database is overloaded. Add a read replica and connect both app servers to it. App servers send reads to the replica and saves to the database. Run again and compare how busy the primary is before and after.
Quick checkCheck your understanding
Words in this lessonThe jargon, in plain English
Every technical word used above. In the lesson, tap any word with an ⓘ to see its meaning right there.
- Asynchronous
- Done later, without making the caller wait for the result.
- Backup
- A copy of your data kept somewhere else, so you can get it back if the original is lost or broken.
- Eventual consistency
- A guarantee that copies will agree once updates stop arriving, but may briefly disagree.
- Failover
- Moving work from a failed component to a healthy one, ideally automatically.
- Fencing
- Cutting off a machine that has been replaced, by blocking its network or taking away its right to write, so it can't keep making changes if it turns out to be still running.
- Message queue
- A buffer that holds messages until a consumer is ready to process them, so producers don't have to wait.
- Monotonic reads
- A promise that once you have seen a change, you won't later see an older version, usually kept by sending each user to the same copy of the data.
- Primary database
- The main copy of a database. Every change is saved here first, then copied to the replicas.
- Read replica
- A copy of a database that follows the primary's changes and serves reads, taking load off the primary.
- Read your own writes
- A promise that after you change something, you'll see your own change, even if others see it a little later.
- Replication lag
- The delay between a change on the primary database and that change appearing on a replica.
- Sharding
- Splitting one large dataset across several databases, each holding a part.
- Synchronous replication
- Copying each change to a replica and waiting for it to confirm before telling the app the save worked. Nothing confirmed is lost if the primary dies, but every save takes a little longer.
- Write-ahead log
- A numbered, ever-growing list of every change a database makes, written down before the change itself. It lets the database recover after a crash, and replicas follow it to copy every change in order.
Enjoyed this? Keep going for free
This lesson is a free preview. Sign up free to save your progress and earn XP, and to unlock all 232 lessons, the simulator challenges and mock interviews.
Sign up free