Capacity Planning In Depth

Capacity planning for PostgreSQL is fundamentally about three resources, CPU, memory, and disk I/O, plus one hard ceiling that behaves differently from all three: the connection limit.

This page builds the mental model that connects those resources to the symptoms teams actually observe, so that a scaling decision starts from evidence rather than from reflexively resizing the instance.

Summary

Core Idea: Capacity planning maps observed symptoms (latency, saturation, errors) back to one of a small number of underlying resource bottlenecks.
Why It Matters: Scaling the wrong resource, like adding CPU to fix an I/O-bound workload, wastes budget without fixing the actual problem.
Key Concepts: saturation, cache hit ratio, connection ceiling, vertical scaling, horizontal scaling, growth forecasting.
When to Use: Initial instance sizing, quarterly capacity reviews, and any incident where latency rose but CPU looks fine.
Limitations / Trade-offs: Every scaling strategy (bigger instance, read replicas, partitioning, sharding) trades cost or complexity for headroom, and none of them fixes a bad query plan.
Related Topics: connection pooling, query performance tuning, monitoring and alerting, managed platform sizing.

Foundations

CPU capacity governs how much query execution, sorting, and autovacuum work the cluster can perform concurrently.

Memory capacity governs cache effectiveness through shared_buffers and the operating system's page cache, determining how often a read is satisfied from RAM versus disk.

Disk I/O capacity governs how fast the storage layer can serve reads that miss cache and absorb the continuous stream of WAL writes every transaction generates.

The connection ceiling, set by max_connections, behaves unlike the other three because it is a hard cutoff rather than a gradual degradation, and once reached, new sessions are rejected outright rather than merely slowed.

A useful mental model is a restaurant kitchen: CPU is the number of cooks who can work at once, memory is counter space that avoids repeated trips to the walk-in fridge, disk I/O is how fast that walk-in fridge can be accessed, and the connection ceiling is the fixed number of tickets the kitchen printer can physically hold.

Adding cooks does not help if the walk-in fridge is the bottleneck, and a bigger fridge does not help if the printer is jammed, which is exactly why capacity planning starts with identifying which resource is actually constrained.

PostgreSQL exposes the evidence for this diagnosis directly through pg_settings, pg_stat_activity, and host-level tools like iostat, so the data needed to distinguish a CPU-bound from an I/O-bound workload is already available on every cluster.

Sizing decisions made without that evidence tend to default to habit, resizing to a familiar instance class rather than to what the workload's actual resource profile demands.

Mechanics & Interactions

The four resources interact through a chain that starts with a query and ends with either fast completion or visible user-facing latency.

A query first competes for CPU to plan and execute, then for memory (work_mem) if it needs to sort or hash, and finally for disk I/O if the data it needs is not already resident in shared_buffers or OS cache.

effective_cache_size does not allocate memory itself, it tells the query planner roughly how much cache is available system-wide, which shapes plan choice, such as favoring an index scan over a sequential scan when the planner expects the relevant pages to already be cached.

Under sustained write load, disk I/O and CPU interact through autovacuum, since a table generating dead tuples faster than autovacuum can reclaim them causes both bloated indexes (more I/O per scan) and more CPU spent evaluating rows that will eventually be discarded.

-- One mechanism made concrete: this signal distinguishes
-- "queries are running" from "queries are queued," which
-- is the first fork in any capacity diagnosis.
SELECT count(*) FILTER (WHERE state = 'active')            AS active,
       count(*) FILTER (WHERE wait_event_type = 'Lock')     AS blocked,
       count(*)                                             AS total
FROM pg_stat_activity
WHERE backend_type = 'client backend';

Connections interact with all three resources indirectly but significantly, because each open backend consumes memory even while idle, and a connection storm from an under-pooled application can exhaust max_connections long before CPU or disk shows any real strain.

A common reasoning mistake is scaling CPU or instance size first, when the actual bottleneck is frequently a missing index or an unbounded connection count, both of which a bigger instance will not fix and will only make more expensive to keep ignoring.

Advanced Considerations & Applications

At scale, capacity planning shifts from reactive firefighting to a forecasting discipline that anticipates resource exhaustion before it becomes an incident.

Storage growth is the resource most teams under-forecast, because table and index growth compounds with time-series or event-log workloads in a way that a single point-in-time snapshot does not reveal, making trend-based projection more useful than a static "percent free" alert.

The choice between vertical scaling, read replicas, and horizontal strategies like partitioning or sharding depends on which resource is actually constrained: a CPU- or memory-bound single-writer workload benefits from vertical scaling, a read-heavy reporting load benefits from replicas, and a single table too large to vacuum or index efficiently benefits from partitioning regardless of instance size.

Sharding specifically should be treated as a last resort in this hierarchy, since it introduces cross-shard query complexity and operational overhead that partitioning or read replicas usually avoid while solving the same underlying pressure.

