Database Design for Scale: Lessons From Processing 1M+ Requests Daily

The Scaling Myth

Everyone thinks scaling is about bigger servers. More CPU, more RAM, larger instances. It's not. Scaling is about design choices that let your system grow without constant rewrites and emergency migrations.

The difference between a database that handles 10,000 users and one that handles 10 million is rarely hardware. It's architecture. And the best time to make those architectural decisions is before you need them.

Choose Based on Access Patterns

Don't start with "what database should I use?" — start with "how will my data be accessed?" The answers to these questions determine your entire data layer:

• Read-heavy or write-heavy? (Read replicas vs. write-optimized storage)
• Complex joins or simple key-value lookups? (Relational vs. document store)
• Time-series data or mutable records? (TimescaleDB vs. PostgreSQL)
• Strong consistency or eventual consistency? (Single-leader vs. multi-leader replication)

The answers determine whether you need PostgreSQL, MongoDB, Cassandra, Redis, or — more likely — a thoughtful combination of two or three.

The Indexing Mistake Everyone Makes

Indexes make reads fast. They also make writes slower, consume disk space, and add complexity to query planning. The mistake that plagues nearly every growing system? Indexing everything "just in case."

A better indexing strategy:

1. 01Index based on actual queries observed in production, not guessed future needs
2. 02Use partial indexes for conditional queries (WHERE status = 'active')
3. 03Monitor index usage weekly and remove any index with zero reads
4. 04Consider covering indexes for critical query paths to avoid table lookups entirely
5. 05Be especially cautious with compound indexes — column order matters enormously

Sharding: Design for It Early or Pay Later

Sharding — distributing data across multiple database instances — is terrifying to retrofit into an existing system. If you know you'll eventually need it (and if you're reading this article, you probably will), design for it from day one.

// Application-level shard routing
function getShardForTenant(tenantId: string): DatabaseConnection {
  // Consistent hashing distributes tenants evenly
  const shardIndex = consistentHash(tenantId, SHARD_COUNT);
  return shardConnections[shardIndex];
}

// Every query is shard-aware from the start
async function getOrders(tenantId: string) {
  const db = getShardForTenant(tenantId);
  return db.query('SELECT * FROM orders WHERE tenant_id = $1', [tenantId]);
}

• Choose a shard key that distributes data evenly and aligns with query patterns
• Ensure queries can be routed to specific shards without cross-shard joins
• Build application-level shard awareness into your data access layer from the start
• Plan for resharding — your initial distribution will eventually become uneven

Real Numbers From Production

A property management platform I worked on grew from 10,000 to 2 million listings over 18 months. Here's what kept us alive and performing well through that 200x growth:

• Read replicas for all reporting and analytics queries — separated read from write load entirely
• Materialized views refreshed every 15 minutes for complex aggregation dashboards
• Table partitioning by date for audit logs (keeping the active partition fast)
• Redis caching layer for hot data with write-through invalidation
• Async processing via message queues for all non-critical writes

The Cache Trap

Caching is the most common scaling band-aid, and it works — until it doesn't. Stale data, cache invalidation storms, thundering herds, and memory pressure all emerge at scale and can cause cascading failures.

Monitoring What Actually Matters

At scale, database monitoring isn't optional — it's survival. These are the metrics that predicted every outage we've ever had, usually 2-3 days before it happened:

• Query performance trends — enable slow query logging and review daily, not weekly
• Connection pool saturation — when you hit 80%, you're already in trouble
• Disk I/O latency and memory pressure — hardware matters when software is optimized
• Replication lag — anything above 1 second should trigger an alert
• Deadlock frequency — even one per hour indicates a design problem

The theme across all of these lessons: scaling databases is about making good decisions early, monitoring relentlessly, and treating your data layer as the critical infrastructure it is — not an afterthought beneath your application code.