DatabasesMid-Level to Staff Backend & Data Platform Engineer19 min read·Updated for 2026 Distributed Data Interview Formats

Database System Design: SQL vs NoSQL, Sharding, Replication & Indexing

A Practitioner's Playbook for CAP Trade-offs, Partition Keys, Quorum Consistency & Storage Engine Internals

Priya Narang

Staff Data Platform Engineer & Distributed Storage Specialist

13+ Years Building Petabyte-Scale Sharded Datastores

Prep Timeline

5 to 7 Weeks

Format

System Design Deep-Dive, Data Modeling, Scalability Trade-offs

Conversion

+74% System Design Round Pass Rate

Database System Design: SQL vs NoSQL, Sharding, Replication & Indexing

Executive Summary & Key Takeaways

What You Must Master to Clear This Track

Choose SQL vs NoSQL based on access patterns and consistency needs, not hype — polyglot persistence is the industry norm, not the exception.
Pick a sharding key that distributes load evenly and matches your dominant query pattern to avoid scatter-gather reads and hot shards.
Understand replication topology trade-offs cold: leader-follower simplicity vs multi-leader conflict resolution vs leaderless quorum tunability.
Know B-Tree vs LSM-Tree internals: read-optimized in-place updates vs write-optimized append-and-compact, and their amplification trade-offs.
Map the CAP theorem and PACELC correctly onto real systems, and be ready to defend a consistency model choice (strong/eventual/causal) under network partitions.

Structured Preparation Timeline

Step-by-Step Study Plan

Follow this sequential roadmap designed to take you from core foundations to advanced architecture and mock interviews.

Phase 1 (Weeks 1-2)

Relational Modeling, NoSQL Data Models & the CAP Theorem

Data Modeling Foundations & SQL vs NoSQL

Normalization vs denormalization, ACID guarantees, document/key-value/wide-column/graph models, and CAP/PACELC reasoning for real systems.

Key Milestones

•Explain the trade-offs between 3NF normalization and deliberate denormalization for read-heavy services.
•Map five real products (banking ledger, product catalog, session store, social graph, time-series metrics) to the right data model.
•Correctly classify at least five real databases as CP or AP under the CAP theorem, and justify it with PACELC's latency/consistency axis.

Recommended Actions

•Whiteboard a schema for a booking system in both a normalized SQL form and a denormalized document form; compare query complexity.
•Never say 'NoSQL scales better' without naming the specific access pattern that breaks a relational design.

Phase 2 (Weeks 3-4)

Partitioning Strategies, Consistent Hashing & Rebalancing

Horizontal Scaling: Sharding & Partitioning

Range, hash, and directory/geo sharding; hot shard mitigation; consistent hashing with virtual nodes; and online shard splitting.

Key Milestones

•Implement a consistent-hashing shard router with virtual nodes from scratch and reason about its rebalancing cost.
•Identify hot-shard risks in a given schema (e.g. a celebrity user, a monotonic timestamp key) and propose a salting or composite-key fix.
•Design a zero-downtime shard-split runbook including dual-write, backfill, and cutover verification steps.

Recommended Actions

•Always state your shard key choice out loud and justify it against the read/write ratio and query filters.
•Practice explaining why modulo(hash, N) sharding is dangerous when the cluster grows.

Phase 3 (Weeks 5-7)

Replication Topologies, Quorum Consistency & Index Internals

Replication, Consistency Models & Storage Engines

Leader-follower, multi-leader, and leaderless/quorum replication; strong/eventual/causal consistency; B-Tree vs LSM-Tree storage engines.

Key Milestones

•Trace the full write and read path through a leaderless (Dynamo-style) N/W/R quorum system, including read repair.
•Explain B-Tree vs LSM-Tree internals well enough to defend a storage engine choice for a given workload (read-heavy vs write-heavy).
•Design a consistency strategy for a multi-region app: which reads need linearizability, which can tolerate eventual consistency.

