System design · Day 5
Databases: choose from access patterns, not labels
Compare relational and non-relational models, indexes, transactions, and the query patterns that should drive storage decisions.
The useful database question is not SQL or NoSQL. It is: what data do we own, how will we read and change it, and which guarantees must hold while we do so?
Model the queries first
List the main reads and writes from the API designed on Day 4. A social feed may need posts by author, a timeline ordered by time, and a lookup by post ID. These access patterns reveal keys, relationships, indexes, and likely hot paths.
Relational databases are excellent defaults when data has relationships, transactions matter, and query needs may evolve. Document or key-value stores fit predictable aggregate lookups, flexible records, or very large horizontal workloads. Both families can be used badly.
Indexes trade write work for read speed
An index is an additional ordered structure that helps locate rows without scanning the whole table. Index fields used for filtering, joining, and ordering—but inspect real query plans, because every index consumes storage and must be updated on writes.
Composite index order matters. An index on user_id then created_at efficiently serves recent posts for one user. Reversing those fields answers a different question. The query should explain the index, not the other way around.
Name the required consistency
Transactions keep related changes atomic: transferring money should not debit without crediting. Other data, such as view counts, can tolerate delayed convergence. Stronger coordination is valuable where correctness requires it and costly where it does not.
Start with one primary database and measure. Day 6 first reduces repeated read pressure with caching; Day 8 later explains replicas and partitions for the moment a single database is truly no longer enough.
“Select storage by the questions your product asks most often.”