LESSON 5.2
SQL or NoSQL
Relational, key-value, document, wide-column and graph databases, what each is good at, and a default you can defend.
6 min read
"Should we use SQL or NoSQL?" is one of the most common questions in design interviews, and one of the most commonly fumbled. The good answer is neither a slogan nor a list of products: it's a choice tied to the data's shape and its access patterns.
The main families
Relational (PostgreSQL, MySQL)
- Joins and flexible queries
- Multi-row ACID transactions
- Mature tools
- Scaling writes past one primary takes sharding work
Pick it when: The default: most products, anything with money or relationships.
Key-value (Redis, DynamoDB)
- Very fast lookups by key
- Easy to partition
- Little or no querying beyond the key
Pick it when: Sessions, caches, counters, simple high-volume lookups.
Document (MongoDB, Firestore)
- Flexible nested JSON documents
- One read fetches a whole aggregate
- Cross-document joins and transactions are limited or costly
Pick it when: Data naturally read as a whole, with varied shapes.
Wide-column (Cassandra, ScyllaDB, Bigtable)
- Massive write throughput
- Linear horizontal scaling
- Multi-region replication
- Query-first modeling
- No joins
- Tunable but weaker consistency
Pick it when: Time series, event logs, messaging at very large scale.
Graph (Neo4j)
- Fast multi-hop relationship queries
- Niche
- Harder to scale
Pick it when: Deep traversals, such as recommendations or fraud rings.
Specialized stores complement these rather than replace them: search engines (Elasticsearch) for full-text queries, object storage for blobs, time-series databases for metrics. Many real systems use several, each for what it does best, with one of them as the source of truth.
What actually decides it
Ask these questions about the requirements:
- How is the data related, and how will it be queried? Lots of relationships and unpredictable queries favor relational. A few known access patterns by key favor key-value or wide-column.
- Do writes need to change several records atomically? Money, inventory and bookings need transactions. Relational databases make this easy.
- How much data and how many writes? Tens of thousands of writes a second for years, across regions, push you toward stores designed to partition.
- How consistent must reads be? Some NoSQL stores default to eventual consistency. Make sure that is acceptable for each read.
- Storage
- up to a few TB comfortably
- Simple reads
- tens of thousands per second
- Writes
- a few thousand to ~10,000 per second
- With read replicas
- reads scale further; writes don't
These numbers cover more products than people expect. A relational database plus a cache and read replicas carries most systems a long way.
The myths
- "NoSQL is faster." A key lookup is fast in any database with the right index. NoSQL stores are faster at scaling out, at the cost of query flexibility.
- "SQL doesn't scale." Large companies run sharded MySQL and Postgres at enormous scale. Sharding takes work, but it is well understood.
- "NoSQL means no schema." The schema moves into your code. Data still has a shape, and changing it still needs migrations or careful versioning.
Try it
Metrics Ingestion HLD
Choose storage for a write-heavy time-series workload.