UNDER PRESSURE

Level 2 · The Database Is the Bottleneck

Session 18: Reading Far More Often Than Writing: Read Replica & Replication Lag

What if 95% of the database load turns out to be just people checking balances and browsing the catalog, not changing data — and when we add backup servers for reading, users complain that the posts they just uploaded suddenly disappeared?

Session 18 / 344 min read

1. Imagine If

A library has a single master book of secret records. 1,000 students show up: 950 only want to read, and 50 want to write new entries. Since there's only one book, the line snakes on for 3 hours.

The head librarian photocopies the master book into 5 copies so the 950 readers can read at the same time. But there's a 10-minute delay for the staff to photocopy every new page that was just written in the master book.

One student has just registered their name in the master book, then immediately checks a copy: their name isn't there yet. They panic, thinking the registration failed, and register again 5 more times!

2. What Actually Happens

Separating read and write load (Read/Write Splitting) is a fundamental database scaling strategy:

  1. Primary (Writer) vs Standby Replica (Reader): - Primary Database: The only node that accepts write transactions (INSERT, UPDATE, DELETE). It writes changes to the WAL (Write-Ahead Log). - Read Replicas: Copy nodes that receive the log data stream from the Primary asynchronously (Streaming Replication). They only serve read queries (SELECT).

  2. Replication Lag: - Because replication runs asynchronously to keep write latency on the Primary lightning fast (< 2 ms), there's a delay of a few milliseconds up to a few seconds before a transaction lands on the replica's disk. - When the Primary gets hammered by heavy write load (e.g., a batch insert or a table migration), replication lag can balloon from 50 ms to 15 seconds!

  3. Read-Your-Own-Writes Problem: - A user updates their profile photo -> The write request goes to the Primary (OK). - The page refreshes -> The read request for the profile photo gets sent by the Load Balancer to a Read Replica that's lagging 2 seconds behind. - The browser shows the old profile photo. The user thinks the system is broken and clicks the upload button over and over.

3. What If We Try...

"Let's just make replication synchronous to every replica!"

Why is this a bad idea for performance? - In synchronous replication (Synchronous Replication), every COMMIT on the Primary has to wait for confirmation that the data has been written to the RAM/disk of ALL replicas. - If a single replica hits a slow disk or a network stutter, every write transaction on the Primary grinds to a halt (Write Latency Spike). System throughput plummets.

4. The Official Name

  • Read/Write Splitting: Separating read and write query connections at the application / pooler level.
  • Replication Lag / Replication Delay: The data delay between the master and its copies.
  • Eventual Consistency: A guarantee that data will definitely become consistent eventually, not instantly.
  • Read-After-Write Consistency (Causal Consistency): A guarantee that users always see the changes they just made themselves.
  • LSN (Log Sequence Number) Tracking: A database log position token used to make sure a replica has caught up to a specific transaction before it's read.

5. In Our World

How engineering teams solve this dilemma: - Sticky Primary Routing (Session Pinning): After a user performs a write, route all of that user's read queries to the Primary for the next 5 seconds (grace period). - LSN-Based Routing: The application includes the LSN token of the last write; a read request is only sent to a replica if the replica's LSN \ge the token's LSN. - Replica Lag Health Check: If a replica's replication lag is > 5 seconds, automatically pull that replica out of the traffic distribution pool.

6. The Performance Tester's Lens

Crucial metrics and tests: 1. Replication Lag Under Peak Write Load: Measure spikes in replica lag (pg_stat_replication.replay_lag / Seconds_Behind_Master) under a write load of 2,000 TPS. 2. Read-After-Write Race Condition Test: Test an automated scenario: create a new order -> immediately read the order details within < 10 ms. What percentage of transactions find the data missing (404)? 3. Primary Write Saturation: Make sure the Primary doesn't run out of CPU from being forced to serve streaming replication to too many replicas (recommendation: max 3–5 direct replicas per primary).

7. Question for the Next Round

Read replicas successfully split the read load across 5 servers. But what if 10,000 users ask for the exact same viral piece of data within a single second — do we really have to ask the database 10,000 times?

The answer is in Session 19: Holding Questions Back from the Database (Redis Cache, Hit Ratio, & Thundering Herd).