Database Capacity Planning: A Practical Guide

Introduction

Capacity planning prevents failures such as running out of disk, saturating CPU during a launch, or exhausting database connections. It helps teams size infrastructure for current demand while using observed workload and growth trends to prepare for future load.

This guide covers compute, memory, storage, connections, and cloud-versus-on-premises trade-offs, with practical estimates and monitoring approaches. It also examines how incorrect forecasts and weak alerts can compound into production failures.

Sizing Compute (CPU)

CPU sizing depends on query complexity and concurrency requirements. A database running simple CRUD operations needs less CPU than one running complex analytical queries.

For CPU, consider both per-core speed and the parallelism available to your workload. Some PostgreSQL operations can use parallel workers, but many query plans and workloads do not scale across every core. Benchmark representative queries before choosing between fewer faster cores and more cores.

As a starting point, size production compute from measured concurrency and representative load tests. Sustained high CPU can signal a need to optimize queries or add compute, but review latency, wait events, and utilization together before upgrading.

Sizing Memory

Memory is where PostgreSQL caches data and does its work. More memory means fewer disk I/O operations, which are orders of magnitude slower than memory access.

The key metric is effective cache size:

SHOW effective_cache_size;

This setting is a planner estimate of the cache available to a query; it does not allocate memory. Choose it from the memory PostgreSQL and the operating system can realistically use for cached data. A value around 50-75% of RAM is a common starting estimate, not a fixed target. If you have 32GB RAM, 24GB may be reasonable after accounting for the workload and other processes.

PostgreSQL’s default shared_buffers is commonly 128MB. For a dedicated 32GB database server, 8GB is a common starting point; benchmark before increasing it, since PostgreSQL also relies on the operating system cache.

work_mem is a limit for each eligible query operation, such as a sort or hash table. A query can have several such operations, and concurrent queries multiply the possible memory use. maintenance_work_mem applies to maintenance operations such as VACUUM and CREATE INDEX; size both for concurrency as well as individual operations.

Sizing Storage

Storage sizing requires estimating current data size, growth rate, and headroom.

Current and Projected Size

Calculate current table and index sizes using the queries from the Data Growth Modeling section above. Then project forward by estimating your growth rate.

Growth rates depend on your application type. A typical pattern:

Once you have your monthly growth rate, calculate how much space you will need in 3, 6, and 12 months. Add headroom based on bloat, maintenance, and unexpected spikes. For example: a 500GB database growing at a compounded 20% per month reaches about 1.5TB in 6 months and 4.5TB in 12 months, before adding headroom. Treat 70% utilization as a possible review trigger, then set the actual threshold using growth rate and storage provisioning lead time.

Storage Type Considerations

Storage type selection determines your database’s I/O ceiling. NVMe/SSD, HDD, and network-attached storage each serve different workload profiles, and the choice comes down to tradeoffs between throughput, latency, cost, and capacity.

NVMe and SSD can provide lower latency and higher random I/O rates than spinning disks, though actual performance depends on the device, workload, and configuration. NVMe connects over PCIe and can offer more parallelism than SATA SSDs. For a write-heavy or latency-sensitive workload, benchmark the intended storage rather than assuming advertised maximums will match database performance. SSD storage generally costs more per GB than HDD.

HDD still makes sense for specific workloads. Large analytical databases running sequential scans across many terabytes benefit from HDD’s sequential read throughput and lower cost per GB. If your workload is read-heavy, mostly sequential, and capacity-hungry rather than IOPS-hungry, HDD can be 5-10x cheaper per GB. The problem is random I/O—database writes, index updates, and VACUUM activity all generate random I/O patterns that spinning disks handle poorly. Mixing sequential analytical scans with random OLTP I/O on the same HDD is a good way to watch your query times climb.

Network-attached storage in cloud environments adds latency. The path from database to storage goes through the network stack, costing you 0.5-2ms compared to local NVMe. For latency-sensitive OLTP, that matters. For analytical or batch workloads where queries run for minutes or hours, the added latency is effectively zero. Cloud storage also has throughput caps that can throttle large sequential scans.

