SQL vs NoSQL: Which Database and When
Pick the right database by looking at the shape of your data, the scale and the consistency you need.
11.1 Why is it used?
- If you choose the wrong database, migrating later is very expensive and risky.
- Data like payments and orders needs strict ACID transactions; SQL is best here.
- Billions of events, chat messages and IoT data must scale horizontally; NoSQL is useful here.
- Big companies use more than one DB (polyglot persistence): the right tool for each job.
11.2 How it works
- Relational (SQL): tables, rows, fixed schema, JOIN, foreign keys, ACID. For example MySQL, PostgreSQL.
- Document DB: JSON-like documents, flexible schema, nested data stored together. For example MongoDB.
- Wide-column DB: data is distributed by partition key, very fast writes, query patterns designed up front. For example Cassandra.
- Key-value DB: give a key, get a value, single-digit ms latency, auto scaling. For example DynamoDB, Redis.
- Graph DB: nodes and relationships (friends, recommendations). For example Neo4j.
- Scaling: SQL mostly scales vertically + read replicas + sharding; NoSQL scales horizontally by design.
- Schema: SQL is schema-on-write (design the table first), NoSQL is more flexible (schema-on-read).
- Query-first design: in Cassandra/DynamoDB write down the access patterns first, then create the table.
- 1.Relational SQL (MySQL, PostgreSQL): users + orders, JOIN, strict ACID: payments, orders
- 2.Document (MongoDB): flexible JSON schema: product catalog
- 3.Wide-column (Cassandra): partition key + columns, billions of events, fast writes
- 4.Key-value (DynamoDB, Redis): cart, session, OTP, single-digit ms
- 5.Graph (Neo4j): nodes + relationships: friends, recommendations
SQL: fixed schema, JOIN, ACID, vertical scale + read replicas + sharding. NoSQL: flexible schema, horizontal by design, access pattern first and table later
11.3 Tools
| Tool | Type | How it is used |
|---|---|---|
| PostgreSQL | Relational DB | Strong ACID, rich SQL, JSONB, extensions (PostGIS, pgvector). Available as managed on AWS RDS/Aurora, Azure, Cloud SQL. |
| MySQL | Relational DB | A very popular OLTP DB, InnoDB engine, read replicas are easy. Used by companies like Flipkart and Uber. |
| MongoDB | Document DB | JSON documents, flexible schema, built-in sharding and replica sets. Atlas is the managed version. |
| Apache Cassandra | Wide-column DB | Masterless, multi-DC, built for write-heavy workloads. Netflix and Apple use it at large scale. |
| Amazon DynamoDB | Key-value / document (managed) | Serverless, auto-scaling, single-digit ms latency. Azure Cosmos DB and GCP Firestore/Bigtable are similar. |
| Neo4j | Graph DB | Queries relationships for social graphs, fraud detection and recommendations. |
11.4 Comparison: MySQL/PostgreSQL vs MongoDB vs Cassandra/DynamoDB
| Point | MySQL / PostgreSQL | MongoDB | Cassandra / DynamoDB |
|---|---|---|---|
| Data model | Tables, rows, fixed schema | JSON documents, flexible schema | Partition key + sort/clustering key, wide rows |
| Transactions | Full ACID, multi-table | ACID multi-document (4.0+), but costly | Limited (DynamoDB transactions, Cassandra LWT) |
| Joins / queries | Powerful JOIN, ad-hoc queries | Aggregation pipeline, $lookup is limited | No joins, only key-based queries |
| Scaling | Vertical + read replicas, manual sharding (Vitess/Citus) | Built-in sharding | Built-in horizontal, linear scale |
| Write throughput | Good, limited by the single primary | Good | Very high, built for write-heavy workloads |
| Consistency | Strong | Strong on primary, tunable | Tunable / eventual by default |
| Best for | Orders, payments, banking, inventory | Catalog, CMS, user profiles | Chat, feeds, IoT, time-series, carts at huge scale |
11.5 When to choose which
- SQL (PostgreSQL/MySQL): when data is relational and you need JOIN and transactions: payments, orders, inventory.
- MongoDB: when every item has a different structure (phone vs t-shirt attributes in a product catalog) and the schema changes often.
- Cassandra: when you need very high writes, multi-region and always-on, and the queries are known in advance.
- DynamoDB: when you are on AWS and want serverless, key-based access and unpredictable traffic (sale spikes).
- If in doubt, start with PostgreSQL: it is enough for most apps.
11.6 Real-world example
E-commerce polyglot persistence (an Amazon/Flipkart-like scenario)
- User login and profile live in a relational DB (MySQL/PostgreSQL).
- The product catalog is in MongoDB: every category has different attributes, flexible schema.
- The cart is in DynamoDB/Cassandra: crores of carts, key = user_id, heavy writes during a sale. Amazon's Dynamo paper was written for the shopping cart itself.
- Orders and payments are in PostgreSQL with ACID transactions: money must never go wrong.
- Elasticsearch for search, and a Redis cache for hot data.
- Every service owns its own DB, and data is synced through events (Kafka).
11.7 Interview tips
- "NoSQL scales, so use NoSQL" is wrong thinking: choosing a DB without understanding the access pattern is a mistake.
- Thinking in SQL terms in Cassandra/DynamoDB is a mistake: ad-hoc queries and joins do not work here.
- Embedding everything in MongoDB is a mistake: a document has a 16 MB limit and huge documents are slow.
- Interview tip: always say "it depends on the access pattern, the consistency need and the scale" and give an example.