PostgreSQL Capacity Planning Review

How to use this review

Quarterly. Its output is a list of dates at which something must change, each with an owner.

A review that produces percentages and graphs is a report. “This volume fills in seven months” and “this vacuum stops fitting the window in March” are planning statements, and they are what the rest of the organisation can act on.

Four capacity limits, and only one is disk

Three of them are invisible on a storage dashboard

Disk. The obvious one, and the easiest to project.

Vacuum throughput against the quiet window. A table whose full vacuum pass exceeds the available window can never be vacuumed to completion, because a cancelled vacuum keeps nothing and the next run starts at the beginning. In one estate a table needing 3 hours 51 minutes was getting twenty minutes a night, and the deficit compounded to 1.1 billion dead tuples and a terabyte of bloat.

Memory against concurrency. work_mem is an allowance per memory-consuming node, per query, per session. Sixty concurrent reports with four such nodes each is sixty times four times work_mem, and the out-of-memory killer acts on the worst case.

Connections against max_connections. Instances times pool maximum, including the rolling-restart case, changes every time a service scales and belongs to nobody by default.

Rates, not readings

A single measurement gives a number. A series gives a rate. Only a rate becomes a date.

The three rates worth having:

-- database size, sampled on a schedule
SELECT datname, pg_database_size(datname) FROM pg_database;

-- WAL generation, from two readings
SELECT pg_current_wal_lsn();

-- query cost, by total rather than mean
SELECT calls, total_exec_time, mean_exec_time, query
FROM pg_stat_statements ORDER BY total_exec_time DESC;

Growth is not always data

A table growing while its row count is flat is bloat, and that is a maintenance question rather than a capacity one. Measure it rather than inferring it:

CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstattuple('orders');

Read free_percent, not dead_tuple_percent. Measured on one table, 1,000,000 updates produced only 200,057 counted dead tuples and 62 percent free space — opportunistic pruning had already converted most dead tuples into free space, and the dead-tuple figure badly understated the bloat.

The vacuum window is a capacity limit

Measure a full vacuum pass on each of the largest tables once a year, and compare it against the quiet period.

If it does not fit, autovacuum cannot complete on that table unaided — and that is a capacity fact to plan around rather than an incident to discover. The remedies are per-table autovacuum settings that make each run smaller, a longer window, or a partitioning change; all of them need lead time.

Headroom in hours

free bytes on pg_wal / measured WAL bytes per hour = hours of headroom

That is the number the disk alert should use. Ninety-five percent of a 20 GB volume was sixteen minutes in one incident — the alert fired, somebody acknowledged it, and the cluster stopped.

The restore time will cross the objective

Backup size and restore duration both grow with the database. Track both, project the restore time forward, and identify the date at which it crosses the recovery time objective.

That date is a capability change, and it arrives whether or not anybody plans for it. The alternative to planning is discovering it during an outage, while somebody asks how much longer.

Where the numbers come from

Every figure is a rate taken from retained series, not a level read today: bytes per day of database growth, bytes per day of WAL, peak connections per hour, transactions per second at peak. Storage headroom is expressed in time — days or weeks to full — because a percentage does not tell anyone when to act.

The restore duration comes from the last measured restore, and it is projected forward at the current growth rate. The date at which it crosses the recovery time objective is the single most useful number this review produces.

Access this needs

Read access to retained metric series covering at least one full business cycle — a quarter is usually the minimum useful window, and a single month will not show the seasonal peak that decides the answer.

A role holding pg_monitor for the current sizes, WAL generation rate and connection counts.

Access to whoever can answer the growth question the database cannot: the product or business owner who knows what the next twelve months are expected to look like. Forecasting from the last quarter alone assumes the future resembles it, and that assumption should be somebody’s, on the record, rather than implicit.

What the review produces

A dated record naming the reviewer, the observation window, and every rate measured, with the projection and the date each resource is expected to be exhausted. Attach the series rather than the summary, so the next review can extend them rather than starting again.

The projected date at which restore time crosses the recovery time objective goes to the service owner regardless of how far away it is, because procurement and architecture decisions have lead times measured in months.

Sign-off

Reviewer: ________________ Date: ___________
Database owner: ___________ Date: ___________
Service owner: ____________ Date: ___________

Every critical item must pass. A failing critical item is a blocker, not a note for the next sprint: record the date, the reviewer, the disposition of every item that did not pass, and the name of whoever accepted the residual risk.