Recommended Actions

•Run EXPLAIN ANALYZE on a real Postgres query and identify whether it hits an Index-Only Scan or a sequential scan.
•Rehearse the failure scenario: 'the leader just died mid-write, walk me through what happens next.'

Deep-Dive Architecture & Concepts

1. SQL vs NoSQL: Data Models, CAP Theorem & PACELC

Every database system design conversation starts with a data model decision. Interviewers are not testing whether you know a list of NoSQL brand names — they are testing whether you can map access patterns and consistency requirements to the right trade-off.

Relational Model & ACID

Rows in normalized tables with foreign-key relationships. Atomicity, Consistency, Isolation, and Durability guarantee that multi-row transactions either fully commit or fully roll back, making SQL the default for financial ledgers and inventory counts.

Document, Key-Value, Wide-Column & Graph NoSQL

Document stores (MongoDB) embed nested JSON for single-entity reads; key-value stores (DynamoDB, Redis) optimize O(1) lookups at massive scale; wide-column stores (Cassandra) optimize high write throughput and time-series; graph databases (Neo4j) optimize multi-hop relationship traversal.

CAP Theorem: Partition Tolerance Is Not Optional

During a network partition, a distributed system must choose Consistency (reject/queue writes until the partition heals) or Availability (serve possibly stale reads). Partition tolerance itself cannot be dropped in any real multi-node deployment.

PACELC: Extending CAP to the Normal Case

Even when there is no partition (the common case), a system still trades Latency for Consistency. Spanner is PC/EC (consistent always, pays latency via TrueTime); Cassandra is PA/EL (available always, tunable low-latency reads).

Interviewer Insights & Pro Tips

Never present SQL vs NoSQL as a binary choice in an interview — describe the specific access pattern (write volume, join complexity, consistency need) that drives the decision, and mention polyglot persistence for mixed workloads.
When asked to justify CAP classification, name the actual failure mode: 'if the network partitions this region, does the system return an error or a stale value?'

Red Flags & Common Pitfalls

Claiming 'NoSQL means no ACID' — many NoSQL engines (MongoDB, DynamoDB transactions) support single-document or even multi-item ACID transactions today.
Forgetting that CAP only applies during an actual partition; most production incidents are about the PACELC latency/consistency trade-off, not CAP.

Deep-Dive Architecture & Concepts

2. Sharding Strategies: Range, Hash, Geo & Avoiding Hot Shards

Once a dataset outgrows a single node's disk, memory, or write throughput, it must be horizontally partitioned. The sharding key you choose determines whether your cluster scales linearly or collapses under a hot partition.

Range-Based Sharding

Contiguous key ranges (e.g. user_id 1-1M on Shard A, 1M-2M on Shard B) map naturally to range scans, but concentrate all new writes on the last shard when the key is monotonically increasing (e.g. auto-increment IDs or timestamps).

Hash-Based Sharding

Hashing the shard key (e.g. hash(user_id) % N or via a consistent-hashing ring) distributes writes uniformly, at the cost of losing efficient range scans across shards.

Directory/Geo-Based Sharding

A lookup service or explicit mapping routes each key (or region, e.g. EU users to an EU shard) to its owning shard, giving full placement control for compliance (GDPR data residency) at the cost of an extra lookup hop.

The Hot Shard Problem

A single celebrity user, viral tenant, or monotonic key can overload one shard while others sit idle. Mitigations: salting the key with a random or hashed suffix, splitting a single hot key across sub-partitions, and caching in front of the hot shard.

Sharded Write Path Through a Consistent-Hashing Router

How a single write request is routed, replicated, and acknowledged across a horizontally sharded cluster.

Client Write Request

Application sends an INSERT/UPDATE carrying a shard key (e.g. tenant_id) to the routing tier.

Hash Ring Lookup

