Library · Interactive guide

Database Selection Guide 2026

How to choose the right kind of database for a specific problem, workload and architecture — without reducing everything to ‘SQL vs NoSQL’.

Updated: 3 Oct 2026
Reading time: 28 min read
Maturity: Working draft
Knowledge as of: 3 Oct 2026
Topics: Computing & Emerging Tech; Systems & Cybernetics

How to read this guide

Claims that matter for a decision carry an evidence label, which says what kind of claim it is and what it rests on.

Evidence: Verified fact: Backed by primary documentation or a reproducible source cited in the sources.
Evidence: Industry observation: Widely reported practice or trend (surveys, engineering blogs), not universally true.
Evidence: Benchmark-dependent: True only under specific workloads, versions or hardware.
Evidence: Interpretation: The author’s (or the AI draft’s) judgement, heuristics or scores.
Evidence: Emerging / uncertain: New, contested, fast-moving or unverifiable at the knowledge-as-of date.

AI-assisted research. This guide was researched and drafted with AI tools, then translated, restructured and checked by Nexate. Claims carry evidence labels; sources are listed at the end. Found an error? It will be corrected and logged in the changelog.

Note on this guide: Researched and drafted with AI tools; translated from Polish, restructured and checked by Nexate.

How to use this guide

This guide is a translated and restructured edition of an interactive handbook on choosing databases. Read it from start to finish, or go straight to a decision aid: the problem picker, the decision tree, Architect Mode and the 30 scenarios. Every result in it is a starting point to check against your own workload, not a verdict.

How the tools work

Database map. Each card shows the type’s tier and tags. Open its details for examples, the typical workload, and when to use it and when not to.
Problem picker. Choose a dominant workload to see what is used most often, the alternative, the trade-off and what not to do by default. Move between workloads with the arrow keys.
Decision tree. Expand a question to see its answer. If several answers apply, you probably have several workloads.
Technology comparison. Filter the table by category or by text, and sort it by any column. The filters are kept in the page address, so a filtered view can be shared.
Database vs database and Indexing visualiser. Both are tabs: the arrow keys, Home and End move between them.
Source of truth. Select a component in the diagram to see its role, whether it is a source of truth, what happens if it is lost and how it is rebuilt.
Architect Mode. Answer ten questions and generate an architecture starting point. ‘How it works’ lists the rules it applies.
Scenarios. Filter the 30 scenarios by category or search them. The filters are kept in the page address.
Checklist. Your ticks are saved in this browser on this device only, and nothing is sent anywhere. ‘Reset’ clears them.
Glossary. Search the terms; every term has its own link.

Keyboard and links

⌘K or Ctrl+K, or / outside a text field, opens the search for this guide. The arrow keys move through the results, Enter goes to the item and Esc closes the search.
Every section heading has a copy-link button. Links to a card, a tab, a scenario or a checklist item open it, for example #scenario-rag or #vs-postgresql-mongodb.
Links from the original handbook, such as #source-truth, #normalization, #cmp0 or #check0, still work.

Evidence, tiers and roles

The original handbook gives no evidence labels, so this edition applies one policy throughout:

Recommendations, tiers, scenarios, comparisons, the decision tree, the cheat sheet and the results of the tools are judgements: Evidence: Interpretation. The label is shown where it matters to a decision: on the tool results, in the complexity column of the technology table and on the scenarios.
Descriptions of technologies, index mechanisms, glossary definitions and the CAP/PACELC explanation are general knowledge: Evidence: Industry observation. Claims checked against the official documentation are described in the methodology.

The tiers say how far a choice is from the sensible start:

Recommendation: Default the sensible starting point.
Recommendation: Specialised tool (Specialised) right for a specific, nameable need.
Recommendation: Premature complexity (Premature) you probably don’t need this yet.

Role chips such as Role: Core or Role: Derived describe what a component does in a system. They are not recommendations.

Callouts

Principles, cautions, pitfalls and notes each carry a visible label, such as ‘Guiding principle’, ‘What not to conclude’, ‘Cost’ or ‘In practice’.

01 Start

01Problem
02Requirements
03Access pattern
04Data model
05Database
06Trade-offs

Guiding principle

Use the simplest system that reliably solves the actual problem. Specialise the architecture only once you can name a specific bottleneck or requirement that the simpler option does not meet.

02 TL;DR: if you only remember 10 things

01
PostgreSQL is a very good default.
For a standard business backend it offers transactions, constraints, relationships, JSONB, advanced indexes and a huge ecosystem.
02
SQLite wins on locality.
Mobile, desktop, edge and local-first need no DB server if the data is local and there are not many concurrent writers.
03
DuckDB = embedded analytics.
Great for local analysis of Parquet/CSV/DataFrame and for OLAP without a full warehouse.
04
Redis is not a ‘faster PostgreSQL’.
It is an in-memory data structure store. It works best as a cache, sessions, counters, hot state or queues/streams.
05
A search engine works on a different model.
Elasticsearch/OpenSearch solve relevance, tokenisation, the inverted index, faceting and hybrid search.
06
A graph DB requires a graph workload.
Merely having relationships is not enough. The key trigger is frequent traversals and multi-hop dependencies.
07
A vector DB is not mandatory for AI.
RAG can often start with PostgreSQL + pgvector or an existing search engine.
08
OLTP ≠ OLAP.
PostgreSQL/SQL Server handle short transactions; ClickHouse/BigQuery/Snowflake are oriented towards scans and aggregations.
09
Cassandra solves a specific scale problem.
A huge, distributed, write-heavy workload with predictable access patterns — not ‘lots of data’ in the abstract.
10
Operational complexity is a cost of the architecture.
Every new engine means backup, monitoring, security, recovery, upgrades, skills and on-call.

03 Interactive map of database types

Select a card to see when to use it and when not to. The categories are no longer clear-cut: modern engines often combine several models, but their ‘natural workload’ still matters.

