Data Storage & Database Design

Choose the right database for every use case — SQL vs. NoSQL trade-offs, schema design, sharding strategies, replication, consistency models, and the data decisions that determine system scalability.

Premium Course Content

This lesson is part of a premium course. Upgrade to Pro to unlock all premium courses and content.

Access all premium courses
1000+ AI skill templates included
New content added weekly

← Back to course overview

🔄 Quick Recall: In the previous lesson, you learned the building blocks — load balancers, caches, CDNs, and message queues. Now you’ll dive into data storage — the database decisions that more than anything else determine your system’s scalability, consistency, and reliability.

Database design is where system design interviews get deep. The interviewer wants to see that you can choose the right database for each use case, design schemas that scale, and make explicit trade-offs between consistency, availability, and performance.

SQL vs. NoSQL Decision Framework

AI prompt for database selection:

Help me choose the right database for [USE CASE]. Data characteristics: [DESCRIBE — structured/unstructured, relationships, query patterns, write/read ratio]. Scale: [TRAFFIC AND STORAGE ESTIMATES]. Consistency requirements: [STRONG/EVENTUAL]. Compare: (1) SQL (PostgreSQL/MySQL) — when it’s the right choice and why, (2) Document store (MongoDB) — when it’s the right choice, (3) Wide-column (Cassandra/HBase) — when it’s the right choice, (4) Key-value (Redis/DynamoDB) — when it’s the right choice. For each: the specific strength for my use case, the specific weakness, and the scale threshold where it becomes problematic.

Database selection matrix:

Use Case	Best Fit	Why	Not Ideal
User profiles, orders	SQL (PostgreSQL)	ACID, relationships, complex queries	> 10TB or > 50K writes/sec
Product catalog	Document (MongoDB)	Flexible schema, nested attributes	Complex joins, strict consistency
Activity feed, time-series	Wide-column (Cassandra)	High write throughput, time-based partitioning	Complex queries, transactions
Session data, cache	Key-value (Redis)	Sub-ms latency, simple lookups	Complex queries, large data sets
Search	Elasticsearch	Full-text search, faceting, ranking	Not a primary data store
Relationships (social graph)	Graph (Neo4j)	Traverse connections efficiently	Scaling, general-purpose queries

Sharding Strategies

AI prompt for sharding design:

Design a sharding strategy for [TABLE/COLLECTION] with [X] rows and [Y] QPS. Access patterns: [DESCRIBE — which columns are queried most, which queries must be fast]. Compare: (1) Hash-based sharding — on which key, how many shards, (2) Range-based sharding — on which field, partition boundaries, (3) Geography-based sharding — if applicable. For each: which queries are fast (single-shard), which are slow (scatter-gather), and how to handle cross-shard queries.

✅ Quick Check: You shard an orders table by order_id. The query “show all orders for user_123” must hit ALL shards because user orders are distributed randomly. What’s the fix? (Answer: If “orders by user” is a frequent query, shard by user_id instead of order_id. All orders for user_123 are on the same shard. The trade-off: looking up a single order by order_id now requires knowing which user placed it, or hitting all shards. Solution: maintain a secondary index mapping order_id→user_id.)

Replication and Consistency

AI prompt for replication design:

Design the replication strategy for [DATABASE] in my system. Consistency requirements: [STRONG/EVENTUAL per data type]. Availability requirements: [UPTIME TARGET]. Read/write distribution: [RATIO AND GEOGRAPHIC SPREAD]. Compare: (1) Single-leader replication — one writer, multiple readers, (2) Multi-leader replication — multiple writers in different regions, (3) Leaderless replication — any node can accept writes. For each: consistency guarantees, latency impact, and failure behavior.

Data Modeling

AI prompt for schema design:

Design the data model for [SYSTEM]. Entities: [LIST]. Relationships: [DESCRIBE]. Access patterns: [MOST COMMON QUERIES]. Generate: (1) the schema — tables/collections with fields, types, and indexes, (2) denormalization decisions — what to duplicate for read performance and why, (3) the trade-off between normalization (no duplication, complex joins) and denormalization (duplication, simple reads).

Key Takeaways