Router hashes the shard key against a consistent-hashing ring (150 virtual nodes per shard) to resolve the owning shard and its replica set.

Leader Write & WAL Append

The shard's leader node appends the write to its write-ahead log before applying it to the memtable or B-Tree page.

Synchronous Quorum Replication

Leader streams the write to follower replicas; the client receives an ack once W replicas confirm (e.g. W=2 of 3).

Async Compaction & Rebalance Watch

Background compaction merges SSTables (on an LSM engine) while a cluster monitor tracks shard size to trigger a split before hot-shard thresholds are hit.

Consistent Hashing Shard Router with Virtual Nodes

typescript

// Consistent hashing ring with virtual nodes to minimize key movement
  // when a physical shard is added or removed.
  import { createHash } from "crypto";
  
  interface ShardNode {
    id: string;
    weight: number; // relative virtual-node count (bigger shard = more weight)
  }
  
  class ConsistentHashRing {
    private ring = new Map<number, string>();
    private sortedPositions: number[] = [];
  
    constructor(nodes: ShardNode[], private vnodesPerWeight = 150) {
      nodes.forEach((node) => this.addNode(node));
    }
  
    private hash(key: string): number {
      const digest = createHash("md5").update(key).digest("hex");
      return parseInt(digest.slice(0, 8), 16); // top 32 bits as ring position
    }
  
    addNode(node: ShardNode): void {
      const vnodeCount = node.weight * this.vnodesPerWeight;
      for (let i = 0; i < vnodeCount; i++) {
        this.ring.set(this.hash(`${node.id}#${i}`), node.id);
      }
      this.sortedPositions = [...this.ring.keys()].sort((a, b) => a - b);
    }
  
    removeNode(nodeId: string): void {
      for (const [pos, id] of this.ring) {
        if (id === nodeId) this.ring.delete(pos);
      }
      this.sortedPositions = [...this.ring.keys()].sort((a, b) => a - b);
    }
  
    getShardForKey(key: string): string {
      const hash = this.hash(key);
      const positions = this.sortedPositions;
      if (hash > positions[positions.length - 1]) return this.ring.get(positions[0])!;
  
      let lo = 0;
      let hi = positions.length - 1;
      while (lo < hi) {
        const mid = (lo + hi) >> 1;
        if (positions[mid] < hash) lo = mid + 1;
        else hi = mid;
      }
      return this.ring.get(positions[lo])!;
    }
  }

Why it matters: Virtual nodes (150 per shard here) ensure that removing or adding one physical shard only redistributes roughly 1/N of the keyspace instead of triggering a full cluster reshuffle, and binary search over sorted ring positions gives O(log V) routing per write.

Interviewer Insights & Pro Tips

Always pick the shard key to match your single most frequent WHERE clause — otherwise every query becomes a scatter-gather across all shards.
Prefer consistent hashing with virtual nodes over naive modulo(hash, N) sharding; modulo forces almost every key to move when N changes.

Red Flags & Common Pitfalls

Sharding by auto-increment primary key, which sends 100% of new writes to the highest-numbered shard.
Designing a schema that needs frequent cross-shard joins or transactions, discovered only after the system is already in production.

Deep-Dive Architecture & Concepts

3. Replication Topologies & Consistency Models

Sharding scales throughput; replication scales availability and read capacity. The topology you pick determines exactly what happens when a node dies, a network link flakes, or two clients write to the same key concurrently.

Leader-Follower (Single-Leader) Replication

All writes go to one leader, which streams a change log to followers. Simple to reason about and strongly consistent at the leader, but the leader is a single write bottleneck and a failover requires promoting a follower and re-pointing clients.

Multi-Leader Replication

Multiple regions each accept local writes and replicate asynchronously to each other, giving low write latency and regional write availability, at the cost of needing conflict resolution (last-write-wins, vector clocks, or CRDTs) when the same key is edited in two regions.

Leaderless Replication & Quorums

