Leyla published a photo and refreshed the page, and the photo was not there. A couple of seconds later it appeared by itself. This is not a bug in the app but a greeting from a database replica that lags a little behind the main server. We can multiply app servers and cache reads, but the database is still one. Let us see how to scale it: in what order to act, how read replicas work and why they lag, how partitioning differs from sharding, how to choose a shard key and why PgBouncer is needed.

Simple things first: the database scaling ladder

Each step is more complex and more expensive than the previous one, so you climb it only when the lower one is exhausted:

  1. Measure and fix queries. EXPLAIN ANALYZE, pg_stat_statements, indexes (the lesson "How Indexes Work: B-Tree and CREATE INDEX"), removing N+1.
  2. A cache (the previous lesson) removes most of the reads.
  3. A bigger server (vertical scaling): simple and often enough.
  4. Read replicas and a connection pool.
  5. Partitioning of big tables.
  6. Sharding comes last.

Prowid Photos numbers: photo metadata is about 36 GB a year (the calculation from the first lesson), about 100 GB with likes and comments. If each user leaves about 5 more likes and comments a day, that is ≈ 60 writes per second on average and ≈ 200 at peak. A single PostgreSQL handles this with a big margin: sharding is not needed today, while replicas are useful for fault tolerance and to offload reads. But imagine 100 times more:

TodayGrowth ×100
Daily active users1 M100 M
API peak350 RPS35,000 RPS
Metadata per year≈ 100 GB≈ 10 TB
Writes at peak≈ 200/s≈ 20,000/s

Here one server is at its limit for writes and volume, and sharding becomes an honest conversation.

Read replicas

A write goes to the primary and the replica receives it with a delay
A write goes to the primary and the replica receives it with a delay

Prowid Photos has a hundred times more reads than writes, an ideal case for replicas. The primary (the leader) accepts all writes. Replicas (followers) receive the stream of changes and answer reads. In PostgreSQL this is streaming replication of the write-ahead log (WAL). Replication comes in two kinds:

  • Asynchronous (the default): the primary confirms a write without waiting for the replica. Fast, but the replica lags, and if the primary fails the latest confirmed writes may be lost.
  • Synchronous (synchronous_standby_names): the primary waits for the replica's confirmation. No data is lost, but writes are slower, and a failed replica may stall writes.

Replicas scale reads, not writes or volume: each holds a full copy. And a replica is not a backup: DROP TABLE reaches the replicas instantly. In the app two connection pools are enough (the Prisma extension @prisma/extension-read-replicas does the same with $primary() and $replica()):

js
import pg from 'pg';

const { Pool } = pg;

const primary = new Pool({ connectionString: process.env.PRIMARY_URL });
const replica = new Pool({ connectionString: process.env.REPLICA_URL });