Database Scaling Patterns

Database scaling is probably one of the trickiest part in system design. You can scale vertically (bigger machine) or horizontally (more machines). Vertical scaling is simple - just add more CPU, memory, storage to same server. This works until you hit hardware limits and becomes very expensive. Horizontal scaling means spreading data across multiple machines, which is more complex but can scale indefinitely if done right.

Read replicas are good starting point for horizontal scaling. You have one master database for writes and multiple read replicas for read queries. This works well if you have read-heavy workload, but all writes still go to single master so it can become bottleneck. Master-slave replication is eventually consistent - replicas might lag behind master by few seconds or minutes. For applications that need strong consistency, this can be problem.

Sharding is when you split data across multiple database servers, each handling subset of data. You can shard by user ID, geographical region, or any other logical partition. The challenge is choosing good shard key that distributes data evenly and doesn’t create hotspots. Cross-shard queries become expensive or impossible, so you need to design your data model carefully. We learned this hard way when our initial sharding strategy created uneven distribution and some shards became overloaded while others were underutilized.

How to Choose Right Scaling Strategy

Don’t jump straight to complex solutions. Start simple and scale based on actual bottlenecks you observe.

Start with vertical scaling if you have budget and haven’t hit hardware limits yet. It’s the simplest approach - just upgrade your machine. Monitor your CPU, memory, and disk usage. If you’re consistently above 70-80%, it’s time to consider horizontal scaling.

Add read replicas when you have read-heavy workload and master CPU is high from read queries. Check your read/write ratio first. If it’s 80% reads, read replicas will help significantly. If it’s mostly writes, read replicas won’t solve your problem.

Consider sharding only when single master can’t handle write load or data size. This is much more complex than read replicas. You need to redesign queries, handle cross-shard operations, and manage data distribution. Don’t shard until you absolutely need to.

Look at your query patterns before choosing shard key. If most queries filter by robot_id for performance metrics, shard by robot. If queries are mostly by factory location for maintenance logs, shard by location. Wrong shard key mean expensive cross-shard queries or uneven distribution.

The biggest mistake I see is over-engineering early. Use simple solution until it breaks, then upgrade to next level of complexity. Each step adds operational overhead.