Cloud environments have their own gotchas. AWS gp3 volumes give you 3,000 IOPS baseline at 125 MB/s throughput but can run out of steam at high block sizes. gp2 volumes burst to 3,000 IOPS but degrade as you chew through the burst budget. Provisioned IOPS (io1/io2) volumes deliver guaranteed performance at 3-5x the cost of gp3. On Google Cloud, regional SSD performs best but adds network latency compared to zonal SSD. Always test storage performance with fio or pg_test_fsync before committing to a volume type in production.

For new deployments, compare local NVMe with the managed cloud storage tiers available in your environment. Reserve HDD for workloads where sequential throughput and capacity matter more than random I/O. Confirm the choice with representative benchmarks and account for durability, failover, and operational requirements.

IOPS Requirements

IOPS (Input/Output Operations Per Second) measures storage throughput. Database workloads are I/O intensive, especially with poor cache hit rates. A database with a 90% cache hit rate still needs to write every transaction to disk through the WAL.

Estimate I/O needs by profiling your actual workload with operating-system tools and PostgreSQL statistics. PostgreSQL 16 and later provide pg_stat_io; PostgreSQL 17 also provides pg_stat_checkpointer. pg_stat_bgwriter remains available, but its counters differ by PostgreSQL version. Capture read/write operations, bytes, latency, and checkpoint behavior over representative peak periods.

• Data and index reads/writes: affected by cache behavior, query patterns, and maintenance
• WAL generation and write latency: track with PostgreSQL WAL statistics and storage metrics
• Checkpoint activity and temporary I/O: include these in peak-load measurements

There is no reliable conversion from transaction rate or WAL volume directly to storage IOPS; write batching, page writes, caching, and checkpoints affect the result. Measure a representative workload and retain enough headroom for observed peaks.

Cloud providers specify IOPS for their storage volumes. AWS gp3 volumes deliver 3,000 IOPS baseline at 125 MB/s throughput. Provisioned IOPS (io2) volumes can reach 256,000 IOPS. Make sure your volume type supports your workload’s I/O requirements, and test with pg_test_fsync before going to production.

Connection Pool Sizing

PostgreSQL connections have process and shared-memory overhead and compete for CPU. A large number of idle connections can add overhead without increasing useful query concurrency.

Direct Connection Limits

max_connections sets the absolute maximum number of PostgreSQL connections. The default is typically 100, though deployments may choose a different limit. More is not always better: work_mem is consumed by eligible query operations, not reserved once per connection, while each connection also adds process and shared-memory overhead. PostgreSQL documents that some resources scale with max_connections.

The right max_connections value depends on your pooler configuration, expected active query concurrency, and available memory. Keep capacity for administrative and replication connections, and validate the setting under load rather than treating one value as suitable for every 4-core server.

Setting max_connections too high relative to your available memory causes PostgreSQL to swap or OOM. Setting it too low causes “too many connections” errors that take down applications. If you find yourself consistently hitting the limit, add PgBouncer before raising max_connections. A pooler lets 200 application connections share 20 actual database connections.

Connection Poolers

For most production workloads, use a connection pooler like PgBouncer or Pgpool-II. Without a pooler, each application instance maintains its own database connections, and at scale this creates a problem: 50 application servers each opening 20 connections is 1,000 PostgreSQL connections, most of them idle at any given moment. A pooler lets 1,000 application connections share 20-50 actual database connections efficiently.

PgBouncer is the most common choice. It operates at transaction level by default, meaning a client connection only occupies a real PostgreSQL connection during an active transaction. This lets hundreds of clients share dozens of real connections. The downside: session-level features break. You cannot use prepared statements with the default prepared statement caching, and SET statements that persist across transactions do not work. For most web applications this is fine.

Pgpool-II offers more features including built-in query caching, load balancing across replicas, and session-level pooling. It adds complexity and higher memory overhead. Use it when you need automatic query routing or caching at the pooler layer.