Relational / RDBMS
Recommendation: Default
Transactions, constraints, relationships and flexible SQL queries.
- OLTP
- ACID
- SQL
Details: Relational / RDBMS
Examples
PostgreSQL, SQL Server, MySQL, Oracle
Typical workload
SaaS, CRM, ERP, e-commerce, billing, fintech
When
When integrity, transactions, joins and referential integrity matter.
When not
Don't use a single RDBMS as a heavy OLAP system just because you already have it.
Document
Recommendation: Specialised tool (Specialised)
The document is the natural aggregate of the data.
- JSON
- NoSQL
- documents
Details: Document
Examples
MongoDB, Couchbase, CouchDB, Firestore
Typical workload
Content, profiles, catalogue metadata, hierarchical records
When
When a large share of the entities is read/written as a whole document.
When not
Don't choose it solely because the API uses JSON.
Key-value
Recommendation: Specialised tool (Specialised)
Very fast access by a known key.
- cache
- lookup
- state
Details: Key-value
Examples
Redis, DynamoDB, etcd
Typical workload
Cache, sessions, counters, hot state, partitioned lookup
When
When the main access pattern is key → value.
When not
Don't expect arbitrary joins and ad-hoc queries as in an RDBMS.
Wide-column
Recommendation: Premature complexity (Premature)
Distributed, write-heavy storage with known access patterns.
- distributed
- write-heavy
- LSM
Details: Wide-column
Examples
Cassandra, ScyllaDB, HBase, Bigtable
Typical workload
Massive ingest, telemetry, distributed writes
When
When you genuinely need horizontal write scale and high availability.
When not
Don't deploy it for a small SaaS ‘because it will grow’.
Graph
Recommendation: Specialised tool (Specialised)
Relationships and traversals are the primary workload.
- graph
- traversal
- relationships
Details: Graph
Examples
Neo4j, Neptune, Memgraph
Typical workload
Fraud, IAM, knowledge graph, dependencies, networks
When
When queries traverse many relationships and depths.
When not
Don't choose it just because the data has relationships.
Time-series
Recommendation: Specialised tool (Specialised)
Time is the main dimension of writes and queries.
- time-series
- metrics
- telemetry
Details: Time-series
Examples
TimescaleDB, InfluxDB, VictoriaMetrics, QuestDB
Typical workload
Telemetry, IoT, monitoring, metrics, ticks
When
When append, time windows, retention and rollups dominate.
When not
A timestamp in a table does not by itself mean you need a TSDB.
Search engine
Recommendation: Specialised tool (Specialised)
Relevance, inverted index, tokenisation, faceting.
- search
- text
- inverted-index
Details: Search engine
Examples
Elasticsearch, OpenSearch, Typesense, Meilisearch
Typical workload
Full-text, product search, logs, hybrid search
When
When search quality and search-specific queries are key.
When not
Don't automatically treat a search index as a system of record.
Columnar OLAP
Recommendation: Specialised tool (Specialised)
Scans and aggregations over huge datasets.
- OLAP
- analytics
- columnar
Details: Columnar OLAP
Examples
ClickHouse, BigQuery, Snowflake, Redshift
Typical workload
BI, dashboards, logs, analytics, warehouse
When
When queries read a few columns from a very large number of records.
When not
It is not a natural choice for classic CRUD/OLTP.
Vector
Recommendation: Specialised tool (Specialised)
ANN / similarity search over embeddings.
- vector
- ANN
- HNSW
- AI
Details: Vector
Examples
Qdrant, Milvus, Weaviate, Pinecone
Typical workload
RAG, semantic search, recommendations
When
When vector retrieval is important enough to justify a separate engine.
When not
Don't automatically deploy a separate vector DB for every AI project.
Distributed SQL
Recommendation: Premature complexity (Premature)
SQL + transactions + distribution across many nodes/regions.
- distributed
- SQL
- global
Details: Distributed SQL
Examples
Spanner, CockroachDB, YugabyteDB, TiDB
Typical workload
Global / distributed transactional OLTP
When
When single-primary/geography/availability is a real constraint.
When not
Don't treat it as simply a ‘better PostgreSQL’.
Embedded
Recommendation: Default
The database runs in the application process.
- embedded
- local
- edge
Details: Embedded
Examples
SQLite, DuckDB, RocksDB, LevelDB
Typical workload
Desktop, mobile, edge, local-first, local analytics
When
When the data is local and you don't need a separate DB server.
When not
Don't confuse SQLite (OLTP) with DuckDB (OLAP).
In-memory
Recommendation: Specialised tool (Specialised)
Data and structures optimised for memory.
- memory
- cache
- low-latency
Details: In-memory
Examples
Redis, KeyDB
Typical workload
Cache, sessions, counters, realtime state
When
When latency and the hot working set are key.
When not
Plan persistence, eviction and recovery before you use it as a primary store.
Multi-model
Recommendation: Specialised tool (Specialised)
One system exposes several models/capabilities.
- multi-model
- convergence
- extensions
Details: Multi-model
Examples
PostgreSQL + extensions, OpenSearch, Neo4j
Typical workload
Systems that want to limit the number of engines
When
When extending the existing platform is good enough.
When not
‘Supports everything’ does not mean ‘is the best at everything’.

04 What problem are you solving?

Choose the dominant workload. The result is an architecture starting point, not an absolute prescription.

Workload picker

How it works

Each of the 16 workloads has a fixed answer taken from the original handbook: the option used most often, an alternative, the main trade-off and what not to reach for by default. The first workload is selected when the page opens. The answers are judgements, not measurements.

Standard CRUD

Relational DB as the starting point Evidence: Interpretation

Most often: PostgreSQL / SQL Server / MySQL
Alternative: Document DB, if the aggregates are naturally document-oriented
Trade-off: First, the transaction and constraint model; don't optimise for a hypothetical scale.
Don't do by default: Cassandra / distributed SQL without a real trigger

Transactions

RDBMS / transactional SQL Evidence: Interpretation

Most often: PostgreSQL, SQL Server, Oracle; distributed SQL when geography requires it
Alternative: Event-driven saga, if the workflow is distributed
Trade-off: Strong invariants usually increase the cost of coordination.
Don't do by default: An eventual-only store for critical transfers without additional mechanisms

Document storage

Document DB or relational + JSONB Evidence: Interpretation

Most often: MongoDB or PostgreSQL + JSONB
Alternative: Couchbase / Firestore depending on the platform
Trade-off: Embedding simplifies reads, but increases duplication and the cost of updates.
Don't do by default: MongoDB just because the request contains JSON

Cache

In-memory KV Evidence: Interpretation

Most often: Redis
Alternative: Application cache / CDN / local cache
Trade-off: Cache invalidation, eviction, stampede and consistency.
Don't do by default: Treating the cache as the only copy of critical data

Full-text search

Search capability Evidence: Interpretation

Most often: PostgreSQL FTS → then OpenSearch/Elasticsearch/Typesense
Alternative: Meilisearch for a simpler application search engine
Trade-off: A search index is usually a derived projection.
Don't do by default: A search cluster as the primary DB without a reason

Analytics / BI

Columnar OLAP / warehouse Evidence: Interpretation

Most often: ClickHouse, BigQuery, Snowflake, Redshift
Alternative: DuckDB locally; PostgreSQL at small scale
Trade-off: Separate ingest, refresh/CDC and governance from OLTP.
Don't do by default: Heavy scans on the primary OLTP during peak hours

Logs

Log/search or columnar analytics Evidence: Interpretation

Most often: ClickHouse or OpenSearch depending on the query
Alternative: Object storage + query engine
Trade-off: Search relevance ≠ analytical aggregation. The choice depends on the dominant questions.
Don't do by default: Keeping a long log history in an expensive hot tier without a retention policy

Telemetry

Time-series / analytical ingest Evidence: Interpretation

Most often: TimescaleDB, InfluxDB, VictoriaMetrics, QuestDB, ClickHouse
Alternative: Cassandra/Scylla for a specific distributed write workload
Trade-off: Retention, rollups, cardinality tags and ingest rate matter more than the number of records alone.
Don't do by default: A TSDB just because the table has a timestamp

Graph traversal

Graph DB Evidence: Interpretation

Most often: Neo4j / Neptune / Memgraph
Alternative: PostgreSQL for shallow and simple relationships
Trade-off: A graph DB wins when traversal is the dominant query, not when relationships merely exist.
Don't do by default: A graph DB for ordinary CRM CRUD

Vector search

Vector capability Evidence: Interpretation

Most often: pgvector → OpenSearch vector → dedicated vector DB
Alternative: Qdrant / Milvus / Weaviate / Pinecone
Trade-off: Dimension, QPS, filters, recall, RAM and update rate determine the choice.
Don't do by default: A dedicated vector DB ‘because RAG’

RAG

Primary metadata + semantic index Evidence: Interpretation

Most often: PostgreSQL + pgvector + object storage
Alternative: Dedicated vector DB, when retrieval requires independent scaling
Trade-off: Permissions, chunking, re-embedding and the source document matter as much as ANN.
Don't do by default: Keeping all of the business logic only in a vector store

High write throughput

Write-optimised distributed store Evidence: Interpretation

