Database Concepts Every System Design Interview Expects You to Know

Scaling applications is not just about adding more servers.

At scale, the database becomes the bottleneck long before your application code does.

In system design interviews — and in real-world production systems — understanding database concepts like replication, sharding, indexing, and CAP theorem is critical.

Let’s break them down clearly.

1️⃣ Vertical vs Horizontal Scaling

Before we talk about distributed databases, we need to understand scaling.

🔼 Vertical Scaling (Scale Up)

You increase the capacity of a single machine:

• More CPU
• More RAM
• Faster SSD

Pros

• Simple
• No architectural changes required

Cons

• Hardware limit
• Expensive
• Single point of failure

Vertical scaling works — but only up to a point.

🔁 Horizontal Scaling (Scale Out)

You add more machines.

Instead of 1 large server → 10 smaller servers.

Pros

• Virtually unlimited scale
• Better fault tolerance

Cons

• Complexity increases
• Requires distributed architecture

Horizontal scaling is where replication and sharding come in.

2️⃣ Database Replication

Replication means copying the same data to multiple database servers.

Why do we replicate?

• Improve read scalability
• Improve availability
• Provide fault tolerance

🔹 Primary–Replica Architecture

A common setup:

          Primary (Writes)
               |
        -----------------
        |               |
     Replica 1       Replica 2

• All writes go to the Primary
• Reads can go to Replicas

Replication Types

🔹 Synchronous Replication

The primary waits for confirmation from replicas before confirming a write.

✔ Strong consistency
❌ Slower writes

🔹 Asynchronous Replication

The primary does not wait for replicas.

✔ Faster writes
❌ Possible replication lag

This introduces eventual consistency.

When to Use Replication?

• Read-heavy applications
• High availability systems
• Systems that cannot tolerate downtime

Example:

• News websites
• E-commerce platforms
• Social media feeds

3️⃣ Database Sharding

Replication copies data.

Sharding splits data.

Instead of one massive database:

Shard 1 → Users 1–1M
Shard 2 → Users 1M–2M
Shard 3 → Users 2M–3M

Each shard contains only part of the data.

Why Shard?

• Handle massive datasets
• Improve write scalability
• Avoid single database bottleneck

Sharding Strategies

🔹 Range-Based Sharding

User ID 1–1000 → Shard A
User ID 1001–2000 → Shard B

Simple but can cause hotspots.

🔹 Hash-Based Sharding

hash(user_id) % N

Distributes load evenly.

Harder to rebalance later.

🔹 Geo-Based Sharding

US Users → US Database
EU Users → EU Database

Useful for:

• Latency optimization
• Regulatory compliance

When to Use Sharding?

• Massive write traffic
• Large datasets
• Clear partitioning strategy

Example:

• Instagram user data
• Large SaaS platforms
• Messaging systems

4️⃣ Replication vs Sharding

They solve different problems — and are often used together.

5️⃣ Indexing

Without an index:

SELECT * FROM users WHERE email = 'x';

The database scans every row.

With an index:

It directly locates the record.

Benefits

• Faster reads
• Efficient lookups

Trade-offs

• Slower writes
• More storage
• Index maintenance overhead

Indexes are not free — they are a trade-off.

6️⃣ Read-Write Splitting

Often used with replication:

Application
   ├── Writes → Primary
   └── Reads  → Replicas

This reduces load on the primary database.

But introduces:

• Consistency concerns
• Replication lag issues

7️⃣ CAP Theorem

In distributed systems, you can’t have all three:

• Consistency
• Availability
• Partition Tolerance

You must choose two.

Most real-world systems choose:

• Availability
• Partition tolerance

Which means accepting eventual consistency.

8️⃣ Caching (Bonus but Critical)

Sometimes the best database optimization is:

👉 Not hitting the database at all.

Tools:

• Redis
• Memcached

Used for:

• Session storage
• Frequently accessed queries
• Rate limiting

Caching drastically reduces database load.

Real-World Architecture Example

Large systems often combine:

• Sharding (scale writes)
• Replication (scale reads)
• Caching (reduce load)
• Indexing (speed queries)

There is no single silver bullet.

Key Takeaways

• Replication improves read scalability and availability
• Sharding improves write scalability and handles large datasets
• Indexing speeds up queries but slows writes
• CAP theorem forces trade-offs
• Caching reduces database pressure
• Database design decisions shape system scalability more than code does.