Dynamo-style systems (Cassandra, DynamoDB) send every write to N replicas and accept success once W acknowledge; reads query R replicas and merge. Choosing W + R > N guarantees at least one overlapping replica between every write and read.

Consistency Models: Strong, Eventual, Causal

Strong/linearizable consistency makes replicas behave as one node (highest coordination cost). Eventual consistency only guarantees convergence once writes stop. Causal consistency sits between them, preserving cause-and-effect ordering (a reply is never seen before the comment it replies to) without full linearizability's cost.

Leaderless Replication: Quorum Write & Read Repair

Dynamo-style N/W/R quorum tuning and how read repair heals a replica that missed a write during a network blip.

Coordinator Receives Write

Client's write for a key lands on any node acting as coordinator; N=3 replicas are responsible for that key.

Fan-Out to N Replicas

Coordinator forwards the versioned write (timestamp or vector clock attached) to all 3 replicas in parallel.

Write Quorum Ack (W=2)

Coordinator returns success to the client once 2 of 3 replicas persist the write; the third may be lagging due to a network blip.

Read From R Replicas (R=2)

A later read queries 2 replicas; since W+R=4 > N=3, at least one queried replica is guaranteed to have the latest write.

Read Repair

Coordinator detects the stale replica's older version and asynchronously pushes the resolved latest value back to it, healing divergence without operator action.

Interviewer Insights & Pro Tips

State your durability requirement before picking sync vs async replication: synchronous replication guarantees zero data loss on failover but adds write latency equal to the slowest replica.
When asked about multi-region writes, immediately raise conflict resolution — it's the detail that separates a senior answer from a junior one.

Red Flags & Common Pitfalls

Reading from a follower for a just-written value on a critical path (e.g. showing a user their own new order) without a read-your-writes guarantee, causing a confusing 'it disappeared' bug.
Configuring W + R <= N and assuming quorum still yields strong consistency — it does not; overlap is not guaranteed.

Deep-Dive Architecture & Concepts

4. Indexing Internals: B-Tree vs LSM-Tree Storage Engines

Every replication and sharding decision sits on top of a storage engine. Knowing whether that engine is a B-Tree or an LSM-Tree explains why a workload is fast or slow, and it is one of the most reliably asked deep-dive topics in senior database design interviews.

B-Tree Indexes (Read-Optimized)

A balanced tree of fixed-size pages (typically 4-16KB) where every lookup, insert, or delete takes O(log N) page traversals. Writes are in-place, so a single row update touches only its own leaf page, making B-Trees the default in PostgreSQL, MySQL InnoDB, and SQL Server.

LSM-Trees (Write-Optimized)

Writes go to an in-memory memtable plus a write-ahead log, and are periodically flushed as immutable sorted files (SSTables) to disk. Writes are always sequential appends, giving very high write throughput; used by Cassandra, RocksDB, LevelDB, and HBase.

Compaction & Write/Read Amplification

LSM-Trees periodically merge multiple SSTables into fewer, larger ones (compaction) to bound the number of files a read must check, which rewrites data multiple times (write amplification) in exchange for lower read amplification over time.

Covering Indexes & Index-Only Scans

A composite index that includes every column a query selects lets the engine answer entirely from the index's leaf pages, skipping the heap/table fetch entirely — the single highest-leverage indexing optimization for hot read paths.

Composite Covering Index Enabling an Index-Only Scan

sql

-- Composite index ordered to satisfy the WHERE equality predicates first,
  -- then the ORDER BY column, so no separate filesort is needed.
  CREATE INDEX idx_orders_customer_status_created
    ON orders (customer_id, status, created_at DESC)
    INCLUDE (total_amount); -- PostgreSQL covering index via INCLUDE
  
  -- This query can be satisfied entirely from the B-Tree's leaf pages:
  EXPLAIN (ANALYZE, BUFFERS)
  SELECT customer_id, status, created_at, total_amount
  FROM orders
  WHERE customer_id = 48213
    AND status = 'SHIPPED'
  ORDER BY created_at DESC
  LIMIT 20;
  
  -- Index Only Scan using idx_orders_customer_status_created
  --   Heap Fetches: 0   <- proof the query never touched the table heap