PgCat is a newer option written in Rust, designed for high-performance connection multiplexing with less overhead than PgBouncer. It supports session and transaction modes and has lower memory footprint. Consider it for greenfield deployments on modern infrastructure.

The pooler you choose matters less than using one. Direct database connections at scale cause connection exhaustion, CPU overhead from context switching, and memory waste from idle connections.

Pool Size Calculation

There is no universal pool-size formula. Start with a pool small enough to keep active database concurrency within the server’s measured capacity, then benchmark representative traffic. OLTP with short transactions and analytical workloads with long queries may need different limits. Monitor queue time, active sessions, and database latency when tuning.

Cloud vs On-Premises Tradeoffs

The cloud versus on-premises decision affects capacity planning significantly.

Cloud gives you elasticity and variety—you can scale up when needed and choose instance types optimized for compute, memory, or I/O. Managed services like RDS and Cloud SQL handle some operational burden. But at large scale, cloud database costs become significant. A $50,000/month database in the cloud might cost $10,000/month on-premises. You also have less control over kernel parameters and storage optimization, and IOPS often costs extra.

Many organizations use a hybrid approach: core production data on-premises or in a private cloud, with burst capacity or read replicas in public cloud for traffic spikes.

Security and Compliance Notes

Capacity forecasts can reveal customer activity, business growth, or infrastructure weaknesses. Limit access to detailed dashboards and exported plans, aggregate tenant-level usage when individual identities are not needed, and keep credentials and connection strings out of planning documents. Set a retention period for forecast exports instead of leaving them in shared storage indefinitely.

Before choosing regions or storage tiers, check data residency, encryption, backup, and audit-retention requirements. Those rules affect replica placement, disaster-recovery capacity, WAL storage, and how long backups and logs must be kept. Include these security and compliance needs in the capacity model, and restrict infrastructure changes to reviewed plans with an audit trail.

Monitoring Trends to Predict Capacity Needs

Capacity planning is not a one-time exercise. You need ongoing monitoring to predict when you will need to scale.

Key Metrics to Track

1. Disk utilization: If consistently above 70-80%, plan storage expansion.
2. CPU utilization: Sustained above 70% indicates underpowered CPU.
3. Memory pressure: Check for swap usage, which indicates insufficient RAM.
4. Connection counts: Watch for approaching max_connections.
5. Query latency trends: Slow queries often precede capacity crises.

Capacity Triggers

Set thresholds that force a capacity review before you hit the wall. These differ from alerts—alerts tell you something is already broken, while triggers give you lead time to act.

For a production PostgreSQL database, these are the numbers I come back to:

Disk above 70%. At 70% disk usage, you have roughly 30% headroom. For a database growing 10GB per day, that headroom is gone in three days. Set your trigger at 70% so you have 60 days to provision new storage. When you hit 80%, you are in emergency mode—execute the plan you made at 70%.

CPU above 80% sustained. Brief spikes are normal. Sustained 80%+ means queries are competing for cycles and latency is degrading. Pull pg_stat_statements to check whether the load comes from a few bad queries or general growth. Bad queries: optimize. Growth: scale compute.

Memory pressure. Swap usage is the clearest signal. If the OS is swapping, shared_buffers and work_mem are undersized. Run vmstat and look for non-zero si values. Even before swap kicks in, low free RAM with heavy disk cache means I/O is suffering.

Connections above 70% of max. Connection exhaustion fails queries with a generic “too many connections” message that is painful to debug. At 70% of max, start evaluating whether to add PgBouncer or raise the limit. At 90%, you are one traffic spike away from outages.

p99 latency trending up. Slow queries are often the first warning of an approaching capacity problem. If your p99 latency is climbing week-over-week, investigate before it becomes a crisis. Set a baseline and alert when p99 exceeds 2x that baseline.

These thresholds compound. A database at 65% disk, 75% CPU, and 60% connections is fine. One at 72% disk, 82% CPU, and 73% connections is circling a failure where multiple resources hit their limits at once. That is the failure mode capacity planning prevents—not any single threshold in isolation.

