Skip to content

Database — Relational (RDBMS)

A relational database (SQL) organizes data in tables.

ACID properties

ACID describes relational database transactions:

Property Meaning
Atomicity Each transaction is all-or-nothing.
Consistency A transaction moves the DB from one valid state to another.
Isolation Concurrent transactions have the same result as if run serially.
Durability Once committed, a transaction stays committed.

Techniques to scale a relational database

  1. Master-slave replication
  2. Master-master replication
  3. Federation (functional partitioning)
  4. Sharding (data partitioning)
  5. Denormalization
  6. SQL tuning

Master-Slave Replication

  • The master serves reads and writes, replicating writes to one or more slaves.

  • Slaves serve reads only, and can replicate to additional slaves (tree-like).

  • If the master fails, the system can run in read-only mode until a slave is promoted to master (or a new master is provisioned).

Disadvantages

  • Extra logic is needed to promote a slave to master.
  • See "Disadvantages: replication" below (applies to both master-slave and master-master).

Master-Master Replication

  • Both masters serve reads and writes, coordinating with each other on writes.
  • If either goes down, the system continues with both reads and writes.

Disadvantages

  • Needs a load balancer or app-logic changes to decide where to write.

  • Most master-master systems are either loosely consistent (violate ACID) or have increased write latency due to synchronization.

  • Conflict resolution grows as more write nodes and higher latency are added.

Disadvantages of replication (both kinds)

  • Potential data loss if the master fails before replicating new writes.
  • Writes are replayed to read replicas; heavy writes can bog them down.
  • More read slaves → more replication → greater replication lag.
  • Some systems write in parallel on the master but only sequentially on replicas.
  • Replication adds hardware and complexity.

Federation (Functional Partitioning)

Splits databases by function. Instead of one monolith, you might have separate databases for forums, users, and products.

Benefits:

  • Less read/write traffic to each DB → less replication lag.
  • Smaller DBs → more data fits in memory → more cache hits (better locality).
  • No single central master serializing writes → parallel writes, higher throughput.

Disadvantages

  • Not effective if your schema needs huge functions/tables.
  • App logic must know which DB to read/write.
  • Joining data across two DBs is more complex.
  • More hardware and complexity.

Sharding (Data Partitioning)

Distributes data across databases so each manages only a subset of the data. Example: as users grow, add more shards to a users database.

Benefits (similar to federation):

  • Less read/write traffic and replication per shard.
  • More cache hits; smaller index sizes → faster queries.

  • One shard failing doesn't take down the others (add replication to avoid data loss).

  • Parallel writes → higher throughput.

Common sharding keys: user's last-name initial or geographic location.

Disadvantages

  • App logic must route to the right shard (complex SQL possible).
  • Data distribution can become lopsided (e.g., power users on one shard).
  • Rebalancing adds complexity; consistent hashing reduces data moved.
  • Joining across shards is complex.
  • More hardware and complexity.

Denormalization

Improves read performance at the cost of write performance by storing redundant copies of data in multiple tables to avoid expensive joins.

  • Some RDBMS (PostgreSQL, Oracle) support materialized views to maintain these redundant copies.

  • Once data is distributed (federation/sharding), cross-data-center joins get harder — denormalization may avoid them.

  • Reads often outnumber writes 100:1 or 1000:1, so optimizing reads pays off.

Disadvantages

  • Data is duplicated.
  • Constraints to keep copies in sync increase design complexity.
  • Under heavy write load, a denormalized DB may perform worse than normalized.

SQL Tuning

A broad topic. The key workflow: benchmark and profile first.

  • Benchmark — simulate high load (e.g., ab).
  • Profile — use tools like the slow query log to find bottlenecks.

Tighten the schema

  • MySQL dumps to disk in contiguous blocks for fast access.
  • Use CHAR over VARCHAR for fixed-length fields (fast, random access).
  • Use TEXT for large blocks of text (e.g., blog posts); supports boolean search.
  • Use INT for numbers up to 2^32 (≈4 billion).
  • Use DECIMAL for currency (avoid float rounding errors).
  • Avoid storing large BLOBs — store the location of the object instead.
  • VARCHAR(255) maximizes a byte in some RDBMS.
  • Set NOT NULL where applicable to improve search performance.

Use good indices

  • Index columns you query on (SELECT, GROUP BY, ORDER BY, JOIN).

  • Indices are usually B-trees: sorted data with logarithmic-time search, insertion, and deletion.

  • Trade-off: indices use memory and slow writes (index must update too).

  • For bulk loads: disable indices, load, then rebuild.

Avoid expensive joins

  • Denormalize where performance demands it.

Partition tables

  • Move "hot spots" into a separate table to help keep it in memory.

Tune the query cache

  • In some cases the query cache can cause performance issues.

Common Interview Questions

Design a URL shortener (Pastebin/Bitly)

Why it's asked here: schema design + read-heavy SQL scaling.

Key points to discuss:

  • Schema: short_code (PK), long_url, created_at, expires_at, user_id.
  • Hash generation (MD5/Base62) and collision handling.
  • Read-heavy (100:1) → master-slave replication for reads.
  • When to shard (by short_code) and denormalize.
flowchart LR
    U[User] --> A[App server]
    A -->|POST /shorten| H[Hash generator]
    H --> DB[(URLs table)]
    U -->|GET /code| C[(Cache)]
    C -->|miss| DB
Design the Twitter timeline / news feed

Why it's asked here: the classic sharding + denormalization problem.

Key points to discuss:

  • Fan-out on write vs fan-out on read trade-off.
  • Sharding the tweets table and the timeline cache.
  • Denormalization to avoid expensive joins when rendering a feed.
  • Master-slave for reads, with replication-lag implications.
flowchart LR
    P[Post tweet] --> F[Fan-out service]
    F --> T[(Timeline cache)]
    F --> D[(Sharded tweets)]
    U[User] --> T
    T -->|miss| D
Design Amazon's sales ranking by category

Why it's asked here: heavy aggregation reads → SQL tuning territory.

Key points to discuss:

  • Indices on (category, sales_count, timestamp).
  • Denormalized/materialized ranking tables to avoid expensive joins.
  • Partition hot categories to keep them in memory.
  • Benchmark + profile (slow query log) before optimizing.
flowchart LR
    S[Sales event] --> O[(Orders DB)]
    O -->|aggregate| V[Materialized view]
    V -->|index category + count| R[Ranking query]

Further reading