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

  1. 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'.
  2. If you need joins, transactions and flexible queries over structured data, start with SQL.
  3. If one simple lookup by key dominates, and must be extremely fast or enormous, consider a key-value store.
  4. If each record is a self-contained document with varying fields, consider a document store.
  5. If writes are massive and reads always follow one key (like chat messages by conversation), consider a wide-column store.
  6. 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

Trade-offs

Common mistakes

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.