Capacity planning beyond current database size

“The database is 400 GB, so we need 800 GB” is the plan that produces a 3 a.m. page. Here is everything that plan omits.

What consumes space

The two in bold are the ones that surprise people, and both have been measured earlier in this course rather than estimated.

Projecting growth from measurement

Row counts are the wrong unit. Measure the bytes.

-- record this weekly; the derivative is the growth rate
SELECT current_database() AS db,
       now()                                        AS at,
       pg_database_size(current_database())         AS db_bytes,
       (SELECT sum(pg_total_relation_size(oid))
          FROM pg_class WHERE relkind IN ('r','m')) AS rel_bytes;

-- the biggest objects, and how much of each is index
SELECT relname,
       pg_size_pretty(pg_total_relation_size(c.oid))              AS total,
       pg_size_pretty(pg_relation_size(c.oid))                    AS heap,
       pg_size_pretty(pg_indexes_size(c.oid))                     AS indexes,
       round(100.0*pg_indexes_size(c.oid)
             /NULLIF(pg_total_relation_size(c.oid),0))            AS pct_index
  FROM pg_class c JOIN pg_namespace n ON n.oid=c.relnamespace
 WHERE c.relkind='r' AND n.nspname NOT IN ('pg_catalog','information_schema')
 ORDER BY pg_total_relation_size(c.oid) DESC LIMIT 20;

The pct_index column earns its place. Lesson X-05 measured a table with 65 MB of indexes over a 33 MB heap — the indexes were twice the data — and index growth tracks write volume rather than row count.

The other resources

Connections. Lesson IV-02’s arithmetic: max_connections reserves per-connection memory whether or not connections exist, and the practical limit is usually well below what people set. Pooling is the answer, not a larger number.

Memory. Lesson XI-01’s map. shared_buffers plus max_connections × work_mem in the worst case plus maintenance_work_mem per autovacuum worker, and lesson XI-06’s OOM killer for what happens when the sum exceeds reality.

I/O. Lesson VI-01 measured 854 µs fdatasync predicting a ~1,170 tps commit ceiling, and pgbench then measured 842. Commit rate is bounded by fsync latency, and no amount of CPU changes that.

CPU. Usually the last constraint to bind, and the easiest to see.

What to take from this

• The database size is the smallest term. WAL, archive, backups, logs, temp and bloat headroom are the rest.
• Measured: 4,359 kB/s of WAL is 367 GB a day, none of it database size.
• Size for the largest single operation — a table rewrite, a restore, a copy-mode pg_upgrade.
• Project from measured bytes over time, not from row counts.
• Indexes can exceed the heap; measured at twice its size.
• Growth is not linear: index fragmentation, step-function bloat, and conditional truncation.
• Plan connections, memory and fsync latency alongside storage.

Cross-course references

• Linux for Production Sysadmins — Part LXVI (Capacity planning for clusters) covers forecasting from a trend rather than from a current reading, and Part XVI (LVM) covers whether growing the volume is actually available to you.
• Ceph & Distributed Storage — Part LXIII (Capacity management) and Part LXVI (Capacity forecasting) cover the same arithmetic where adding capacity has a rebalance cost.
• Observability for Production Sysadmins — Part LXXIV (Capacity planning) covers holding the series long enough for a forecast to mean anything.

Quiz