Interview Guide: Databases in High-Level Design (HLD)

Understood. Here is a deep, senior-level rewrite of your article: Interview Guide – Databases in HLD.

This is now a CTO-level, FAANG-grade guide—fully aligned in depth and sophistication with your earlier advanced articles (WAL, SSTables, LSM Trees, replication, sharding, polyglot persistence, etc.).

This article is now suitable as:

• The master playbook for handling database questions in system design interviews
• A core chapter on Kavriq
• A revision checklist before any high-level interview
• A meta-framework tying together all your prior articles

Let’s begin.

A Deep, Structured Framework for FAANG & Senior Engineering Interviews

System design interviews overwhelmingly revolve around databases — how to choose them, how to scale them, how to guarantee correctness, and how to handle failures in distributed systems.

This guide provides a complete, senior-level playbook for approaching any database question in a system design interview.

1. How to Think About Databases in HLD Interviews

Most candidates jump straight into:

“Use NoSQL for scale.”

This is a fail.

Senior-level interviews expect:

• Reasoning, not guesswork
• Trade-offs, not buzzwords
• Evolution, not big-bang solutions
• Failure handling, not happy paths
• Deep understanding of internals (WAL, replication, sharding, MVCC, LSM, durability)

So here is the structured thinking process.

2. Step-by-Step Database Design Framework (The 8-Step Master Method)

This framework will allow you to answer any database HLD question:

STEP 1 — Clarify Requirements

You must ask:

✔ Workload Type

• Read-heavy?
• Write-heavy?
• Mixed OLTP?
• Analytics?

✔ Data Size & Growth Pattern

• Is the dataset 10 GB, 1 TB, 100 TB?
• Does it grow linearly? exponentially? event-driven?

✔ Latency Expectations

• sub-ms (Redis)
• single-digit ms (Postgres)
• tens of ms (Elastic / search)
• seconds (analytics)

✔ Access Patterns

• Point lookups?
• Range queries?
• Joins?
• Full-text search?
• Graph traversal?

✔ Consistency Requirements

• Can we tolerate stale reads?
• Strong consistency vs eventual?
• Is write-after-write or read-after-write consistency needed?

✔ Availability Expectations

• 99.99%?
• Multi-region?
• Zero downtime?

🔥 Interview Tip: Asking good questions shows seniority before you’ve drawn a single box.

STEP 2 — Start with the Simplest Possible Design

Every system starts with:

• One app server
• One relational DB (Postgres/MySQL)
• Optional Redis cache

Why this is important:

• Interviewers want “progressive scaling”
• Starting with microservices, sharding, DynamoDB, Kafka = red flag

Say:

“Let’s begin with a single-node system with Postgres/MySQL — simplest, strongest consistency, easiest to reason about.”

STEP 3 — Identify Bottlenecks Scientifically

Instead of generic statements (“DB will break”), mention:

✔ Read Bottlenecks

• Too many queries / slow queries
• Missing indexes / bad indexes
• Buffer pool misses
• Cache thrashing
• Full table scans

✔ Write Bottlenecks

• WAL fsync cost
• B+ Tree page splits
• MVCC bloat
• Replication lag
• Shard hotspots

✔ Storage Bottlenecks

• Single machine disk capacity
• IOPS saturation
• Partition table growing too large

✔ Network Bottlenecks

• Large payloads
• Chatty transactions

🔥 Interview Tip: Mentioning observability (slow query log, Prometheus metrics, DB dashboards, explain plans) sets you apart.

STEP 4 — Scale Vertically First (Scale-Up)

Upgrade machine → easiest win.

Say:

“Before distributing systems, always exhaust vertical scaling — improves CPU parallelism, buffer pool size, reduce I/O amplification.”

Vertical scaling solves:

• moderate growth
• short bursts
• small startups

But vertical scaling hits:

• diminishing returns
• cost explosion
• single point of failure

Thus…

STEP 5 — Add Horizontal Scaling Components Gradually

This is where senior candidates shine.

A. Add Replication (Scale Reads + Improve Availability)

Explain internals:

• Primary writes to WAL → replicas stream WAL
• Replication lag = stale reads
• Failover risks: split-brain, read-after-write inconsistency

Use cases:

• Follow timelines
• Profile pages
• Product catalog

🔥 Senior Level Insight: Mention read-your-own-write consistency and how to avoid stale reads (read from primary, or use session consistency).

B. Introduce Caching (Reduce DB Load)

Explain patterns:

• Cache-aside
• Write-through
• Write-back (dangerous)
• TTL-based expiration
• Hot key handling (Redis clustering)

Mention cache invalidation strategies — a must-have in interviews:

• Version-based invalidation
• Event-driven invalidation
• Pub/sub
• Hash tags

C. Sharding (Scale Writes + Scale Storage)

This is where interviews get hard. Show deep understanding:

Shard Key Design

A good shard key:

• High cardinality
• Uniform distribution
• Doesn’t change
• Aligns with query path

Bad shard keys cause hotspots, skew, cross-shard fan-out queries, massive rebalancing pain.

Sharding Strategies

• Range (problem: hotspots)
• Hash (problem: no range queries)
• Directory-based (problem: metadata availability)
• Consistent hashing (minimizes moves)

Shard Routing

• Client-side libraries (Cassandra, DynamoDB)
• Router/proxy nodes (MongoDB mongos, Vitess)
• Metadata services (ZooKeeper, etcd, Config servers)

Rebalancing

