What is the difference between SQL and NoSQL databases, and when do you choose each?
Take a moment.
Say your answer out loud or write it down first — retrieval is what makes it stick. Reveal when you're ready.
Short answer
SQL databases (PostgreSQL, MySQL) use structured schemas, relational tables, and ACID transactions — ideal for complex queries and data integrity. NoSQL databases (MongoDB, Cassandra, Redis, DynamoDB) trade strict schemas for flexibility, horizontal scale, and specific access patterns: document, key-value, wide-column, or graph. Choose SQL when data relationships and transactions matter; choose NoSQL when you need massive scale, flexible schemas, or a specific access pattern that a relational model fights against.
Detailed explanation
SQL databases store data in rigid tables with predefined schemas and relationships enforced by foreign keys. They excel at complex JOINs, ad-hoc queries, and ACID transactions (Atomicity, Consistency, Isolation, Durability) — critical for banking, inventory, any domain where an incomplete write causes corruption. Scaling is typically vertical or via read replicas; sharding is possible but manual and painful. NoSQL is an umbrella of four families: document (MongoDB, Couchbase) stores semi-structured JSON, great for catalogs and content with varying fields; key-value (Redis, DynamoDB) is a giant hash map with single-digit-millisecond lookups by primary key; wide-column (Cassandra, HBase) organizes data by column families and partitions, built for huge write throughput and time-series; graph (Neo4j) models relationships natively for social networks and recommendations. NoSQL trades JOINs and often strong consistency for horizontal scale and schema flexibility. The decision: reach for SQL by default when you need transactions and complex querying; reach for NoSQL when one of these holds — your data is naturally a single aggregate with no joins (document), you only access by primary key at huge scale (key-value), you write massive volumes across partitions (wide-column), or your domain is relationship-heavy (graph). Many systems use both (polyglot persistence): PostgreSQL for orders, Redis for sessions, Elasticsearch for search.
Example
SQL choice (e-commerce orders): orders, payments, inventory must stay transactional — a payment and its order status update atomically or not at all. PostgreSQL with foreign keys.
NoSQL choice (product catalog): each product has different attributes (a laptop has RAM; a shirt has size), read-heavy, no transactions. MongoDB document store.Interview tip
Give the four NoSQL families with one example each, then give a concrete decision rule. Mentioning polyglot persistence (using both SQL and NoSQL in one system) signals real architectural experience.
Common mistakes
- Saying NoSQL always scales better without qualifying the trade-offs.
- Not knowing the four NoSQL categories.
- Forgetting that NoSQL often sacrifices joins and strong consistency.