SQL vs NoSQL: When to Use Each
Fast recall
Quick revision
Core answer
Your ability to reason about data modeling trade-offs and choose the right persistence tool for a given problem rather than defaulting to one technology.
Claiming NoSQL is always faster or more scalable, or that SQL cannot scale -- both are oversimplifications. PostgreSQL with read replicas handles enormous scale; MongoDB has its own consistency trade-offs.
Discuss polyglot persistence -- using both in the same system for different data (e.g., PostgreSQL for orders, Redis for sessions, Elasticsearch for search).
Interview-ready guide
Interview answer
The choice between SQL and NoSQL comes down to your data model, consistency requirements, and scale patterns. SQL databases like PostgreSQL shine when your data has clear relationships, you need multi-row transactions with ACID guarantees, and your access patterns involve joining across entities -- think orders, customers, and payments in an e-commerce system.
NoSQL databases are the right tool when your data model does not fit neatly into tables.
- A product catalog where each product has wildly different attributes maps naturally to MongoDB documents.
- A user activity feed that needs millions of writes per second fits Cassandra's wide-column model.
- A session store benefits from Redis's in-memory key-value lookup.
The more nuanced answer is that modern production systems use both -- a pattern called polyglot persistence. You might store canonical business data in PostgreSQL, cache hot reads in Redis, power full-text search with Elasticsearch, and store analytics events in a column-store like BigQuery. Each database does what it does best.
A common interview mistake is claiming NoSQL is always faster.
- Cassandra can do millions of writes per second but makes multi-row transactions very difficult.
- PostgreSQL with connection pooling and read replicas can handle substantial scale for most applications.
- The right question is not which is faster in the abstract, but which fits your specific read and write patterns.
SQL vs NoSQL: When to Use Each
Direct Answer
Use SQL (relational) databases when data has clear relationships, you need ACID transactions, and the schema is stable. Use NoSQL when you need horizontal scalability, flexible schemas, very high write throughput, or data models that map poorly to tables such as documents, graphs, or wide-column data. The pragmatic default for greenfield systems is PostgreSQL; add specialized stores only when measured need exceeds what it provides.
Code Example
The choice is not ideology — it is data model, access patterns, and consistency needs. This side-by-side shows the same "save an order" operation in each style.
-- SQL (PostgreSQL): normalized schema, relational integrity, SQL ad-hoc queries
CREATE TABLE users (id BIGSERIAL PRIMARY KEY, email TEXT UNIQUE NOT NULL);
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(id),
total NUMERIC(10,2) NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW()
);
BEGIN;
INSERT INTO orders (user_id, total) VALUES (42, 99.90);
UPDATE inventory SET qty = qty - 1 WHERE sku = 'ABC';
COMMIT; -- ACID: both rows or neither
-- Ad-hoc reporting is trivial
SELECT u.email, SUM(o.total)
FROM users u JOIN orders o ON o.user_id = u.id
WHERE o.created_at > NOW() - INTERVAL '7 days'
GROUP BY u.email ORDER BY 2 DESC LIMIT 10;
// NoSQL (MongoDB): denormalized document, flexible schema, horizontal scale
db.orders.insertOne({
_id: ObjectId(),
user: { id: 42, email: "alice@example.com" }, // embedded copy — read-optimized
items: [
{ sku: "ABC", qty: 1, price: 99.90 },
{ sku: "XYZ", qty: 2, price: 15.00 }
],
total: 129.90,
createdAt: new Date()
});
// Single-document reads/writes are atomic; multi-document txns exist but cost more
db.orders.find({ "user.id": 42 })
.sort({ createdAt: -1 }).limit(10);
| Aspect | SQL (Postgres/MySQL) | NoSQL (Mongo/Dynamo) |
|---|---|---|
| Schema | rigid, migrations | flexible, per-doc |
| Transactions | full ACID across tables | single doc ACID; multi-doc ok but slower |
| Joins | cheap (the engine's job) | usually denormalize instead |
| Scale | vertical + read replicas; sharding is work | built-in sharding, horizontal by default |
| Ad-hoc queries | SQL is unbeatable | aggregation pipelines, less flexible |
| Best fit | financial, reporting, complex relations | catalogs, events, user-generated content, high write throughput |
- Reach for SQL when data has stable structure, multi-row transactions matter, and the business will ask unpredictable reporting questions.
- Reach for NoSQL when the access pattern is known and narrow, writes dominate reads, documents vary in shape, or you need geographic/horizontal scale without operational pain.
- Polyglot persistence is the honest answer for most platforms: Postgres for orders, Mongo for catalog, Redis for session, Elasticsearch for search — each doing what it is best at.
Common Mistakes & Pitfalls
Claiming NoSQL is always faster or more scalable, or that SQL cannot scale -- both are oversimplifications. PostgreSQL with read replicas handles enormous scale; MongoDB has its own consistency trade-offs.
Check your understanding
Answer these without looking back at the article.
- How would you migrate from MongoDB to PostgreSQL as your data model matures?
- What is polyglot persistence and when does it make sense?
- How does Cassandra achieve high write throughput?
Related interview prep categories
2Looking for a structured roadmap?
Every module on this page is also part of the full Java backend interview track.