Choosing a Database Cheat Sheet
12 groups · 44 topics · Updated
Start here
Four questions that narrow the field before any names come up.
Pick by the shape of the data
| Your data | Family |
|---|---|
| Rows that relate to rows | Relational |
| Nested records, varying fields | Document |
| One value per known key | Key–value |
| Huge, written constantly | Wide-column |
| Text people search by words | Search |
| Meaning, not keywords | Vector |
| Connections that matter most | Graph |
| Readings stamped with a time | Time-series |
| Billions of rows to aggregate | Column store |
Pick by the access pattern
Do I know the key? key-value
Do I filter many fields? relational
Do I follow links deeply? graph
Do I aggregate millions? column store
Do I match on meaning? vector- The question you ask most often should be the cheapest one to answer.
Sensible defaults
| Situation | Start with |
|---|---|
| A normal web app | PostgreSQL |
| One file, one process | SQLite |
| Cache, queue, rate limit | Redis or Valkey |
| Nested records, varying shape | MongoDB |
| Search by words | Your database, then Elastic |
| Search by meaning | pgvector or Atlas |
| Dashboards over billions of rows | ClickHouse |
| Metrics and alerts | Prometheus |
- If you genuinely cannot decide, pick PostgreSQL. It does documents, search, vectors and queues well enough to postpone the question.
One database, or several
- Every extra store adds backups, migrations, monitoring and a way for data to disagree with itself.
- Add a second only when the first one measurably cannot do the job.
Relational
Tables, rows and columns, with joins and transactions. The default for most applications.
What it looks like
SELECT o.id, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'paid'
ORDER BY o.created_at DESC
LIMIT 10;- Strengths: joins, constraints, ACID transactions, decades of tooling.
- Weakness: changing the shape of a table is a migration.
PostgreSQL
- The most capable open-source database, and the usual default.
- Has JSON columns, full-text search,
pgvectorfor embeddings, PostGIS for maps, and extensions for time-series. - Pick it when you want one database to cover several needs.
MySQL and MariaDB
- The classic web database: fast, everywhere, simple to run.
- MariaDB is a community-developed branch of MySQL with its own features.
- Pick it for familiar web workloads, or when your host or framework assumes it. See the MySQL sheet.
SQLite
sqlite3 app.db "SELECT * FROM notes;"- The whole database is one file, inside your process. No server to run.
- Probably the most widely deployed database there is: phones, browsers, desktop apps, small sites.
- Pick it for local, embedded or single-server use. Its own docs on when to use it are honest about the limits.
SQL Server and Oracle
- Commercial databases with deep enterprise tooling, strong optimisers and formal support.
- Pick them when the organisation already runs them, or a vendor requires them.
Managed and serverless SQL
| Service | Is |
|---|---|
| Amazon RDS, Cloud SQL | Hosted Postgres or MySQL |
| Amazon Aurora | Cloud-rebuilt, compatible |
| Neon, PlanetScale | Serverless, branching |
| CockroachDB, Yugabyte | Distributed, SQL, global |
| Supabase | Postgres plus an API layer |
- Managed hosting removes the backups and patching, not the schema design.
Document
JSON-like documents in collections, where each one can have its own shape.
What it looks like
db.orders.find({ customerId: 7,
status: "paid" })
.sort({ createdAt: -1 })
.limit(10)- Strengths: nested data in one read, no migration to add a field.
- Weakness: nothing enforces a shape unless you add validation, and joins are awkward.
MongoDB
- The best-known document database, with an aggregation pipeline, transactions and Atlas Vector Search.
- Pick it for records that vary in shape and are read whole. See the MongoDB sheet.
Firestore and Realtime Database
- Google's hosted document stores, built for mobile and web clients.
- They push changes to connected clients and work offline, with rules instead of a server.
- Pick them for apps where the client talks to the database directly.
CouchDB and PouchDB
- Replication-first: a copy runs in the browser or on a device and syncs when it can.
- Pick them when offline work and later merging is the whole problem.
Key–value and cache
One value per key, fetched in microseconds. The simplest model there is.
What it looks like
SET cart:7 "[...]" EX 3600
GET cart:7
INCR page:views- Strengths: extremely fast, simple to reason about.
- Weakness: you can only find things by the key you chose. See hash tables explained.
Redis and Valkey
- In-memory, with lists, sets, sorted sets, streams, pub/sub and expiry built in.
- Used for caching, sessions, rate limits, queues and leaderboards.
- Licensing differs: Redis 8 is tri-licensed (AGPLv3, RSALv2, SSPLv1), while Valkey is BSD under the Linux Foundation and compatible with older Redis.
Memcached
- Caching only: strings in memory, nothing else.
- Pick it when you want a cache with no other features to misuse.
DynamoDB
Partition key -> which server
Sort key -> order within it- AWS's serverless key–value and document store, with predictable latency at any size.
- You design the keys around your queries first; ad-hoc questions are expensive or impossible.
etcd and Consul
- Small, strongly consistent stores for configuration and service discovery.
- etcd is what Kubernetes keeps its state in. Not for application data.
Wide-column
Rows spread over many machines by a partition key, built for constant writes.
What it looks like
SELECT * FROM orders_by_customer
WHERE customer_id = 7
LIMIT 10;- You create one table per query, repeating the data, because there are no joins.
- Strengths: enormous write volume, no single point of failure, predictable scaling.
Cassandra and ScyllaDB
- Cassandra is the original; ScyllaDB is a C++ rewrite aimed at lower latency.
- Pick them for event logs, messaging and sensor data at a scale one server cannot hold.
Bigtable and HBase
- Google's Bigtable and its open counterpart HBase, for very large sparse tables.
- Pick them inside an existing analytics stack rather than for a new web app.
Search
Inverted indexes that rank documents by how well words match.
What it looks like
{ "query": { "match": {
"title": "blue running shoes" } } }- Strengths: typo tolerance, stemming, ranking, facets and highlighting.
- Weakness: it is a derived index, not your source of truth. Keep the real data elsewhere.
Elasticsearch and OpenSearch
- Elasticsearch is the standard; OpenSearch is the Apache-2.0 alternative maintained by AWS.
- Both also store vectors, so one engine can do words and meaning together.
- Pick them for large catalogues, log search and analytics over text.
Meilisearch and Typesense
- Small, fast, easy to run, with good defaults and typo tolerance out of the box.
- Pick them for site and product search where Elasticsearch is more than you need.
Or just use your database
-- Postgres, no extra service
SELECT * FROM articles
WHERE to_tsvector(body)
@@ plainto_tsquery('shoes');- Postgres and MySQL both do full-text search. Reach for a search engine when ranking or scale demands it.
Vector
Embeddings stored so that nearest neighbours are the most similar meanings.
What it looks like
// find by meaning, not by words
search(embed("something to run in"), {
limit: 10,
})- Strengths: matches paraphrases and synonyms. It is how retrieval for AI works.
- See the RAG cheat sheet for chunking, embeddings and retrieval.
Dedicated vector databases
| Store | Note |
|---|---|
| Pinecone | Managed, no operations |
| Qdrant | Open source, Rust, filters |
| Weaviate | Open source, hybrid search |
| Milvus | Open source, very large sets |
| Chroma | Light, good for prototypes |
Or a database you already run
pgvectorin Postgres, Atlas Vector Search in MongoDB, vectors in Elasticsearch or Redis.- Under a few million vectors this is usually the right answer: one less system to run.
Graph
Nodes and relationships, where following connections is the main operation.
What it looks like
MATCH (a:Person)-[:BOUGHT]->(p:Product)
<-[:BOUGHT]-(b:Person)
WHERE a.name = 'Ada'
RETURN b.name LIMIT 10- Strengths: deep and variable-length traversals stay cheap as the data grows.
- Pick it for recommendations, fraud rings, permissions and network analysis.
Neo4j and the rest
| Database | Note |
|---|---|
| Neo4j | The standard, Cypher query language |
| Amazon Neptune | Managed, Gremlin and SPARQL |
| ArangoDB | Graph, document and key–value |
| Apache AGE | Graph inside Postgres |
When a join is enough
- Two or three hops are fine in SQL. A graph database earns its place when the depth is unknown or large.
Time-series and analytics
Two different jobs that both mean "a great many rows, read in bulk".
OLTP and OLAP
| OLTP | OLAP | |
|---|---|---|
| Does | Many small writes | Few huge reads |
| Reads | Whole rows | A few columns |
| Stores | Row by row | Column by column |
| Example | Postgres, MySQL | ClickHouse, BigQuery |
- Running reports on your production database is the most common cause of a slow application.
Time-series stores
| Store | For |
|---|---|
| Prometheus | Metrics and alerting |
| InfluxDB | Sensor and event series |
| TimescaleDB | Time-series inside Postgres |
| VictoriaMetrics | Large metric volumes |
- They compress by time and expire old data automatically, which a normal table will not.
Column stores
- ClickHouse, Apache Druid and Apache Pinot read only the columns a query names, so aggregates over billions of rows return in seconds.
- Pick them for dashboards, product analytics and log analysis.
Warehouses and lakes
| Service | Note |
|---|---|
| Snowflake | Managed, storage split from compute |
| BigQuery | Serverless, pay per query |
| Redshift | AWS's warehouse |
| Databricks | Lakehouse, Spark and SQL |
- A warehouse is where data from every system is combined for analysis, loaded in batches.
DuckDB
duckdb -c "SELECT count(*) FROM 'a.parquet'"- An embedded column store: the analytics counterpart to SQLite, in your process, no server.
- Pick it for local analysis of files and for tests.
The ideas behind the choice
The handful of terms that decide which trade-off you are making.
ACID
| Letter | Promise |
|---|---|
| Atomic | All of it, or none of it |
| Consistent | Constraints hold at the end |
| Isolated | Concurrent work does not mix |
| Durable | A commit survives a crash |
- Most databases now offer transactions; the question is over how many documents, rows or machines.
Consistency and CAP
When the network splits, choose:
stay consistent, or stay available- "Eventually consistent" means a read may be stale for a while. Fine for a timeline, not for a balance.
- Jepsen tests what databases actually do, which is often not what they claim.
Normalise or repeat
| Approach | Cost |
|---|---|
| Normalised | Joins on read, one copy to update |
| Denormalised | Fast reads, every copy to update |
- Relational databases default to normalised; document and wide-column stores expect you to repeat.
Replicas and shards
Replica: a copy. Survives failure,
spreads reads, may lag.
Shard: a slice. Spreads writes,
makes cross-slice joins hard.- Replicate first. Shard only when one machine genuinely cannot take the writes.
Indexes are not free
- An index speeds reads and slows every write, and takes space. See Big O notation explained.
- Add them from measured slow queries, not in advance.
Watch out
The choices people regret.
Common mistakes
| Mistake | Instead |
|---|---|
| Choosing by popularity | Choose by access pattern |
| A new database per feature | Stretch the one you have |
| NoSQL to avoid schema design | The design moves, it does not vanish |
| Reports on the production database | A replica or a warehouse |
| A search engine as the source of truth | Keep the real data in a database |
| A graph database for two joins | SQL |
| Sharding early | Replicate, index, cache first |
| MongoDB for heavily relational data | Relational |
| Postgres for 100,000 writes a second | Wide-column |
| Cache with no expiry or invalidation | Set a TTL and a rule |
| Never testing the restore | Restore it on a schedule |
| Picking for a scale you may never reach | Fit today, plan an exit |
More help
The official pages to check details against.
Official documentation
| Database | Page |
|---|---|
| PostgreSQL | PostgreSQL documentation |
| MySQL | MySQL 8.0 Reference Manual |
| MariaDB | MariaDB knowledge base |
| SQLite | When to use SQLite |
| MongoDB | MongoDB Manual |
| Redis | Redis documentation |
| Valkey | Valkey |
| DynamoDB | Amazon DynamoDB guide |
| Cassandra | Apache Cassandra documentation |
| Elasticsearch | Elastic documentation |
| Qdrant | Qdrant documentation |
| Neo4j | Neo4j documentation |
| Prometheus | Prometheus overview |
| TimescaleDB | Timescale documentation |
| ClickHouse | ClickHouse documentation |
| DuckDB | DuckDB documentation |
| Firestore | Cloud Firestore |
| Correctness | Jepsen analyses |