Database Capacity Planning

Production engineering guide for database capacity planning covering patterns, implementation strategies, and operational best practices.

Database capacity planning is the process of estimating and provisioning database resources—storage, compute, memory, I/O, and network—to meet current and projected workloads. It matters when a database begins to exhibit latency, connection saturation, or storage exhaustion under load, and when scaling decisions must be made before a performance crisis. It is not a one-time task, but a recurring practice tied to release cycles, growth forecasts, and operational alerts.

Estimating Storage Needs

Storage capacity planning begins with the rate of data growth and the expected retention period. The total storage required is the sum of current data size, daily growth, and overhead for indexes, backups, and transaction logs.

To estimate daily data growth, collect the rate of insertions and updates, and measure the average size of each record. Use pg_stat_user_tables in PostgreSQL to track row counts and total table size:

SELECT 
  schemaname, 
  tablename,
  n_tup_ins, 
  n_tup_upd,
  pg_total_relation_size(oid) AS total_size_bytes
FROM pg_stat_user_tables
ORDER BY total_size_bytes DESC;

For a table with 10 million rows, each 1 KB, and 20% daily growth from inserts and updates, the storage increase is:

10,000,000 rows × 1 KB = 10 GB
10 GB × 20% = 2 GB/day

Account for index bloat and WAL (Write-Ahead Log) overhead. On average, indexes consume 15–30% of data size. For a 10 GB table, add 1.5–3 GB for indexes. WAL writes consume about 25% of total data written.

To estimate total storage over time, apply a formula:

Total storage = (current data size) + (daily growth × retention days) × 1.35

The factor 1.35 includes index overhead, WAL, and a 10% buffer for unexpected growth. For a 2-year retention with 2 GB/day growth:

Total storage = 10 GB + (2 GB/day × 730 days) × 1.35 = 10 GB + 1,989 GB ≈ 2 TB

Use df -h and du -sh to monitor actual disk usage:


df -h /data/db

