Databases in System Design: SQL vs NoSQL, Replication, and Sharding Explained
We have covered how to distribute traffic across multiple servers. Now those servers all have the same problem: they are pointing at one database.
The database is almost always the first bottleneck in a growing system. App servers are stateless and easy to add. Databases store your most critical data and cannot be scaled carelessly. Get this wrong and you lose data, corrupt records, or bring your entire system down.
This post covers everything you need to know about databases for system design interviews — the SQL vs NoSQL decision, read replicas, sharding, and the one framework for choosing the right database every time.
SQL vs NoSQL: The Decision That Shapes Everything
This is the most common database question in system design interviews. Interviewers do not want you to just name a database. They want to hear your reasoning. The right answer is always: it depends — and here is what it depends on.
SQL (Relational Databases)
SQL databases store data in tables with rows and columns. Data has a fixed schema — you define the structure upfront. Relationships between tables are enforced with foreign keys. Examples: PostgreSQL, MySQL, SQLite.
Use SQL when:
Your data is structured and relationships between entities matter. Users have orders. Orders have products. Products have categories.
You need ACID transactions. Money transfers, inventory updates, anything where partial writes would cause serious problems.
Your query patterns are complex — joins across multiple tables, aggregations, filtering by multiple fields.
NoSQL (Non-Relational Databases)
NoSQL is not one thing. It is a category of databases that each solve a specific problem SQL was not designed for. The four main types:
Document stores (MongoDB, DynamoDB) — store data as JSON-like documents. Flexible schema. Good for user profiles, product catalogs, content.
Key-value stores (Redis, DynamoDB) — extremely fast lookups by key. Good for caching, sessions, leaderboards.
Column stores (Cassandra, HBase) — optimized for writing and reading huge volumes of time-series or event data. Good for analytics, logs, metrics.
Graph databases (Neo4j) — store relationships as first-class citizens. Good for social networks, recommendation engines, fraud detection.
Use NoSQL when:
You need to scale horizontally to handle massive write volumes. Cassandra and DynamoDB are built for this.
Your data structure varies across records. Not every user profile has the same fields.
You are optimizing for one specific access pattern rather than flexible queries.
ACID vs BASE: What These Actually Mean
These terms come up constantly in interviews. Know them precisely.
ACID (SQL databases)
Atomicity — a transaction either fully completes or fully rolls back. No partial writes.
Consistency — every transaction moves the database from one valid state to another.
Isolation — concurrent transactions don’t interfere with each other.
Durability — once committed, data is saved even if the system crashes immediately after.
BASE (NoSQL databases)
Basically Available — the system is always available even if some data is stale.
Soft state — data may be in flux. Replicas may not be in sync at any given moment.
Eventually consistent — given enough time without new writes, all replicas will converge to the same value.
The interview insight: BASE sacrifices consistency for availability and scale. For a social media feed, showing a post that is 2 seconds old is fine. For a bank transfer, it absolutely is not. Match the consistency model to the business requirement.
The CAP Theorem: The Trade-off Every Distributed Database Makes
The CAP theorem states that a distributed database can only guarantee two of these three properties simultaneously:
Consistency — every read returns the most recent write or an error.
Availability — every request receives a response, even if it might be stale.
Partition tolerance — the system keeps working even when network failures split it into isolated parts.
Since network partitions are unavoidable in real distributed systems, the real choice is between Consistency and Availability during a partition.
CP systems (consistency over availability): return an error rather than stale data. Used in banking, inventory systems. Examples: HBase, MongoDB.
AP systems (availability over consistency): return potentially stale data rather than an error. Used in social media, DNS. Examples: Cassandra, DynamoDB.
Database Replication: Solving the Read Bottleneck
Most applications read data far more than they write it. Twitter reads tweets millions of times for every one tweet created. This read-heavy pattern means your database is spending most of its time serving reads.
Read replicas solve this. You have one primary database that handles all writes. That primary continuously replicates its data to one or more replica databases. All reads are distributed across the replicas.
Benefits:
Read capacity scales horizontally. Add more replicas to handle more read traffic.
Fault tolerance. If the primary fails, a replica can be promoted to primary.
Geographic distribution. Put replicas close to users in different regions for lower latency.
The trade-off:
Replication lag. Replicas are not always perfectly in sync with the primary. A user writes a post and immediately reads it — they might hit a replica that has not received the write yet and see nothing. This is eventual consistency in practice. For most features this is acceptable. For critical reads immediately after writes, route to the primary.
Database Sharding: Solving the Write Bottleneck
Read replicas solve the read problem. But what about writes? All writes still go through one primary database. At massive scale — millions of writes per second — even the best hardware hits its limit.
Sharding splits your data horizontally across multiple databases. Instead of one database with all users, you have three databases each containing a subset of users. Users 1-10M on shard 1. Users 10M-20M on shard 2. Users 20M-30M on shard 3.
Common sharding strategies:
Range-based sharding — shard by ID range (1-10M, 10M-20M). Simple but creates hot spots if new users are all on the latest shard.
Hash-based sharding — hash the user ID to determine the shard. Distributes evenly but makes range queries difficult.
Directory-based sharding — a lookup table maps each record to its shard. Most flexible but the lookup table becomes a single point of failure.
The real cost of sharding:
Sharding introduces significant complexity. Cross-shard queries are expensive or impossible. Joins across shards don’t work. Rebalancing shards when you add new ones is painful. This is why sharding should be a last resort — exhaust read replicas, caching, and query optimization first. Mention this in interviews. It shows maturity.
The Framework for Choosing a Database in Any Interview
When you need to choose a database in a system design interview, ask these four questions in order:
Do I need ACID transactions? If yes, SQL.
Is my data structured with clear relationships? If yes, SQL.
Do I need to scale writes to massive volumes? If yes, NoSQL.
Is my access pattern simple and predictable? If yes, NoSQL.
Most real systems use both. SQL for core transactional data. NoSQL for specific high-scale use cases. Don’t feel like you have to pick one. The best answer often includes both with a clear reason for each.
The Complete Interview Answer on Databases
Here is how a strong database answer sounds for a system like Twitter:
“I’d use a combination. For user accounts and relationships I’d use PostgreSQL — the data is relational and we need consistency. For tweets themselves I’d use Cassandra — it’s optimized for high write throughput and time-series data, which matches our access pattern of reading the latest tweets by user. I’d add read replicas to the PostgreSQL instance to handle the heavy read traffic on user profiles and set up Redis as a caching layer in front of both to serve the hottest data from memory.”
That answer shows you can reason about data access patterns, understand the trade-offs between database types, and know when to combine multiple storage solutions.
What Comes Next
Even with read replicas and smart database choices, your database is still handling millions of requests. Many of those requests are for the exact same data — the same popular tweet, the same product page, the same user profile.
Next week we cover caching — how to serve the most frequently requested data from memory so your database barely has to work at all.