Why it matters: A composite B-Tree index whose leading columns match the equality predicates and whose trailing column matches ORDER BY lets Postgres perform an Index-Only Scan with zero heap fetches, turning what would be a sequential scan into a single bounded index descent.

Interviewer Insights & Pro Tips

When asked to pick a storage engine, ask about the read:write ratio first — B-Tree engines win for read-heavy OLTP, LSM-Tree engines win for write-heavy ingestion and time-series workloads.
Mention bloom filters when discussing LSM-Tree reads: they let the engine skip SSTables that provably do not contain a key, cutting read amplification.

Red Flags & Common Pitfalls

Adding a secondary index for every filterable column without considering that each additional index slows down every write, since the engine must maintain it transactionally.
Choosing an LSM-Tree engine for a point-lookup-heavy workload without tuning compaction strategy or bloom filter false-positive rate, leading to read amplification worse than a B-Tree would have given.

Real-World Example

Splitting a Monolithic Postgres Table Before It Became a Scaling Wall

A B2B SaaS analytics company ran all customer event data in a single 900GB PostgreSQL 'events' table shared across every tenant. During Q4 onboarding pushes, write latency p99 spiked past 2 seconds and autovacuum stalls began blocking dashboard reads for every customer simultaneously.

1Profiled query and write patterns and found over 95% of reads and writes filtered by tenant_id, making it the natural shard key instead of the existing auto-increment event_id.
2Stood up a consistent-hashing router in front of 8 new PostgreSQL shards (128 virtual nodes per shard) and migrated historical data via a dual-write-plus-backfill process.
3Switched each shard from a single instance to one leader with two asynchronous follower replicas, routing analytics dashboard queries to followers to isolate them from OLTP write load.
4Added a composite covering index on (tenant_id, event_type, created_at) per shard, eliminating the sequential scans that had been driving CPU above 90% during ingestion spikes.
5Ran a two-week shadow-read verification period comparing results from the old monolith and the new sharded cluster before cutting traffic over behind a feature flag.

Outcome: p99 write latency dropped from 2.1 seconds to 140 milliseconds, and the team was able to onboard four times as many tenants without provisioning another read replica.

Real-World Interview Questions

Mistakes That Sink Otherwise Strong Candidates

Sharding by an auto-increment ID or raw timestamp.

Why it happens: It's the easiest key to reach for since it already exists as the primary key and requires no extra design work.

The fix: Hash the key or choose a composite/business key (e.g. tenant_id, user_id) with high cardinality so new writes spread across all shards instead of piling onto the newest one.

Choosing NoSQL purely because 'it scales better,' without validating query patterns first.

Why it happens: Cargo-culting the stack used by a well-known high-scale company without checking whether the same access patterns actually apply.

The fix: Model the dominant read/write access patterns first (single-table design exercise), then choose the storage engine that best serves those patterns.

Assuming eventual consistency means 'eventually, within milliseconds' with no explicit bound.

Why it happens: Default asynchronous replication configurations hide the actual lag until a traffic spike or network issue widens it.

