Where the data lives
- Choose a database for your application
For developers starting an app: answer questions on where data lives, its shape and its consistency needs, and get a database type to use.
General practice, vendor-neutral. Most apps do well on one relational database. Add a specialised store only when a need below is real and measured.
Before you start, list the 5 to 10 queries your app runs most, the data size you expect in a year, and how many processes write at once.
- Will several machines write to the database over a network?
SQLite's own checklist asks three things: is the data separated from the app by a network, are there many concurrent writers, and is the data approaching a terabyte?
- No, one machine: go to step 3, Will the data stay well under a terabyte?
- Yes: go to step 5, Who will look after the database day to day?
- Not sure: go to step 5, Who will look after the database day to day?
- Will the data stay well under a terabyte?
- Use SQLite, embedded in your app
SQLite is a library, not a server. It suits phones, desktop apps, devices, tests, data analysis and many small or medium websites. sqlite.org says under 100K hits a day is a conservative figure.
It allows unlimited readers but only one writer at any instant. WAL mode lets readers and the writer run at the same time.
Check: WAL mode needs all processes on the same host, not a network filesystem. If you later need several app servers, plan a move to a client/server database.
Who runs it
- Who will look after the database day to day?
- No ops staff: go to step 6, Plan on a managed service for any server database
- Your own team: go to step 7, Plan backups, upgrades and failover yourselves
- Plan on a managed service for any server database
A managed service runs backups, patching and failover. Check the backup retention period and how far back point-in-time recovery reaches.
Then go to step 8, What shape is most of your data?
- Plan backups, upgrades and failover yourselves
Test a full restore before launch. Copy backups off the machine, and outside the data centre, at least daily.
Data shape
- What shape is most of your data?
Pick the shape of your core records, the ones you can't lose. Caches, search and analytics can be added later.
- Related tables: go to step 9, Does your team already run MySQL well?
- Documents: go to step 12, Do records vary in shape and get read whole?
- Something more specialised: go to step 16, Which of these describes the core workload?
- Keys and values: go to step 17, Is this data a cache you could rebuild from somewhere else?
- Connected entities: go to step 21, Do key queries follow many hops of relationships?
- Does your team already run MySQL well?
Related tables means records that point to each other: customers, orders and invoices, with joins and transactions across them.
- Yes: go to step 10, Use MySQL with the InnoDB engine
- No or unsure: go to step 11, Use PostgreSQL as your main database
- Use MySQL with the InnoDB engine
InnoDB is MySQL's default engine. It is ACID-compliant with row-level locking, foreign keys and crash recovery.
Check: the MySQL manual lists a 64TB limit per table or tablespace at the default 16KB page size, and your file system may set a lower one. Check which features your MySQL version supports, such as JSON and full-text indexes.
- Use PostgreSQL as your main database
ACID-compliant, supports all SQL standard isolation levels including Serializable, and handles JSON/JSONB, arrays, ranges and full-text search.
Extensions such as PostGIS (maps), TimescaleDB (time series) and pgvector (embeddings) let one database cover several needs.
Check: plan connection pooling early. Each Postgres connection is a server process, so many short-lived connections need a pooler.
- Do records vary in shape and get read whole?
Documents are self-contained records, like a product with its variants and specs, where fields differ from one record to the next.
- Do you often need to change several documents in one transaction?
- Rarely: go to step 14, Use a document store such as MongoDB
- Often: go to step 15, Use PostgreSQL with JSONB columns
- Use a document store such as MongoDB
Store data that is read together in one document. A write to a single document is atomic.
Multi-document transactions exist, but MongoDB's docs say they cost more than single-document writes and are no substitute for good schema design.
Check: add schema validation to the fields that need it. A flexible schema can still drift into inconsistent data.
- Use PostgreSQL with JSONB columns
Keep the fixed fields as columns and the variable part in a JSONB column. You keep joins and multi-row transactions.
Check: index the JSON keys you filter on, or queries will scan every row.
- Which of these describes the core workload?
- Time-stamped events: go to step 23, Already using PostgreSQL for the rest of the app?
- Text to search: go to step 26, Need typo tolerance, facets or search across many sources?
- Embeddings: go to step 29, Can the vectors live next to your PostgreSQL data?
- None or not sure: go to step 11, Use PostgreSQL as your main database
Special workloads
- Is this data a cache you could rebuild from somewhere else?
Sessions, rate-limit counters, leaderboards and cached query results are typical key-value workloads.
- Use a key-value cache such as Redis
Redis keeps data in memory, so reads and writes are very fast. Persistence can be turned off for a pure cache.
Check: set an eviction policy and a memory limit, and make your app work, slowly, when the cache is empty.
- Can you accept losing up to the last second of writes?
Redis with append-only file (AOF) persistence and the default fsync every second can lose about one second of writes in a crash. Snapshots alone can lose minutes.
- Use Redis with AOF and RDB persistence
Redis docs suggest using both methods if you want data safety comparable to PostgreSQL.
Check: copy RDB snapshots off the machine for backups, and size memory for the whole dataset.
- Do key queries follow many hops of relationships?
For example, friends of friends, fraud rings, supply chains or permission inheritance, where the depth of the path is not fixed.
- Yes, many hops: go to step 22, Use a graph database such as Neo4j
- One or two hops: go to step 11, Use PostgreSQL as your main database
- Use a graph database such as Neo4j
Graph databases store nodes, relationships and properties, and traverse relationships without join operations.
Check: graph databases are rarely the system of record for billing or orders. Many teams keep those in a relational database and sync a graph.
- Already using PostgreSQL for the rest of the app?
Time-series data is metrics, sensor readings, prices and logs that arrive in time order and are queried by time range.
- Use PostgreSQL with the TimescaleDB extension
Hypertables split data into chunks by time range, such as one day or one week, while you keep using SQL and joins.
Check: set a retention policy early. Time-series data grows without limit.
- Use a dedicated time-series database
Built for high write rates, time-bucketed queries and automatic expiry of old data.
Check: confirm how it handles updates, deletes and joins. Many are built mainly for appends, so keep customer and billing records in your main database.
- Need typo tolerance, facets or search across many sources?
Full-text search means finding documents by words and ranking them by relevance, not exact matches.
- Use a search engine such as Elasticsearch or OpenSearch
Built for relevance ranking, fuzzy matching, facets and aggregations at large scale.
Check: it is near real-time, so new documents usually become searchable within about 1 second. Keep the source data in your main database and reindex from it.
- Use PostgreSQL full-text search
Postgres full-text search adds stemming, stop words, ranking and GIN indexes that plain LIKE queries lack.
Check: it matches word stems, not typos. Add the pg_trgm extension if you need fuzzy matching.
- Can the vectors live next to your PostgreSQL data?
Embeddings are lists of numbers from a machine-learning model, searched by similarity. pgvector indexes up to 2,000 dimensions, or 4,000 at half precision.
- Use PostgreSQL with the pgvector extension
You keep ACID transactions, joins, point-in-time recovery and filters on ordinary columns in the same query.
Check: approximate indexes (HNSW, IVFFlat) trade recall for speed. Measure recall on your own data after you add one.
- Use a dedicated vector database
Fits very large vector collections, or teams not running PostgreSQL.
Check: you now sync two systems. Plan how deletes and permission changes reach the vector store.