Databases
The main families of databases, which questions each one makes cheap, and how to choose one for each part of a design by its access pattern instead of by fashion.
Overview
A database keeps data safe on disk and answers questions about it. Every database makes some questions cheap and others expensive, and that is decided mostly by its data model: tables of rows, whole documents, values found by a key, rows grouped into partitions, nodes and edges, measurements over time, or an index of words.
In a design interview, the database is usually the hardest part to change later. A stateless server can be replaced in an afternoon, but moving billions of rows to a new system while it keeps serving traffic is a project of its own. So interviewers listen closely when you choose one. They want to hear the access patterns first (what the system reads and writes, by which key, how often, and how fresh the answer must be), and the product second.
This chapter covers the seven families you are expected to know, what each is good and bad at, the split between everyday transactions and analytics, and a decision table you can use in the interview. Transactions, replication, sharding and indexes each have their own chapter; here they appear only as far as they change the choice.
What you will be able to do
- Describe a workload as a list of access patterns before naming any product.
- Explain what relational, document, key-value, wide-column, graph, time-series and search databases each make cheap, and what each makes hard.
- Tell OLTP from OLAP, and say why analytics usually runs on a separate, column-oriented system.
- Model the same data as tables, as documents and as partitions sorted by a key.
- Pick a database for each part of a design with a decision table, and say what each choice costs.
Key concepts
- Access patterns
- Relational
- Document
- Key-value
- Wide-column
- Graph
- Time-series
- Search engines
- OLTP and OLAP
- Polyglot persistence
Interview questions
- How do you choose a database for a new system?
- When would you not use a relational database?
- Design the storage for the message history of a chat app.
- In a document database, when do you embed data and when do you reference it?
- Redis and DynamoDB are both key-value stores. How do they differ?
- What is the difference between OLTP and OLAP, and why not run analytics on the production database?
- How would you add full-text search to a product catalog stored in PostgreSQL?
- When is a graph database worth it?
- How would you store metrics from 10,000 servers?
- How do you scale a relational database as it grows?
Sign up free to read the full topic
More System Design topics
- Data Lakes and Lakehouse Table Formats
- Data Partitioning
- Data Pipeline Orchestration and Backfills
- Data Quality, Contracts and Lineage
- Data Warehousing and Dimensional Modeling
- Database Indexes
- Dealing with Contention
- Delivery Semantics
- Deployment System
- Digital Wallet
- Distributed Cache
- Distributed File Systems
Start free on Cariara · Coding problems · System design studio