SQL vs NoSQL: How to Choose the Right Database

SQL vs NoSQL: How to Choose the Right Database

"It depends" is not an answer. Here's how to reason about database selection in an interview — and in production.

When SQL wins

1. Complex relationships: User → Orders → OrderItems → Products — relational data with many join-heavy queries
2. Strict consistency required: Financial transactions, inventory management
3. Ad-hoc queries: You don't know the access patterns in advance (reporting, analytics)
4. Mature tooling: PostgreSQL has decades of production hardening

When NoSQL wins

NoSQL Type	Best For	Examples
Document	Hierarchical data, flexible schema	MongoDB, Firestore
Key-Value	Session storage, caching, feature flags	Redis, DynamoDB
Wide-Column	Time-series, write-heavy, scale	Cassandra, HBase
Graph	Social networks, recommendations	Neo4j, Amazon Neptune

The framework

Ask three questions:

1. What are my access patterns? (key-value lookup vs complex joins)
2. Do I need ACID transactions across multiple entities?
3. What are my scale requirements? (NoSQL is easier to shard horizontally)

Common interview answer

"For the URL shortener, I'd choose a key-value store like DynamoDB or Cassandra. The access pattern is pure key-value lookup by short code — there are no joins, no complex queries. DynamoDB gives us single-digit millisecond latency and scales horizontally without resharding."

The real answer

For most applications at startup/mid-scale: PostgreSQL. It's relational, ACID-compliant, supports JSONB for flexible columns, and handles hundreds of thousands of QPS with good indexing. Only switch when you've hit a genuine bottleneck.