Your single database is reaching its limits. How do you decide whether to shard, and how would you do it?

What the interviewer wants to hear

• Try simpler options first: tuning, read replicas, caching, partitioning, bigger hardware
• Choose a shard key that matches the main access pattern
• Avoid hot spots and plan rebalancing
• Cross-shard queries and transactions become hard
• Routing layer or tools like Citus or Vitess
• Migrate gradually with dual writes or logical replication

Model answer

Sharding adds a lot of complexity, so I first check simpler options: query and index tuning, archiving old data, partitioning, read replicas for read load, caching, connection pooling, and vertical scaling. If write volume or data size still exceeds one server, I shard. The most important choice is the shard key. It should match the main access pattern, like tenant_id or user_id, so most queries hit one shard, and it should spread load evenly. Time-based or sequential keys create hot spots. I prefer a lookup directory or many logical shards mapped to fewer physical servers, so rebalancing is easy. Cross-shard joins, global unique IDs and distributed transactions become hard, so the app design must accept that. I would consider Citus for PostgreSQL or Vitess for MySQL, and migrate gradually using logical replication and verification.

More hard Database Administrator questions

• Design a high-availability setup for a critical PostgreSQL database with RPO near zero and RTO under one minute.
• 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?
• The business expects traffic to grow ten times over the next 18 months. How do you plan database capacity?
• 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?