System Design — Data Systems
Databases
Databases are the backbone of every system. Choosing the right database, understanding its trade-offs, and designing for scale is one of the most impactful decisions in system design.
- SQL vs NoSQL — Structured vs flexible data models
- Indexing — Making reads fast at the cost of writes
- Sharding — Distributing data across machines for horizontal scaling
Data is the new oil—but only if you can store, query, and scale it effectively.
Database Taxonomy
SQL vs NoSQL
| Dimension | SQL | NoSQL |
|---|---|---|
| Schema | Fixed, predefined | Dynamic, flexible |
| Relationships | JOINs, foreign keys | Denormalized, embedded |
| Scaling | Vertical (scale up) | Horizontal (scale out) |
| Consistency | Strong (ACID) | Eventual (BASE) |
| Query Language | SQL | Varies by database |
| Best For | Complex queries, transactions | High throughput, flexible data |
Indexing
Indexes are data structures that speed up reads at the cost of write performance and storage.
B-Tree Index Structure
Index Types
| Type | Structure | Best For |
|---|---|---|
| B-tree | Balanced tree | Range queries, equality, ORDER BY |
| Hash | Hash table | Exact equality lookups only |
| GIN | Inverted index | Full-text search, array containment |
| GiST | Generalized search tree | Geometric, full-text, range |
| Composite | Multi-column | Queries filtering on multiple columns |
Index Design Rules
- Index selective columns: Low-cardinality columns (e.g., boolean) benefit less
- Covering indexes: Include all columns needed for a query to avoid table lookups
- Leftmost prefix: Composite indexes work left-to-right
- Avoid over-indexing: Each index slows writes and uses storage
- Partial indexes: Index only rows matching a condition
Replication
Replication copies data across multiple nodes for availability and read scaling.
Replication Strategies
| Strategy | Consistency | Availability | Use Case |
|---|---|---|---|
| Synchronous | Strong | Lower (waits for replicas) | Financial, critical data |
| Asynchronous | Eventual | Higher (doesn't wait) | Social media, analytics |
| Semi-synchronous | Between | Balanced | Most production systems |
Replication Topology
Replication Lag
Sharding
Sharding distributes data across multiple database instances to achieve horizontal scalability.
Sharding Strategies
| Strategy | How It Works | Trade-off |
|---|---|---|
| Hash-based | hash(key) % num_shards | Even distribution, range queries hard |
| Range-based | Shard by key ranges | Range queries easy, hotspots possible |
| Directory-based | Lookup table maps keys to shards | Flexible, single point of failure |
| Geographic | Shard by region | Data locality, regulatory compliance |
Sharding Challenges
Hash-Based Sharding Example
Shard 1: hash(user_id) % 4 == 0 → users: 4, 8, 12, 16, ...
Shard 2: hash(user_id) % 4 == 1 → users: 1, 5, 9, 13, ...
Shard 3: hash(user_id) % 4 == 2 → users: 2, 6, 10, 14, ...
Shard 4: hash(user_id) % 4 == 3 → users: 3, 7, 11, 15, ...
Database Selection Guide
| Use Case | Recommended Database |
|---|---|
| Complex transactions (banking) | PostgreSQL |
| High-throughput key-value | Redis, DynamoDB |
| Document store (CMS, profiles) | MongoDB |
| Time-series data (metrics) | InfluxDB, TimescaleDB |
| Graph relationships (social) | Neo4j |
| Full-text search | Elasticsearch |
| Wide-column analytics | Cassandra, ScyllaDB |
| Caching layer | Redis, Memcached |
Practice Exercises
-
Design: Design a database schema for a URL shortener. Include tables for URLs, users, and analytics. What indexes would you create?
-
Trade-offs: Compare PostgreSQL and MongoDB for an e-commerce product catalog. Consider: schema flexibility, query patterns, scaling needs, and transaction requirements.
-
Architecture: Design a sharding strategy for a social media platform with 1 billion users. How do you handle user profile lookups, friend lists, and news feeds?
-
Analysis: Given a PostgreSQL database serving 10,000 QPS with 500ms p99 latency, identify the top 3 things you would investigate to improve performance.
What to Learn Next
-> Caching Strategies Redis, Memcached, cache invalidation, and write strategies.
-> Load Balancing Algorithms, health checks, and L4 vs L7.
-> Message Queues Kafka, RabbitMQ, event-driven architecture.
-> CAP Theorem Consistency models, availability, and partition tolerance.
-> Microservices Service decomposition, discovery, and API gateways.
-> Scalability Fundamentals Vertical vs horizontal scaling and capacity planning.