Most often: Cassandra / ScyllaDB / DynamoDB (depending on the model)
Alternative: Sharded/partitioned relational, if the requirements call for it
Trade-off: Known access patterns, partition key design, compaction and consistency.
Don't do by default: A distributed store before measurement shows a problem

Global transactions

Distributed SQL Evidence: Interpretation

Most often: Spanner / CockroachDB / YugabyteDB
Alternative: Regional primary + async replicas, if the business tolerates it
Trade-off: Consensus and geographical distance cost latency.
Don't do by default: Active-active global writes without a business requirement

Local app

Embedded OLTP Evidence: Interpretation

Most often: SQLite
Alternative: Local-first sync layer + SQLite
Trade-off: Many concurrent writers and network sharing may require a server DB.
Don't do by default: A full cloud DB for purely local data

Embedded analytics

Embedded OLAP Evidence: Interpretation

Most often: DuckDB
Alternative: SQLite for app state; a cloud warehouse for central scale
Trade-off: Great locally, but multi-user governance requires a different architecture.
Don't do by default: DuckDB as the application's transactional backend

File storage

Object storage + metadata DB Evidence: Interpretation

Most often: S3-compatible object storage + PostgreSQL metadata
Alternative: Filesystem for small local scale
Trade-off: Lifecycle, versioning, egress, consistency metadata.
Don't do by default: Millions of large files as BLOBs in OLTP without a specific reason

05 Decision tree: which database should you choose?

Expand the questions. If several branches are true at once, you probably have several workloads — and you may need a primary database plus derived stores.

1.Should the data be local to a single application / device?

Local OLTP / app state
Recommendation: Default
SQLite. Zero server, ACID transactions, a very small operational burden.
Local analytics
Recommendation: Specialised
DuckDB. Columnar/vectorised OLAP, great scans over Parquet/CSV/DataFrame.

2.Shared backend: do you need relationships, constraints and transactions?

Yes → relational DB as the first candidate: PostgreSQL, SQL Server, MySQL, Oracle depending on the ecosystem and requirements.

If the answer is ‘partly’, still start from the invariants: what must change atomically, and where do you need referential integrity?

3.Is the natural unit of reading/writing an entire hierarchical document?

Yes → document DB becomes a candidate. The justification should follow from the aggregate model and the access patterns, not from the fact that the input is JSON.

4.Is the operation mainly an exact key → value lookup?

Yes → key-value store. Cache/session/hot state → Redis. Cloud-native, massive partitioned KV/document on AWS → DynamoDB. Coordination/config state → an etcd-like strongly consistent store.

5.Do relevance queries, stemming, faceting, autocomplete and fuzzy matching dominate?

Simple search can stay in PostgreSQL FTS. When search becomes a first-class feature → Elasticsearch/OpenSearch/Typesense/Meilisearch.

6.Do multi-hop relationships and traversals dominate?

Yes → graph database. Especially fraud rings, dependency graphs, IAM relationships, knowledge graphs and network analysis.

7.Do timestamp + measurements + time-window queries dominate?

Yes → time-series capability. Consider TimescaleDB, InfluxDB, QuestDB, VictoriaMetrics or ClickHouse depending on the kind of data and queries.

8.Does the query scan and aggregate hundreds of millions / billions of records?

Yes → columnar OLAP. ClickHouse for real-time analytics/logs, BigQuery/Snowflake/Redshift/Databricks for a cloud warehouse, DuckDB for embedded/local analytics.

9.Is similarity search over embeddings the main workload?

Start with pgvector if the embeddings are part of relational entities and the scale is moderate. Search + vectors → OpenSearch/Elasticsearch. A dedicated vector DB → when you need independent scaling, huge collections, high QPS, specialised filters/quantisation/multi-tenancy.

10.Do you really need distributed write scale or global transactional SQL?

Massive write-heavy + predictable access patterns → Cassandra/ScyllaDB/DynamoDB-like. Relational model + global/distributed transactions → Spanner/CockroachDB/YugabyteDB. Don't deploy it ‘just in case’.

06 Technology comparison

11 technologies

Database technologies compared
Type	Examples	Model	Best workload	Transactions	Scaling	Strengths	Weaknesses	ComplexityEvidence: Interpretation
Relational	PostgreSQL, SQL Server, MySQL	tables/relations	OLTP, business CRUD	strong	vertical + replicas + sharding	constraints, joins, tooling	harder distributed scale	Medium
Document	MongoDB, Couchbase	documents	hierarchical aggregates	yes	replicas + sharding	flexible document model	duplication, join-like workload less natural	Medium
Key-value	Redis, DynamoDB, etcd	key → value	lookup, cache, state	depends on the engine	very good	simple and fast access path	limited ad-hoc queries	Low–medium
Wide-column	Cassandra, ScyllaDB	partitioned wide rows	massive distributed writes	limited / model-specific	horizontal	availability, write throughput	model designed around the queries, compaction	High
Graph	Neo4j, Neptune	nodes/edges	traversal, fraud, networks	yes / it depends	scale depends on the product	natural traversals	unnecessary for ordinary relationships	Medium–high
Time-series	TimescaleDB, InfluxDB	timestamp + dimensions	metrics, telemetry	it depends	good	retention, time windows, compression	cardinality / ingest tuning	Medium
Search	OpenSearch, Elasticsearch	inverted index	text, relevance, facets	not like a classic RDBMS	cluster	search quality, hybrid search	operational cost, derived state	High
Columnar OLAP	ClickHouse, BigQuery, Snowflake	columnar	BI, scans, logs	not for classic OLTP	very good	compression, scans, aggregates	not natural for CRUD	Medium–high
Vector	Qdrant, Milvus, Weaviate	vectors + metadata	ANN / semantic retrieval	not a main feature	sharding / managed	vector-native features	another system to maintain	Medium–high
Distributed SQL	Spanner, CockroachDB, YugabyteDB	relational distributed	global transactional OLTP	strong	horizontal / multi-region	SQL + distributed guarantees	consensus latency + complexity	High
Embedded	SQLite, DuckDB	in-process	local app / local analytics	yes in SQLite	locally	zero DB server	concurrency / multi-user limitations	Low

Complexity is rated on five steps, from low to high; the rating is a judgement, not a measurement. Sorting by complexity follows that order.

07 Database vs database

PostgreSQL vs MongoDB

PostgreSQL
Relationships, constraints, transactions, ad-hoc SQL, operational reporting.
MongoDB
The document is the natural aggregate, the model is hierarchical, embedding simplifies the access pattern.

What not to conclude

JSON is not a sufficient argument for MongoDB; PostgreSQL has JSONB.

PostgreSQL vs ClickHouse

PostgreSQL
Short OLTP, CRUD, transactions.
ClickHouse
Large scans, aggregations, event/log analytics.

What not to conclude

These are not ‘two SQL databases for the same thing’. SQL is the interface; the workload is different.

PostgreSQL vs SQLite

PostgreSQL
Shared backend, many clients, concurrent writes.
SQLite
Local/embedded/mobile/desktop, a single file, minimal operational burden.

What not to conclude

SQLite is not just a toy; it has a different deployment model.

PostgreSQL vs DuckDB

PostgreSQL
The application's operational database.
DuckDB
Embedded analytical engine, Parquet/CSV/DataFrame, OLAP.

What not to conclude

DuckDB is not a replacement for a transactional backend.

PostgreSQL vs Cassandra

PostgreSQL
Relational model, transactions, flexible queries.
Cassandra
Massive distributed writes and known access patterns.

What not to conclude

‘Scale’ without a specific bottleneck does not justify Cassandra.

PostgreSQL vs Neo4j

