Capacity Planning and Bloat Audits

What to Forecast

Disk Growth

SELECT pg_size_pretty(pg_database_size(current_database()));
SELECT pg_size_pretty(pg_total_relation_size(t.oid)) AS total,
       relname
FROM pg_class t
WHERE relkind = 'r'
ORDER BY pg_total_relation_size(t.oid) DESC LIMIT 20;

Per-Table Growth Tracking

INSERT INTO size_history (ts, tablename, size_bytes)
SELECT NOW(), relname, pg_total_relation_size(oid)
FROM pg_class WHERE relkind = 'r';

IOPS Estimation

SELECT relname, seq_tup_read, idx_tup_fetch,
       seq_tup_read + idx_tup_fetch AS total_reads
FROM pg_stat_user_tables
ORDER BY total_reads DESC LIMIT 20;

RAM Sizing

Bloat Audit

SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(n_dead_tup::numeric / NULLIF(n_live_tup, 0), 2) AS dead_ratio,
       last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

Index Bloat

CREATE EXTENSION pgstattuple;

SELECT relname,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size,
       (pgstatindex(indexrelid::regclass)).leaf_fragmentation
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC LIMIT 20;

Unused Indexes

SELECT schemaname, relname, indexrelname,
       pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

Connection Audit

SELECT datname, usename, application_name, state, COUNT(*)
FROM pg_stat_activity
GROUP BY 1,2,3,4 ORDER BY 5 DESC;

Long Transactions

SELECT pid, state, xact_start, NOW() - xact_start AS xact_age, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_age DESC NULLS LAST LIMIT 20;

WAL and Archives

SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0')) AS total_wal_generated;

Plan for Failover

Recap

Quick Check