# Monitor growth over time
du -sh /data/db/* | sort -hr

Silent failures occur when pg_wal is not archived or when checkpoint_completion_target is misconfigured. If WAL files accumulate faster than they can be archived, pg_stat_wal_receiver shows last_msg_send_time lagging. A checkpoint that takes longer than max_wal_size causes frequent I/O bursts and reduced availability.

Determining Compute and Memory Requirements

CPU and memory sizing depend on query load, concurrency, and cache hit rates. The primary tool is workload profiling using query execution plans and duration distribution.

Use EXPLAIN ANALYZE to identify slow queries and their cost drivers:

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT *
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.created_at > '2024-01-01'
ORDER BY o.total DESC
LIMIT 100;

The output includes actual time, rows, loops, and Buffers—the number of shared buffer hits and misses. High rows and loops indicate inefficient joins or scans.

To estimate CPU demand, calculate the total query execution time per day:

# Extract query durations from pg_stat_statements
SELECT 
  query,
  total_exec_time,
  calls,
  total_exec_time / calls AS avg_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

For example, if the top ten queries consume 1.2 hours total per day, and each query requires 100 ms of CPU, then:

1.2 hours/day × 1000 ms/hour = 1,200,000 ms/day
1,200,000 ms / 100 ms per query = 12,000 queries/day

With 12,000 queries/day, 10,000 concurrent connections, and each connection consuming 100 KB of memory, total memory for connection state is:

10,000 connections × 100 KB = 1 GB

Add memory for shared buffers, WAL buffers, and temp buffers. PostgreSQL’s shared_buffers should be at least 25% of total RAM. For a 32 GB server, set:

shared_buffers = 8GB
effective_cache_size = 24GB
work_mem = 4MB
maintenance_work_mem = 256MB

The effective_cache_size guides the cost model in query planning. If it is underestimated, the planner favors index scans over sequential scans, even when memory is insufficient.

A common failure is memory pressure from temp buffers. When work_mem is too low, queries spill to disk, increasing I/O and execution time. Monitor pg_stat_bgwriter and pg_statio_user_tables to detect buffer pressure:

SELECT 
  checkpoints_timed,
  checkpoints_req,
  buffers_checkpoint,
  buffers_clean,
  buffers_backend,
  buffers_alloc
FROM pg_stat_bgwriter;

If buffers_clean is significantly higher than buffers_checkpoint, the system is writing dirty buffers to disk under load. If buffers_backend is high, queries are creating temp buffers that are not reused.

Planning for Concurrency and Connection Load

Concurrency planning involves sizing the number of concurrent connections and tuning the connection pool.

The maximum number of connections is limited by max_connections in PostgreSQL, which defaults to 100. Each connection consumes about 100 KB of memory. For 50,000 concurrent users, the total memory cost is:

50,000 × 100 KB = 5 GB

To handle this, use a connection pooler like PgBouncer or a lightweight proxy like HAProxy.

Configure PgBouncer with a pgbouncer.ini file:

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
admin_users = admin,postgres
server_reset_query = DISCARD ALL
server_check_query = SELECT 1
server_check_delay = 15
server_check_timeout = 5
server_check_query = SELECT 1
server_check_query = SELECT 1

The server_check_query is run every server_check_delay seconds to validate server readiness. If the query times out, PgBouncer marks the server as down and fails over.

Use SHOW SERVERS in PgBouncer to inspect connection state:

SHOW SERVERS;

Output includes status, server_addr, server_port, num_waiting, num_active, num_idle, num_used, and avg_query_time.

A common failure is connection starvation when server_check_query takes longer than server_check_delay. If server_check_query takes 8 seconds but server_check_delay is 5 seconds, the connection pooler may check servers every 5 seconds, but the first check is still running when the second arrives, causing overlapping checks and CPU spikes.

Also, server_check_query should be lightweight. A complex query like SELECT * FROM stats WHERE day = current_date will delay the check cycle and reduce responsiveness.

Sizing for High Availability and Failover

For high availability, plan for replication lag and failover time. The primary database must be able to serve writes during failover, and the standby must be able to catch up quickly.

Monitor replication lag using pg_stat_replication:

SELECT 
  application_name,
  client_addr,
  state,
  sync_state,
  pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) AS lag_bytes,
  pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag_bytes
FROM pg_stat_replication;

The lag_bytes is the difference between current WAL position and the last WAL sent by the primary. The replay_lag_bytes is the difference between sent and replayed WAL.

If the replication lag exceeds 2 GB, the system cannot recover from a primary failure within 30 seconds. The standby must replay this 2 GB of WAL in less than 30 seconds.

To estimate failover time, compute:

Failover time = (replication lag in bytes) / (WAL write throughput in bytes/second)

If WAL is written at 100 MB/s, a 2 GB lag requires 20 seconds to replay. If the standby is not warmed (e.g., no shared buffers), the warm-up adds 5–10 seconds.

Use pg_wal_replay_pause() and pg_wal_replay_resume() to control replay during maintenance:

-- Pause replay to apply schema changes
SELECT pg_wal_replay_pause();
-- Apply changes
ALTER TABLE orders ADD COLUMN status TEXT;
-- Resume replay
SELECT pg_wal_replay_resume();

A silent failure occurs when pg_wal_replay_pause() is called but pg_wal_replay_resume() is not called. The standby remains paused, and the database becomes stale. Monitoring tools must alert on pg_stat_wal_receiver where replay_lsn is not advancing.

Anticipating Growth and Scaling Triggers

Capacity planning is not static. Set triggers based on key metrics to initiate scaling.

Define thresholds for storage, CPU, memory, and replication lag:

Use systemctl to monitor and trigger actions:

# Check database service status
systemctl status postgresql

# Restart service with delay
systemctl restart postgresql --wait

# Trigger failover script on lag
#!/bin/bash
if pg_wal_lsn_diff pg_current_wal_lsn(), replay_lsn > 1073741824; then
  echo "Replication lag exceeds 1 GB. Initiating failover."
  psql -c "SELECT pg_promote();"
fi

A common mistake is delayed scaling due to under-sampled metrics. A query that runs every 10 minutes may miss a 2-minute spike. Use pg_stat_statements with track_activity_query_size to capture query patterns and detect short-lived but high-impact queries.

Managing Capacity Over Time

Capacity planning is a continuous loop: monitor, analyze, predict, act, and validate.

Use a regular cadence of capacity reviews—weekly, monthly, quarterly.

At each review, answer:

Update the capacity model with actual data and revise projections.

Use git to version the capacity plan:

# Track capacity changes
git add capacity-plan.md
git commit -m "Increased shared_buffers to 12GB; added 500GB storage for 2025"
git push origin main

A subtle failure is forgetting to validate the plan after scaling. After adding a new server, the new instance does not receive queries. Use pg_stat_statements and pg_stat_activity to verify that new connections are being used.

Finally, document assumptions: retention period, daily growth rate, query distribution, and expected uptime. When the database fails, these assumptions are the first to be questioned—and the plan is only as good as the assumptions it rests on.

This page was rewritten on 17 October 2026. It replaced a templated version that was not specific to its own title. How these pages are written and checked. If anything here is wrong, tell us at [email protected] and we will correct it.