PostgreSQL
Typical relationships and joins.
Neo4j
Graph traversal is the central problem.

What not to conclude

Relationships in the data ≠ automatically a graph database.

pgvector vs vector DB

PostgreSQL + pgvector
Embeddings are part of relational entities; you want one system.
Dedicated vector DB
Vector retrieval is the core workload and requires independent scaling/features.

What not to conclude

There is no single magic number of vectors at which you must migrate.

Postgres FTS vs OpenSearch

PostgreSQL FTS
Simple full-text, moderate scale, one system.
OpenSearch
Advanced relevance, faceting, analyzers, typo tolerance, search scale, hybrid search.

What not to conclude

A search engine is usually a projection, not a system of record.

ClickHouse vs BigQuery vs Snowflake

ClickHouse
Real-time analytics, logs, high ingest, control over the engine.
BigQuery / Snowflake
Managed cloud warehouse, separation of compute/storage, governance and enterprise analytics.

What not to conclude

The choice depends on ingest, latency, concurrency, the cloud ecosystem and the cost model.

08 OLTP vs OLAP

OLTP
Operations
Small, frequent queries. Point lookups. Inserts/updates. Transactions. High concurrency.
```
UPDATE inventory
SET qty = qty - 1
WHERE sku = 'ABC-42';
```
Typical: PostgreSQL, SQL Server, MySQL.
OLAP
Analysis
Large scans. Aggregations. Columnar storage. Compression. Parallelism.
```
SELECT country, SUM(revenue)
FROM sales
WHERE date >= '2026-01-01'
GROUP BY country;
```
Typical: ClickHouse, BigQuery, Snowflake, DuckDB.

The same shop, two different workloads

PostgreSQL / OLTP
- orders
- inventory
- customer
- payments
- constraints and transactions
ClickHouse / Warehouse / OLAP
- sales by day
- revenue by country
- cohort analysis
- product performance
- billions of events

Anti-pattern

Overloading the operational PostgreSQL with heavy BI scans when they compete for CPU/I/O with critical application traffic. The solution may be a read replica, materialised views or a separate OLAP layer — depending on the scale.

09 Source of truth / system of record

The most important question in polyglot persistence: which system holds the canonical fact? A cache and a search index can be fast, but they are often derived, rebuildable data.

Component roles

PostgreSQLSource of truth

Rediscache
OpenSearchsearch index
ClickHouseanalytics
Object Storagefiles / raw
Vector DBsemantic index
Kafkaevents

Roles of the components in the diagram
Component	Role	Source of truth?	If lost	Rebuild
PostgreSQL	Authoritative transactional state	Yes	Loss without a proper backup/DR is a critical incident.	Not always — this is the primary truth.
Redis	Cache / hot state	No	Cache miss / degraded latency.	Yes, from the primary DB.
OpenSearch	Searchable projection	No	Search may be temporarily unavailable.	Yes, through a reindex.
ClickHouse	Analytical copy	No	Dashboards/analytics may be incomplete.	Yes, through CDC / replay.
Object storage	Blobs, files, raw datasets	Often yes, for the objects themselves	Losing objects is a permanent loss of files.	Depending on the backup/replication policy.
Vector store	Semantic index / embeddings	Usually not	RAG may lose retrieval.	Yes, from the documents + re-embedding.
Kafka	Event transport / replayable log	It depends	Consumers lose their replay source if the retention period has passed.	Partly — depends on event sourcing and retention.

10 Polyglot persistence

A large system can use several engines, because each workload has a different optimum. This is not free, however.

PostgreSQL
Role: Core
Transactional truth: users, orders, billing, permissions.
Redis
Role: Derived
Cache, sessions, counters, hot state. Rebuildable, if it is only a cache.
OpenSearch
Role: Derived
Search projection, relevance, facets, text + vector/hybrid search.
ClickHouse
Role: Derived
Event analytics, dashboards, log/telemetry queries.
Kafka
Role: Pipeline
Decoupling of producers and consumers, replay, event streaming.
S3 / object storage
Role: Storage
Files, media, Parquet, models, raw datasets, backups.

Cost

Every engine needs its own monitoring, backup/recovery, capacity planning, upgrades, secrets, security, observability and team skills. Polyglot persistence makes sense only when the workload benefit outweighs this cost.

11 Architecture evolution: specialise only after a trigger

Stage 1
PostgreSQL
Standard backend. Trigger for Redis: repeated hot reads / sessions / counters / expensive computations that need a cache.
Stage 2
PostgreSQL + Redis
Trigger for search engine: relevance, stemming, typo tolerance, faceting or search scale stop being comfortable in FTS.
Stage 3
+ Search
Trigger for OLAP: heavy scans, BI and aggregations compete with OLTP for resources.
Stage 4
+ ClickHouse / Warehouse
Trigger for streaming: many independent consumers, replay, event-driven integrations, large ingest.
Stage 5
+ Kafka / streaming
Trigger for distributed primary: single-primary, geography or write scale become a measurable constraint.
Stage 6
Distributed SQL / sharding / specialised stores
This is not the ‘final level’. It is an answer to specific constraints: global writes, data locality, throughput, availability or regulatory topology.

12 You probably don't need this yet

Cassandra
Recommendation: Premature complexity (Premature)
Why it's tempting
It sounds like a ‘Big Data’ solution.
Cost
Partition keys, compaction, consistency trade-offs, distributed ops.
Real trigger
Sustained distributed write throughput / HA problem.
Simpler default
PostgreSQL + partitioning/replicas.
Kafka
Recommendation: Premature complexity (Premature)
Why it's tempting
‘Event-driven’ sounds modern.
Cost
Brokers, partitions, consumer lag, schema evolution, retention.
Real trigger
Many independent consumers, replay, decoupling.
Simpler default
DB outbox + a queue / a simpler broker.
Elasticsearch / OpenSearch
Recommendation: Premature complexity (Premature)
Why it's tempting
Search is common.
Cost
Cluster ops, reindex, mappings, shard planning.
Real trigger
Postgres FTS doesn't meet the relevance/search SLO.
Simpler default
PostgreSQL FTS / Typesense/Meilisearch depending on needs.
Neo4j
Recommendation: Premature complexity (Premature)
Why it's tempting
The data has relationships.
Cost
A new model, query language, synchronisation with the truth.
Real trigger
Deep/multi-hop traversals dominate the workload.
Simpler default
Relational schema + good indexes.
Dedicated vector DB
Recommendation: Premature complexity (Premature)
Why it's tempting
The project uses LLM/RAG.
Cost
Re-embedding pipelines, extra infra, sync, shard/index tuning.
Real trigger
Vector QPS/scale/features are a real bottleneck.
Simpler default
pgvector or vector search in the existing engine.
Distributed SQL
Recommendation: Premature complexity (Premature)
Why it's tempting
‘Global scale’.
Cost
Consensus latency, topology, cross-region costs.
Real trigger
Single-primary/geography limits the required SLO.
Simpler default
Regional RDBMS + replicas.
Sharding
Recommendation: Premature complexity (Premature)
Why it's tempting
‘We are going to have lots of data’.
Cost
Routing, rebalance, cross-shard joins/tx.
Real trigger
Vertical scaling / replicas / partitioning have stopped being enough.
Simpler default
Scale-up, indexing, partitioning.
Kubernetes
Recommendation: Premature complexity (Premature)
Why it's tempting
‘Production-grade’.
Cost
Platform team, networking, observability, upgrades.
Real trigger
You have many services, a need for orchestration and a team to run it.
Simpler default
Managed PaaS / containers without K8s.
Multi-region active-active
Recommendation: Premature complexity (Premature)
Why it's tempting
Global users.
Cost
Conflict resolution or synchronous latency, DR complexity.
Real trigger
Local writes in many regions are a business requirement.
Simpler default
Single writer + read replicas/CDN.

