Skip to content

SQL vs NoSQL

1. Decision

Choose SQL for structured data requiring ACID transactions, complex relational joins, and strict schema guarantees; choose NoSQL for high-velocity, massive-scale, unstructured/semi-structured data needing horizontal partitionability or flexible schemaless writes.

2. 30-Second Answer

In System Design interviews, default to relational SQL (e.g., PostgreSQL) when core domain entities have strong relationships and require financial-grade ACID transactions. SQL provides linear consistency, expressive multi-table joins, and declarative queries, but scales horizontally primarily via read-replicas or complex sharding. Select NoSQL (Document, Key-Value, Columnar, Graph) when traffic exceeds single-node write limits (10k+ write QPS) or requires single-digit millisecond latency at petabyte scale. NoSQL trades joins and multi-row transactions for partition tolerance, linear scale-out, and flexible schemas (BASE model).

3. Decision Matrix

Option Best For Avoid When Latency Scalability Consistency Operational Complexity
SQL (RDBMS) Financial ledgers, complex domains, strict schemas, reporting Ultra-high write throughput (>50k QPS), unstructured data Low (<10ms read/write single-node) Vertical (scale-up); Horizontal via Read Replicas / Manual Sharding Strong / Serializable / Snapshot Low to Medium (High when sharded)
NoSQL (Document) Catalogs, user profiles, rapidly evolving schemas Multi-table joins, cross-document transactions Very Low (<5ms point reads) Horizontal via auto-sharding (hash/range) Eventual to Strong (configurable per query) Medium
NoSQL (Wide-Column) Time-series, analytics, IoT telemetry, high write rates Arbitrary ad-hoc queries without partition key Ultra-Low write (<2ms), low read Extreme Horizontal (linear scale-out across 100s of nodes) Eventual / Tunable Quorum High
NoSQL (Key-Value) Session stores, caching, feature flags Complex search filtering, multi-field queries Sub-millisecond (in-memory) Linear Horizontal Strong (single key) or Eventual Low

4. Selection Rules

Requirement Best Choice Rationale / Example
Need ACID & Multi-Row Transactions SQL (PostgreSQL, MySQL) Banking, payment processing, inventory reservation
Flexible / Unstructured Schema Document NoSQL (MongoDB, DynamoDB) User profile attributes, CMS, product catalogs
Massive Write Throughput (>50k QPS) Wide-Column NoSQL (Cassandra, ScyllaDB) Clickstream tracking, sensor data, chat history
Full-Text Search & Fuzzy Queries Search Engine (Elasticsearch / OpenSearch) E-commerce product search, log aggregation
Time-Series / Metric Metrics Columnar / TSDB (Timescale, Influx, ClickHouse) Prometheus metrics, financial ticker data
Blob / Unstructured Media Storage Object Storage (AWS S3, GCS) Video uploads, PDF invoices, images
Complex Graph / Social Graph Graph DB (Neo4j, AWS Neptune) Fraud detection networks, social connections

5. Tradeoffs

  1. Schema Rigidity vs Write Flexibility
  2. Gain: SQL enforces data integrity at the storage engine level; invalid data is rejected.
  3. Lose: Migration DDL overhead on large tables (table locks/schema drift management).
  4. ACID Guarantees vs Horizontal Write Scale
  5. Gain: SQL guarantees strict isolation (Serializable/Repeatable Read) across multi-table operations.
  6. Lose: Distributed commit protocols (2PC) severely degrade write performance and availability.
  7. Declarative Joins vs Denormalization & Read Amplification
  8. Gain: SQL allows arbitrary N-table joins without data duplication.
  9. Lose: NoSQL requires application-level joins or heavy data denormalization (write amplification).
  10. Normalized Storage Efficiency vs High Read Speed
  11. Gain: SQL normalization eliminates data duplication and inconsistency.
  12. Lose: Requires N disk reads across index and tables vs single point read in Document NoSQL.
  13. Standardized Tooling vs Specialized Engine Performance
  14. Gain: Universal SQL ecosystem (ORMs, BI tools, standard drivers).
  15. Lose: Standard SQL engines struggle to optimize for niche access patterns like graph traversals or vector search.

6. Interview Counter Questions

  1. How do you decide between scaling a relational DB horizontally vs migrating to NoSQL?
  2. What happens to ACID transactions when you shard a SQL database across multiple nodes?
  3. How does storage engine design (B+ Tree vs LSM Tree) dictate SQL vs NoSQL write performance?
  4. How do you handle schema evolution in a NoSQL document database at scale?
  5. What is the CAP theorem implication when choosing PostgreSQL vs Cassandra?
  6. Can modern NoSQL databases (e.g., MongoDB 4.0+, DynamoDB Transact) replace SQL for transactions?
  7. How do you execute complex analytical joins on a NoSQL database without crashing read instances?
  8. How do secondary indexes work in distributed NoSQL DBs vs SQL DBs, and why are they dangerous?
  9. When would you choose Spanner/CockroachDB (NewSQL) over traditional SQL or NoSQL?
  10. How do you maintain data consistency between a primary SQL store and a secondary NoSQL search index?

