[reference] — from the roadmap.sh system-design roadmap. The database is almost always the first thing to buckle under load, so scaling it is central to system design. This is the scaling view; the databases reference covers the fundamentals.
SQL vs NoSQL — the first choice
- SQL (relational) — structured tables, a rigid schema, ACID transactions, and joins. Strong consistency, powerful queries. The right default for most applications and anything needing correctness (money, relationships between entities). Scales up easily; scaling out (writes) is harder.
- NoSQL — a family, each trading relational features for scale/flexibility:
- Key-value (Redis, DynamoDB) — dead-simple, blazing fast, scales horizontally trivially. For caching, sessions, simple lookups.
- Document (MongoDB) — JSON-like documents, flexible schema. For semi-structured, aggregate-oriented data.
- Wide-column (Cassandra) — huge write throughput, tunable consistency, scales to petabytes. For time-series, event logs, write-heavy at massive scale.
- Graph (Neo4j) — nodes and edges. For relationship-heavy queries (social graphs, recommendations).
The honest guidance: default to SQL — it’s more capable, and “we might need to scale” is usually premature. Reach for NoSQL when you have a specific need it serves: extreme write scale, a flexible schema, or an access pattern (key lookups, graph traversal) SQL handles poorly. “NoSQL because it scales” without that specific need trades away transactions and joins you’ll miss.
Scaling reads: replication
Most load is reads, so the first database scaling move is replication — copies of the database that serve reads:
- Leader-follower (primary-replica) — writes go to one leader, which replicates to read-only followers. Route reads to followers to multiply read capacity. The standard pattern.
- The catch: replication lag — a follower may be slightly behind the leader, so a read right after a write can see stale data (eventual consistency). “Read-your-own-writes” (route a user’s reads to the leader briefly after they write) is the common fix.
Replication also buys availability (a follower can be promoted if the leader dies — failover). The mechanics are in replication & consistency.
Scaling writes: sharding (partitioning)
Replication multiplies reads but every node still handles every write. To scale writes (and data beyond one machine), shard: split the data across multiple databases, each holding a subset.
- Shard key — the field you partition on (user_id, geography). The critical decision — a bad key creates hot shards (one shard gets most traffic) and makes cross-shard queries painful.
- Strategies — range partitioning (by key range — good for range scans, prone to hotspots) vs hash partitioning (hash the key — even distribution, but kills range queries). Consistent hashing (partitioning) minimizes reshuffling when nodes are added/removed.
- The costs — sharding is a big step: cross-shard joins/transactions become hard or impossible, rebalancing is operationally painful, and it adds real complexity. Exhaust replication + caching + a bigger box first; shard when you genuinely must.
Other scaling tools
- Indexing — the highest-leverage single-DB optimization: an index turns an O(n) table scan into an O(log n) lookup. Under-indexing is the #1 cause of slow queries; over-indexing slows writes (every index must be maintained — the load-then-index insight). Know what your queries need.
- Denormalization — deliberately duplicating data to avoid expensive joins at read time, trading storage and write-complexity for read speed (the same tradeoff as everywhere). The transaction-record denormalization is a real example.
- Connection pooling, query optimization, materialized views — squeeze more from one database before distributing.
The order of operations
When the database is the bottleneck, escalate in roughly this order (cheap/simple → expensive/complex): optimize queries + indexes → add caching → add read replicas → denormalize → shard. Each step buys time; sharding is the last resort because it’s the most complex and hardest to undo.
Related
- Replication & Consistency — how replication actually works
- Caching — the layer that protects the database
- Databases reference — SQL/schema/indexing fundamentals
Software engineering
Infrastructure & security
Computer science
Hardware & engineering