SQL vs NoSQL Trade-offs

Overview

SQL databases (relational databases such as PostgreSQL and MySQL) store data in tables with a fixed schema, link tables with keys, and answer any question you can write as a query, joins included, inside ACID transactions. NoSQL is a loose name for everything else: key-value stores, document stores, wide-column stores and graph databases. Most of them give up joins and some flexibility of querying, and in return make one kind of access very fast and easy to spread across many machines.

The choice is less of a battle than it used to be. PostgreSQL has a binary JSON type with indexes, MongoDB and DynamoDB support transactions across several items, and distributed SQL databases such as Spanner and CockroachDB shard tables automatically while keeping SQL and serializable transactions. So the real question is not which camp to join, but which data model and which guarantees fit each part of your system.

Interviewers use this topic to see whether you reason from requirements. A strong answer names the entities and how they are read and written, the consistency each operation needs, and the expected size and growth, and only then names a store and what it costs.

What you will be able to do

• Describe the relational model and the four NoSQL families, and what each is good at.
• Model the same data both ways and explain what each shape makes easy and hard.
• Compare transactions, schema changes and scaling across real products, with their documented limits.
• Choose a store per workload with a short, defensible argument, and know when polyglot persistence is worth its cost.

Key concepts

• Relational model and joins
• Key-value, document, wide-column, graph
• Query-first data modeling
• Denormalization
• Transactions across items
• Schema on write vs schema on read
• Partition keys
• Distributed SQL
• Polyglot persistence

Interview questions

• How do you decide between SQL and NoSQL for a new system?
• Model an e-commerce order in a relational database and in a document store. What does each make easy or hard?
• Do NoSQL databases support transactions?
• How do SQL and NoSQL databases scale differently?
• Can PostgreSQL replace a document database?
• What is polyglot persistence and what does it cost?
• Your team wants to move from PostgreSQL to a NoSQL database 'to scale'. How do you respond?

More System Design topics

• Social Commerce Platform
• Soft vs Hard TTL
• Speech-to-Text System
• Split Brain
• Spotify
• SQL Isolation Levels Deep Dive
• Stateful vs Stateless Services
• Sticky Sessions
• Stock Exchange
• Stock Trading App (Robinhood)
• Strava Fitness Tracking
• Strong vs Eventual Consistency