SQL vs NoSQL Trade-offs

SQL vs NoSQL Trade-offs

"Which database should we use?" is one of the most consequential early decisions in a system, and a staple of design interviews. The honest answer is never "NoSQL scales, SQL doesn't" or "always use Postgres". It comes from matching a database's data model, query capabilities, consistency guarantees and scaling approach to your application's access patterns.

Relational (SQL) databases

PostgreSQL, MySQL, SQL Server and Oracle store data in tables with a fixed schema, linked by keys. Data is normalized: each fact is stored once, and queries join tables back together.

Strengths:

• Flexible querying: any question can be asked later with joins, aggregations and window functions, even ones nobody anticipated when the schema was designed.
• Transactions (ACID) across any rows and tables: transfers, orders plus inventory, and so on.
• Integrity enforced by the database: types, NOT NULL, foreign keys, unique constraints, check constraints.
• Maturity: decades of tooling, optimizers, operational knowledge and developers who know SQL.

Weaknesses:

• Horizontal scaling of writes is harder. A single primary handles writes, and read replicas scale reads. Sharding a relational database is possible (Citus, Vitess, application-level sharding), but cross-shard joins and transactions get hard.
• Schema changes on huge tables need careful, online migrations.
• Mapping deeply nested objects to tables takes joins and ORM work (the object–relational impedance mismatch).

NoSQL: four different families

"NoSQL" isn't one thing. It covers databases built around different data models, each optimized for particular access patterns:

Document stores: MongoDB, Couchbase, Firestore

Data is stored as JSON-like documents. Related data that's read together is embedded in one document:

{
  "_id": "order_981",
  "customerId": "c_42",
  "status": "PAID",
  "items": [ { "sku": "KB-01", "qty": 1, "price": 89.99 }, { "sku": "MS-07", "qty": 2, "price": 19.5 } ],
  "shippingAddress": { "city": "Pune", "pin": "411001" }
}

One read returns the whole order with no joins. The schema is flexible per document, although most teams still enforce one in the application or with schema validation. They're good for catalogues, content, user profiles, orders and event data, where each entity is naturally a self-contained aggregate. MongoDB supports secondary indexes, aggregations and, since version 4, multi-document ACID transactions. Schema modelling is covered in Schema Design Patterns.

Key-value stores: Redis, DynamoDB (at its core), Memcached

A key maps to a value, with extremely fast lookups by key and little else. They're good for caching, sessions, feature flags, rate-limit counters, leaderboards (Redis sorted sets) and simple high-scale lookups.

Wide-column stores: Cassandra, ScyllaDB, HBase, Bigtable

Rows are grouped into partitions by a partition key, and sorted within a partition by clustering columns. They're built for massive write throughput across many nodes, and for queries that read one partition in order. They're good for time series, IoT telemetry, messaging history and activity feeds. The design rule is "model tables around queries": you often store the same data in several tables, one per access pattern, because ad-hoc queries and joins aren't supported.

Graph databases: Neo4j, Amazon Neptune

Nodes and edges are first-class, and traversing relationships many hops deep is fast. They're good for social graphs ("friends of friends who like X"), fraud rings, recommendation paths and dependency graphs, where relational joins across many hops become slow and awkward.

Comparing them on what actually matters

Consistency: what CAP does and doesn't say

The CAP theorem says that during a network partition, a distributed data store must choose between consistency (every read sees the latest write) and availability (every request gets a response). It's often misquoted as "SQL is CA, NoSQL is AP". That's misleading:

• A single-node relational database isn't really in CAP's scope, because it isn't partition-tolerant as a distributed system. Once you add replicas, you choose: synchronous replication (consistency first) or asynchronous (availability and latency first).
• Many NoSQL systems are tunable. Cassandra lets you pick quorum reads and writes per query. MongoDB with majority write and read concerns behaves as a consistent (CP-leaning) system. DynamoDB offers strongly consistent reads as an option.

The more useful framing for design is PACELC: if there's a Partition, choose Availability or Consistency; Else, in normal operation, choose lower Latency or stronger Consistency. That captures the everyday trade-off that replication creates, even when nothing is broken. See CAP Theorem for details.

A decision process that works

1. List the access patterns first: the top reads and writes, with their frequency, latency targets and data volumes.
2. Identify what needs strong consistency: money, inventory, bookings, uniqueness. That usually favours a relational database, or careful single-document designs.
3. Check whether queries are known and fixed (wide-column and key-value friendly), or open-ended and evolving (relational friendly).
4. Estimate scale honestly. A single well-tuned PostgreSQL instance handles far more than most products ever need. Horizontal write scaling is a real requirement for fewer systems than people assume.
5. Consider the team's operational experience. A database nobody knows how to run is a risk of its own.

Polyglot persistence is normal in real systems: PostgreSQL as the source of truth, Redis for caching and rate limits, Elasticsearch or OpenSearch for full-text search, and a wide-column or time-series store for telemetry, each fed from the source of truth via events or change data capture.

Follow-up questions this topic invites — and their answers

Q: When would you choose MongoDB over PostgreSQL? A: When the domain is naturally made of self-contained aggregates that are read and written as a whole (catalogue items with varying attributes, content, orders with embedded lines), when the schema evolves quickly, and when built-in horizontal sharding matters. PostgreSQL is usually the better default when data is highly relational, you need ad-hoc querying and reporting, or many operations span multiple entities transactionally. Its JSONB support also covers many "flexible document" needs.

Q: Isn't NoSQL always faster? A: No. For lookups by primary key, both are fast. NoSQL systems gain speed and scale by restricting what you can ask: no joins, and queries designed in advance. A relational database with good indexes is extremely fast for most workloads. Performance depends on data modelling and indexing more than on the category.

Q: How do you model a many-to-many relationship in a document database? A: Choose based on access patterns. Embed arrays of IDs on one side if the list is bounded (a user's roles). Use a separate collection of relationship documents if the relationship is large or unbounded (followers). Or denormalize frequently needed fields for read efficiency, accepting the cost of updating copies.

Q: What does "model around queries" mean for Cassandra? A: You design one table per query pattern, choosing a partition key so each query reads a single partition, and clustering columns that give the needed sort order. The same data is often written to several tables. Writes are cheap, and cross-partition queries are expensive or impossible.

Q: Can a system use both SQL and NoSQL? A: Yes, and most large systems do. A relational database often holds the authoritative transactional data, while specialized stores serve caching, search, analytics or high-volume event data, kept in sync through events or change data capture. The cost is operational complexity and consistency between stores, so add each one only for a clear reason.