Your database is two databases now, and one is behind

When you start learning backend, you get buried in words.

Sharding. WAL. Replication. Partitioning. Connection pooling. Eventual consistency. CAP theorem. Every architecture video says them fast, like you already know what they mean, and you nod along and look up none of them.

I did that for a while. Then I was reading through some backend code at work and saw two database connections where I expected one. Not a pool, two separate connections, one of them read-only.

I knew the phrase "read replica." I could have defined it in an interview. But I could not have told you how the data actually gets from one machine to the other, or why anyone would ever read from the slower copy on purpose.

So I went down. All the way to level 0, then back up.

This is that walk. If you only remember one thing from it, remember that a replica is always slightly behind, and that everything hard about replicas comes from that one fact.

Start with one database#

Level 0 is one box.

Your app writes to it and reads from it. It holds all your data and it answers every question. For most apps this is correct and you should not change it.

Then traffic grows and the box starts to sweat. Queries get slower. The dashboard that loads a big list takes four seconds. Somebody says "we need to scale the database."

Before you do anything, look at the ratio. In almost every product, reads massively outnumber writes. A user writes a comment once and it gets read a thousand times. A test run is created once and its results page is opened all day.

So the database is not evenly busy. It is overwhelmingly busy answering questions, not recording new facts. That imbalance is the opening. If you could move the questions somewhere else, the one box would have a much easier life.

To move the questions somewhere else, you need a second copy of the data.

Why copying the file does not work#

The naive idea is to copy the database files onto a second machine and point some reads at it.

This fails for two reasons, and both are worth understanding.

First, it is a snapshot. The moment the copy finishes it starts going stale, and it keeps going stale forever. You would have to re-copy the whole thing constantly, which is exactly the load you were trying to avoid.

Second, and worse, a running database is never in a clean state on disk. It has changes sitting in memory that have not been written out yet. It has half-finished transactions. Copy those files mid-flight and you get a corrupted database, not a copy.

What you actually want is not a photo of the data. You want a live feed of the changes.

That feed already exists. It just was not built for this.

The log that makes everything possible#

Here is the piece I did not know, and it turned out to be the centre of the whole topic.

When a database changes a row, it does not change the row first. It writes down what it is about to do, into an append-only file, and only then touches the actual data.

Postgres calls this the write-ahead log, or WAL. It is a strictly ordered list of every change, written before the change lands.

Why do databases do this? Crash safety. If the power dies halfway through an update, the database restarts, reads the log, and replays anything that did not finish. That is how your data survives a machine dying mid-write. The log is the source of truth, the tables are just the current result of replaying it.

Now sit with that for a second.

The log contains every change, in exact order, in a form that can rebuild the database. That is a complete, replayable history of everything that ever happened.

And if a log can rebuild a database on the same machine after a crash, there is nothing stopping you from shipping that same log to a different machine and replaying it there.

That is exactly what Postgres does. One stream, two jobs.

MySQL deserves a note here, because people mix this up constantly and I did too. MySQL has two logs, not one. The InnoDB redo log is its real write-ahead log and handles crash recovery. The binary log, or binlog, is a separate record of changes living at the server layer, and the binlog is the one replication reads. Two logs, two jobs, and the database has to keep them agreeing with each other on every single commit.

The core idea survives either way: a log of changes is enough to rebuild your data somewhere else. Just know that "the write-ahead log" and "the log replication reads" are the same file in Postgres and different files in MySQL.

A replica is crash recovery pointed at another machine#

That is all a replica is.

A second database that connects to the first, streams its change log, and replays every entry in the same order. It never gets its own writes. It just re-does whatever the primary did, forever.

The vocabulary from here on:

• The primary is the original. It takes every write.

• A replica is a copy that only replays the primary's log. It is read-only.

• Replication is the act of streaming that log across.

• Replication lag is how far behind the replica currently is.

One naming note, because it will confuse you otherwise. Plenty of articles, tutorials, and code still running in production today call these two things master and slave. Same concepts, older words.

The industry moved away from that language a few years ago and the databases moved with it. MySQL now officially says source and replica. Postgres says primary and standby. Redis kept master but swapped slave for replica.

So you will run into all of these, sometimes in the same codebase. Master, source, and primary are the same role. Slave, standby, and replica are the same role. I use primary and replica for the rest of this post, because that is what you will see in anything written recently.

Now the payoff. You have two boxes holding the same data. Writes must go to the primary, because a replica physically cannot accept them. But reads can go to either one.