Connection pooling interacts with capacity planning at an architectural level, because a pooler like PgBouncer changes the actual resource being sized from "connections per application instance" to "connections per pool," which is what makes horizontally scaled application tiers (many pods, many serverless invocations) compatible with a fixed max_connections ceiling.

Capacity reviews conducted on a quarterly cadence, correlating table growth, connection peaks, and top query cost, catch the slow drift toward a resource ceiling long before it becomes a page at 3am.

Approach	Strength	Weakness	Best Fit
Vertical scaling (bigger instance)	Simple, no application changes	Cost grows faster than linear at the top end, has a ceiling	CPU- or memory-bound single-writer workloads
Read replicas	Offloads read traffic, improves read latency	Replication lag, does not help write throughput	Read-heavy reporting or analytics load
Partitioning	Bounds vacuum and index maintenance cost per partition	Query and migration complexity increase	Very large single tables with a natural key (time, tenant)
Sharding	Distributes both read and write load horizontally	Cross-shard queries, significant operational overhead	Write throughput that exceeds any single primary, last resort

Common Misconceptions

"High latency always means the instance needs more CPU." - PostgreSQL workloads are frequently I/O- or lock-bound, so CPU can look healthy while queries queue behind disk waits or blocking transactions.
"max_connections should be set high 'for growth.'" - every open connection consumes memory whether idle or active, so a high ceiling without pooling risks memory exhaustion, not headroom.
"Bigger shared_buffers always means better cache hit ratio." - returns diminish well before 100% of RAM, and the OS page cache does meaningful work that oversized shared_buffers can crowd out.
"Read replicas solve write bottlenecks." - replicas offload read traffic only; a write-bound primary needs a different strategy such as partitioning or, eventually, sharding.
"Sharding is the default answer to running out of capacity." - it is usually the most operationally expensive option and is best reserved for write throughput that vertical scaling and partitioning genuinely cannot solve.
"Capacity planning is a one-time sizing exercise." - workload and data volume change continuously, so sizing decided at launch routinely becomes wrong within a year without a review cadence.

FAQs

What are the core resources involved in PostgreSQL capacity planning?

CPU for query execution, memory for caching, disk I/O for reads and WAL writes, and the connection ceiling as a distinct hard limit.

Why can latency rise even when CPU utilization looks low?

PostgreSQL is frequently bound by disk I/O or lock contention, both of which can saturate independently of CPU.

What does `effective_cache_size` actually control?

It does not allocate memory itself; it informs the query planner's estimate of available cache, which influences plan choice like index versus sequential scans.

Why is the connection ceiling treated differently from CPU, memory, and disk?

It is a hard cutoff rather than a gradual degradation, rejecting new sessions outright once max_connections is reached instead of merely slowing them down.

How does autovacuum connect to both CPU and disk I/O capacity?

A table accumulating dead tuples faster than autovacuum reclaims them causes bloated indexes that cost more I/O per scan and more CPU evaluating discardable rows.

When does vertical scaling make sense versus a read replica?

Vertical scaling fits a CPU- or memory-bound single-writer workload, while a read replica fits read-heavy reporting traffic that does not touch the write bottleneck.

Why is sharding considered a last resort rather than a default scaling path?

It introduces cross-shard query complexity and operational overhead that partitioning or read replicas usually avoid while addressing the same underlying pressure.

How does connection pooling change the capacity picture?

It shifts the resource being sized from connections-per-application-instance to connections-per-pool, letting a horizontally scaled app tier stay under a fixed max_connections ceiling.

Why is storage growth commonly under-forecast?

A single point-in-time snapshot does not reveal compounding growth from time-series or event-log workloads, so trend-based projection catches what a static free-space check misses.

Is capacity planning something you do once at launch?

No, workload and data volume change continuously, so a quarterly review cadence is needed to catch drift before it becomes an incident.

Does a bigger instance always fix a slow query?

Rarely; a missing index or a bad query plan typically dominates over raw instance size, and scaling hardware first often just makes the same inefficiency more expensive.

What evidence should precede a scaling decision?

pg_stat_activity wait events, cache hit ratio, host-level I/O metrics, and connection saturation, so the decision targets the resource that is actually constrained.

Capacity Basics - the recipe-level starting point for the resource model described here.
Connection Math - the pooling mechanics referenced in this page's discussion of the connection ceiling.
Scale-Up vs Scale-Out - a deeper comparison of the scaling strategies in the table above.
Storage Growth Forecasting - the forecasting discipline described in Advanced Considerations.
Sharding: Last Resort - why sharding sits at the far end of the scaling hierarchy.
Monitoring & Metrics Key Points - the signals used to diagnose which resource is constrained.

Stack versions: This page was written for PostgreSQL 18.4 (stable major 18, maintenance line 17) and PgBouncer 1.x.