13 Common anti-patterns

Anti-pattern
MongoDB because JSON
Why it's tempting
Modern APIs return JSON.
Where it goes wrong
The transport format does not determine the persistence model.
When it may make sense
When the document is the natural aggregate and embedding simplifies the access pattern.
Safer default
PostgreSQL + JSONB or a classic relational model.
Anti-pattern
NoSQL because scalability
Why it's tempting
NoSQL is associated with hyperscale.
Where it goes wrong
SQL can scale very far; name the limit first.
When it may make sense
When a specific NoSQL model fits the measured workload.
Safer default
RDBMS + indexes + replicas + partitioning.
Anti-pattern
Cassandra because Big Data
Why it's tempting
It sounds like a solution for a large number of records.
Where it goes wrong
Storage size ≠ a distributed write problem.
When it may make sense
Massive ingest + HA + predictable queries.
Safer default
PostgreSQL/ClickHouse depending on the query.
Anti-pattern
Graph DB because relationships
Why it's tempting
Relationships exist everywhere.
Where it goes wrong
A CRM with FKs doesn't need a graph engine.
When it may make sense
Multi-hop traversal is the main query.
Safer default
RDBMS and joins.
Anti-pattern
Vector DB because AI
Why it's tempting
RAG is associated with vector DBs.
Where it goes wrong
It is another system and pipeline to maintain.
When it may make sense
The vector workload requires separate scaling/features.
Safer default
pgvector / existing search engine.
Anti-pattern
Elasticsearch as the source of truth
Why it's tempting
Search has a great API and queries.
Where it goes wrong
A search index is often a projection; recovery and constraints are different.
When it may make sense
Only if you consciously accept its semantics as a primary store.
Safer default
RDBMS as the SoR + a search index.
Anti-pattern
Redis without persistence design
Why it's tempting
It is extremely fast.
Where it goes wrong
Eviction/persistence/replication/RPO tend to be overlooked.
When it may make sense
When the durability plan is explicit or the data is rebuildable.
Safer default
Redis as a cache + a durable primary.
Anti-pattern
PostgreSQL for absolutely everything
Why it's tempting
It is remarkably versatile.
Where it goes wrong
Heavy OLAP/search/vector/telemetry may have a different optimum.
When it may make sense
If the extensions meet the SLO and simplify operations.
Safer default
Add specialisation only after measurement.
Anti-pattern
Sharding too early
Why it's tempting
Migrating later seems frightening.
Where it goes wrong
You pay in complexity now for a future problem.
When it may make sense
When there is a measurable capacity ceiling.
Safer default
Scale-up, partitioning, replicas.
Anti-pattern
Microservices = DB per service
Why it's tempting
Service autonomy is attractive.
Where it goes wrong
You can end up with distributed transactions and a reporting nightmare.
When it may make sense
When bounded contexts genuinely require independent ownership.
Safer default
Logical separation of data without an excess of engines.
Anti-pattern
Benchmark says X is fastest
Why it's tempting
A single number is easy to compare.
Where it goes wrong
A benchmark without your workload tells you little.
When it may make sense
When the test reproduces the query mix, dataset, durability and p99.
Safer default
A benchmark of your own workload.

14 Indexing visualiser

Sometimes the ‘wrong database’ is simply a good database with the wrong index, schema or query plan.

B-tree

Queries: =, <, >, BETWEEN, ORDER BY
Where: RDBMS

General-purpose balanced tree; a great default for selective lookups/range queries.

Hash

Queries: Equality (=)
Where: RDBMS / KV internals

Specialised for exact matching; does not naturally provide range ordering.

hash(k)
bucket 3
→
row

GIN / inverted

Queries: Membership / full-text / arrays / JSONB
Where: PostgreSQL / search engines

Key → posting list. Very good when one record contains many indexed tokens/values.

database→A,B
vector→C
sql→A
search→B,C

GiST

Queries: Ranges / geometry / custom metrics
Where: PostGIS / PostgreSQL

An index framework for structures that an ordinary B-tree does not describe.

region A
region B
overlap
nearest

BRIN

Queries: Huge tables correlated with physical order
Where: PostgreSQL

Stores a summary for blocks; a very small index, great e.g. for append-by-time.

block 1
min/max
block 2
min/max

LSM tree

Queries: Write-heavy storage
Where: Cassandra, RocksDB

Writes go to memory + immutable sorted files; the cost shifts to compaction/read amplification.

memtable
SST1
SST2
compact

HNSW

Queries: Approximate nearest neighbour
Where: Vector search

A multi-level ANN graph: a recall ↔ memory/build/search cost trade-off.

L2 graph
L1 graph
L0 graph
nearest

Column skipping / sparse index

Queries: Large analytical scans
Where: ClickHouse-like OLAP

The sort key and sparse metadata let you skip large ranges of data.

part A
part B
skip
scan

In practice

Before you change the engine, check the query plan, cardinality estimates, indexes, N+1, payload size, working set, partition pruning and contention for I/O.

15 CAP and PACELC in practice

CAP does not mean ‘pick any 2 of 3’ in normal operation. It is about what you do during a network partition.

Partition

Consistency

You can reject / delay some operations so as not to return an inconsistent state.

Partition

Availability

You can keep responding, but allow temporary divergence between the copies.

PACELC: and when there is no partition?

Else

Latency

A local write can return sooner if it does not wait for distant regions.

Else

Consistency

Synchronous coordination / consensus gives stronger guarantees at the cost of latency.

Example

London → New York → Singapore. A global synchronous commit may have higher latency for physical reasons. Eventual replication may be faster locally, but for some time the regions may see different states.

16 Normalisation vs denormalisation

Normalised OLTP
Role: Source of truth
```
Customers(id, name)
Orders(id, customer_id)
OrderItems(order_id, product_id, qty)
Products(id, price)
```
Less duplication, referential integrity, one canonical representation of a fact.
Denormalised read model
Role: Derived
```
DailySales(
  date,
  country,
  category,
  revenue,
  orders
)
```
More storage and harder synchronisation in exchange for faster reads / simpler BI.

Three valid strategies

Normalisation
When correctness, updates and constraints matter more than minimising the cost of reads.
Materialised / analytical view
When it is worth pre-aggregating or preparing a read model. You have to define the refresh/CDC and the source of truth.
Document embedding
When the data belongs to one aggregate and is most often read together. Cost: duplication / larger documents / updates.

17 Architect Mode: design your own system

Fill in the form. The result is described as an architecture starting point, because real design needs data about the workload, tests and observation of production.

Architect Mode

How it works

Architect Mode applies fixed rules taken from the original handbook. A core database is chosen first: SQLite (plus DuckDB for event data) for local apps; MongoDB or PostgreSQL + JSONB for documents without critical transactions; PostgreSQL + TimescaleDB, or ClickHouse at massive scale, for time-series; PostgreSQL as the truth plus a graph DB for graphs; ClickHouse for events with heavy analytics; otherwise PostgreSQL. Multi-region data with critical transactions at massive scale switches the core to distributed SQL. Search, analytics, vector, file and streaming needs each add one component, and massive event data whose consistency is not strong (mixed or eventual) adds a wide-column option to consider. These are interpretations, not measurements.

The rules are kept exactly as the original applied them, including these behaviours:

