Read Replicas

A learning platform, similar to this one, had 500,000 registered users. The database handled everything: user signups, lesson progress writes, quiz scoring, AND the heavy read queries for the dashboard, leaderboard, course catalog, and roadmap.

The write load was manageable, maybe 200 writes per second during peak. But reads were crushing. The leaderboard page ran an expensive aggregation query. The course catalog page queried lessons with filters and pagination. The dashboard pulled progress data with multiple JOINs. During peak hours. 6 PM to 10 PM, the database CPU sat at 95%. Read queries started queuing behind write transactions. Everything got slower.

The instinct is to throw a bigger server at the problem. Vertical scaling. They went from 4 cores to 8 cores. Bought themselves three months. Then from 8 to 16. Bought two more months. This doesn't end well, eventually there's no bigger server to buy, and each upgrade costs double.

The real fix: stop making one database do everything. Writes go to the primary. Reads get distributed across replicas. This is read replication, and it's how every database-heavy platform scales beyond a single server.

The concept is straightforward. You have one primary database (also called "master" or "leader") that handles all writes. You create one or more replicas (also called "followers" or "standbys") that receive copies of the data from the primary and handle read queries.

The replication process:

1. A write happens on the primary (INSERT, UPDATE, DELETE)
2. The primary writes the change to its Write-Ahead Log (WAL)
3. The WAL records are shipped to each replica (streaming replication)
4. Each replica applies the WAL records to its own copy of the data
5. The replica is now up to date (with some lag)

From the application's perspective, you route queries based on type:

• Writes (INSERT, UPDATE, DELETE, transactions) go to the primary
• Reads (SELECT) go to a replica

// Simplified connection routing
import { neon } from '@neondatabase/serverless';

// Primary, for writes
const primary = neon(process.env.DATABASE_URL!);

// Replica, for reads
const replica = neon(process.env.DATABASE_READ_REPLICA_URL!);

// Write: always primary
await primary`INSERT INTO lesson_progress (user_id, lesson_id, completed) VALUES (${userId}, ${lessonId}, true)`;

// Read: use replica
const progress = await replica`SELECT * FROM lesson_progress WHERE user_id = ${userId}`;