The fix: Add read-your-writes guarantees (route a user's own reads to the leader shortly after their write) or use causal consistency instead of assuming the lag window is negligible.

Reading from a follower replica for a critical, just-written value.

Why it happens: Read replicas are added purely to reduce leader load, without considering which specific reads must see the latest write.

The fix: Route session-critical reads (e.g. confirming a user's own new order) to the leader, or implement sticky-session / version-token read-your-writes guarantees.

Over-normalizing a schema in a system that needs very high write throughput.

Why it happens: Relational best practices (3NF) are taught as a default without weighing them against the actual read/write ratio of the system.

The fix: Selectively denormalize hot write/read paths, or introduce materialized views/CQRS read models for the specific queries that suffer from excessive joins.

Configuring quorum reads/writes with W + R less than or equal to N.

Why it happens: Teams tune N, W, and R independently for latency without checking the overlap guarantee that makes quorum consistency meaningful.

The fix: Always verify W + R > N when strict quorum consistency is required, and understand that sloppy quorums during a partition can still return stale data.

Picking an LSM-Tree storage engine for a heavy point-lookup workload without tuning it.

Why it happens: LSM-Trees are chosen reflexively for their write throughput reputation without checking the workload's actual read/write ratio.

The fix: Tune bloom filter false-positive rates and choose an appropriate compaction strategy (leveled vs size-tiered), or use a B-Tree engine if the workload is genuinely read-heavy.

Rebalancing a sharded cluster with a naive modulo(hash, N) scheme.

Why it happens: Modulo sharding is the simplest formula to implement and works fine until the first time a node is added or removed.

The fix: Use consistent hashing with virtual nodes so scaling the cluster only remaps a small fraction of the keyspace instead of nearly all of it.

Not planning for cross-shard queries until a reporting requirement surfaces in production.

Why it happens: Initial sharding design optimizes purely for OLTP write throughput and ignores analytics/reporting access patterns.

The fix: Design the shard key around the dominant OLTP query pattern, and stream changes via CDC into a separate OLAP store for cross-shard analytical queries.

Cheat Sheet

Quick-Reference Cheat Sheet

SQL vs NoSQL Decision Matrix

Structured data with relational joinsSQL (PostgreSQL/MySQL)

Flexible, evolving schema per entityNoSQL Document (MongoDB)

Simple key lookups at massive scaleNoSQL Key-Value (DynamoDB/Redis)

High write throughput / time-seriesWide-Column (Cassandra/HBase)

Deep multi-hop relationship traversalGraph DB (Neo4j)

ACID transactions at global scaleNewSQL (Spanner/CockroachDB)

CAP / PACELC at a Glance

CP systemSacrifices availability during a partition (HBase, MongoDB majority reads)

AP systemSacrifices consistency during a partition (Cassandra, DynamoDB)

PACELC 'else' branchEven with no partition, trade Latency vs Consistency

PC/EC exampleSpanner: always consistent, pays latency via TrueTime

PA/EL exampleCassandra: always available, tunable low-latency reads

Sharding Key Selection Criteria

High cardinalityAvoid keys with few distinct values (e.g. boolean flags)

Even distributionHash-based keys spread load; avoid raw monotonic IDs/timestamps

Query localityKey should match the most frequent WHERE clause to avoid scatter-gather

Hot key mitigationSalt or split celebrity/tenant keys that draw disproportionate traffic

Rebalance costConsistent hashing minimizes key movement when adding/removing shards

Replication Topology Trade-offs

Leader-FollowerSimple, strongly consistent at leader; single write bottleneck

Multi-LeaderRegional write availability; needs conflict resolution (CRDTs/LWW)

Leaderless (Quorum)Tunable via N/W/R; require W + R > N for strict quorum overlap

Synchronous replicationZero data loss on failover; adds write latency

Asynchronous replicationLow write latency; risk of data loss on failover

B-Tree vs LSM-Tree Storage Engines

Write patternB-Tree: in-place random writes; LSM: sequential append to memtable/WAL

Read patternB-Tree: single O(log N) descent; LSM: checks multiple SSTables + bloom filters

Write amplificationLSM higher (compaction rewrites); B-Tree lower

Read amplificationLSM higher (multiple levels); B-Tree lower (one index)

Best forB-Tree: read-heavy OLTP (Postgres/MySQL); LSM: write-heavy (Cassandra/RocksDB)

Consistency Model Cheat Sheet

Strong / LinearizableEvery read sees the latest write; needs coordination (Spanner, Raft/etcd)

EventualReplicas converge once writes stop; no ordering guarantee in the interim

CausalPreserves cause-effect order (a reply is never seen before its comment)

Read-Your-WritesA client always sees the effect of its own prior writes

Monotonic ReadsA client's successive reads never appear to go backward in time

Assessment Integration

Recommended Practice Quizzes on QuizCluster

Test your retention and prepare for timed live coding and MCQ technical screening rounds:

310 Quizzes

SQL & NoSQL Engines

Drill relational schema design, NoSQL data modeling, indexing, and transaction isolation across major database engines.

Start Practice Test

28 Scenarios

High-Level System Design (HLD)

Practice sharding, replication, caching, and CAP theorem trade-off scenarios in full system design contexts.

Start Practice Test

Frequently Asked Questions

Is it better to over-prepare SQL or NoSQL for a database system design interview?

Prepare both, but frame your answer around trade-offs rather than allegiance to one camp. Interviewers reward candidates who can justify a specific choice for a specific access pattern over those who default to one technology.

Do I need to memorize exact internals of every database (Postgres, Cassandra, DynamoDB, MongoDB)?

No — focus on the underlying mechanisms (B-Tree vs LSM-Tree, leader-follower vs leaderless replication, CAP/PACELC positioning) that generalize across products. Naming one or two real systems as examples is enough to show applied knowledge.

How deep should I go into consistency models for a mid-level interview versus a staff-level interview?

Mid-level candidates should clearly explain strong vs eventual consistency and when each is acceptable. Staff-level candidates are expected to also discuss causal consistency, session guarantees, and the operational cost of achieving linearizability.

What is the single most common mistake candidates make in this topic area?

Picking a sharding key or replication topology without stating the trade-off out loud. Interviewers are evaluating your reasoning process, not just whether you land on a 'correct' final answer.

Explore Other Preparation Guides

Software Engineering

How to Prepare for SDE Interview: Complete 2026 Roadmap

16 min readRead →

Java Ecosystem

How to Prepare for Java Developer Interview: Core to Spring Boot & JVM

18 min readRead →

Microservices & Distributed Systems

How to Prepare for Microservices Developer Interview: Distributed Architecture & Cloud

17 min readRead →

System Design

System Design Interview Guide: Complete 2026 Roadmap

21 min readRead →

Databases

SQL Interview Questions & Preparation Guide: Beginner to Advanced

17 min readRead →

Programming Languages

Python Interview Preparation: Complete Guide for 2026

17 min readRead →

Frontend Engineering

React Interview Preparation: React 19 & Next.js Guide

17 min readRead →

Cloud & DevOps

Kubernetes Interview Guide: Architecture, Pods, Networking & Troubleshooting

17 min readRead →

Cloud & DevOps

AWS Solutions Architect Interview Guide: Real Architecture Scenarios

17 min readRead →

Microservices & Distributed Systems

Kafka Interview Guide: Architecture, Consumers, Partitions & Exactly-Once Semantics

17 min readRead →

Backend Engineering

REST API Design Interview Guide: Authentication, Pagination, Versioning & Rate Limiting

15 min readRead →

Cloud & DevOps

Docker Interview Guide: Images, Containers, Networking & Production Debugging

15 min readRead →

Programming Languages

JavaScript & TypeScript Interview Guide: From Closures to the Event Loop

17 min readRead →

Backend Engineering

Node.js Backend Interview Guide: Event Loop, Streams, APIs & Scaling

17 min readRead →

Databases

Redis System Design Guide: Caching, Eviction, Persistence & Distributed Locks

17 min readRead →

Software Engineering

Concurrency Interview Guide: Threads, Locks, Race Conditions & Deadlocks

17 min readRead →

Software Engineering

Dynamic Programming Patterns: How to Recognize and Solve DP Problems

17 min readRead →

Database System Design: SQL vs NoSQL, Sharding, Replication & Indexing

What You Must Master to Clear This Track

Step-by-Step Study Plan

Relational Modeling, NoSQL Data Models & the CAP Theorem

Partitioning Strategies, Consistent Hashing & Rebalancing

Replication Topologies, Quorum Consistency & Index Internals

1. SQL vs NoSQL: Data Models, CAP Theorem & PACELC

2. Sharding Strategies: Range, Hash, Geo & Avoiding Hot Shards

3. Replication Topologies & Consistency Models

4. Indexing Internals: B-Tree vs LSM-Tree Storage Engines

Splitting a Monolithic Postgres Table Before It Became a Scaling Wall

Top Must-Know Interview Questions & Model Answers

Q1: When would you choose NoSQL over a relational database for a new service, and what are you giving up?

Q2: What is polyglot persistence and when does it add more operational risk than it removes?

Q3: Explain the differences between document, key-value, wide-column, and graph NoSQL models with one canonical use case each.

Q4: What is NewSQL and how do systems like Google Spanner or CockroachDB provide ACID transactions at global scale?

Q5: Why can normalizing a schema hurt performance at scale, and when should you deliberately denormalize?

Q6: How do you choose a sharding key, and what happens if you pick a low-cardinality or monotonically increasing key?

Q7: Explain consistent hashing and why it minimizes data movement compared to modulo-based sharding when nodes are added or removed.

Q8: What is a 'hot shard' and name three mitigation strategies.

Q9: How do you handle cross-shard joins or transactions in a sharded relational database?

Q10: Compare range-based, hash-based, and directory/geo-based sharding strategies.

Q11: How would you design an online shard-splitting/rebalancing operation with zero downtime?

Q12: Compare leader-follower, multi-leader, and leaderless replication topologies.

Q13: What is replication lag, and what problems does it cause for a read-after-write user experience?

Q14: How does synchronous vs asynchronous replication affect durability and latency trade-offs?

Q15: In multi-leader replication, how are write conflicts detected and resolved (LWW, vector clocks, CRDTs)?

Q16: Explain quorum reads/writes (N, W, R) in leaderless replication systems like Cassandra or DynamoDB.

Q17: Walk through what happens during a leader failover in a Raft or Paxos-based replicated database.

Q18: Explain how a B-Tree index works and why it's efficient for read-heavy OLTP workloads.

Q19: Explain how an LSM-Tree (memtable + SSTables + compaction) works and why it's optimized for write-heavy workloads.

Q20: Compare write amplification and read amplification between B-Tree and LSM-Tree storage engines.

Q21: What is a covering index, and how does it let a query be satisfied by an Index-Only Scan?

Q22: Why can having too many secondary indexes hurt write throughput?

Q23: Explain bloom filters and their role in reducing unnecessary SSTable reads in an LSM-Tree engine.

Q24: Explain the CAP theorem and why 'consistency, availability, and partition tolerance: pick two' is a common misconception.

Q25: What is PACELC and how does it extend CAP theorem reasoning to the normal (non-partitioned) case?

Q26: Differentiate strong consistency, eventual consistency, and causal consistency with concrete examples.

Q27: What are session guarantees like read-your-writes and monotonic reads, and why do client applications need them?

Q28: How does a distributed database achieve linearizability, and what's the performance cost?

Mistakes That Sink Otherwise Strong Candidates

Quick-Reference Cheat Sheet

Recommended Practice Quizzes on QuizCluster

SQL & NoSQL Engines

High-Level System Design (HLD)

Frequently Asked Questions

Is it better to over-prepare SQL or NoSQL for a database system design interview?

Do I need to memorize exact internals of every database (Postgres, Cassandra, DynamoDB, MongoDB)?

How deep should I go into consistency models for a mid-level interview versus a staff-level interview?

What is the single most common mistake candidates make in this topic area?

Explore Other Preparation Guides