‘Multi-region / global’ matters only for rule R6; otherwise it behaves like ‘Shared backend’.
For local deployments, scale does not change the core card (SQLite, plus DuckDB for event data); massive event data whose consistency is not strong still adds the wide-column option to consider.
‘Useful’ and ‘Not very important’ transactions give the same result, as do ‘Mixed’ and ‘Eventual is acceptable’ consistency, which only affect the wide-column option to consider.
With event data and heavy analytics, ClickHouse appears in both the core card and the analytics card.
Local deployments still receive the server-side add-ons when those options are chosen.
With the default choices, the result is a single PostgreSQL card.

Core database: one card, always first. The rules are checked from the top and the first match wins; R6 is checked last and replaces that result. Every core card is a default.

Core database rules
Rule	When	Core database
R1	Locality: Local / desktop / mobile Dominant data type: Events / logs	SQLite + optionally DuckDB
R1	Locality: Local / desktop / mobile	SQLite
R2	Dominant data type: Documents / hierarchical Transactions and constraints: not Critical	MongoDB or PostgreSQL + JSONB
R3	Dominant data type: Time-series / telemetry Scale / throughput: Massive / distributed	ClickHouse / specialised TSDB
R3	Dominant data type: Time-series / telemetry	PostgreSQL + TimescaleDB / TSDB
R4	Dominant data type: Graph / multi-hop relationships	PostgreSQL as the truth + a graph DB if traversal is core
R5	Dominant data type: Events / logs Analytics: Heavy scans / billions of records	ClickHouse + durable metadata DB
R0	Any other combination	PostgreSQL
R6Override	Locality: Multi-region / global Transactions and constraints: Critical Scale / throughput: Massive / distributed	Distributed SQL (e.g. Spanner/Cockroach/Yugabyte)

Add-on cards follow the core card, always in this order: search, analytics, vector, files, streaming, consider. Each of them adds at most one card.

Add-on rules
Card	When	Adds	Tier
Search	Full-text search: Advanced / relevance	OpenSearch / Elasticsearch	Recommendation: Specialised
Search	Full-text search: Simple	FTS in the core DB	Recommendation: Specialised
Analytics	Analytics: Heavy scans / billions of records	ClickHouse / BigQuery / Snowflake	Recommendation: Specialised
Analytics	Analytics: Dashboards / BI	Read replica / materialised views → then a warehouse	Recommendation: Specialised
Vector	Vector / semantic search: Yes, moderately	pgvector / the existing search engine	Recommendation: Specialised
Vector	Vector / semantic search: Yes, core workload	Dedicated vector DB	Recommendation: Specialised
Files	Large files / media: Yes	Object storage	Recommendation: Specialised
Streaming	Streaming / replay: Yes, many consumers / replay	Kafka-like platform	Recommendation: Specialised
Consider	Scale / throughput: Massive / distributed Dominant data type: Events / logs Consistency: not Strong / correctness first	Cassandra / Scylla / DynamoDB-like	Recommendation: Premature

Architect Mode needs JavaScript; the rules it applies are listed below, and the 30 scenarios show worked examples.

Core database: one card, always first. The rules are checked from the top and the first match wins; R6 is checked last and replaces that result. Every core card is a default.

Core database rules
Rule	When	Core database
R1	Locality: Local / desktop / mobile Dominant data type: Events / logs	SQLite + optionally DuckDB
R1	Locality: Local / desktop / mobile	SQLite
R2	Dominant data type: Documents / hierarchical Transactions and constraints: not Critical	MongoDB or PostgreSQL + JSONB
R3	Dominant data type: Time-series / telemetry Scale / throughput: Massive / distributed	ClickHouse / specialised TSDB
R3	Dominant data type: Time-series / telemetry	PostgreSQL + TimescaleDB / TSDB
R4	Dominant data type: Graph / multi-hop relationships	PostgreSQL as the truth + a graph DB if traversal is core
R5	Dominant data type: Events / logs Analytics: Heavy scans / billions of records	ClickHouse + durable metadata DB
R0	Any other combination	PostgreSQL
R6Override	Locality: Multi-region / global Transactions and constraints: Critical Scale / throughput: Massive / distributed	Distributed SQL (e.g. Spanner/Cockroach/Yugabyte)

Add-on cards follow the core card, always in this order: search, analytics, vector, files, streaming, consider. Each of them adds at most one card.

Add-on rules
Card	When	Adds	Tier
Search	Full-text search: Advanced / relevance	OpenSearch / Elasticsearch	Recommendation: Specialised
Search	Full-text search: Simple	FTS in the core DB	Recommendation: Specialised
Analytics	Analytics: Heavy scans / billions of records	ClickHouse / BigQuery / Snowflake	Recommendation: Specialised
Analytics	Analytics: Dashboards / BI	Read replica / materialised views → then a warehouse	Recommendation: Specialised
Vector	Vector / semantic search: Yes, moderately	pgvector / the existing search engine	Recommendation: Specialised
Vector	Vector / semantic search: Yes, core workload	Dedicated vector DB	Recommendation: Specialised
Files	Large files / media: Yes	Object storage	Recommendation: Specialised
Streaming	Streaming / replay: Yes, many consumers / replay	Kafka-like platform	Recommendation: Specialised
Consider	Scale / throughput: Massive / distributed Dominant data type: Events / logs Consistency: not Strong / correctness first	Cassandra / Scylla / DynamoDB-like	Recommendation: Premature

18 30 practical scenarios

Every scenario is a judgement, not a measurement: Evidence: Interpretation

30 scenarios

