Choosing the right database cheat sheet

1. 1Start from the dataDescribe the data before naming a product: its shape, the questions the app asks, how much there is and how correct it must be. The decision chart turns those answers into a database type.7 concepts

   1. 1Decision chart: data and access pattern to databaseA quick map from the kind of data and the way it is used to the database type that usually fits.
   2. 2Structured, semi-structured and unstructured dataStructured data fits rows and columns with known types.
   3. 3Access patternsThe questions an app asks its data again and again, such as "get user by email" or "last 20 orders for a shop", with a rough idea of how often each runs.
   4. 4Read-write mix and data sizeWhether an app mostly reads or mostly writes, how many records it will hold after a year and how big each one is.
   5. 5Consistency needsHow correct data must be at every moment.
   6. 6ACID transactionsA transaction groups changes so they are atomic (all or nothing), consistent (rules hold), isolated (concurrent transactions do not interfere) and durable…
   7. 7Questions to ask before choosingA short list answered for every project: data shapes, top access patterns, size after a year, what must be exact, any search, similarity or reporting needs…

   7 in this chapterOpen this chapter
2. 2Relational databasesPostgreSQL, MySQL, SQLite and SQL Server store related records in tables with keys, constraints and transactions. For most apps one of them is the right default, with JSON columns for the flexible parts.7 concepts

   1. 1Relational databaseA database that stores data in tables of rows and columns, links tables with keys and is queried with SQL.
   2. 2PostgreSQL as the defaultPostgreSQL is an open source relational database that also stores and indexes JSON, has built-in full-text search and gains vectors, maps and time series…
   3. 3MySQL, SQLite and SQL ServerMySQL is common behind websites and PHP apps.
   4. 4JSON columnsColumns that store JSON inside a relational table.
   5. 5ConstraintsRules the database enforces on every write: NOT NULL, UNIQUE, CHECK and foreign keys, which make a value refer to an existing row in another table.
   6. 6Migrations, drivers and ORMsA driver sends queries from your code; an ORM maps tables to objects.
   7. 7One Postgres is often enoughOne PostgreSQL database with jsonb, full-text search, pgvector, and TimescaleDB or PostGIS when needed covers relational data, flexible fields, search…

   7 in this chapterOpen this chapter
3. Document databasesMongoDB and Firestore store each record as a JSON-like document. They suit data that varies in shape and is read one whole record at a time.7 conceptsOpens with credits
4. Key-value stores and cachingKey-value stores fetch a value by its key very quickly. Redis serves as a cache and for sessions and rate limits; DynamoDB is a durable key-value primary database.8 conceptsOpens with credits
5. SearchReal search needs text analysis, an inverted index and relevance ranking. Start with PostgreSQL full-text search and add a search engine when typos, facets or ranking demand it.7 conceptsOpens with credits
6. Vector search for AIEmbeddings turn text and images into vectors; vector search finds the nearest ones. pgvector covers most early apps, and dedicated vector databases add features at larger sizes.8 conceptsOpens with credits
7. Columnar databases and warehousesReports over millions of rows need columnar storage. Know OLTP from OLAP, and where BigQuery, Snowflake, Redshift, ClickHouse and DuckDB fit.8 conceptsOpens with credits
8. Graph, time series and object storageGraph databases follow connections, time-series databases handle readings over time, and object storage holds files. Know when each earns its place.7 conceptsOpens with credits
9. Choosing, combining and running itManaged or self-hosted, the matching services on each cloud, free tiers, cost, Firebase versus Supabase and a decision you can explain.10 conceptsOpens with credits
10. Each type at a glanceOne card per database type: use it when, avoid it when, example products and managed options. Read with the decision chart.9 conceptsOpens with credits
11. 11Glossary, A to ZEvery concept in the sheet and the smaller terms it uses, in one list.141 terms

SQL, document, cache, search, vector and more.