Polyglot persistence (different databases for different data types) is the right answer at scale: user profiles in PostgreSQL (consistency), news feed in Redis+Cassandra (throughput), search in Elasticsearch (full-text). State the trade-off: operational complexity vs. performance optimization
The sharding key determines which queries are fast and which are slow: choose based on your most important access patterns. Hash sharding distributes evenly but breaks range queries; range sharding enables range queries but can create hot partitions
The Transactional Outbox pattern solves cross-system consistency without distributed transactions: write the operation and the message to the same database atomically, then a separate process publishes the message
Always state the consistency model explicitly: “This data uses strong consistency because a user updating their name must see the change immediately” vs. “This uses eventual consistency because seeing a new post 2 seconds late is acceptable”
AI helps you practice the database selection conversation: describe your use case and AI walks through the options with specific trade-offs, just like a senior engineer in a design review

Up Next

In the next lesson, you’ll learn distributed systems concepts — CAP theorem, consensus, event-driven architecture, and the patterns that make systems reliable at scale.

Knowledge Check

1. You're designing a social media system. User profiles need strong consistency (a user updates their name, everyone sees the new name immediately). The news feed needs high throughput (500M reads/day). A candidate uses the same PostgreSQL database for both. Is this correct?

Yes — PostgreSQL handles both well, and it keeps the design simple Probably not at this scale. Different access patterns justify different databases: (1) User profiles — relational data with strong consistency, moderate traffic. PostgreSQL is a good fit: ACID transactions ensure profile updates are immediately consistent. (2) News feed — high read throughput, complex aggregation (posts from friends, ranked by relevance), eventual consistency is acceptable (seeing a post 2 seconds late is fine). Better fit: a combination of Redis (pre-computed feed cache) and Cassandra (persistent feed storage, designed for high write/read throughput). The principle: polyglot persistence — use the right database for each data type. The trade-off: operational complexity (managing multiple databases) vs. performance optimization (each database excels at its specific workload). In an interview, stating this trade-off explicitly is key Use MongoDB for everything — it's more flexible than PostgreSQL

2. Your users table has 2 billion rows. Queries are getting slow. You decide to shard by user_id using hash-based sharding across 16 shards. A product manager asks 'Can we query all users in country=US?' What's the problem?

Add an index on country — it'll work across shards Hash-based sharding on user_id distributes users randomly across shards — all US users are spread across all 16 shards. A query for country=US must hit ALL 16 shards (scatter-gather), which is slow and expensive. The options: (1) Accept the scatter-gather — for rare analytical queries, this may be acceptable. (2) Add a secondary index table — a separate table mapping country→user_ids, sharded by country. Fast lookup, but adds maintenance overhead. (3) Consider a different sharding key — if queries by country are frequent, shard by country instead of user_id. But this creates hot shards (the US shard is much larger than Luxembourg). (4) Composite sharding — shard by country first, then hash user_id within each country partition. In an interview, the key insight: the sharding key determines which queries are fast (single-shard) and which are slow (scatter-gather). Choose the sharding key based on your most important access patterns Shard by country instead of user_id — then the query hits only one shard

3. Your e-commerce system processes payments. The payment service writes to the database and then sends a message to the notification service. If the database write succeeds but the message send fails, the user is charged but never notified. If the message sends but the database write fails, the user is notified but not charged. How do you ensure both happen or neither happens?

Use a database transaction that includes both the write and the message send Use the Transactional Outbox pattern. The problem: you can't put a database write and a message queue publish in the same transaction because they're different systems. The solution: (1) Write the payment record AND a message record to the same database in a single transaction (this is atomic — both succeed or both fail). (2) A separate process reads the outbox table and publishes messages to the queue (at-least-once delivery). (3) The notification service processes the message idempotently (handles duplicates safely). This guarantees: if the payment is recorded, the notification WILL be sent (eventually). If the payment fails, no notification is sent. The trade-off: notification is eventually consistent (seconds delay, not immediate), and the outbox table needs periodic cleanup. AI explains this pattern visually: 'The outbox pattern ensures cross-system consistency without distributed transactions' Implement two-phase commit across the database and message queue

Answer all questions to check