# Databases at Scale — Replication, Sharding, SQL vs NoSQL and CAP

Source: https://skills101.in/learn/system-design/databases-at-scale — Skills101 free tutorial (System Design). Updated 2026-11-09.

The database is usually the **hardest part** to scale, because it holds state. App servers can be cloned easily; data must stay correct.

## 1. First: the cheap wins

Before anything complex:
- Add the right **indexes** (see [Indexing](https://skills101.in/learn/dbms-sql/database-indexing)).
- Fix slow queries; avoid N+1 queries.
- Add a **cache** for hot reads.
- Use connection pooling.

## 2. Replication: copies of the data

![Replication: one primary takes writes and copies data to two read replicas; sharding: users are split across shards by name range. Big systems use both](https://skills101.in/learn-img/system-design/replication-sharding.svg)

- One **primary** handles writes; **replicas** copy its data and serve **reads**.
- Most apps read far more than they write (often 10:1 or 100:1), so replicas multiply read capacity.
- If the primary fails, a replica is **promoted** (failover).

**Replication lag:** replicas may be a few milliseconds behind. After a user updates their profile, read it from the **primary** for that request ("read your own writes").

## 3. Sharding: splitting the data

When one machine can't hold the data or handle the writes, **split** rows across several databases (shards).

| Strategy | How | Watch out |
|---|---|---|
| **Range** | users A–H, I–P, Q–Z | hot ranges (many names start with S) |
| **Hash** | `hash(user_id) % 4` | even spread; resharding moves data (use **consistent hashing**) |
| **Geographic** | India users in Mumbai, US users in Virginia | users who move |
| **Directory** | a lookup table says which shard | the lookup service must scale too |

Sharding costs: cross-shard joins and transactions become hard, and rebalancing is work. Shard only when you must.

## 4. SQL vs NoSQL

| | SQL (PostgreSQL, MySQL) | NoSQL (MongoDB, Cassandra, DynamoDB, Redis) |
|---|---|---|
| Data model | tables with a fixed schema | documents, key-value, wide-column, graph |
| Relationships / joins | excellent | limited |
| Transactions (ACID) | strong | varies (often limited) |
| Scaling writes | harder (sharding) | built for horizontal scale |
| Best for | money, orders, users, anything relational | huge volume, flexible data, simple access patterns (chats, logs, feeds, sessions) |

**Default to SQL** for most apps; add NoSQL for specific needs. Many companies use both.

## 5. The CAP theorem

![CAP triangle: Consistency (every read sees the latest write), Availability (always answer) and Partition tolerance; networks will split, so systems choose CP or AP](https://skills101.in/learn-img/system-design/cap.svg)

In a distributed database, when the network **partitions** (servers can't talk to each other), you must choose:

- **CP** — stay **consistent**: refuse some requests until the network heals. (Banking, inventory.)
- **AP** — stay **available**: answer with possibly old data and reconcile later. (Likes, view counts, feeds.)

**Eventual consistency:** all copies become the same **eventually** (usually within seconds). Fine for a like count; not for your bank balance.

## Common mistakes

- Sharding too early instead of indexing and caching.
- Reading from a replica right after a write and confusing users.
- Picking NoSQL because it's "faster" for data that is clearly relational.

## Quick recap

- Index, optimise, cache first.
- Replication: primary writes, replicas read; watch replication lag.
- Sharding: range, hash (consistent hashing), geo, directory — costs joins and simplicity.
- SQL by default; NoSQL for scale and flexible data. CAP: choose CP or AP during partitions.


Practice quiz and free certificate: https://skills101.in/learn/system-design/databases-at-scale