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 Velloreth
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:
- What is the access pattern? A lookup by key, a query by several fields, a traversal, a text search.
- What is the consistency requirement? Must a read see the latest write?
- 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.