Choosing a database: relational, document, key-value, column, graph and time-series

Why the choice deserves care

A database is one of the hardest components to replace. Applications are rewritten around their data, not the other way round; migrating terabytes of live data while the business keeps running can take a year. The choice of store is therefore a one-way door that deserves an explicit decision, while many teams make it by habit or by fashion.

Chapter 0 introduced relational databases and the four NoSQL families. This section goes further: what each family is really good at, the newer categories (time-series, search, vector and distributed SQL), how to choose by evidence rather than enthusiasm, and what it costs to run several kinds at once. The answer is rarely one database for everything, and almost never a different database for every service.

Relational: still the default, for good reasons

A relational database stores data in tables with declared types, keys and constraints, and answers declarative SQL queries, an idea from Edgar Codd's 1970 paper. Fifty years on it remains the sensible default for business data, for four reasons. First, transactions and constraints: the database itself guarantees that a payment and its ledger entries commit together and that no order points at a missing customer, so these rules do not have to be reimplemented, imperfectly, in every service. Second, query flexibility: you do not need to know every future question when you design the schema; a new report is a new query, not a new table. Third, maturity: decades of tooling for backup, replication, monitoring, migration and performance diagnosis, and engineers who already know it. Fourth, range: modern relational systems store and index JSON (PostgreSQL's jsonb), support full-text search, geospatial data (PostGIS), time partitioning and vector similarity (the pgvector extension), so one well-run database can cover many needs at moderate scale.

The real limits are specific. Writes on a single primary are bounded by one machine; very large tables need careful partitioning; schema changes on huge tables need technique (schema evolution); and some access patterns, such as deep graph traversals or relevance-ranked text search, are served much better by specialised engines. A single modern server with fast solid-state drives can serve thousands of transactions per second and terabytes of data, which covers the needs of most organisations.

The non-relational families

A document database stores each record as a self-contained document, usually JSON or a binary form of it. It fits aggregates in the domain-driven design sense: things read and written as a whole, such as a restaurant's menu with its sections, items and option groups. Reading one document is one lookup with no joins. The trade-off appears when the same data must be queried in another shape (all items containing peanuts across all menus) or changed in many documents at once; joins and multi-document transactions exist in modern products but are costlier and less natural than in SQL.

A key-value store is a distributed dictionary: put a value under a key, get it back. Because the store never looks inside values, it can be extremely fast and easy to partition. Redis and its open-source fork Valkey keep data in memory with optional persistence and add data structures (sorted sets, streams, geospatial indexes) and expiry; Amazon DynamoDB is a managed, partitioned key-value and document store. They suit sessions, carts, rate-limit counters, feature flags, caches and the latest position of each courier.

A wide-column store (Bigtable, Apache Cassandra, ScyllaDB, HBase) groups rows by a partition key and stores them sorted by a clustering key within each partition. It is designed for massive write volumes spread across many nodes and data centres. The discipline it demands is query-first modelling: you design one table per query, duplicating data freely, because queries that do not start from the partition key are expensive or impossible. Cassandra descends in part from Amazon's 2007 Dynamo paper, whose replication ideas appear in the next section but one.

A graph database stores nodes and relationships as first-class records and answers traversal queries such as customers within three hops of this fraud ring without the cascading joins that make such queries slow in SQL. Neo4j's Cypher is the best-known query language, and ISO published a standard graph query language, GQL (ISO/IEC 39075), in 2024. Graphs are excellent for fraud detection, recommendations and network analysis, and usually a poor primary store for transactional business data.

Time-series, search and vector stores

A time-series database is optimised for timestamped measurements that arrive in order and are rarely updated: metrics, sensor readings, prices, location history. It compresses heavily, partitions by time, downsamples old data (keep every point for a week, one-minute averages for a year) and deletes expired data cheaply by dropping whole time partitions. Examples include InfluxDB, Prometheus (for monitoring) and TimescaleDB, which is a PostgreSQL extension and shows how often a relational database can stretch to fit.

A search engine such as Elasticsearch, OpenSearch or Apache Solr (all built on the Apache Lucene library) maintains an inverted index from words to the documents containing them, with text analysis (stemming, synonyms, typo tolerance) and relevance ranking. It answers vegan ramen near me in milliseconds. It should almost never be a system of record: indexes are refreshed in near real time rather than transactionally, and the usual design feeds the search engine from the owning database and rebuilds the index from it when needed.

