What you will learn
- How to choose between relational and NoSQL stores.
- What indexes do, and what they cost.
- Leader-follower replication, synchronous vs asynchronous, and replication lag.
- Multi-leader and leaderless replication in one paragraph each.
SQL or NoSQL?
| Relational (PostgreSQL, MySQL) | Key-value (Redis, DynamoDB) | Document (MongoDB) | Wide-column (Cassandra, HBase) | |
|---|---|---|---|---|
| Model | Tables, joins, schema | Key → value | JSON documents | Rows with many columns, partitioned |
| Strength | Transactions, flexible queries, constraints | Simple, very fast lookups | Flexible schema, nested data | Massive write throughput, scale-out |
| Weakness | Harder to shard | Few query types | Weaker cross-document guarantees | Query patterns must be designed up front |
| Good for | Orders, payments, users | Sessions, caches, counters | Catalogs, content | Messages, time series, logs |
A sensible default: start with a relational database unless a requirement rules it out. Reach for NoSQL when you need horizontal write scale beyond what sharding SQL comfortably gives, a very simple access pattern at huge volume, or a data shape that fits documents or wide rows naturally. Many systems use both.
Indexes
Without an index, SELECT * FROM links WHERE code = 'g9' scans every row. A B-tree index on code finds it in a few page reads (O(log n)).
Costs: every insert and update must also update each index (slower writes, more storage), and unused indexes are pure overhead. Index the columns your frequent queries filter and sort on; check with EXPLAIN. A composite index on (user_id, created_at) serves “this user’s latest posts” directly.
Write-heavy stores like Cassandra and RocksDB use LSM trees instead of B-trees: writes go to memory and are flushed to sorted files, trading some read cost for very fast writes. You build one in our Go database course.
Replication
Replication keeps copies of the data on several machines, for availability (survive a machine loss), read scaling, and latency (a replica near the user).
Leader-follower
writes -> [leader] --replication log--> [follower 1]
\-------------------> [follower 2]
reads -> leader or any follower
All writes go to the leader; it streams changes to followers. Reads can be spread across followers.
- Synchronous replication: the leader waits for a follower to confirm before acknowledging the write. No data loss if the leader dies, but writes are slower and stall if that follower is down. Common compromise: one synchronous follower, the rest asynchronous.
- Asynchronous replication: the leader acknowledges immediately. Fast, but followers lag, and the last few writes can be lost on failover.
Replication lag problems
With async followers, a user may post a comment and then not see it (the read hit a lagging follower). Fixes:
- Read-your-writes: read the user’s own recent data from the leader (e.g. for a minute after they write).
- Monotonic reads: pin a user to one follower so time never appears to go backwards.
Failover
When the leader dies, a follower is promoted. Hard parts: detecting death reliably (timeouts vs slow networks), choosing the most up-to-date follower, and preventing split brain, where the old leader comes back still thinking it’s in charge. Use a consensus-based coordinator (etcd, ZooKeeper, or the database’s built-in tooling) rather than ad-hoc scripts.
Multi-leader and leaderless
Multi-leader: each region has a leader accepting writes; conflicts between regions must be resolved (last-write-wins, merging, or CRDTs). Useful for multi-region writes and offline clients.
Leaderless (Dynamo-style, Cassandra): clients write to and read from several replicas with quorums, covered in lesson 7.
Key takeaways
- Default to a relational database; choose NoSQL for specific scale or data-shape needs.
- Indexes speed reads at the cost of writes and storage; index your real query patterns.
- Leader-follower replication scales reads and survives failures; async is fast but lags and can lose recent writes.
- Handle lag with read-your-writes and monotonic reads; use consensus-based failover to avoid split brain.
Exercises
- Design the tables and indexes for “users follow users; show a user’s followers and who they follow, newest first”.
- A product page reads stock from a follower that lags 2 seconds. Describe a bug this causes at checkout and how to avoid it.
- Compare the write latency and durability of 1 sync + 2 async followers versus 3 async followers.
Quiz
- What is the main cost of adding an index?
- Why might a user not see their own comment right after posting it?
- What is split brain?
Show answers
- Slower writes (every index must be updated) and extra storage.
- The write went to the leader but the read was served by an asynchronous follower that hadn’t received it yet (replication lag).
- Two nodes both believe they are the leader and accept writes, causing conflicting data.