Capacity Basics

PostgreSQL capacity is three resources: CPU for query execution and autovacuum, memory for shared_buffers and OS page cache, and disk I/O for WAL and heap/index reads. Right-size for workload shape (OLTP vs reporting), not generic "db.m5.large" habit.

Recipe

-- Quick capacity snapshot
SELECT name, setting, unit
FROM pg_settings
WHERE name IN ('shared_buffers', 'work_mem', 'max_connections', 'effective_cache_size');
 
SELECT pg_size_pretty(pg_database_size(current_database())) AS db_size;
 
SELECT sum(calls) AS total_calls,
       round(sum(total_exec_time)::numeric, 1) AS total_ms
FROM pg_stat_statements;

# Host: CPU iowait and disk latency
vmstat 1 5
iostat -x 1 3

When to reach for this:

• Initial instance sizing on RDS or VM
• Quarterly review before renewals
• Latency up but CPU low - suspect I/O or locks

Working Example

Size a 2k TPS OLTP API database on 8 vCPU / 32 GB RAM.

-- Memory-oriented GUC starting point (tune with load test)
-- shared_buffers ~ 25% RAM (8GB on 32GB)
ALTER SYSTEM SET shared_buffers = '8GB';
ALTER SYSTEM SET effective_cache_size = '24GB';
ALTER SYSTEM SET work_mem = '16MB';          -- careful: per sort node * connections
ALTER SYSTEM SET maintenance_work_mem = '1GB';
ALTER SYSTEM SET max_connections = 200;        -- with PgBouncer, not 2000 app pods
SELECT pg_reload_conf();

-- CPU demand signal: active backends
SELECT count(*) FILTER (WHERE state = 'active') AS active,
       count(*) AS total
FROM pg_stat_activity
WHERE backend_type = 'client backend';

# Disk: keep 20% free; WAL + data on durable network storage (gp3/io2)
df -h /var/lib/postgresql

What this demonstrates:

• shared_buffers and effective_cache_size guide planner, not magic cache of everything
• work_mem scales with concurrent sorts; pooler reduces real backend count
• max_connections is a hard ceiling - size with PgBouncer math
• Active session count vs vCPU indicates CPU headroom

Deep Dive

Resource Mapping

OLTP Sizing Heuristic

• Start 4-8 vCPU for mid OLTP until load test proves otherwise
• RAM: 16-64 GB common; more RAM helps cache hit ratio
• Storage: gp3 with baseline IOPS; raise IOPS when pg_stat_bgwriter shows write stalls
• Connections: app instances × pool size < max_connections × 0.8

Review Cadence

Quarterly: table growth, top queries, connection peak, disk forecast, replica lag.

Gotchas

• max_connections = 500 "for growth" - each connection consumes RAM. Fix: PgBouncer; keep Postgres backends modest.
• work_mem = 256MB globally - 100 concurrent sorts can OOM. Fix: 8-32MB default; raise per role for reporting only.
• CPU autoscale only on serverless - provisioned instances need manual resize or Aurora ACU policy.
• Ignoring WAL and backup disk - data volume full stops writes. Fix: alert separate mount points.
• One size staging = prod - OK for major version; instance class can differ if query shapes match.
• Scale CPU before fixing seq scans - 10x CPU loses to one btree index. Fix: pg_stat_statements first.

Alternatives

FAQs

Related

• Connection Math - pool × instances
• Storage Growth Forecasting - disk planning
• Scale-Up vs Scale-Out - scaling strategies
• Monitoring Basics - saturation signals
• Capacity Planning Best Practices - review checklist

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+.

cGJxcnRodnFyZi52YnxwdHZiMTA2NHwyMDI2MDc=