Skip to content
Velloreth
Programming 6 min read

SQL vs NoSQL: Choosing a Database

Relational and non-relational databases make different trade-offs. Learn the categories, the guarantees, and how to choose.

By

The categories

Relational (SQL) — tables with a fixed schema, a primary key, a foreign key, and a join. PostgreSQL, MySQL, SQLite.

Document — a collection of self-contained documents, usually JSON. MongoDB, CouchDB.

Key-value — a map from a key to a value. Redis, DynamoDB.

Wide-column — rows organised by a partition key and a clustering key. Cassandra, ScyllaDB.

Graph — nodes and edges, optimised for a traversal. Neo4j.

Search — an inverted index, optimised for a text query. Elasticsearch, OpenSearch.

Relational

The strengths:

  • A schema enforces the shape of the data, which catches an error at write time.
  • A join combines data from several tables, which avoids duplication.
  • A transaction with ACID guarantees, which is essential for a financial operation.
  • A mature query language, with a planner that optimises a query.
  • Constraints — a foreign key, a unique index, a check — enforce an invariant in the database.

The weaknesses:

  • A schema change is a migration, which is harder to do incrementally.
  • A horizontal scale is harder, because a join across shards is expensive.
  • A very large write volume can exceed a single primary.

Use it for the transactional core: an order, a payment, a user account, anything with a relationship and an invariant.

Document

The strengths:

  • A self-contained document is read in one request, with no join.
  • A flexible schema allows a document to vary, which suits a changing model.
  • A nested structure matches the shape of the data, which is natural for a content item.

The weaknesses:

  • A duplicated field across documents must be updated everywhere, which is the update anomaly.
  • A cross-document transaction is limited or absent.
  • A join is possible but not the strength.

Use it for a content item, a user profile, a product catalogue, or an event log where the document is the unit of access.

Key-value

The strengths:

  • The simplest model, and the fastest lookup.
  • A predictable latency, which suits a cache.
  • A TTL, which suits a session or a rate-limit counter.

The weaknesses:

  • No query beyond the key, unless the store adds a secondary index.
  • No relationship, so a join is the application’s job.

Use it for a cache, a session, a rate-limit counter, a feature flag, a leaderboard.

Wide-column

The strengths:

  • A very high write throughput, distributed across nodes.
  • A linear scale, by adding nodes.
  • A tunable consistency, per query.

The weaknesses:

  • The query pattern must be known in advance, because the data is organised by the key.
  • No ad-hoc query, and no join.
  • A read that does not match the key is a full scan.

Use it for a time series, an event log, a message store, a very large write-heavy workload.

The CAP theorem

A distributed system can guarantee at most two of:

  • Consistency — every read sees the latest write.
  • Availability — every request gets a response.
  • Partition tolerance — the system works despite a network partition.

A partition is unavoidable in a distributed system, so the real choice is between consistency and availability during a partition.

A CP system refuses a request rather than return stale data. A bank balance.

An AP system returns the best available data, which may be stale. A social feed.

The theorem applies to a distributed system. A single-node database is not subject to it, which is why a single Postgres is consistent and available until it is not.

Transactions

ACID — atomic, consistent, isolated, durable. The guarantee a relational database provides.

BASE — basically available, soft state, eventual consistency. The trade many distributed stores make.

The isolation level matters. A read committed is the common default; a serializable is the strongest and the slowest. A phantom read, a non-repeatable read, and a lost update are the anomalies the levels prevent.

For a financial operation, use a transaction with the right isolation level. For a feed, eventual consistency is fine.

Choosing

Ask three questions:

  1. What is the access pattern? A lookup by key, a query by several fields, a traversal, a text search.
  2. What is the consistency requirement? Must a read see the latest write?
  3. What is the scale? The data volume, the write rate, and the read rate.

A relational database is the default for most applications. It handles a surprising scale, and the schema and the transaction are worth a lot.

A document store when the data is read as a unit and the schema varies.

A key-value store for a cache or a session.

A wide-column store for a very large write-heavy workload with a known pattern.

A search store for a text query, alongside the source of truth.

The polyglot approach

Using several stores is common and reasonable.

A relational database as the source of truth, with a search index for a text query and a cache for a hot read.

The cost is the sync. The search index must be updated when the source changes, and a missed update is a stale result. An outbox pattern or a change-data-capture stream is the usual answer.

The rule: one source of truth, and the other stores are derived. A derived store can be rebuilt from the source, which makes a bug recoverable.

Common mistakes

Choosing NoSQL for scale you do not have. A single Postgres handles more than most applications need.

Choosing SQL for a document-shaped access pattern. A join on every read is slower than a self-contained document.

No schema at all. A document store without a validation layer allows a document to drift, which is a bug that appears months later.

Ignoring the consistency requirement. An AP store for a balance is a correctness bug.

No source of truth. Two stores that both accept a write will diverge.

Practical guidance

  • Default to a relational database.
  • Choose by the access pattern, not the label.
  • State the consistency requirement.
  • Use a document store for a self-contained unit.
  • Use a key-value store for a cache.
  • Keep one source of truth.
  • Validate the schema even in a document store.
  • Do not choose for scale you do not have.

For the related guides, see database-normalization-explained and database-indexing-explained. For the broader context, caching-strategies-explained and database-migrations-guide.

Quotable summary

A relational database enforces a schema and supports a join, which suits structured data with relationships, while a document store holds a self-contained document, which suits data that is read as a unit. A key-value store is the simplest and the fastest, and it suits a cache or a session, while a wide-column store suits a very large write-heavy workload with a known access pattern. The CAP theorem says a distributed system can guarantee at most two of consistency, availability, and partition tolerance, and since a partition is unavoidable, the real choice is between consistency and availability during a partition. The choice is about the access pattern and the consistency requirement rather than the label, and a relational database is the default for most applications because it handles a surprising scale.

Frequently asked questions

Is NoSQL faster than SQL?
Not inherently. A key-value lookup is faster than a join, but a relational database with an index is fast for the same lookup. The difference is the access pattern, not the category.
What is the CAP theorem?
A distributed system can guarantee at most two of consistency, availability, and partition tolerance. Since a partition is unavoidable, the real choice is between consistency and availability during a partition.
What is eventual consistency?
A write is visible to all readers after some delay, rather than immediately. It allows higher availability, at the cost of a read that may return stale data.
When should I use a document store?
When the data is read as a self-contained unit and the schema varies, such as a content item or a user profile. It avoids a join and matches the access pattern.
Can I use both?
Yes, and it is common. A relational database for the transactional core and a document or search store for a specific access pattern, kept in sync.
What is a wide-column store?
A store that organises data by a partition key and a clustering key, optimised for a very large write-heavy workload with a known query pattern, such as Cassandra.