System Design Roadmap 04 - Databases and Storage Models

Database design choice

Choose storage based on access patterns, consistency needs, query shape, data size, write/read ratio, transactions, and operational maturity.

SQL vs NoSQL

SQL favors structured schema, joins, transactions, and rich queries. NoSQL favors flexible schema, horizontal scaling, and specialized access patterns.

Database replication

Copies data across nodes for availability, read scaling, and disaster recovery. Common issues are replication lag, failover correctness, and split brain.

Leader-follower replication

Writes go to a leader and replicate to followers. Reads can scale on followers but may be stale; leader failover must be carefully handled.

Multi-leader replication

Multiple nodes accept writes and replicate to each other. It improves regional availability but needs conflict detection and resolution.

Sharding

Split data across partitions by key or range so storage and traffic scale horizontally. The shard key determines load balance and query efficiency.

Bad shard key symptom

Hot shards, cross-shard queries, uneven storage, or difficult rebalancing. Pick keys using real access patterns, not just uniqueness.

Federation

Split databases by function or domain, such as users, orders, and catalog. It reduces blast radius but complicates cross-domain queries and transactions.

Denormalization

Duplicate or precompute data to make reads faster. It improves read performance at the cost of write complexity and consistency maintenance.

SQL tuning

Use indexes, query plans, batching, pagination, connection pooling, avoiding N+1 queries, and schema changes to reduce database load.

Key-value store

Stores values by key with very fast lookups. Good for sessions, cache, preferences, counters, and feature flags; weak for ad hoc querying.

Document store

Stores JSON-like documents. Good for flexible records and nested data; beware unbounded documents and difficult cross-document transactions.

Wide-column store

Stores rows with many sparse columns across distributed partitions. Good for huge write-heavy/time-series-like workloads. Model queries first.

Graph database

Optimized for relationships and traversals such as social graphs, fraud rings, recommendations, and dependency maps.

Index table pattern

Maintain a separate table optimized for a specific query path. Speeds reads but must be kept in sync with source data.

Materialized view

Persist a precomputed query result for fast reads. Refresh strategy determines staleness and write overhead.

CQRS

Separate command/write models from query/read models. Useful when reads and writes have very different shapes, scale, or consistency needs.

Event sourcing

Store every state change as an immutable event and rebuild current state from the log. Great auditability, but schema evolution and replay need care.