Choosing the Right Database for the Job
You'll learn to
- -Apply a decision framework for SQL vs NoSQL vs specialized stores
There is no "best" database, only the best database for a specific access pattern. The question a system designer actually answers isn't "SQL or NoSQL" in the abstract, it's a handful of concrete questions about how the data will actually be read and written.
Ask These Questions First
- -Access pattern: point lookups by key, range scans, complex multi-table joins, or full-text search?
- -Consistency needs: does a stale read cause real harm (a bank balance), or is "eventually correct" fine (a like count)?
- -Write volume: hundreds of writes per second, or hundreds of thousands?
- -Schema stability: does every record look the same, or does the shape vary and evolve constantly?
A Simple Decision Framework
- -Need transactions spanning multiple related entities (orders + inventory + payments)? Reach for SQL.
- -Need millions of writes per second with a simple, predictable access pattern? Reach for a wide-column store.
- -Records vary in shape and you want to iterate the schema fast? Reach for a document store.
- -Need graph traversal like "friend of a friend"? Reach for a graph database.
- -Need sub-millisecond lookups in front of a slower database? That's not a database decision at all; that's the Cache module, next.
Most real systems are polyglot: Postgres for core business data, Redis for caching, Elasticsearch for search, all in the same architecture. Picking one database for an entire system is itself usually the wrong instinct.
"What database would you use for this system?" as the very first question in a design interview, how do you answer?
"I'd use PostgreSQL, it's reliable and well-supported."
"I'd rather answer that after we've nailed down the access pattern and consistency needs, not before; the same system might reasonably use Postgres for orders, Redis for session data, and Elasticsearch for search, all at once. Committing to one database before knowing how the data will actually be read and written is answering a question I haven't earned the right to answer yet."
What does the "I" in ACID guarantee?
Level 6: Key-Value Store puts this decision into practice.