Practical Capacity Planning Process

flowchart TD
    Start["Start: New workload or growth trigger"] --> Measure["1. Measure current:<br/>Disk, CPU, Memory,<br/>Connections, IOPS"]
    Measure --> Characterize["2. Characterize workload:<br/>Read/write ratio?<br/>Query complexity?<br/>Peak patterns?"]
    Characterize --> Project["3. Project growth:<br/>3, 6, 12 month<br/>trajectory"]
    Project --> Size["4. Size resources:<br/>Compute, Storage,<br/>Memory, IOPS"]
    Size --> Buffer["5. Add 30-50% buffer:<br/>Growth headroom +<br/>Traffic spikes"]
    Buffer --> Monitor["6. Monitor trends<br/>Quarterly review:<br/>Adjust projections"]
    Monitor --> Adjust["Adjust projections"] --> Project

A practical approach to capacity planning: baseline your current usage, characterize your workload, project growth, add buffer, and monitor trends. Update projections quarterly.

Production Failure Scenarios

Capacity Planning Failure Patterns

Two common failure patterns show why forecasts need to include retention and concurrency assumptions:

Storage exhaustion

Suppose a database forecast includes table growth but omits WAL retention, backups, index growth, or temporary files. Usage can then outpace the forecast, and an alert set close to the disk limit may leave too little time to expand storage. Track each storage consumer, project its growth, and set review triggers using the actual provisioning lead time.

Connection pool exhaustion

As an application adds instances and background workers, each may create its own pool. The aggregate number of database connections can exceed the limit even if each service’s pool looks small. Track pools across services, reserve capacity for administrative and replication connections, and load-test peak concurrency.

These patterns are easier to manage when assumptions are explicit and monitoring tracks both current usage and its rate of change.

Quick Recap Checklist

• Size CPU from representative load tests; use sustained utilization and latency to trigger review
• Set effective_cache_size to a realistic estimate of cache available to the planner
• Tune shared_buffers from a measured baseline while accounting for OS cache
• Size work_mem for concurrent query operations, not just sessions
• Size maintenance_work_mem for maintenance workload and worker concurrency
• Monitor disk usage weekly; trigger capacity review at 70%, expand at 80%
• Use SSD/NVMe storage for OLTP workloads — HDD cannot handle high IOPS
• Benchmark pool size against active query concurrency and database latency
• Add 30-50% headroom above projected storage need for bloat and growth
• Track disk growth week-over-week to establish accurate growth rate
• Separate WAL onto dedicated volume to isolate backup I/O from production
• Monitor connection utilization — alert at 70% of max_connections
• Use PgBouncer or similar for connection pooling in production
• Plan compute and storage upgrades when metrics exceed 70-80% sustained
• Review and adjust capacity quarterly based on actual growth patterns

Related Posts

• Database Scaling Strategies - Horizontal and vertical scaling approaches
• Database Monitoring - Key metrics to track
• Cost Optimization - Optimizing database costs

Interview Questions

Further Reading

• Database Monitoring — Metrics and PostgreSQL monitoring approaches

• Database Scaling Strategies — Options for scaling a database as demand grows

• Cost Optimization — Cost trade-offs for infrastructure capacity

• PostgreSQL Resource Configuration — Memory and connection settings

• Disk Usage Monitoring — Understanding storage consumption

• PgBouncer — Connection pooler for PostgreSQL

• pg_activity — Top-like activity monitoring for PostgreSQL

• PostgreSQL Monitoring Best Practices — DataDog’s comprehensive guide

Conclusion

Capacity planning is continuous, not a one-time event. The goal is not to predict perfectly—it is to have enough visibility into trends that you are not surprised.

Build monitoring into your routine. Know your growth rate. Understand your workload characteristics. Make capacity decisions based on data, not guesswork.

Good capacity planning happens before anyone notices a problem. You want boring infrastructure—the kind that simply works, reliably, as load grows.