How Do You Approach Database Capacity Planning?
Learn how to forecast database growth and plan capacity for storage, throughput, and connections ahead of demand.
Expected Interview Answer
Database capacity planning means forecasting future storage, throughput, and connection needs from current growth trends and peak-load patterns, then provisioning (or scaling) infrastructure ahead of time so the system never hits a hard resource ceiling under normal or expected-peak traffic.
It starts with measuring current baselines — data growth rate per month, peak queries-per-second, connection counts, and I/O throughput — then projecting those trends forward against expected business growth (like a marketing launch or seasonal spike) to estimate when a resource will be exhausted. Planners build in headroom, typically targeting utilization well below 100 percent so traffic spikes do not cause outages, and decide between vertical scaling (bigger instance), horizontal scaling (read replicas, sharding), and data lifecycle actions (archiving old data) based on which resource is the actual constraint. Regular load testing validates that projections hold before they become emergencies.
- Avoids emergency scaling under production load
- Aligns infrastructure spend with actual growth trends
- Identifies whether to scale vertically, horizontally, or archive data
- Turns capacity risk into a scheduled, tested change instead of an incident
AI Mentor Explanation
A stadium's ground staff do not wait until a sold-out final to discover the parking lot is too small; they track attendance trends across the season and expand parking or add shuttle buses well before a marquee match sells out. Database capacity planning works the same way: tracking storage and traffic growth trends lets a team add capacity before a predictable peak, like a product launch, overwhelms the system.
Step-by-Step Explanation
Step 1
Measure current baselines
Track data growth rate, peak QPS, connection counts, and I/O throughput over recent months.
Step 2
Project forward
Extrapolate trends against known business events (launches, seasonality) to estimate when a resource will be exhausted.
Step 3
Choose a scaling strategy
Decide between vertical scaling, adding read replicas, sharding, or archiving old data based on the actual bottleneck.
Step 4
Validate with load testing
Test the projected peak load against the planned capacity before the real event, then schedule the scale-up ahead of time.
What Interviewer Expects
- A trend-based forecasting approach, not just reacting to alerts
- Distinguishing storage capacity from throughput/connection capacity
- Knowledge of vertical vs horizontal scaling trade-offs
- Mentioning load testing to validate projections before they matter
Common Mistakes
- Only thinking about disk space and ignoring connection or throughput limits
- No headroom built in, provisioning right up to the current peak
- Not tying capacity planning to known business events like launches
- Treating capacity planning as a one-time task instead of an ongoing process
Best Answer (HR Friendly)
“I would track how storage, query load, and connections are growing over time, project that trend forward against upcoming business events, and provision extra capacity — whether that is a bigger instance, more read replicas, or archiving old data — well ahead of when we would actually hit a limit. I would also load test before a known peak to confirm the plan holds.”
Code Example
-- Table and index size growth, useful for storage trend tracking
SELECT
relname AS table_name,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;
-- Row count growth over time (snapshot into a tracking table weekly)
INSERT INTO capacity_snapshots (snapshot_date, table_name, row_count, total_bytes)
SELECT
current_date,
relname,
n_live_tup,
pg_total_relation_size(relid)
FROM pg_stat_user_tables;
-- Peak connections observed vs configured max
SHOW max_connections;
SELECT max(numbackends) FROM pg_stat_database;Follow-up Questions
- How would you decide between vertical scaling and adding read replicas?
- What headroom percentage would you target and why?
- How does data archiving fit into a capacity planning strategy?
- How would you capacity-plan for a sharded database differently?
MCQ Practice
1. What is the primary goal of database capacity planning?
Capacity planning forecasts storage, throughput, and connection growth so infrastructure is scaled before hitting hard limits.
2. Why is it important to build in headroom rather than provisioning right up to current peak usage?
Provisioning exactly to peak usage leaves no buffer, so any unexpected spike immediately causes saturation or failures.
3. Which of these is a valid response to hitting a storage capacity limit driven by old, rarely-accessed data?
Archiving or tiering cold data off the primary store reduces active storage pressure without losing the data outright.
Flash Cards
What is database capacity planning? — Forecasting future storage, throughput, and connection needs and provisioning ahead of predictable exhaustion.
What three scaling options does capacity planning weigh? — Vertical scaling, horizontal scaling (replicas/sharding), and data lifecycle actions like archiving.
Why build in headroom? — So unexpected traffic spikes do not immediately exhaust capacity and cause an outage.
How do you validate a capacity plan before a known peak? — Run load tests that simulate the projected peak traffic against the planned capacity.