Fundamentals
How Should You Think About Databases?
The database should be selected from the application's access patterns and correctness requirements, not from technology popularity.
Before saying PostgreSQL, MongoDB, Cassandra or Redis, answer the questions that actually determine storage design.
Avoid saying 'NoSQL scales, SQL does not'
Workload
OLTP vs OLAP
First identify whether your database primarily serves operational transactions or analytical queries.
OLTP — Transaction Processing
OLAP — Analytical Processing
Storage Models
Major Types of Databases
Each database family is optimized for a different kind of access pattern.
Relational Database
SQLStructured data, relationships, constraints and transactions.
Examples
PostgreSQL, MySQL, SQL Server, Oracle
Use Cases
• Payments
• Orders
• Bookings
• Inventory
• Banking
• Wallets
• User accounts
Benefits
• ACID transactions
• JOIN support
• Foreign keys
• Constraints
• Strong query capabilities
• Mature tooling
Trade-offs
• Schema changes need planning
• Horizontal scaling may require more work
• Poor indexes and joins can become expensive
Choose when
Correctness, transactions and relationships are important.
Document Database
DocumentStores flexible JSON-like documents.
Examples
MongoDB, Couchbase, Firestore
Use Cases
• CMS
• Profiles
• Product catalogs
• Content
• Dynamic metadata
• Rapidly changing schemas
Benefits
• Flexible schema
• Natural JSON model
• Nested structures
• Easy application mapping
• Good document locality
Trade-offs
• Data duplication is common
• Complex relations are less natural
• Bad document boundaries can create huge records
Choose when
Data naturally belongs together as independent JSON documents.
Key-Value Database
Key ValueFast access when a known key identifies the value.
Examples
Redis, DynamoDB, Aerospike
Use Cases
• Caching
• Sessions
• Shopping carts
• Counters
• Rate limiting
• Temporary state
• Feature flags
Benefits
• Very fast
• Simple model
• Easy key partitioning
• High throughput
• Good TTL support in many systems
Trade-offs
• Limited joins
• Limited ad-hoc queries
• Access patterns often need to be known beforehand
Choose when
Your main query is essentially get value by key.
Wide-Column Database
Wide ColumnDistributed high-throughput storage designed around known query patterns.
Examples
Cassandra, ScyllaDB, HBase
Use Cases
• Large event histories
• Activity feeds
• IoT
• Messaging metadata
• Write-heavy systems
• Large distributed workloads
Benefits
• High write throughput
• Horizontal scale
• Replication
• High availability
Trade-offs
• Query-first schema design
• Joins usually avoided
• New query pattern can require a new table
Choose when
You need huge distributed throughput and know your query patterns.
Graph Database
GraphOptimized for relationships and graph traversal.
Examples
Neo4j, Amazon Neptune, JanusGraph
Use Cases
• Social graph
• Fraud detection
• Recommendation graph
• Knowledge graph
• Network topology
• Dependencies
Benefits
• Natural nodes and edges
• Fast relationship traversal
• Useful for many-hop queries
Trade-offs
• Not ideal for every CRUD workload
• Different query model
• Distributed graph traversal can be difficult
Choose when
Relationships are the primary data you need to explore.
Time-Series Database
Time SeriesOptimized for timestamped measurements and append-heavy workloads.
Examples
InfluxDB, TimescaleDB, VictoriaMetrics
Use Cases
• Monitoring
• CPU metrics
• IoT sensors
• Application telemetry
• Financial ticks
• Observability
Benefits
• Time-window queries
• Retention policies
• Compression
• Downsampling
• High append throughput
Trade-offs
• Not intended for general transactional workloads
• Relational workflows may be unnatural
Choose when
Almost every record has a timestamp and queries are primarily time ranges.
Search Engine
SearchOptimized for text search, ranking and inverted indexes.
Examples
Elasticsearch, OpenSearch, Solr
Use Cases
• Product search
• Website search
• Autocomplete
• Log search
• Faceted filtering
• Text ranking
Benefits
• Full-text search
• Fuzzy matching
• Ranking
• Aggregations
• Tokenization
Trade-offs
• Usually not the transactional source of truth
• Index synchronization is required
• Operational complexity at scale
Choose when
Users need search behavior rather than exact row lookup.
Vector Database
VectorNearest-neighbor search over embeddings.
Examples
Pinecone, Milvus, Weaviate, Qdrant, pgvector
Use Cases
• RAG
• Semantic search
• Image similarity
• Recommendations
• AI memory
• Embedding retrieval
Benefits
• Semantic retrieval
• Similarity search
• ANN indexes
• Metadata filtering
Trade-offs
• Not a transactional database replacement
• Approximate search has recall/latency trade-offs
• Embedding refresh needs management
Choose when
You need similarity based on meaning rather than exact equality.
Columnar / Analytical Database
OLAPOptimized for large scans and analytical aggregations.
Examples
ClickHouse, BigQuery, Snowflake, Redshift
Use Cases
• Analytics
• Dashboards
• Reporting
• BI
• Event analysis
• Data warehouse
Benefits
• Column compression
• Large scans
• Fast aggregations
• Efficient analytical queries
Trade-offs
• Not usually designed for frequent small row transactions
• Different latency and cost characteristics
Choose when
You run large SUM, COUNT, GROUP BY and analytical queries.
Distributed SQL
Distributed SQLRelational semantics with distributed replication and horizontal scaling.
Examples
Google Spanner, CockroachDB, YugabyteDB
Use Cases
• Global transactional applications
• Multi-region SQL
• Highly available transactional systems
• Large relational workloads
Benefits
• SQL
• Transactions
• Horizontal scale
• Replication
• Multi-region support
Trade-offs
• Distributed transactions cost latency
• Operational complexity
• May be unnecessary for smaller applications
Choose when
You truly need relational transactions across distributed infrastructure.
Decision Guide
Which Database Should You Use?
Choose storage based on what the system actually needs.
Identify Access Pattern
Transactions? Search? Analytics? Relationships?
Transactions
PostgreSQL / MySQL
Flexible Documents
MongoDB
Key Lookup
Redis / DynamoDB
Huge Writes
Cassandra
Relationships
Graph DB
Metrics
Time-Series DB
Text Search
Elasticsearch
Semantic Search
Vector DB
Analytics
ClickHouse / BigQuery
Polyglot persistence
Data Modeling
Normalization vs Denormalization
Normalization reduces duplication and anomalies. Denormalization intentionally duplicates selected data to improve read performance.
Normalization
Denormalization
Normal Forms
1NF
Keep values atomic and avoid repeating groups.
2NF
Meet 1NF and remove partial dependency on part of a composite key.
3NF
Meet 2NF and remove unnecessary transitive dependencies.
System design perspective
Transactions
ACID and BASE
These models help reason about transactional correctness and distributed consistency trade-offs.
Atomicity
All operations in a transaction succeed together or fail together.
Consistency
Transactions preserve defined database rules and constraints.
Isolation
Concurrent transactions interact according to a defined isolation model.
Durability
Committed data survives failures according to durability guarantees.
BEGIN;
UPDATE accounts
SET balance = balance - 100
WHERE id = 'A';
UPDATE accounts
SET balance = balance + 100
WHERE id = 'B';
COMMIT;BASE
Basically Available
Soft State
Eventual Consistency
Concurrency
Transaction Isolation Levels
Isolation determines which effects concurrent transactions are allowed to observe.
| Level | Dirty Read | Non-repeatable Read | Phantom | Concurrency |
|---|---|---|---|---|
| READ UNCOMMITTED | Possible | Possible | Possible | Highest |
| READ COMMITTED | Prevented | Possible | Possible | High |
| REPEATABLE READ | Prevented | Prevented | DB dependent | Medium |
| SERIALIZABLE | Prevented | Prevented | Prevented | Lowest |
Dirty Read
Transaction reads uncommitted data written by another transaction.
Non-repeatable Read
The same row returns a different committed value later in the same transaction.
Phantom Read
A repeated range query returns additional or missing rows.
Lost Update
Two transactions overwrite each other's changes.
Concurrency Control
Optimistic vs Pessimistic Locking
These are two common ways to protect updates when multiple users can modify the same logical record.
Pessimistic Locking
Lock the record while performing critical work.
BEGIN;
SELECT *
FROM seats
WHERE id = 10
FOR UPDATE;
UPDATE seats
SET status = 'BOOKED'
WHERE id = 10;
COMMIT;Good when conflicts are likely.
Optimistic Locking
Allow concurrent reads and reject the update if the version changed.
UPDATE seats
SET
status = 'BOOKED',
version = version + 1
WHERE
id = 10
AND version = 7;Good when conflicts are uncommon.
Concurrency
MVCC — Multi-Version Concurrency Control
MVCC allows readers and writers to interact using row versions or snapshots instead of forcing every read to block every write.
Transaction A
Reads version 1
Row Version 1
balance = 100
Row Version 2
balance = 150
Depending on isolation semantics, Transaction A can continue reading its previous consistent snapshot while newer transactions observe the updated version.
MVCC does not mean 'no locks ever'
Distributed Systems
CAP Theorem
CAP describes the trade-off a distributed system faces during a network partition.
Consistency
Requests observe behavior compatible with the system's chosen consistency guarantee.
Availability
Every request to a non-failing node receives a response.
Partition Tolerance
The system continues operating despite network communication failure between nodes.
CAP is about what happens during a partition
Performance
Database Indexes
Indexes reduce read work by maintaining structures that make selected lookups faster.
SELECT *
FROM users
WHERE email = '[email protected]';
Potential result:
Scan millions of rows
until matching email is found.CREATE INDEX idx_users_email
ON users(email);B-Tree
Great general-purpose structure for equality, ranges and ordered access.
Hash
Useful for equality lookups in systems that support it.
Composite
Indexes multiple columns and should match common query ordering.
Covering
Contains enough indexed data to satisfy a query without fetching the full row in some databases.
Full Text
Supports text-search behavior.
Spatial
Supports geospatial queries.
Vector
Supports nearest-neighbor vector search.
Partial
Indexes only a subset of rows when supported.
Indexes have a write cost
Composite Index Ordering
CREATE INDEX idx_orders_user_status
ON orders(user_id, status);The order of columns matters because the index is organized according to its key sequence. Design indexes from actual query patterns.
Query Performance
Query Planner & Optimizer
When SQL arrives, the database must decide how to execute it efficiently.
SQL Query
Parser + Planner
Generate possible plans
Cost Estimator
Rows, indexes, statistics
Execution Plan
Index scan / sequential scan / join strategy
EXPLAIN ANALYZE
SELECT
id,
user_id,
status
FROM orders
WHERE user_id = 42
AND status = 'PAID';Look For
Optimization Inputs
Database Connections
Connection Pooling
Opening a new database connection for every HTTP request is expensive and can overwhelm the database.
App 1
App 2
App N
Connection Pool
Example: 50 active DB connections
Database
Autoscaling can create a connection explosion
Availability
Database Replication
Replication maintains multiple copies of data for availability, durability and sometimes read scalability.
Single-Leader Replication
Primary
Accept writes
Replica 1
Reads
Replica 2
Reads
Replica 3
Failover
Replication Models
Single Leader
One leader accepts writes and replicates them to followers.
Multi Leader
More than one leader can accept writes. Conflict resolution becomes important.
Leaderless
Clients or coordinators write to multiple replicas and use quorum-style techniques.
Sync vs Async
Synchronous
Asynchronous
Read Scaling
Read / Write Splitting
A common architecture sends writes to the primary and appropriate reads to replicas.
Write Traffic
Read Traffic
Primary
Writes
Replica Router
Read distribution
R1
R2
R3
Read-after-write problem
Large Tables
Database Partitioning
Partitioning divides a large logical dataset into smaller pieces that can be managed independently.
Horizontal Partitioning
Divide rows.
orders_2025
orders_2026
orders_2027Common for large time-based or range-based datasets.
Vertical Partitioning
Separate columns or logical features.
users
----------------
id
email
name
user_profiles
----------------
user_id
bio
avatar
preferencesApplication
Shard Router
Find owning shard
Shard A
Subset
Shard B
Subset
Shard C
Subset
Sharding Strategies
Hash Sharding
hash(key) determines the shard. Often distributes traffic reasonably evenly.
Range Sharding
Different value ranges live on different shards. Useful for range locality but can create hotspots.
Directory Sharding
A mapping service determines which shard owns each entity.
Geo Sharding
Data is partitioned by region for locality or data residency.
Sharding Problems
Complexities Introduced
Shard-Key Design
Hot Shards & Celebrity Problem
A system can have many shards but still fail if most traffic lands on one shard.
User
User
User
Millions
Shard A
🔥 overloaded
Shard B
Mostly idle
Shard C
Mostly idle
Good Shard Key
Possible Hot-Shard Solutions
Distribution
Consistent Hashing
Consistent hashing reduces how many keys need to move when nodes are added or removed.
Node A
Node B
Node C
Node D
Hash Ring
Keys map to positions and are assigned to the next appropriate node around the ring.
Virtual nodes
Leaderless Databases
Quorum Reads and Writes
Some distributed stores write and read from multiple replicas using configurable quorum values.
N
Total number of replicas.
W
Number of replica acknowledgements required for a write.
R
Number of replicas consulted for a read.
N = 3
W = 2
R = 2
A write succeeds after 2 replicas acknowledge.
A read consults 2 replicas.R + W > N is useful intuition, not the complete consistency story
Distributed Reads
Consistency Models
Consistency is not simply strong or eventual. Different applications need different guarantees.
Strong Consistency
Reads behave according to a strong latest-value style guarantee provided by the system.
Eventual Consistency
Replicas can temporarily differ and converge later.
Read Your Writes
A client sees its own successful updates.
Monotonic Reads
After seeing a newer state, a client should not later see an older one.
Bounded Staleness
Reads may lag, but only within an allowed time or version bound.
Causal Consistency
Causally related operations are observed in a consistent causal order.
Durability
Write-Ahead Log — WAL
Many databases record intended changes in a durable log before considering the transaction safely committed.
Application Write
Write-Ahead Log
Durable record of change
Data Pages
Flushed later
After a crash, the database can use its transaction log to replay committed operations and recover state.
Internal Caching
Database Buffer Pool
Databases themselves cache frequently accessed pages in memory.
SQL Query
Buffer Pool
Frequently used DB pages in RAM
Disk / SSD
Database read does not always mean disk read
Disaster Recovery
Backup, PITR, RPO & RTO
Replication protects availability, but replication alone is not a backup strategy.
Backup Techniques
Recovery Questions
RPO
Recovery Point Objective
Maximum acceptable amount of data loss measured in time.
RTO
Recovery Time Objective
Maximum acceptable time to restore service.
Production Changes
Schema Migration Without Downtime
Large tables cannot always be changed with one blocking migration.
Expand — add the new schema in a backward-compatible way.
Deploy code that understands both old and new representations.
Backfill existing data in controlled batches.
Switch reads and writes to the new representation.
Verify migration and monitoring.
Contract — remove the old schema later.
Avoid huge one-shot migrations
Data Lifecycle
Soft Delete vs Hard Delete
Deletion semantics affect auditing, privacy, storage and query behavior.
Hard Delete
DELETE FROM users
WHERE id = 42;Record is physically removed from the logical table.
Soft Delete
UPDATE users
SET
deleted_at = NOW()
WHERE
id = 42;Record remains stored but is logically hidden.
Soft delete adds query complexity
Storage Lifecycle
Hot, Warm & Cold Data
Not every record needs to remain forever in the expensive primary transactional database.
Hot Data
Primary DB • frequently accessed
Warm Data
Archive tables / cheaper storage
Cold Data
Object storage / long-term archive
Historical logs, completed orders and old events can often be moved out of the latency-sensitive primary storage while remaining available for analytics or compliance.
Security
Database Security
A scalable database that exposes customer data is still a failed architecture.
Access Security
Data Security
Never expose the database directly to the public internet unless there is a very deliberate secured architecture
Scaling Strategy
How Should You Scale a Database?
Sharding should usually not be the first optimization.
Measure the bottleneck first.
Fix inefficient queries.
Improve schema design.
Add appropriate indexes.
Use connection pooling.
Cache suitable hot reads.
Scale vertically if cost-effective.
Add read replicas for read-heavy traffic.
Partition huge tables.
Archive cold data.
Shard when one database can no longer satisfy storage or write requirements.
Measure before scaling
Case Study
BookMyShow-Style Database Architecture
A high-concurrency booking platform is a good example of combining multiple storage technologies.
Interview-style architecture
Users
Booking Service
SQL
Bookings + payments
Redis
Seat holds + cache
Search
Movie discovery
Analytics
Reporting
Core SQL Schema
movies
-------------------------
id
title
language
theatres
-------------------------
id
name
city
shows
-------------------------
id
movie_id
theatre_id
screen_id
start_time
show_seats
-------------------------
show_id
seat_id
status
price
version
bookings
-------------------------
id
user_id
show_id
status
total_amount
booking_seats
-------------------------
booking_id
show_id
seat_id
payments
-------------------------
id
booking_id
provider_txn_id
status
amountPrevent Final Double Booking
UPDATE show_seats
SET
status = 'BOOKED',
version = version + 1
WHERE
show_id = :showId
AND seat_id = :seatId
AND status = 'AVAILABLE'
AND version = :expectedVersion;If zero rows are updated, another transaction has probably changed the seat.
Alternative: Pessimistic Lock
BEGIN;
SELECT *
FROM show_seats
WHERE
show_id = :showId
AND seat_id = :seatId
FOR UPDATE;
-- verify AVAILABLE
UPDATE show_seats
SET status = 'BOOKED'
WHERE ...;
COMMIT;Why Redis Still Helps
Redis can provide a short-lived seat hold during checkout, reducing contention and improving user experience. The durable database remains responsible for final booking correctness.
SET
show:992:seat:A10:lock
booking-token-123
NX
PX 300000Layered correctness
Production
Database Failure Scenarios
Good system design covers what happens when the storage layer becomes slow, unavailable or inconsistent.
Primary Failure
Promote a healthy replica or use managed failover while clients reconnect safely.
Replica Lag
Consistency-sensitive reads may need to go to the primary.
Connection Exhaustion
Use bounded pools, backpressure and appropriate timeouts.
Hot Shard
Change partition strategy, split hot data or cache high-volume reads.
Slow Query
Inspect EXPLAIN plans, indexes, row estimates, lock waits and scanned data.
Deadlock
Keep transactions small, use consistent lock ordering and handle transaction retry safely.
Disk Full
Capacity monitoring, archival, cleanup and emergency storage procedures are required.
Backup Failure
Alert immediately and regularly test actual restore procedures.
Region Failure
Use cross-region replication and a clearly tested disaster-recovery process when required.
Observability
Database Metrics to Monitor
Database performance problems often appear in metrics before they become full outages.
Interview Preparation
Database System Design Interview Questions
Use these for revision after studying the complete page.
How do you choose between SQL and NoSQL?
When would you choose PostgreSQL?
When would you choose MongoDB?
When is Cassandra a good choice?
When should Redis be used?
Why should Elasticsearch usually not be the transactional source of truth?
When should you use a graph database?
What problem does a time-series database solve?
When do you need a vector database?
OLTP vs OLAP?
Normalization vs denormalization?
Explain 1NF, 2NF and 3NF.
What are ACID properties?
What is BASE?
What are transaction isolation levels?
What is a dirty read?
What is a non-repeatable read?
What is a phantom read?
What is a lost update?
Optimistic vs pessimistic locking?
What is MVCC?
Explain CAP theorem.
What is a database index?
B-Tree vs hash index?
What is a composite index?
Why do indexes make writes slower?
What does EXPLAIN ANALYZE do?
How does a query optimizer select an execution plan?
Why do applications use connection pools?
How can autoscaling cause database connection exhaustion?
What is replication?
Single-leader vs multi-leader vs leaderless replication?
Synchronous vs asynchronous replication?
What is replication lag?
What is read/write splitting?
How do you solve read-after-write inconsistency?
What is horizontal partitioning?
What is vertical partitioning?
Partitioning vs sharding?
What is database sharding?
Hash vs range sharding?
What makes a good shard key?
What is a hot shard?
What is consistent hashing?
Why use virtual nodes?
What are N, R and W in quorum systems?
Strong consistency vs eventual consistency?
What is read-your-writes consistency?
What is WAL?
How does WAL help durability?
What is a database buffer pool?
Replication vs backup?
What are RPO and RTO?
How would you perform a zero-downtime schema migration?
Soft delete vs hard delete?
How do you archive old data?
How should databases be secured?
How would you scale a read-heavy database?
How would you scale a write-heavy database?
When should you finally introduce sharding?
How would you design BookMyShow's booking database?
How would you prevent double seat booking?
What database metrics would you monitor?
Quick Revision
Frequently Asked Questions
Short answers to frequently searched database system-design questions.
How should I choose a database in a system design interview?
Start from access patterns, transaction requirements, consistency requirements, relationships, expected scale, read and write volume, query flexibility and operational complexity.
What is the difference between partitioning and sharding?
Partitioning means splitting data into smaller logical pieces. Sharding commonly means distributing those pieces across different database nodes or machines.
What is replication?
Replication stores copies of data on multiple nodes to improve availability, durability or read scalability.
What is a hot shard?
A hot shard receives a disproportionate amount of reads or writes compared with other shards, creating a bottleneck despite unused capacity elsewhere.
Why do databases use indexes?
Indexes maintain additional structures that allow the database to find rows without scanning the complete table, trading extra storage and write cost for faster reads.
What is MVCC?
Multi-Version Concurrency Control allows transactions to work with versions or snapshots of rows so reads and writes can often proceed with less blocking.
Learn complete system design, not isolated database concepts.
Learn how databases, caching, load balancing, queues, rate limiting, locking, concurrency and scaling work together in complete interview-style architectures.
Continue Learning