Purpose: pick the right store, then keep it alive and make it fast as data outgrows one node. Data is the last thing you want to lose and the first thing that becomes a bottleneck.
| SQL (Postgres, MySQL) | NoSQL (MongoDB, Cassandra, DynamoDB) | |
|---|---|---|
| Schema | Fixed, enforced | Flexible |
| Joins/transactions | Native, strong | Limited/weaker |
| Scaling | Vertical first, then read replicas | Horizontal by design |
| Best for | Money, relationships, anything that must not be wrong | High write throughput, flexible documents, hot paths |
The senior answer is never "NoSQL for scale" or "SQL for correctness" as a reflex — it's "which guarantees does this data need?" Money and orders belong in SQL; notification history and tokens are fine in NoSQL.
Indexes: the query-shaped shortcut. Every index speeds up its reads and slows writes. A senior looks at a schema and reads the query patterns first.
Leader-follower: one leader accepts writes, followers serve reads. Gains: - Read scaling (follower = more read capacity) - Availability (follower promoted if leader dies)
Cost: replica lag — a follower can be a moment behind. If a user writes then immediately reads their own data, a laggy read shows the old value. That's why "read-your-writes" matters and why some reads pin to the leader.
Vertical scaling: bigger machine. Simple, but you hit a ceiling and a SPOF. Horizontal (sharding): split rows across many nodes by a shard key — like partitions in a stream, but for storage.
| Method | Splits by | Trade-off |
|---|---|---|
| Range | Key ranges (A–M, N–Z) | Great for range scans; hot ranges if keys cluster |
| Hash | hash(key) → node | Even distribution; loses range scans; key can't change later |
The cardinal rules — exactly like stream partitions: - The shard key is permanent. Change it and you re-shard everything. - The shard key must distribute evenly. Choose by access pattern, not by convention. - Hot keys exist here too (one popular user's data pins one shard).
Your notification store: Mongo sharded by user_id, hot data short-retention, cold data archived — your own calibration insight. The dedup "seen" check is the same data, partitioned to make it local (Lesson 1).