Point your heavy reads at the replica and the primary suddenly has far less work. Add a second replica and you have more read capacity again. This is why replicas are the first scaling move almost everyone makes: you get more read capacity without changing a single query.

What this looks like in your app#

Two connections instead of one.

That is genuinely the whole change on the application side. You open a connection to the primary the way you always did, then you open a second one to the replica. Each gets its own connection pool, because they are separate machines with separate capacity, and you size them separately.

Then every query picks a side:

• Anything that writes goes to the primary. It has no choice. The replica will refuse it.

• Anything that reads can go to either one, and you decide.

Both connections get built from config rather than hardcoded, and that detail matters more than it sounds. If no replica is configured, the second connection is never created and the code quietly hands back the primary instead.

That fallback is what keeps this from infecting your whole codebase. The same code runs on your laptop against one database and in production against two. Nothing above the data layer has to know which world it is in, and nobody has to run a replica locally just to boot the app.

Notice what is not in that list. No proxy sitting in front of your database, no middleware inspecting your SQL and routing it for you. Those tools exist and they have their place, but the common version of this is two connections and a decision per query.

Which raises the obvious question: which reads go where? That turns out to be the hard part of the whole topic, and to understand why, you need to know what the primary is doing in the moment it replies to you.

Waiting, or not waiting#

Now the fork in the road that decides everything.

When you write a row, the primary saves it and has to tell your app "done." The question is whether it says that before or after the replica has the change.

Asynchronous. The primary commits, immediately tells your app it is done, and ships the log to replicas in the background. Writes stay fast. The primary does not care if a replica is slow or offline. This is the default nearly everywhere.

Synchronous. The primary waits for the replica to confirm before telling your app anything. Now your write is safe in two places before the user ever sees a success message, and depending on how you configure it, the replica can be current enough to read from immediately. But every write is now as slow as your slowest replica plus a network round trip, and if that replica goes down, your writes can stall completely.

Semi-synchronous. The middle ground. The primary waits only until a replica has received the log, not until it has finished applying it. You lose less data in a disaster, and you pay less latency than full sync.

Almost everyone runs async, because write latency is user-visible and people notice it.

And that choice is where the entire rest of this post comes from. Async replication means there is a window, after your write is confirmed, where the replica genuinely does not have your data yet.

Where lag actually comes from#

In a healthy system, lag is a few milliseconds. You would never notice.

It stops being a few milliseconds when:

• A big write lands. A bulk import or a batch job dumps a huge amount into the log, and the replica has to chew through all of it.

• Applying is slower than writing. The primary can write changes in parallel across many connections. A replica replaying a log has far less room to parallelise, so it can fall behind a busy primary even on identical hardware.

• A long transaction blocks the line. The log is strictly ordered. One slow entry holds up everything behind it.

• The replica is busy being useful. It is also serving all your heavy read traffic. Those queries compete with the replay work.

• The network hiccups. Especially if the replica lives in another region.

So lag is not constant. It is usually tiny and occasionally terrible, and it gets worst exactly when your system is busiest. That is a nasty shape for a bug.

The bug that looks like your fault#

Here is how it shows up, and it almost never gets diagnosed correctly the first time.

A user fills in a form and hits save. Your app writes to the primary. The write succeeds. Your app then redirects to the list page, which fetches the records, and that read goes to a replica.

The replica has not replayed the change yet.

So the list comes back without the thing the user just created. They stare at a screen that is confidently telling them their work vanished. They hit refresh and now it is there.

Every instinct sends you the wrong way. You suspect a caching layer. You suspect a race condition in your own code. You add a delay and it seems to fix it, which is the most dangerous outcome of all, because you have not fixed anything, you have just made the window smaller.

This has a proper name: read-your-own-writes consistency. The guarantee that a user who writes something will see it in their next read. Async replication quietly takes that guarantee away, and nothing in your code will tell you.

How to actually fix it#

The reflex answer is "send that read to the primary." That works, and if you do it everywhere you have deleted the entire reason you added replicas.

Better options, cheapest first.

1. Do not re-read at all. Your write already returned the saved record. Use it to update the screen instead of fetching again. Free, and it removes the problem rather than working around it. This solves a surprising number of real cases.

2. Stick to the primary for a short window. After a user writes, route that user's reads to the primary for the next few seconds. Everyone else keeps hitting replicas. Simple, effective, and the cost is contained to one user for a moment.

