Capacity Planning

In one line

Estimate reads, writes and transactions per second at peak; storage per day and per year including indexes, WAL and backups; memory for the working set; connections; IOPS and network. Then choose instance sizes, replicas and partitioning with headroom (target ~50–60% utilisation at peak) and a growth horizon of 12–18 months.

0/5 · 0%

Think of it like this

Planning a wedding venue. Guests (users) × how many plates each eats (requests per user) gives the kitchen load at the peak hour; the hall size (storage) must fit next year's larger guest list too.

Key ideas

01
Traffic: DAU × actions per user per day / 86,400 × peak factor (2–10×). Split reads and writes; note transactions per request.
02
Storage: rows per day × row size (data + ~24-byte tuple header + alignment) × (1 + index overhead, often 0.5–1.5×) × bloat factor (~1.2–1.5); add WAL volume (retention for replicas and PITR) and backup storage (full + incrementals + WAL).
03
Memory: the working set (hot data and index pages) should fit in RAM for OLTP; shared_buffers ~25% plus OS cache. Connections: pool sizes × instances; memory per backend (~5–10 MB plus work_mem when active).
04
IOPS: cache misses × pages per query, plus WAL and checkpoint writes. Cloud volumes have provisioned IOPS and throughput limits (e.g. gp3 baseline 3,000 IOPS, 125 MB/s).
05
Validate estimates with load tests and production metrics; revisit quarterly; plan the next scaling step (bigger instance, more replicas, partitioning, sharding) before hitting 70–80%.

Code & diagrams

capacity-worksheet.txttext

E-commerce orders DB, 12-month horizon
Users: 20M, DAU 4M; orders/day 1.2M; order lines 3 per order
Writes: 1.2M orders/day -> 14/s avg, x8 peak = 110/s orders (~500 row writes/s)
Reads:  order history + status: 30 reads per order -> 36M/day -> 420/s avg, 3,400/s peak

Row sizes (with header/alignment): orders 180 B, order_line 120 B
Per day:  1.2M x 180 B + 3.6M x 120 B = 216 MB + 432 MB = ~650 MB
Indexes (x1.0) + bloat (x1.3): ~1.7 GB/day -> ~620 GB/year
WAL: ~3x data change volume -> ~2 GB/day; 7-day PITR retention -> 14 GB
Hot working set: last 90 days of orders + indexes ~ 150 GB -> 256 GB RAM instance
Connections: 30 app pods x 10 via PgBouncer -> 60 server connections

Interview problem

The problem

Capacity plan for a chat service

10M DAU, 40 messages sent per user per day, average message 200 bytes, each message read ~3 times, 1-year hot retention. Estimate write and read QPS, storage per day and year, and propose the store and cluster size.

When it breaks

Planning with averages only

What you see

The database handles average load but peak hour (8× average) saturates IOPS, and year-end campaigns cause outages.

Fix & prevent

Plan for peak with a stated peak factor, validate with load tests, and keep ~40% headroom.

Explain it without notes

What components of storage do people forget?

Practice

Estimate RAM needed if the hot set is the last 30 days of a table growing 5 GB/day with indexes 80% of table size.

Trade-offs

↔
Over-provisioning wastes money; under-provisioning causes outages. Headroom plus regular review balances them.

Done when you can

I can estimate QPS, storage, memory, connections and IOPS and turn them into a sizing plan.

Command Palette