Brief #06 · Backend
Scaling PostgreSQL to 10M Users
Hard⏱ 20-30 h
Take one Postgres from 1 million to 10 million users, stage by stage: indexes, pooling, replicas, partitions, then shards, with a benchmark at every step.
Outlines are free · building with tracking needs Pro
SThe briefFrom the team
Our social app runs on one Postgres server. At 1 million users it was fine; marketing promises 10 million by next year, and the feed already takes 3 seconds at 9 p.m. Before anyone says 'just add more servers', show us, with numbers, which change buys how much, and when sharding is actually worth its pain.
What you'll build
- 1Seed 1M users, follows and posts with a script, and write a k6 or pgbench workload that mimics the feed (90% reads, 10% writes). Record p95 and throughput as your baseline.
- 2Find the slow queries with
pg_stat_statementsandEXPLAIN ANALYZE; fix them with the right indexes (composite, partial, covering) and show the plan before and after. - 3Put PgBouncer in front in transaction mode and show what happens to connections and latency with 2,000 clients.
- 4Add two streaming read replicas; route reads to them and handle replication lag so a user always sees their own new post (read-your-writes).
- 5Partition the posts table by month, then grow to 10M users and shard by user_id across four Postgres nodes with a routing layer, and move one shard's data online without downtime.
Step-by-step walkthrough
Done when
- A results table: p95 and throughput after every stage, on the same workload and hardware.
- No query in the hot path does a sequential scan on a large table.
- Killing the primary and promoting a replica loses no committed write, and you've measured how long it took.
- Moving a shard keeps the app answering throughout, and a check confirms row counts match afterwards.
Stretch goals
- Compare your hand-rolled sharding with Citus on the same workload.
- Find a hot shard (one celebrity user) and fix it.