Blog
Primary DB
PostgreSQL or SQLite
Secondary storage
Object storage for media
Cache
optional CDN
Search
FTS, optional
Analytics
none or light
Why
Simple content + metadata.
Probably not
Cassandra / graph DB
CMS
Primary DB
PostgreSQL
Secondary storage
Object storage
Cache
Redis, optional
Search
Postgres FTS / search engine
Analytics
warehouse later
Why
Relational core + JSONB for flexible fields.
Probably not
A distributed DB without a need
Small SaaS
Primary DB
PostgreSQL
Secondary storage
Object storage
Cache
Redis only after a trigger
Search
FTS, optional
Analytics
read replica / warehouse later
Why
The simplest solid default.
Probably not
Polyglot from day one
Large SaaS
Primary DB
PostgreSQL / managed RDBMS
Secondary storage
Object storage
Cache
Redis
Search
OpenSearch
Analytics
ClickHouse/warehouse
Why
Specialisation according to the bottlenecks.
Probably not
One database for everything
E-commerce
Primary DB
PostgreSQL
Secondary storage
Object storage
Cache
Redis
Search
OpenSearch/Elastic
Analytics
warehouse
Why
Orders/payments require transactions; search is separate.
Probably not
A search engine as the truth
Banking
Primary DB
Enterprise RDBMS / distributed SQL as required
Secondary storage
Immutable/audit storage
Cache
controlled
Search
separate
Analytics
warehouse
Why
Correctness, audit, HA, RPO/RTO.
Probably not
An eventual store without guardrails
Fintech
Primary DB
PostgreSQL / SQL Server / equivalent
Secondary storage
Object storage
Cache
Redis, with care
Search
separate
Analytics
warehouse
Why
Ledger/transactions + event/audit pipelines.
Probably not
A cache as the source of truth
Social network
Primary DB
Relational/KV mix
Secondary storage
Object storage
Cache
Redis
Search
search engine
Analytics
OLAP
Why
The model depends on the scale of the feeds and the graph.
Probably not
Neo4j just because there are friend connections
Messaging
Primary DB
Relational/KV/distributed store
Secondary storage
Object storage
Cache
Redis
Search
search, optional
Analytics
event analytics
Why
Append-heavy, ordering and delivery semantics.
Probably not
One huge table without a partition plan
IoT
Primary DB
TSDB / ClickHouse / wide-column
Secondary storage
Object storage
Cache
optional cache
Search
none
Analytics
OLAP
Why
Ingest + time windows + retention.
Probably not
A classic CRUD schema for everything
Monitoring
Primary DB
VictoriaMetrics / Timescale / Influx-like
Secondary storage
Object storage
Cache
none
Search
none
Analytics
dashboard layer
Why
Metrics, labels, retention, rollups.
Probably not
Graph DB
Log aggregation
Primary DB
ClickHouse or OpenSearch
Secondary storage
Object storage
Cache
none
Search
OpenSearch if search-heavy
Analytics
ClickHouse if analytics-heavy
Why
The choice depends on search vs aggregation.
Probably not
An OLTP DB as a log archive
BI dashboard
Primary DB
Warehouse / OLAP
Secondary storage
Object storage
Cache
semantic cache
Search
none
Analytics
core workload
Why
Aggregations and BI concurrency.
Probably not
The primary OLTP as heavy BI
Data warehouse
Primary DB
BigQuery/Snowflake/Redshift/Databricks
Secondary storage
Object storage
Cache
none
Search
none
Analytics
core workload
Why
Governance, scans, separation of compute/storage.
Probably not
SQLite/Postgres for PB-scale
Recommendation
Primary DB
PostgreSQL + feature data
Secondary storage
Object storage
Cache
Redis
Search
vector/search, optional
Analytics
warehouse
Why
Retrieval + ranking + offline features.
Probably not
A vector DB as the whole system
Fraud detection
Primary DB
PostgreSQL / ledger DB
Secondary storage
Object storage
Cache
stream state
Search
graph/search
Analytics
warehouse
Why
Canonical tx + graph/event derived models.
Probably not
Neo4j as the only transaction ledger
Knowledge graph
Primary DB
Graph DB
Secondary storage
Object storage
Cache
none
Search
vector/search add-ons
Analytics
analytics, optional
Why
Relationships and traversals are core.
Probably not
A plain KV
RAG
Primary DB
PostgreSQL + pgvector
Secondary storage
Object storage
Cache
Redis, optional
Search
hybrid search, optional
Analytics
usage analytics
Why
Metadata, permissions and embeddings together.
Probably not
A dedicated vector DB without a trigger
Chatbot history
Primary DB
PostgreSQL
Secondary storage
Object storage
Cache
Redis for sessions
Search
vector index, optional
Analytics
warehouse
Why
Conversation history is ordinary application data.
Probably not
A vector store as the only storage
AI agent platform
Primary DB
PostgreSQL
Secondary storage
Object storage
Cache
Redis
Search
vector/search
Analytics
ClickHouse/warehouse
Why
Durable state + ephemeral state + artefacts + traces.
Probably not
A single ‘AI database’ for everything
CRM
Primary DB
PostgreSQL / SQL Server
Secondary storage
Object storage
Cache
optional
Search
search, optional
Analytics
warehouse
Why
A strongly relational business model.
Probably not
A graph DB as the default
ERP
Primary DB
RDBMS
Secondary storage
Object storage
Cache
controlled
Search
search
Analytics
warehouse
Why
Constraints, accounting, workflows.
Probably not
NoSQL without a clear need
Gaming backend
Primary DB
Relational/KV mix
Secondary storage
Object storage
Cache
Redis
Search
none
Analytics
analytics
Why
Durable account state + hot realtime state.
Probably not
Redis-only without a persistence plan
Geospatial app
Primary DB
PostgreSQL + PostGIS
Secondary storage
Object storage/tiles
Cache
tile cache
Search
geo search
Analytics
OLAP, optional
Why
Spatial indexes and joins.
Probably not
Hand-rolled geo in plain strings
Telemetry platform
Primary DB
Timescale/ClickHouse/TSDB
Secondary storage
Object storage
Cache
none
Search
none
Analytics
core
Why
Append + time analytics + retention.
Probably not
An RDBMS without a partition/retention plan
High-frequency events
Primary DB
Cassandra/Scylla/Kafka/ClickHouse depending on the query
Secondary storage
Object storage
Cache
none
Search
none
Analytics
ClickHouse/warehouse
Why
Ingest semantics and access patterns first.
Probably not
A product chosen only on the strength of a marketing benchmark
Mobile offline-first
Primary DB
SQLite
Secondary storage
sync backend
Cache
local cache implicit
Search
local search
Analytics
none
Why
Local data, then sync.
Probably not
A cloud DB on every click
Desktop / local-first
Primary DB
SQLite
Secondary storage
filesystem/object sync
Cache
none
Search
FTS locally
Analytics
DuckDB, optional
Why
Embedded deployment.
Probably not
A DB server without a need
Millions of files
Primary DB
PostgreSQL metadata
Secondary storage
Object storage
Cache
CDN/cache
Search
metadata search
Analytics
warehouse, optional
Why
The DB holds metadata and references, not necessarily the blob.
Probably not
All blobs in OLTP without a reason
Billion-row analytics
Primary DB
ClickHouse/BigQuery/Snowflake
Secondary storage
Object storage
Cache
result cache
Search
none
Analytics
core
Why
Columnar storage and scan efficiency.
Probably not
Standard OLTP as the primary analytical engine

20 Final principle

Use the simplest database that reliably solves the actual problem.

01Problem
02Requirements
03Access patterns
04Consistency / Tx
05Measure
06Specialise

Database Selection Guide 2026
An interactive reference. Workload and guarantees first, product second.

21 Database selection cheat sheet 2026

Standard SaaS: PostgreSQL
Local app: SQLite
Embedded analytics: DuckDB
Cache / sessions: Redis
Advanced full-text: OpenSearch / Elasticsearch
Heavy analytics: ClickHouse / BigQuery / Snowflake
Graph traversal: Neo4j / graph DB
Vectors: pgvector first → dedicated DB if justified
Files / media: Object storage
Event streaming: Kafka-like platform
Geospatial: PostgreSQL + PostGIS
Time-series: Timescale / TSDB
Massive distributed writes: Cassandra / ScyllaDB
Global transactional SQL: Distributed SQL if required

22 Glossary

A

ACID: Atomicity, Consistency, Isolation, Durability — the classic properties of transactions.
ANN: Approximate Nearest Neighbour — approximate search for the nearest vectors.

B

B-tree: A balanced index tree for equality/range/order queries.

C

CAP: The trade-off between consistency and availability during a network partition.
CDC: Change Data Capture — propagating changes from the primary store to other systems.
Consistency: Guarantees about which state of the data different clients can see.

D

Data lake: Large, flexible storage for raw/semi-structured data.

E

Eventual consistency: Copies may differ temporarily, but they tend to converge when there are no new changes.

H

HNSW: Graph-based ANN index: fast search at the cost of memory/build complexity.
HTAP: Hybrid Transactional/Analytical Processing — OLTP and OLAP in a single or closely integrated platform.

I

Inverted index: Token/term → list of documents; the basis of many search engines.

L

Lakehouse: Lake storage combined with the table/transaction/governance capabilities of a warehouse.
LSM: Log-Structured Merge Tree — write-optimised storage with a memtable, SSTables and compaction.

O

OLAP: Online Analytical Processing — scans, aggregations and analytics over large datasets.
OLTP: Online Transaction Processing — short, frequent transactional operations.

P

PACELC: If Partition: A/C; Else: Latency/Consistency — extends the thinking beyond the network failure itself.
Partitioning: Logical/physical division of the data into parts.

