SQL or NoSQL
| Type | Examples | Strengths | Typical uses |
|---|---|---|---|
| Relational (SQL) | PostgreSQL, MySQL | Joins, transactions, constraints, flexible queries | Users, orders, payments: most business data |
| Key-value | Redis, DynamoDB | Very fast lookups by key, easy to partition | Sessions, caches, carts, feature flags |
| Document | MongoDB, Couchbase | Flexible nested records | Catalogues, content, user profiles |
| Wide-column | Cassandra, HBase | Huge write throughput, scales across many nodes | Time series, messages, activity logs |
| Graph | Neo4j | Traversing relationships | Social graphs, recommendations, fraud rings |
| Search | Elasticsearch, OpenSearch | Full-text search and aggregations | Search boxes, log analysis |
Start with a relational database unless you have a reason not to: it's well understood, flexible, and scales further than most systems need. Choose NoSQL for a specific access pattern: enormous write volume, simple key lookups at massive scale, or schemaless data.
Indexes
Without an index, a query scans every row. A B+ tree index keeps keys sorted in a shallow, wide tree, so a lookup or a range scan touches only a few disk pages. Indexes speed up reads but slow down writes (every insert updates every index) and take space.
- Index the columns you filter, join and sort on.
- A composite index
(country, city)helps queries oncountryand oncountry + city, but not oncityalone (leftmost prefix rule). - Write-heavy stores often use LSM trees (Cassandra, RocksDB): writes go to memory and are flushed to sorted files, trading some read cost for very fast writes.
Transactions and ACID
- Atomicity: all of a transaction happens or none of it.
- Consistency: constraints hold before and after.
- Isolation: concurrent transactions don't see each other's partial work (to a chosen level: read committed, repeatable read, serializable).
- Durability: once committed, data survives a crash (thanks to the write-ahead log).
Many NoSQL stores offer weaker guarantees (often called BASE: basically available, soft state, eventually consistent) in exchange for availability and scale.
Replication
Replication keeps copies of the data on several machines, for availability and to spread reads.
| Setup | How | Watch out for |
|---|---|---|
| Primary–replica (leader–follower) | One primary takes writes and streams changes to read replicas | Replication lag: a replica may return stale data |
| Multi-primary | Several nodes accept writes | Write conflicts must be resolved |
| Leaderless (quorum) | Write to W of N nodes, read from R; R + W > N gives overlap | Tuning W and R; repair of stale copies |
Synchronous replication waits for replicas before acknowledging (no data loss on failover, slower writes). Asynchronous replication acknowledges immediately (fast, but recent writes can be lost if the primary dies).
Read-your-own-writes: after a user updates their profile, read it back from the primary (or wait for the replica to catch up), or they'll see their change disappear.
See it animated
Interview questions
Try answering out loud first, then open the model answer.
Q1How do you choose between SQL and NoSQL?intermediate
Look at the data and access patterns. Relational data with transactions and ad-hoc queries suits SQL. Choose NoSQL for a specific need: very high write throughput, simple key-based access at huge scale, flexible schemas, or graph traversal. Many systems use both.
Q2Why do indexes slow down writes?beginner
Every insert, update or delete must also update each index on the table, which means extra disk writes and tree rebalancing. That's why you index for your real query patterns rather than every column.
Q3What is replication lag and how do you handle it?intermediate
With asynchronous replication, replicas apply changes a little after the primary, so a read from a replica can be stale. Handle it by reading your own recent writes from the primary, routing a user to the same replica (monotonic reads), or waiting for a replica to reach a known position.
Q4Explain quorum reads and writes.advanced
With N replicas, a write succeeds after W acknowledge and a read queries R replicas. If R + W > N, every read set overlaps every write set, so at least one replica returns the latest value. N = 3, W = 2, R = 2 is a common balance.
Q5What do the isolation levels protect against?advanced
Read committed prevents dirty reads (seeing uncommitted data). Repeatable read also prevents non-repeatable reads (a row changing between two reads in one transaction). Serializable prevents phantoms and every other anomaly by making transactions behave as if run one at a time, at a cost in throughput.
Quick check
0 of 3 answered