The business expects traffic to grow ten times over the next 18 months. How do you plan database capacity?

What the interviewer wants to hear

• Measure current baselines: data size growth, QPS, CPU, I/O, connections and headroom
• Find the first bottleneck and model growth per resource
• Use the cheap wins first: query and index tuning, caching, read replicas, pooling
• Then scale up, partition, archive old data, and only shard when needed
• Load test against forecasts and review the plan regularly

Model answer

I would start with data, not guesses: current database size and monthly growth, peak queries per second, CPU, memory, I/O latency and IOPS, connection counts and replication lag, and how close each is to its limit. Then I model what ten times traffic means for each resource and find the first wall, which is often writes on the primary or a few expensive queries. Cheap wins come first: tuning the top queries from pg_stat_statements, better indexes, application caching, read replicas for read traffic and a connection pooler. Next come bigger instances and faster storage, partitioning large tables and archiving old data. Sharding is the last step because of its complexity. I would validate the plan with load tests on production-like data, set budget estimates, and review the forecast every quarter.

More hard Database Administrator questions

• Design a high-availability setup for a critical PostgreSQL database with RPO near zero and RTO under one minute.
• Your single database is reaching its limits. How do you decide whether to shard, and how would you do it?
• How would you upgrade a large production PostgreSQL database to a new major version with minimal downtime?
• The application is timing out and you suspect lock contention in production. Walk through how you diagnose and fix it.
• Someone ran DELETE without a WHERE clause on an important production table 20 minutes ago. What do you do?
• Design a disaster recovery plan for a critical database that must survive the loss of a whole cloud region.
• How would you migrate a 5 TB on-premises PostgreSQL database to a managed cloud service with less than 15 minutes of downtime?
• A critical 1 TB PostgreSQL table is 60 percent bloat and queries are slowing. How do you fix it without downtime and stop it coming back?
• Your company must meet compliance rules (such as PCI DSS or GDPR) for a database holding personal and card data. What controls do you put in place?