R

Replication: Keeping copies of the data on many nodes/regions.
RPO: Recovery Point Objective — the maximum amount of data that may be lost.
RTO: Recovery Time Objective — how long restoring the service may take.

S

Sharding: Splitting the data across independent shards, usually for scale-out.
Source of truth: The canonical place that defines the current business fact.
System of record: The system responsible for the authoritative record of specific data.

V

Vector embedding: A numerical representation of an object/text in a vector space.

W

WAL: Write-Ahead Log — writing changes to a log before the final write to the main structures.

23 Sources and further reading

The links below lead mainly to the projects’ official documentation. The page works without loading them; they are only links for verification and further reading.

PostgreSQL Documentation
Official documentationPublisher: PostgreSQL Global Development GroupAccessed 3 Oct 2026Evidence: Verified fact
Transactions, constraints, JSONB, the index types (B-tree, Hash, GiST, SP-GiST, GIN, BRIN) and full-text search. The current link follows new releases; it showed PostgreSQL 18.6 on the access date.
SQLite — Appropriate Uses
Official documentationPublisher: SQLiteAccessed 3 Oct 2026Evidence: Verified fact
Where SQLite fits; the page recommends a client/server database for high concurrency and notes that SQLite allows a single writer at a time.
DuckDB — Why DuckDB
Official documentationPublisher: DuckDBAccessed 3 Oct 2026Evidence: Industry observation
In-process analytics and Parquet are confirmed on the page; CSV and DataFrame support were not found in its static text.
Redis Persistence
Official documentationPublisher: RedisAccessed 3 Oct 2026Evidence: Verified fact
The RDB and AOF persistence options. This link now redirects to the same page under the ‘Operate’ section of the Redis docs.
MongoDB Data Modeling
Official documentationPublisher: MongoDBAccessed 3 Oct 2026Evidence: Verified fact
Embedding versus references in a document data model.
Apache Cassandra Architecture
Official documentationPublisher: Apache Software FoundationAccessed 3 Oct 2026Evidence: Verified fact
The storage engine: memtable, SSTables and compaction.
ClickHouse Documentation
Official documentationPublisher: ClickHouseAccessed 3 Oct 2026Evidence: Verified fact
Columnar OLAP and sparse primary indexes (granules).
OpenSearch Documentation
Official documentationPublisher: OpenSearch ProjectAccessed 3 Oct 2026Evidence: Verified fact
Search, including vector and hybrid search.
Neo4j Documentation
Official documentationPublisher: Neo4jAccessed 3 Oct 2026Evidence: Industry observation
Graph database documentation; the link was checked, the claims about graph databases were not checked against it one by one.
pgvector
Official documentationPublisher: pgvector projectAccessed 3 Oct 2026Evidence: Verified fact
The project README: vector similarity search for Postgres, with HNSW (a multilayer graph) and IVFFlat indexes.
Qdrant Documentation
Official documentationPublisher: QdrantAccessed 3 Oct 2026Evidence: Industry observation
Dedicated vector database documentation; the link was checked, the claims about vector databases were not checked against it one by one.
Amazon DynamoDB Developer Guide
Official documentationPublisher: Amazon Web ServicesAccessed 3 Oct 2026Evidence: Verified fact
The key-value and document data models.
Google Cloud Spanner Documentation
Official documentationPublisher: Google CloudAccessed 3 Oct 2026Evidence: Verified fact
Globally distributed transactional SQL with external consistency (TrueTime). This link now redirects to the Spanner documentation on docs.cloud.google.com.
Apache Kafka Documentation
Official documentationPublisher: Apache Software FoundationAccessed 3 Oct 2026Evidence: Verified fact
Events are retained for a configured period, not deleted when they are consumed. This link opens a redirect page that needs JavaScript to reach the versioned documentation (under /43/).
PostGIS Documentation
Official documentationPublisher: PostGIS ProjectAccessed 3 Oct 2026Evidence: Verified fact
Spatial indexes built on GiST.

Methodology

Conversion. This edition was converted from a single-file interactive handbook written in Polish. The prose was translated into British English without summarising; names, numbers and versions are unchanged. The handbook’s built-in data and repeated blocks became datasets with named fields (the takeaways, database types, workloads, decision tree, technology table, comparisons, system roles, polyglot stack, evolution stages, ‘you probably don’t need this yet’ items, anti-patterns, index types, Architect Mode fields and outputs, scenarios, checklist, process flows, cheat sheet and glossary), and automated tests check every count against the original. Two sections moved: Architect Mode now comes directly before the 30 scenarios, which are worked examples of what it produces, and the untitled final principle became its own section before the reference material. Links into the original handbook still resolve.

Links. Every source link was checked on 3 Oct 2026. All of them work; three redirect (Redis Persistence, Google Cloud Spanner Documentation and Apache Kafka Documentation). The original addresses are kept, and the notes give the current targets.

Evidence. The handbook cites no sources in its text and gives no evidence labels. Selected statements were checked against the official documentation listed above on 3 Oct 2026, and none was changed. Recommendations, tiers, scenarios, comparisons and tool results are labelled interpretation; descriptions of technologies and mechanisms are observation unless a source above confirms them. The categorisation of individual products and heuristic thresholds such as ‘hundreds of millions / billions of records’ stay unverified judgements.

Architect Mode. The generator applies the original handbook’s fixed rules unchanged: a core database chosen from the data type, locality, transactions, scale and analytics, then one component for each search, analytics, vector, file and streaming need, and a wide-column option to consider for massive event data whose consistency is not strong (mixed or eventual). The rules are listed under ‘How it works’ in the tool, and automated tests compare the port with the original for all 43,740 combinations of answers.

Changelog

2026-10 — first published on Nexate, translated and restructured from the original handbook.

How to use this guide

How the tools work

Keyboard and links

Evidence, tiers and roles

01 Start

02 TL;DR: if you only remember 10 things

PostgreSQL is a very good default.

SQLite wins on locality.

DuckDB = embedded analytics.

Redis is not a ‘faster PostgreSQL’.

A search engine works on a different model.

A graph DB requires a graph workload.

A vector DB is not mandatory for AI.

OLTP ≠ OLAP.

Cassandra solves a specific scale problem.

Operational complexity is a cost of the architecture.

03 Interactive map of database types

Relational / RDBMS

Document

Key-value

Wide-column

Graph

Time-series

Search engine

Columnar OLAP

Vector

Distributed SQL

Embedded

In-memory

Multi-model

04 What problem are you solving?

Standard CRUD

Transactions

Document storage

Cache

Full-text search

Analytics / BI

Logs

Telemetry

Graph traversal

Vector search

RAG

High write throughput

Global transactions

Local app

Embedded analytics

File storage

05 Decision tree: which database should you choose?

Local OLTP / app state

Local analytics

06 Technology comparison

07 Database vs database

PostgreSQL vs MongoDB

PostgreSQL

MongoDB

PostgreSQL vs ClickHouse

PostgreSQL

ClickHouse

PostgreSQL vs SQLite

PostgreSQL

SQLite

PostgreSQL vs DuckDB

PostgreSQL

DuckDB

PostgreSQL vs Cassandra

PostgreSQL

Cassandra

PostgreSQL vs Neo4j

PostgreSQL

Neo4j

pgvector vs vector DB

PostgreSQL + pgvector

Dedicated vector DB

Postgres FTS vs OpenSearch

PostgreSQL FTS

OpenSearch

ClickHouse vs BigQuery vs Snowflake

ClickHouse

BigQuery / Snowflake

08 OLTP vs OLAP