3. Wait for the replica to catch up. Every change in the log has a position. Postgres has an LSN (log sequence number), MySQL has a GTID (global transaction ID). Record the position of your write, then have the read wait until the replica has reached that position before it answers. This is the precise fix. You pay a little latency only on the reads that actually need it.

Which you pick is a real engineering choice, and it is the kind of thing worth being able to argue about in an interview.

There is a fourth approach, and it is the one that changed how I think about all of this. It comes later in the post, because it is less a fix than a different way of deciding.

Reading backwards in time#

One more failure mode, because it only appears once you have more than one replica, and it is genuinely surprising.

Two replicas will not have identical lag. One might be current, the other two seconds behind.

Now a user loads a page and the read hits the caught-up replica. They see their new comment. They click to page two, that request lands on the lagging replica, and the comment is gone. They go back, and it is there again.

Nothing crashed. No error was logged. Data just appeared to travel backwards in time.

The guarantee you want here is called monotonic reads: a user should never see the clock run backwards. The usual fix is to pin one user's session to one replica, so at worst they see consistently slightly-old data instead of flickering between two versions of reality.

Consistently stale is fine. Inconsistently stale is what makes people think your product is broken.

The rule nobody writes down#

Back to the code that started this.

I expected to find a global setting somewhere: "reads go to the replica." There was no such setting. Instead, the choice was passed in per query, one call at a time, as a plain argument. Something like this:

getRecordById(id, useReplica = false)
getFilteredList(filters, useReplica = true)

At first I read that as messy. Why would you make this decision hundreds of times instead of once?

Then I looked at which queries chose which, and the pattern was not what I expected.

A search and filter query, the kind that runs when a user types into a search box, forced itself onto the replica even when the caller had not asked for it. A fetch one record by its ID did not. That one stayed on the primary unless it explicitly opted in.

Both are reads. Only one goes to the replica by default. So "reads go to the replica" was not the rule being followed.

The actual rule is this: the question is not "is this a read," it is "would a user notice if this answer were one second old."

Search results, filtered lists, dashboards, history, analytics: nobody can tell if those are a second stale, and they are exactly the expensive queries that were hurting the primary. Ship them to the replica.

Fetching the single record a user may have created two seconds ago: they will absolutely notice. Keep it on the primary.

That reframing is the whole thing. Replicas are not a read/write split. They are a staleness-tolerance split, and reads just happen to be where most of the tolerance lives.

One more detail from that code worth stealing. The helper that returns the read connection falls back to the primary when no replica is configured. So the same code runs on a laptop with one database and in production with two, and nothing in between has to know which world it is in. Optional infrastructure, no branching.

Three things a replica is not#

Worth being clear about, because these get mixed up constantly.

A replica is not a backup. Replication is faithful. If you delete a table on the primary, that delete is in the log, and the replica deletes it too, in milliseconds. A replica protects you from a machine dying. It does nothing about a bad migration or a wrong DELETE. You still need real backups.

A replica is not sharding. A replica is a full copy and it splits your read load. A shard is a partial copy and it splits your data, which means it splits writes too. Replicas are the easy first move. Sharding is the hard later one, and you reach for it when a single primary can no longer keep up with writes, which is a much rarer problem than people think.

A replica is not automatic failover. You can promote a replica to become the new primary when the old one dies, and this is a real strategy. But with async replication the replica may be missing the last few writes, so promoting it loses them. And if the old primary comes back up still thinking it is in charge, you now have two primaries accepting writes, which is called split-brain and is exactly as bad as it sounds. Failover is its own topic with its own sharp edges.

What I would tell myself at level 0#

The concept is genuinely simple. A replica is a second database that replays the first one's change log. Two connections instead of one. You can set it up in an afternoon.

The hard part is not the setup. It is that you have quietly traded a guarantee away, and you traded it before you knew you had it. Your database used to be one thing that always told you the truth. Now it is two things, and one of them is a little behind, and it is your job to decide which questions it is allowed to answer.

So the next time you see a word like WAL or sharding go past in a video, pick one and go all the way down to the bottom of it. The bottom is usually simpler than the word suggests.

It is the part above the bottom, the part where you have to make a judgement call, that is actually worth learning.

And start with this one. MySQL, Postgres, or something that has not been written yet. Go, Node, Python, it makes no difference at all. Replication is not a feature of one database, it is an idea that every serious system ends up needing, so learn it before the day you need it.