SQL vs NoSQL

Additional TopicsMidnosqlsql

The Question

What are the differences between SQL and NoSQL databases?

What a Strong Answer Covers

• At least 3 NoSQL types named. ACID vs eventual consistency. Use case for each.

Senior-Level Answer

SQL (relational) databases organize data into tables with a predefined schema, use SQL for queries, and enforce ACID properties (Atomicity, Consistency, Isolation, Durability). They are optimized for complex joins, multi-table transactions, and enforcing referential integrity. Examples: PostgreSQL, MySQL, SQL Server, Oracle.

NoSQL databases is an umbrella term for non-relational stores that trade strict consistency and rigid schemas for flexibility, horizontal scalability, or specialized data access patterns. There are four main categories. Document stores (MongoDB, Firestore) store JSON-like documents and suit hierarchical, varied data. Key-value stores (Redis, DynamoDB) offer O(1) lookups by key; ideal for caching and sessions. Wide-column stores (Cassandra, HBase) organize data into sparse column families, optimized for time-series and write-heavy workloads at massive scale. Graph databases (Neo4j) model data as nodes and edges, making multi-hop relationship traversal efficient.

The core trade-offs map to the CAP theorem: under a network partition, a system can maintain either Consistency or Availability, but not both. Most SQL databases prioritize consistency; many NoSQL stores (like Cassandra and DynamoDB) prioritize availability and offer eventual consistency. This is not a defect -- it is an explicit engineering choice for systems where uptime and scale matter more than strict consistency.

Scaling patterns differ fundamentally. SQL databases scale vertically or with read replicas; sharding relational databases is complex and often breaks joins. NoSQL stores like Cassandra are designed for horizontal sharding from the start -- you add nodes to increase capacity linearly.

Choose SQL when: you need ACID transactions across multiple entities, your data is structured and relational, and you need ad-hoc query flexibility. Choose NoSQL when: you need horizontal scale beyond a single machine, your access patterns are well-defined and simple, your data is inherently document-shaped or graph-shaped, or you need specific performance characteristics such as sub-millisecond cache reads.

Key Differences

What Separates a 2/3 from a 3/3

Common Mistakes

• Saying NoSQL is not SQL without knowing the four categories -- document, key-value, wide-column, graph are very different systems
• Claiming NoSQL has no transactions -- modern MongoDB has multi-document ACID transactions; DynamoDB has transactions
• Treating eventual consistency as a flaw rather than an engineering trade-off for availability and partition tolerance
• Picking NoSQL because it scales better without explaining what access patterns and scale justify the loss of join and transaction support

Follow-Up Questions

• What does eventual consistency mean in practice? — Replicas may serve stale reads briefly after a write; all replicas converge to the same value once the write propagates -- eventually means no guarantee on when.
• When would you use a graph database instead of a document store? — When your queries traverse relationships such as friends-of-friends -- graph databases optimize for multi-hop traversal that is expensive as recursive joins in SQL.
• How does DynamoDB handle transactions? — DynamoDB supports ACID transactions across multiple items using TransactWriteItems and TransactGetItems -- implemented with two-phase commit internally.

Related Questions

• CAP theorem
• Big O basics
• N+1 problem
• AI tools in workflow
• Redis Internals