Capacity planning from your own data

conceptThe concept

Four numbers drive most plans:

• Growth: rows per day or month for the largest tables, from a timestamp column or from periodic snapshots of pg_total_relation_size.
• Bytes per row including indexes and TOAST: total relation size divided by row count.
• Working set: how much of that data is touched by daily traffic, compared with RAM.
• Memory per connection: each active query can use several work_mem allocations (one per sort or hash node), plus per-backend overhead.

Plan for headroom: keep disk below about 70% so VACUUM FULL, index rebuilds and WAL spikes have room, and plan a resize when the forecast crosses that line.

whyWhy it matters

Running out of disk stops PostgreSQL from writing WAL, which stops the database. Growth is usually predictable; outages from it are a planning failure.

Use caseReal-world scenario

A monthly job stores pg_total_relation_size for every table in a history table. A forecast query fits a trend per table and opens a ticket when any database will reach 70% of its volume within 90 days.

Try itExamples

Run and edit these in the interactive lesson.

SELECT date_trunc('month', created_at)::date AS month, count(*) AS orders
FROM orders
GROUP BY 1
ORDER BY 1 DESC
LIMIT 6;

WITH size AS (
  SELECT pg_total_relation_size('orders') AS bytes, (SELECT count(*) FROM orders) AS rows_now
), rate AS (
  SELECT count(*) / 3.0 AS rows_per_month
  FROM orders
  WHERE created_at >= (SELECT max(created_at) FROM orders) - interval '3 months'
)
SELECT round(bytes::numeric / rows_now) AS bytes_per_row,
       round(rows_per_month) AS rows_per_month,
       pg_size_pretty((bytes::numeric / rows_now * rows_per_month * 36)::bigint) AS growth_3_years
FROM size, rate;

WITH m AS (
  SELECT date_trunc('month', created_at) AS month, count(*) AS n
  FROM orders
  WHERE created_at < date_trunc('month', (SELECT max(created_at) FROM orders))
  GROUP BY 1
), f AS (
  SELECT regr_slope(n, extract(epoch FROM month)) AS slope,
         regr_intercept(n, extract(epoch FROM month)) AS icpt,
         max(month) AS last_month
  FROM m
)
SELECT to_char(d, 'YYYY-MM') AS month, round((slope * extract(epoch FROM d) + icpt)::numeric) AS forecast_orders
FROM f, generate_series(last_month + interval '1 month', last_month + interval '6 months', interval '1 month') d;

SELECT current_setting('max_connections')::int AS max_connections,
       current_setting('shared_buffers') AS shared_buffers,
       current_setting('work_mem') AS work_mem,
       pg_size_pretty(current_setting('max_connections')::bigint * 3
                      * pg_size_bytes(current_setting('work_mem'))) AS worst_case_three_sorts_each;

internalsUnder the hood

Table size is not proportional to row count alone: dead tuples awaiting VACUUM, fillfactor, index bloat and TOAST compression all change bytes per row. Measure after a VACUUM for the baseline, and track bloat separately.

Avoid / preferDo this, not that

-- size the disk for today's data plus 20%

-- size for the forecast at your planning horizon, then add
-- headroom for WAL spikes, index rebuilds and VACUUM FULL

SET work_mem = '1GB';  -- globally, to speed up reports

SET LOCAL work_mem = '256MB';  -- in the reporting transaction only

PitfallsCommon mistakes

• Forecasting from row counts while ignoring indexes, which can be as large as the table.
• Forgetting WAL and backup storage in the plan.
• Fitting a straight line to seasonal traffic and missing the holiday peak.

LimitsWhen not to use it

Do not build detailed forecasts for databases that are tiny relative to their volume and growing slowly; a disk usage alert at 70% is enough. Spend the effort on the few databases that are large or fast-growing.

SecuritySecurity notes

Capacity history tables are harmless, but the queries that build them often run as superuser from cron. Give the collection job a role with pg_monitor and nothing more.

ChallengeTry it yourself

WITH s AS (
  SELECT pg_total_relation_size('events')::numeric AS bytes,
         count(*) AS n,
         count(*) FILTER (WHERE at >= m.max_at - interval '30 days') AS last_30_days
  FROM events, (SELECT max(at) AS max_at FROM events) m
)
SELECT round(bytes / n) AS bytes_per_row,
       last_30_days,
       round((10 * 1024.0 ^ 3 - bytes) / (bytes / n * last_30_days), 1) AS months_to_10gb
FROM s;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• High availability and replication
• Continuous archiving and PITR
• Logical replication