SQL vs NoSQL: choosing a database
SQL (relational) databases store data in tables with a fixed schema and support joins and transactions. NoSQL is a family of other models: key-value, document, wide-column and graph.
Why it matters
Different data and different access patterns need different storage. A wrong choice hurts later: slow queries, joins you can't do, or a database that can't keep up with the writes.
The idea
Start from your data and how it's accessed. Relational databases (PostgreSQL, MySQL) suit structured data with relationships and strong consistency. Key-value stores (Redis, DynamoDB) give fast lookups by key. Document stores (MongoDB) hold flexible JSON-like records. Wide-column stores (Cassandra) handle huge write volumes. Graph databases (Neo4j) suit highly connected data like social networks.
SQL is a well-organised filing cabinet with labelled folders and cross-references. NoSQL is a set of specialised containers: a key rack, a box of folders, a giant ledger, a map of connections.
A filing cabinet can't grow past the office. Relational databases actually scale a long way with replicas and sharding, and many NoSQL stores now support transactions. The line is blurrier than the slogan.
How it works
- List the main queries: 'get a user's last 20 orders', 'find all messages in a chat by time', 'count the likes on a post'.
- If you need joins, transactions and flexible queries over structured data, start with SQL.
- If one simple lookup by key dominates, and must be extremely fast or enormous, consider a key-value store.
- If each record is a self-contained document with varying fields, consider a document store.
- If writes are massive and reads always follow one key (like chat messages by conversation), consider a wide-column store.
- If the questions are about relationships ('friends of friends who like X'), consider a graph database.
Example
Take an online shop. Orders and payments go in PostgreSQL, because transactions matter. Shopping carts and sessions go in a key-value store: fast and simple. Product search goes in Elasticsearch. 'People also bought' goes in a precomputed table or a graph.
Key points
- Choose by access pattern: what are the main reads and writes, and by which key?
- SQL: joins, ACID transactions, a strict schema; scales with replicas and, with more effort, sharding.
- Key-value: very fast by key, no complex queries. Document: flexible schema, nested data.
- Wide-column (Cassandra): massive write volume; queries designed around the partition key.
- Graph: relationships first (friends of friends).
- Default in most interviews: start with SQL unless there's a clear reason not to.
- Hands-on: the SQL course teaches the language itself, from SELECT to window functions, with every query runnable.
Trade-offs
- SQL: flexible queries and strong guarantees, but harder to scale writes horizontally.
- NoSQL: easy scale-out for specific patterns, but limited queries, and you must design around access patterns up front.
Common mistakes
- Choosing NoSQL because it 'scales', without checking the queries you need.
- Designing a NoSQL schema like SQL tables, then needing joins.
- Forgetting that different parts of one system can use different databases.
Not true: A system that has to scale: choose NoSQL, since SQL databases stop at one machine.
In fact: A SQL database scales a long way with replicas and sharding. Choose by the reads and writes you need: NoSQL gives up joins, and often transactions too.
Not true: One system: one kind of database for the orders, the sessions and the search alike.
In fact: Different parts have different needs. Orders may sit in SQL, sessions in a key-value store and search in a search engine, all in one system.
In the interview
I'd choose based on the access patterns. For orders and payments with relationships and transactions, PostgreSQL. For very high write volume with simple key-based reads, like chat messages, a wide-column store like Cassandra. For sessions and hot data, Redis. Using different stores for different jobs is normal.
Part of Unit 3: Data: databases, storage and scaling data in the free System Design course.