Storage Growth Forecasting

Disk surprises stop writes: table and index growth, bloat, WAL retention from replication slots, and backup artifacts all consume bytes. Forecast monthly growth from measured insert rates and autovacuum health, not initial provisioning guess.

Recipe

-- Database and top table sizes
SELECT pg_size_pretty(pg_database_size(current_database())) AS db_total;
 
SELECT relname,
       pg_size_pretty(pg_total_relation_size(relid)) AS total,
       pg_size_pretty(pg_relation_size(relid)) AS heap,
       n_live_tup,
       n_dead_tup
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 15;

-- WAL directory size (self-hosted)
SELECT count(*) AS wal_files,
       pg_size_pretty(sum(size)) AS wal_bytes
FROM pg_ls_waldir();

When to reach for this:

• Annual budget for RDS storage autoscale max
• Bloat incident after bulk delete or failed vacuum
• Replication slot disk pressure alert

Working Example

Forecast 90-day growth for events append-only table.

-- Current size
SELECT pg_relation_size('events') AS bytes,
       pg_size_pretty(pg_relation_size('events')) AS pretty;
 
-- Daily insert rate (from stats or app metrics)
SELECT relname, n_tup_ins, n_tup_upd, n_tup_del, last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'events';
 
-- Rough forecast: avg_row_bytes × daily_inserts × 90
-- Measure avg row:
SELECT avg(pg_column_size(e)) AS avg_row_bytes FROM events e TABLESAMPLE SYSTEM (1);

-- Index overhead ratio
SELECT indexrelname,
       pg_size_pretty(pg_relation_size(indexrelid)) AS idx_size
FROM pg_stat_user_indexes
WHERE relname = 'events';

# Spreadsheet model (conceptual)
# daily_new_bytes = inserts_per_day * (avg_row + index_factor)
# index_factor often 0.3-1.0 depending on indexes
# add 25% headroom for bloat if autovacuum marginal

What this demonstrates:

• Heap size + indexes = pg_total_relation_size
• n_dead_tup signals bloat risk after bulk updates
• WAL size separate from user data - monitor slots
• Sampled pg_column_size estimates row growth

Deep Dive

Growth Components

Bloat Detection

SELECT schemaname, relname, n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 100000
ORDER BY n_dead_tup DESC;

pgstattuple or pg_squeeze for deep bloat percent on suspect tables.

Replication Slot WAL Retention

SELECT slot_name, slot_type, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots;

Inactive slot with large retained WAL is disk incident waiting to happen.

Managed Autoscale

Set max allocated storage ceiling on RDS; alert at 80% of current allocation.

Gotchas

• Autoscale without ceiling - runaway bill. Fix: max cap + paging at 85% used.
• Bulk delete without vacuum plan - disk does not shrink; bloat grows. Fix: VACUUM (ANALYZE) schedule; partition drops for archival.
• Forgotten staging refresh - prod copy doubles disk in non-prod. Fix: subset staging data.
• Index explosion - every new filter column adds permanent bytes. Fix: partial indexes, drop unused via pg_stat_user_indexes idx_scan.
• Logical replication slot on busy primary - WAL held until consumer catches up. Fix: monitor slot lag bytes; drop stale slots.
• Forecast from row count only - wide JSON rows differ 10x. Fix: sample pg_column_size.

Alternatives

FAQs

Related

• Capacity Basics - I/O and RAM
• Replication Slots - WAL retention
• Partitioning Pitfalls - archival pattern
• Disk Full on pgdata - incident runbook
• Capacity Planning Best Practices - review cadence

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.