Skip to content
ReadMe Daily

Search articles, problems, patterns and every cheat sheet topic.

Choosing a Database Cheat Sheet

12 groups · 44 topics · Updated

There is no best database, only the one that fits how you read and write your data. This sheet lists the popular ones by family, says what each is for, and shows what a query looks like. Two of them have sheets of their own: MySQL and MongoDB.

1

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.
2

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, pgvector for 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.
3

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.
4

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.
5

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.
6

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.
7

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

  • pgvector in 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.
8

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.
9

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.
10

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.
11

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
12

More help

The official pages to check details against.