7. Gold Standard Answers

  1. Scale SQL first via read-replicas, connection pooling (PgBouncer), caching (Redis), and vertical scaling. Only shard SQL or migrate to NoSQL if write QPS exceeds single-node capability (~10k-20k write QPS) or dataset size (>2TB) causes memory index spill.
  2. Cross-shard SQL transactions require Two-Phase Commit (2PC), which introduces blocking locks, network roundtrips, single-point-of-failure coordinators, and massive latency spikes. Applications usually avoid 2PC using Saga patterns or local sharding keys.
  3. SQL DBs (PostgreSQL/MySQL) historically use B+ Trees optimized for fast random reads (\(O(\log N)\)) but incur random I/O writes. NoSQL (Cassandra/RocksDB) uses LSM Trees append-only writes (\(O(1)\)) converted to sequential I/O, sacrificing read latency (compaction overhead) for ultra-high write QPS.
  4. Use double-writing or schema version flags inside documents ("schemaVersion": 2). Application code contains adapter transformers to lazily migrate old document schemas upon read, accompanied by background batch migration jobs.
  5. PostgreSQL enforces CP (Consistency/Partition tolerance) with strong single-leader guarantees; network splits reject writes on minority partitions. Cassandra defaults to AP (Availability/Partition tolerance), allowing reads/writes on all partitions via tunable quorum (\(R+W > N\)).
  6. Multi-document NoSQL transactions exist, but carry significant latency penalties (2x-5x), single-shard lock constraints, and strict timeout limits. They should be used sparingly for edge cases, not as a replacement for relational transactional domain modeling.
  7. Offload analytics via Change Data Capture (CDC like Debezium) streaming events into a data warehouse / OLAP column-store (ClickHouse, Snowflake, BigQuery) rather than running scatter-gather queries on live NoSQL nodes.
  8. In NoSQL, global secondary indexes require scatter-gather queries across all nodes or async maintained index shards, causing massive read amplification or stale index reads. SQL secondary indexes live locally on disk pages tied to primary B+ Tree keys.
  9. Choose NewSQL (Spanner/CockroachDB) when you need strict global ACID transactions, SQL interface, and horizontal multi-region scalability, provided you can tolerate higher write latency (~50ms due to Paxos/Raft consensus and TrueTime/HLC synchronization).
  10. Use CDC (Debezium reading WAL/binlog) feeding Kafka, which asynchronously updates the secondary index (Elasticsearch). Avoid dual-writing from application code due to partial failure risks and race conditions.

8. Common Mistakes

  • Choosing NoSQL purely because of "scalability" hype when dataset fits comfortably in PostgreSQL (<500GB).
  • Attempting to perform multi-table joins in application code after choosing NoSQL without denormalizing schemas.
  • Using SQL for high-velocity logging/telemetry, leading to severe disk I/O bottlenecks and table bloating.
  • Assuming NoSQL means "no schema management" – ignoring schema versioning leads to runtime deserialization errors.
  • Dual-writing to SQL and Elasticsearch synchronously inside HTTP handlers, corrupting index state upon transient failures.

9. Keywords

ACID, BASE, B+ Tree, LSM Tree, Write-Ahead Log (WAL), Sharding, Horizontal Scaling, CAP Theorem, Eventual Consistency, Change Data Capture (CDC), Debezium, NewSQL, Linear Scale-Out, Secondary Index Scatter-Gather, Read/Write Amplification.

10. Cheat Sheet

  • Core Rule: SQL for ACID & complex relationships; NoSQL for massive write scale & flexible schema.
  • SQL Scaling Limit: ~10k-20k Write QPS single node; scale reads via Replicas, writes via Sharding.
  • NoSQL Scaling Limit: Virtually unlimited linear scale-out by hashing partition keys across nodes.
  • Transactions: SQL = Native multi-row ACID; NoSQL = Single-key atomic (multi-document carries high latency overhead).
  • Storage Engines: SQL (B+ Tree: Fast Reads, Random Write I/O); NoSQL LSM (Append-only: Ultra-fast Writes, Compaction cost).
  • Secondary Indexes: SQL = Local B+ Tree lookup; NoSQL = Global async index or expensive Scatter-Gather.
  • Analytics Strategy: Never run heavy analytics on OLTP/NoSQL; stream via CDC to ClickHouse/Snowflake.

11. Related Decisions