Choosing a Database
There is no best database, only a best fit for an access pattern. This page maps the eight families, from relational to vector, compares what each gives and takes, offers a decision guide by the way the data is read and written, previews polyglot persistence, and lays out the pages that follow.
The Database Families
"NoSQL" is a crowd, not a category. A more useful way to read the landscape is by data model: what is the shape of the thing you store, and what questions do you ask of it? Each family below is optimised for one shape and answers some questions superbly and others poorly.
| Family | Data model | What it is for | Representative products |
|---|---|---|---|
| Relational | Tables of rows with a schema, related by keys | Structured business data, ad-hoc queries, joins, transactions, constraints. The default system of record | PostgreSQL, MySQL, Oracle Database, Microsoft SQL Server |
| Key-value | A key mapped to an opaque value | Lookups by a known key at very high rates; caches, sessions, counters, leaderboards, shopping carts | Redis, Memcached, Amazon DynamoDB, etcd |
| Document | Self-describing JSON-like documents, queryable by field | Records whose shape varies, read and written as a whole: catalogues, profiles, content | MongoDB, Couchbase, Google Cloud Firestore, Amazon DynamoDB (also key-value) |
| Wide-column | Rows grouped by partition key, with many sparse columns, sorted within the partition | Massive write volumes, known access paths, time-ordered data per entity across data centres | Apache Cassandra, ScyllaDB, Apache HBase, Google Cloud Bigtable |
| Graph | Nodes, relationships and properties | Queries about connections: friends of friends, fraud rings, recommendations, dependency graphs | Neo4j, Amazon Neptune |
| Search | Documents with an inverted index and relevance scoring | Full-text search, filtering and faceting, log analytics | Elasticsearch, OpenSearch, Apache Solr |
| Time-series | Points keyed by time, with tags and values | Metrics, sensor and event data with retention and downsampling | InfluxDB, TimescaleDB, Prometheus (monitoring) |
| Vector | High-dimensional embeddings with an approximate nearest-neighbour index | Similarity search: semantic search, recommendations, retrieval for AI applications | Milvus, Qdrant, Pinecone, Weaviate, PostgreSQL with the pgvector extension |
Boundaries are blurry on purpose. PostgreSQL stores JSON documents and, with extensions, vectors and time-series; MongoDB supports multi-document transactions; DynamoDB is both key-value and document; Elasticsearch is used as a document store. A product belongs to the family it is best at, not the only one it can imitate.
What Each Family Gives and Takes
The table is a generalisation: individual products differ and configuration moves the dots. Use it to build intuition, then check the specific product.
| Query power | Scales out | Typical consistency | Transactions | Schema | |
|---|---|---|---|---|---|
| Relational | Highest: SQL, joins, aggregates, ad-hoc | Reads easily; writes need sharding work | Strong (single node or primary) | Full ACID, multi-row | Enforced |
| Key-value | Lowest: get/put by key (plus structure operations in Redis) | Very well | Tunable, from eventual to strong | Single key; limited multi-key | None |
| Document | Good: queries and indexes on fields, aggregation pipelines | Well (sharding) | Tunable | Single document; multi-document in some | Flexible, optionally validated |
| Wide-column | Narrow: by partition key and sort range; designed per query | Excellent | Tunable (quorums) | Single partition, lightweight | Column families |
| Graph | Excellent for traversals, weak for bulk analytics | Harder (graph partitioning is difficult) | Often strong | Usually ACID on one cluster | Flexible |
| Search | Excellent for text and filters; not for transactions | Well (shards) | Near-real-time: new data appears after a refresh | None | Mappings |
| Time-series | Time-range scans and aggregates | Varies | Varies | Limited | Tags + fields |
| Vector | Similarity (approximate); usually metadata filters too | Varies | Varies | Usually none | Vectors + metadata |
Three things recur. Query flexibility falls as scale-out rises. Tunable consistency means you pay latency for stronger reads, the PACELC trade covered in 01 NoSQL Fundamentals. Search and vector stores are almost always a secondary copy: the truth lives elsewhere and the index is rebuilt from it.
- Elasticsearch is excellent at finding documents and not designed to be the only home for data that must never be lost or must be strongly consistent. Keep the source of truth in a database and treat the index as a derived, rebuildable view.
A Decision Guide by Access Pattern
Start from how the data is read and written, not from the technology. For each pattern, the table names the family that is built for it and the usual trap.
| Dominant access pattern | Reach for | Because | Trap |
|---|---|---|---|
| Point lookup by a known key, huge rate, low latency | Key-value (Redis, DynamoDB) or a cache in front of the database | One key, one machine, no planning | Cannot ask "all items where..." without a second index or copy |
| Range scan in key order ("orders of customer 42 since Monday") | Wide-column, DynamoDB sort key, or a relational index | Data stored sorted inside a partition | A range across partitions is a scatter; choose the partition key carefully |
| Ad-hoc queries nobody planned in advance | Relational | SQL and a query planner exist for this | Choosing NoSQL early forces you to know the questions up front |
| Joins across several entities | Relational | Joins are native and indexed | Denormalising in NoSQL moves the join into your writes |
| Documents read and written whole, shape varies | Document store (MongoDB, Couchbase) | One read returns the whole aggregate | Unbounded arrays and huge documents |
| Full-text search, relevance, facets | Search engine (Elasticsearch, OpenSearch) | Inverted index and scoring | Treating it as the source of truth; eventual (refresh) visibility |
| Aggregation over large volumes | Columnar warehouse or relational database; some document stores for modest volumes | Scans over columns | Key-value stores cannot aggregate; scanning one costs time and money |
| Graph traversal (depth, paths, neighbours of neighbours) | Graph database (Neo4j, Neptune) | Following edges is cheap; multi-hop joins in SQL get slow and awkward | Using a graph database for data with few relationships |
| Time series: append-heavy, time-range queries, retention | Time-series database; or partitioned relational tables; or wide-column keyed by (entity, time bucket) | Compression, downsampling, expiry by time | High-cardinality tags can overwhelm the index |
| Similarity search over embeddings | Vector index (Milvus, Qdrant, pgvector, ...) | Approximate nearest-neighbour index | Approximate results; the embedding model, not the database, decides quality |
- What are the top three queries, exactly, with their rates?
- How big will the data be in a year, and how fast does it grow?
- What consistency does each piece of data need? (Money and stock: strong. Likes and views: eventual.)
- Do we need joins, transactions or ad-hoc reporting? (If yes, start relational.)
- Who will operate it? A managed service is a feature; an unfamiliar cluster at 3 a.m. is a risk.
If after those questions the answer is still "relational", that is a perfectly good answer. Modern PostgreSQL and MySQL cover documents (JSON columns), full-text search, time-series (with extensions) and vectors (pgvector) well enough for many workloads, and one database is far easier to run than four.
More Than One Database, On Purpose
Polyglot persistence means using several database types in one system, each for the job it does best. A typical shop keeps orders in PostgreSQL, product search in Elasticsearch, sessions and hot product data in Redis, and clickstream in a warehouse. Page 07 treats this in depth; this is the preview.
| Rule | Why |
|---|---|
| One system of record per fact | If two stores can both be edited independently, they will disagree and nobody knows which is right |
| Derived stores are fed from the record, asynchronously | Through change events or an outbox, not by writing to everything from the request handler (the dual-write problem; see Event-Driven Patterns) |
| Every derived store is rebuildable | If the index or cache is lost, you can replay from the source |
| Accept lag, and show it | The search index may be seconds behind; design screens so that does not hurt (read-your-writes where it matters) |
| Add a store only for a measured need | Each one is more to patch, back up, monitor, secure and learn |
- A team adds Redis, Mongo, Elasticsearch and Cassandra to a system that served 50 requests per second from PostgreSQL. Now there are four failure modes, four upgrade paths, four sets of credentials, and five ways for data to disagree. The cost of each extra store is paid every week, forever.
How This Section Relates to SQL and to Caching
This section assumes you know the relational basics, which the SQL guide teaches: tables, joins, indexes, transactions and schema design. Everything here is easier if you can say what the relational answer would be first. A few places they meet directly:
| Topic | In SQL | In this section |
|---|---|---|
| Indexes | Indexes and performance | Secondary indexes in a partitioned store (fundamentals, chapter 7) |
| Schema design | Schema design | Modelling from queries, denormalisation, embedding or referencing (fundamentals, chapter 8) |
| Transactions | ACID in a single database | What is lost and regained: single-item atomicity, conditional writes (fundamentals, chapter 9) |
| Joins and aggregation | Joining tables, Aggregation | Replaced by precomputed shapes, or by a search or warehouse store |
A cache is not a database family but a role: a fast, disposable copy of data that lives elsewhere. Redis and Memcached are used for it. Caching is the first scaling step before any change of database: if most reads hit the same few thousand items, a cache can remove much of the load from your primary store with no remodelling. Page 06 covers cache-aside, write-through, invalidation and stampedes; the Spring side is in Spring: Caching and Resilience.
| A cache is... | A database is... |
|---|---|
| Disposable: losing it costs speed, not data | Durable: the record of truth |
| Filled on demand, expired by time or by event | Written by the business transaction |
| Allowed to be stale within a stated limit | Expected to be correct |
- First measure and fix queries and indexes in the existing database.
- Then add a cache or read replicas for read-heavy traffic.
- Then partition or move one specific workload to a family built for it (a key-value store for sessions, a search engine for search).
- Only then consider moving the system of record, and do it by the strangler approach in fundamentals chapter 10.
Pages in This Section
Read Fundamentals first: every later page leans on its vocabulary (partition key, quorum, eventual consistency). After that, take the pages in the order your project needs.
- 01 · NoSQL Fundamentals - CAP and PACELC, consistency, replication and quorums, partitioning, secondary indexes, modelling, with a plain-Java quorum simulation
- 02 · Redis - data structures, persistence, replication, cluster, caching and beyond
- 03 · DynamoDB - partition and sort keys, indexes, capacity modes, single-table design, transactions
- 04 · MongoDB - documents, indexes, aggregation, replica sets and sharding, schema design
- 05 · Elasticsearch - inverted indexes, mappings, queries, relevance, shards, operations
- 06 · Caching Patterns - cache-aside, write-through, invalidation, stampedes, TTLs, consistency with the database
- 07 · Polyglot Persistence - combining stores safely: source of truth, derived views, synchronisation, failure modes
- SQL Guide - the relational foundation, schema design and indexes.
- Messaging - events and outbox patterns that keep derived stores in sync.
- Spring Guide - Spring Data and caching on the application side.
- Performance Engineering - measurement and database limits that decide when to scale.
Cheat Sheet and Recap
| If the dominant need is... | Start with | Watch out for |
|---|---|---|
| Known-key lookups at huge scale | Key-value (Redis, DynamoDB) | No ad-hoc queries |
| Whole documents of varying shape | Document store (MongoDB) | Unbounded arrays, schema drift |
| Huge write volume with known queries | Wide-column (Cassandra) or DynamoDB | Hot partitions, query-first design |
| Ad-hoc queries, joins, transactions | Relational (PostgreSQL, MySQL) | Nothing: this is the default |
| Full-text and facets | Elasticsearch or OpenSearch | Not a source of truth |
| Multi-hop relationships | Graph (Neo4j, Neptune) | Few relationships means little benefit |
| Metrics and sensor data | Time-series database | High-cardinality tags |
| Semantic similarity | Vector index (pgvector, Milvus, Qdrant) | Approximate results |
| Speed up reads on any store | Cache (Redis) | Invalidation and stampedes |
- Pick by access pattern, not by brand or by what is popular.
- Relational is the default system of record; add other families for a named, measured need.
- Every family trades query power for scale or speed. Ask what you are giving up.
- Search, cache and vector stores are derived copies. Keep one source of truth and make the rest rebuildable.
- A cache comes before a new database, and measuring comes before both.
- Each extra store is permanent operational cost: patches, backups, monitoring, skills.
Product names and families on this page are given for category only; no limits, defaults or prices are quoted, so nothing here is version-sensitive. Check each product's documentation for the version you will run.
- 01 NoSQL Fundamentals - CAP, consistency, replication, partitioning and modelling, with a plain-Java quorum simulation.
- SQL Guide - revisit the relational baseline before choosing a different model.