SQL vs NoSQL — How to Choose the Right Database
The Interview Question
When would you choose SQL over NoSQL? What are the tradeoffs?
Expert Answer
SQL databases (PostgreSQL, MySQL) store data in tables with fixed schemas, support complex JOINs, and provide ACID transactions (Atomicity, Consistency, Isolation, Durability). They excel when your data has clear relationships, you need transactional integrity (financial systems, user management), and your query patterns involve complex joins and aggregations. NoSQL is an umbrella term covering several data models. Document stores (MongoDB) store flexible JSON-like documents — good when your data structure varies between records or evolves rapidly. Key-value stores (Redis, DynamoDB) offer the fastest reads/writes for simple lookups. Wide-column stores (Cassandra) handle massive write throughput across distributed nodes. Graph databases (Neo4j) excel at relationship-heavy queries. The real answer in interviews: most applications should default to PostgreSQL. It handles 95% of use cases, has excellent JSON support for semi-structured data, and scales vertically to handle millions of users. You reach for NoSQL when you have a specific need that relational databases handle poorly — massive write throughput, highly variable schemas, or graph-shaped queries.
Key Points to Hit in Your Answer
- SQL: fixed schema, ACID transactions, complex JOINs, vertical scaling
- NoSQL document (MongoDB): flexible schema, horizontal scaling, no JOINs
- NoSQL key-value (Redis): fastest reads, simple data model, caching layer
- NoSQL wide-column (Cassandra): massive write throughput, distributed
- NoSQL graph (Neo4j): relationship queries, social networks
- PostgreSQL handles most use cases — default to SQL unless you have a specific reason not to
- ACID vs BASE: consistency vs availability tradeoff (CAP theorem)
- Many 'NoSQL' databases now support transactions and joins — the lines are blurring
Code Example
// When SQL shines: transactional integrity
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 'sender';
UPDATE accounts SET balance = balance + 100 WHERE id = 'receiver';
INSERT INTO transactions (from_id, to_id, amount) VALUES ('sender', 'receiver', 100);
COMMIT;
-- All three succeed or all three fail — ACID guarantee
// When document store shines: flexible schema
// User profiles with varying fields
{
"_id": "user_123",
"name": "Alice",
"preferences": {
"theme": "dark",
"notifications": { "email": true, "push": false },
"custom_fields": { ... } // varies per user
},
"addresses": [
{ "type": "home", "city": "Austin" },
{ "type": "work", "city": "SF" }
]
}
// When key-value shines: session storage, caching
await redis.set('session:abc123', JSON.stringify(sessionData), 'EX', 3600);
const session = await redis.get('session:abc123'); // sub-millisecondWhat Interviewers Are Really Looking For
Don't trash either approach. The best answer discusses specific use cases: financial data needs ACID → SQL. User-generated content with variable structure → document store. Session management → key-value. Social graph queries → graph DB. Mentioning that PostgreSQL's JSONB support blurs the SQL/NoSQL line shows you're pragmatic, not dogmatic.
Practice This Question with AI Grading
Reading about interview questions is a start — but practicing with real-time AI feedback is how you actually get better. Goliath Prep grades your answers instantly and tells you exactly what you're missing.
Start Practicing Free →