A vector database stores embeddings, lists of numbers that represent the meaning of text, images or products, and finds the items nearest to a query vector. Exact search compares the query with every vector, so these stores use approximate nearest-neighbour indexes such as HNSW graphs, trading a little recall for speed. They power semantic search, recommendations and retrieval for large language models, covered in retrieval-augmented generation. Dedicated products exist (Milvus, Qdrant, Pinecone, Weaviate), but for millions rather than billions of vectors, an extension to an existing database such as pgvector is often enough.

Distributed SQL

Distributed SQL databases, sometimes called NewSQL (a term coined by the analyst Matthew Aslett in 2011), keep the relational model, SQL and ACID transactions but partition and replicate data automatically across many nodes, often across regions. Google Spanner, described in a 2012 OSDI paper, uses tightly synchronised clocks (its TrueTime API, backed by GPS and atomic clocks) to give globally consistent transactions. CockroachDB, YugabyteDB and TiDB offer similar models on ordinary hardware; CockroachDB uses serializable isolation by default.

The price is physics and operations. A transaction that must be agreed by replicas in several regions waits for a consensus round trip, so single-row writes take milliseconds to tens or hundreds of milliseconds rather than well under a millisecond. Query planning across partitions is subtler, costs are higher, and fewer engineers know the failure modes. Distributed SQL is the right tool when you genuinely need relational transactions beyond one server's capacity or across regions (a global payments ledger, a multi-region bank), and an expensive one when a single PostgreSQL primary with replicas would do.

Choosing: criteria that matter

Choose per data set, not per system. For each important data set, collect evidence on six criteria and write the decision in an ADR.

Data shape. Is it naturally tabular with many relationships, a self-contained aggregate, a network, a time series, free text or vectors? Access patterns. Which queries run, how often, with what latency targets, and how many are ad hoc? Read-heavy or write-heavy? Always by key, or by many attributes? Consistency needs. Does the data need multi-item transactions and constraints (balances, stock, bookings), or is a few seconds of staleness harmless (likes, recommendations)? See consistency models. Scale. Current and projected data size, writes per second and growth, measured, not guessed; a store that scales to petabytes is no advantage for 50 gigabytes. Operations. Who will run it: backups, upgrades, failover, monitoring, security patches? Is there a managed service in your cloud, and what does it cost at your volumes? Team skills. What does the team already know well enough to debug at 3 a.m.? Unfamiliar technology carries unknown failure modes.

Two habits improve the choice. Write down the five or ten most important queries with their target latencies before choosing, and test the candidate with realistic data volumes; many disappointments come from a store that was benchmarked on a laptop with a thousand rows. And separate the system of record from derived stores: the system of record needs the strongest guarantees and the most conservative technology, while derived stores (search indexes, caches, read models) can be specialised precisely because they can be rebuilt.

Polyglot persistence and its cost

Polyglot persistence means using different storage technologies for different data within one system, chosen by how the data is used. Martin Fowler popularised the term in 2011, crediting Scott Leberknight with first using it. It follows naturally from database per service: each owner can pick the right tool.

The cost is easy to underestimate. Each additional database type needs its own expertise, backup and restore procedures, monitoring, capacity planning, security hardening, upgrade cycle and on-call knowledge. Data now moves between stores, so you need pipelines (CDC, events) and must reason about the lag and failure of each. Consistency between stores is eventual at best. For a small team, five databases can consume more effort than the product. A reasonable rule is to justify each new store by a requirement the existing ones demonstrably cannot meet, and to prefer managed services for the specialised ones.

Common mistakes

Résumé-driven choices: picking a store because it is new or because a large company uses it, without the requirement that justified it there. Choosing for imagined scale: designing for a billion users with a product that has a thousand, paying the complexity tax from day one. Using a derived store as a system of record: a search index or cache that becomes the only copy of data, with weaker durability than anyone realised. Ignoring the query side: picking a key-value or wide-column store and then discovering the business needs ad hoc reports and filters it cannot serve. Underrating operations: forgetting that someone must restore it from backup, upgrade it without downtime and diagnose it under load. Finally, treating the choice as permanent truth: record the assumptions (volumes, queries, team skills) so the decision can be revisited when they change.