SQL vs NoSQL: Choosing a Data Model
Relational, document, key-value, wide-column, graph, search, time-series and vector are not a ladder from old to scalable; each is a different bet on which access pattern you will need most, and the price is the patterns you give up.
The wrong framing
"SQL is old and does not scale; NoSQL is new and does" is wrong in both halves. PostgreSQL serves tens of thousands of transactions per second on one machine, and most systems never exceed one machine. MongoDB, Cassandra and DynamoDB each scale a specific access pattern by *refusing* others — no joins, no ad-hoc queries, no cross-partition transactions. The question is never "which is more scalable" but "which access patterns do I need to be cheap, and which can I live without?"
The models
Relational (PostgreSQL, MySQL, SQLite): normalised tables, joins, transactions, constraints, ad-hoc queries. The default; it answers questions you have not thought of yet. Scales vertically far, then via replicas, then via sharding with pain. Document (MongoDB, Couchbase, Firestore): JSON-like documents with nested structure, queried by field, indexed. Shines when the unit of read is the unit of write — a whole order, a whole profile — and the shape varies. Joins are weak; transactions are per document, or newer and slower across them. Key-value (Redis, DynamoDB, Riak): get/put by key, nothing else. Fastest and most scalable model precisely because it promises the least. Wide-column (Cassandra, ScyllaDB, HBase): rows grouped by partition key, sorted by clustering key, written at enormous rates across many nodes. Every query must name the partition; tables are designed per query. Graph (Neo4j, Neptune): nodes and edges with traversal as the primitive. Wins when queries are multi-hop — friends-of-friends, shortest path, fraud rings — which relational joins handle badly beyond two hops. Search (Elasticsearch, OpenSearch): inverted indexes, relevance ranking, facets, fuzzy matching. Not a system of record; fed from one. Time-series (TimescaleDB, InfluxDB, ClickHouse): append-heavy, time-partitioned, columnar compression, range aggregates. Vector (pgvector, Pinecone, Qdrant, Weaviate): embeddings and approximate nearest-neighbour — see Vector Search: Embeddings, Similarity and ANN.
| Model | Cheap | Expensive or absent | Reach for it when |
|---|---|---|---|
| Relational | Joins, transactions, constraints, ad-hoc queries | Horizontal write scaling | Default. Relationships matter and requirements will change. |
| Document | Whole-object reads/writes, flexible shape | Joins, multi-document transactions | The document is the unit of work and its shape varies. |
| Key-value | Point reads/writes at any scale | Everything except get/put by key | Sessions, caches, counters, feature flags. |
| Wide-column | Massive write throughput, partition-local reads | Ad-hoc queries, joins, secondary indexes | Time-ordered events per entity at very high volume. |
| Graph | Multi-hop traversal | Aggregates, bulk scans | Queries are paths: recommendations, fraud, dependencies. |
| Search | Relevance, facets, fuzzy text | Being the source of truth | Search is a product feature, not a WHERE clause. |
| Time-series | Range aggregates over time, compression, retention | Updates, point lookups by non-time key | Metrics, telemetry, sensor data. |
| Vector | Similarity search over embeddings | Exact queries, joins | Semantic retrieval for RAG and recommendations. |
How to decide
Write down the access patterns with frequencies. If they include joins across entities, ad-hoc reporting, or invariants across rows — start relational. If one pattern dominates by orders of magnitude and it is "get this whole thing by id" or "append events for this key", a document or wide-column store may pay. If the pattern is a traversal, graph. If it is text relevance, search. Then ask what you give up and whether you can afford it: no joins means the application does them; no transactions means the application handles partial failure; no ad-hoc queries means every new question is a schema change.
Most real systems end up polyglot: PostgreSQL as the system of record, Redis for hot paths, a search engine fed from Postgres, maybe a vector store or a time-series store for one workload. Each additional store is a consistency boundary, a backup, an on-call surface. Add them for measured reasons. See the Database Finder for the interactive decision tree.
Key points
- Every model is a bet on one access pattern being cheap, paid for with others being expensive or impossible.
- Relational is the default because it answers questions you have not asked yet.
- Choose from written-down access patterns and frequencies, then name what you are giving up.
- Polyglot is normal; each extra store is a consistency boundary you own.
When to use — and when not
- Any new system, and any system whose access pattern has drifted from its original design.
- Choosing a NoSQL store because the relational one "might not scale" without a number behind "might".
Failure modes
- A document store with a schema that turns out to be relational, and joins done in application code.
- A key-value store asked to answer a range query.
- Three databases for a workload one would have served.
See how this works internally →
Descend one layer: the same topic explained from the machinery up.