One of the hardest distributed systems problems.

Discuss:

• hash slot migration
• chunk splits
• range movement
• online migration vs offline migration
• double-writing during rebalancing

🔥 Senior-level hint: Mention “rebalancing needs to be automated and gradual to avoid availability drops.”

STEP 6 — Consider Polyglot Persistence (Use Multiple DBs)

This is where your deep article on polyglot persistence ties in.

Explain:

• Relational DB → source of truth
• Redis → cache
• Elasticsearch → search index (materialized view)
• Cassandra → write-optimized event store
• Kafka → change propagation
• Snowflake/BigQuery → analytics

Mention CDC (Debezium), Outbox pattern, CQRS, materialized views, Sagas for distributed transactions.

This shows real-world architectural maturity.

STEP 7 — Discuss Consistency, Availability & Fault Tolerance

This must be explicit.

✔ CAP theorem

• CP systems (Postgres with sync replication)
• AP systems (Cassandra, DynamoDB)

✔ PACELC

When no partition:

• Choose Latency or Consistency

Explain trade-offs:

• Strong consistency → higher latency, lower availability
• Eventual consistency → high throughput, risk of stale reads
• Tunable consistency (Cassandra) → various trade-offs

STEP 8 — Failure Modes (Real Interview Differentiator)

Mention:

✔ Replica Lag

→ Solution: read from primary, monotonic reads, session consistency.

✔ Shard Outage

→ Solution: RF > 2, quorum reads, replica promotion.

✔ Node Crash During Write

→ Solution: WAL durability, fsync, Raft/Paxos consensus.

✔ Search Index Out of Sync

→ Solution: CDC reindex, backfill, versioning.

✔ Cache Stampede

→ Solution: dogpile protection, locking, request coalescing.

🔥 These are the areas where great candidates differentiate themselves.

3. Common Pitfalls (Deep Version)

Your earlier list was good. Here is the senior-level version:

1. Overengineering Too Early

Bad:

“Let’s use microservices, Cassandra, Kafka, ElasticSearch.”

Good:

“Start with SQL → scale up → replicate → cache → shard → polyglot as last step.”

2. Ignoring Internal DB Mechanics

Bad:

“We shard for scale.”

Good:

“We shard because WAL throughput on a single primary is saturating, buffer pool misses are increasing, and MVCC garbage is creating bloat.”

3. Not Thinking About Write Path

Reads are easy. Writes are hard.

Mention:

• I/O amplification
• replication cost
• LSM compaction
• B+ tree page splits

4. Using NoSQL Without Understanding Trade-offs

Bad:

“NoSQL scales better.”

Good:

“Cassandra scales writes because LSM makes inserts sequential, but read amplification increases and multi-row transactions are hard.”

5. Forgetting Lifecycle of Data

Example:

• hot data → Redis
• warm data → Postgres
• cold data → object storage (S3)

6. Missing Idempotency

Especially for messaging and retry logic.

7. Forgetting Observability

Always mention:

• slow query log
• indexing stats
• metrics: QPS, p99 latency, replicas lag, compaction queue

4. Interview Checklist (Upgraded)

Before you answer any system design question, ensure you cover:

✔ DB choice (reasoned, tied to workload)

✔ Data model (row vs document vs wide column vs search index)

✔ Indexing strategy (cover primary + secondary + composite)

✔ Caching (patterns + invalidation)

✔ Write path & durability (WAL, fsync, Raft)

✔ Read path optimization (buffer pool, fan-out, locality)

✔ Scaling:

• vertical → replication → caching → sharding → polyglot

✔ Consistency model (strong vs eventual vs tunable)

✔ Availability (failover, replicas, quorum)

✔ Data synchronization (CDC, outbox, sagas)

✔ Failure modes

✔ Capacity planning + QPS estimates

If you cover these, you cannot fail a database HLD round.

5. Deep Example: Design a Scalable Messaging System

Your example was good, but here’s the senior version.

Step 1 — Start simple

• App → Postgres → Redis cache for hot conversations

Step 2 — Identify bottlenecks

Messaging is write-heavy, sequential, append-only.

Bottlenecks will hit:

• WAL fsync
• IOPS
• replication lag
• large table bloat

Step 3 — Scale writes

• Shard messages by (conversation_id) or (user_id)
• Use consistent hashing or range-based sharding
• Replicate each shard (RF = 3)

Step 4 — Reduce read load

• Redis for hot chats
• Materialized inbox per user

Step 5 — Improve delivery pipeline

• Use Kafka for durability and fan-out
• Consumers update search index, analytics

Step 6 — Consistency

• Strong consistency within conversation
• Eventual consistency for analytics

Step 7 — Real-world reference

WhatsApp → Erlang actors + Cassandra + eventual consistency.

This type of reasoning wins interviews.

6. Recap

To excel in database-focused HLD interviews:

✔ Think systematically

✔ Start small, scale gradually

✔ Talk about internals (WAL, LSM, replication lag, shard-key etc.)

✔ Understand consistency trade-offs

✔ Use failure modes to show depth

✔ Use real company architectures

✔ Use a structured framework

This guide + your previous articles = elite-level preparation.

If you’d like, I can now produce:

✔ A Database HLD Cheat Sheet (one-pager for revision)

✔ A list of 100 database interview questions

✔ A scenario-based mock interview

✔ A diagram pack illustrating scaling patterns

✔ A PDF compilation of your entire database series (formatted professionally)

Tell me what you’d like next.