Capacity Planning for PostgreSQL Over Five Years

Four Things Grow, Not One

Most capacity plans take the current size of the data, apply a growth percentage, and multiply by five. That produces a number, and the number is almost always too small — not because the growth rate was wrong, but because data is only one of four things that grow, and the other three are the ones that run you out of room.

Indexes grow, often faster than the tables they serve. Write-ahead log volume grows with write rate rather than data size, and it governs how much bandwidth your archiving and replication need. Bloat grows as a function of update and delete patterns, which means two tables of identical size can need very different headroom. Plan for all four or plan again next year.

Start From Measurements, Not Estimates

Before modelling anything, get the current state from the database rather than from memory. These two queries give you the starting point for everything that follows.

-- Per-table size, split into heap, indexes and TOAST
SELECT c.relname,
       pg_size_pretty(pg_table_size(c.oid))    AS heap_toast,
       pg_size_pretty(pg_indexes_size(c.oid))  AS indexes,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS total
FROM   pg_class c
JOIN   pg_namespace n ON n.oid = c.relnamespace
WHERE  c.relkind = 'r' AND n.nspname = 'public'
ORDER  BY pg_total_relation_size(c.oid) DESC
LIMIT  25;

-- Write pressure and dead tuples, per table
SELECT relname, n_tup_ins, n_tup_upd, n_tup_del,
       n_live_tup, n_dead_tup,
       CASE WHEN n_live_tup > 0
            THEN round(100.0 * n_dead_tup / n_live_tup, 1) END AS dead_pct
FROM   pg_stat_user_tables
ORDER  BY n_tup_upd + n_tup_del DESC
LIMIT  25;

One caution on the second query: n_live_tup is a statistics estimate maintained by the collector, not a count. It is the right input for a capacity model and the wrong input for anything that must be exact. If you need a true figure, count the rows.

Indexes Are the Line People Forget

Run the size query above and look at the ratio of index bytes to heap bytes. On a transactional schema that has accumulated indexes over years, it is common to find tables where the indexes exceed the data. That ratio travels with you: if indexes are 1.4x the heap today and you expect the heap to triple, the index footprint triples too.

This is also the cheapest place to find headroom before you buy any. Unused indexes cost write throughput and disk and return nothing, and PostgreSQL will tell you which they are.

SELECT relname, indexrelname,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size,
       idx_scan
FROM   pg_stat_user_indexes
WHERE  idx_scan = 0
ORDER  BY pg_relation_size(indexrelid) DESC;

Treat a zero scan count as a question rather than a verdict — the counter resets with statistics, and an index that serves a quarterly report will look unused for eleven weeks. Check how long the statistics have been accumulating before you drop anything.

WAL Scales With Writes, Not Size

A 2TB reporting database that is written once a night can produce less WAL in a day than a 50GB order-entry database under constant load. WAL volume drives three things you have to size: local disk for the pending segments, network and storage for archiving, and replication bandwidth for every replica you intend to keep.

Measure it rather than deriving it. Record the current WAL position, wait a representative interval — a busy hour, not a quiet one — and take the difference.

-- Run twice, an hour apart, then subtract
SELECT now(), pg_current_wal_lsn();

-- Bytes between two positions
SELECT pg_size_pretty(
         pg_wal_lsn_diff('0/9A000000', '0/12000000')
       ) AS wal_in_interval;

Scale the result to a day, then to a peak day, and remember that full-page writes after a checkpoint make the first writes to each page far more expensive than later ones. A plan built on an average hour understates the peak.

Bloat Is a Workload Property

PostgreSQL's MVCC means an update writes a new row version and leaves the old one for autovacuum to reclaim. The practical effect is that a table's steady-state size depends on its churn, not just its logical content. An append-only table of 200GB stays near 200GB. A 200GB table where every row is updated daily needs materially more room, and will keep needing it.

Plan headroom per table according to its write pattern, using the n_tup_upd and n_tup_del figures from the earlier query. The high-churn tables are usually a short list, and they are where the headroom belongs.

Autovacuum Capacity Is Capacity

This is the part most plans omit entirely. Autovacuum has a throughput ceiling set by its cost-delay settings, and if dead tuples are created faster than it can reclaim them, the table grows regardless of how much disk you bought. You end up with a database that has free space and still behaves as though it does not.

As your write volume grows, autovacuum settings that were comfortable become the binding constraint. The defaults are deliberately conservative for a small machine; a database that has grown an order of magnitude since install is usually running vacuum far below what its hardware allows. Treat autovacuum_vacuum_cost_limit, the scale factors, and the worker count as numbers to revisit on the same cadence as disk.

Partition Before You Need To

Declarative partitioning is straightforward to introduce on a table you are creating and awkward on a 2TB table already in production, because the conversion needs either a rewrite or a careful attach-and-backfill. If your five-year model says a table will pass the point where you would want it partitioned, partition it during the migration while it is empty and the cost is zero.

The signal to look for is not size alone but whether the data has a natural retention or access boundary — usually time. A table you will eventually want to detach old months from is a table to partition now.

The Plan Is the Review, Not the Number

Any five-year projection is a guess about a business, and the useful part is not its precision but the fact that you will compare it against reality. Write the plan down with its assumptions visible — growth rate per major table, index ratio, WAL per day, churn headroom — then review it quarterly against actuals and correct the assumptions that were wrong.

A simple model reviewed four times a year beats a sophisticated one written once and filed. The first review usually shows that one or two tables account for most of the growth, and from then on the plan is mostly about them.