Book 04 · Cheat sheet

Databases

How data is stored, queried and kept consistent: SQL, NoSQL, indexes and transactions.

01ACIDAtomicity, Consistency, Isolation, Durability: ACID is a set of four guarantees, atomicity, consistency, isolation, and durability, that keep database transactions reliable even when errors or crashes occur.
Atomicity: a transaction either fully succeeds or is fully rolled back.
Consistency: every transaction leaves the data valid according to the database's rules.
Isolation: concurrent transactions don't interfere with each other's unfinished work.
02CAP TheoremConsistency, Availability, Partition Tolerance: The CAP theorem says that if a network failure splits a distributed database, the system must choose between consistency and availability; it can't have both.
CAP stands for consistency, availability, and partition tolerance.
During a network partition, a distributed system must choose consistency or availability.
Partition tolerance is mandatory in practice, so the real choice is CP or AP.
03Connection Pool: A connection pool is a cache of open database connections that an application reuses across requests, avoiding the cost of opening a new connection every time.
A pool keeps database connections open and reuses them across requests.
It avoids the slow setup of a new connection for every query.
Key settings are the maximum size, idle connections, and wait timeout.
04Data Lake: A data lake is a central storage repository that holds large amounts of raw data in its original format, structured or not, until someone needs to analyze it.
A data lake stores raw data of any type, usually as files in object storage.
Structure is applied when the data is read, not when it is written.
Columnar formats such as Parquet and a data catalog make lakes usable.
05Data Warehouse: A data warehouse is a central database built for analytics that collects historical data from many sources so teams can run large reporting queries quickly.
A data warehouse is optimized for analytics, not for running an application.
It combines data from many sources into one consistent structure.
Data is loaded through ETL or ELT pipelines.
06Database: A database is an organized collection of data stored on a computer, managed by software that lets applications save, search, and update it efficiently.
A database stores data so applications can save and retrieve it reliably.
A DBMS such as PostgreSQL or MongoDB is the software that manages the data.
Relational databases use tables and SQL; NoSQL databases use other data models.
07Database Index: A database index is a data structure that helps a database find rows quickly without scanning a whole table, much like the index at the back of a book.
Indexes speed up reads by avoiding full table scans.
Most indexes are B-trees, which keep values sorted for fast lookups.
Primary keys are indexed automatically.
08Database Migration: A database migration is a versioned script that changes a database's schema, such as adding a column, so every environment applies the same changes in order.
A migration is a versioned file describing one schema change.
Migrations are committed with the code and applied in a fixed order.
The tool records applied migrations so each one runs only once per database.
09Database Normalization: Database normalization is the process of organizing tables so each fact is stored only once, reducing duplicate data and preventing inconsistent updates.
Normalization stores each fact once to avoid duplicate and contradictory data.
Tables are linked with primary and foreign keys instead of copying data.
The normal forms 1NF, 2NF, and 3NF are progressively stricter rules.
10Database Replication: Database replication is the continuous copying of data from one database server to others, so several servers hold the same data for reliability and scale.
Replication copies the same data to multiple database servers.
In primary-replica setups, the primary handles writes and replicas serve reads.
Failover promotes a replica if the primary goes down.
11Database Schema: A database schema is the blueprint of a database that defines its tables, columns, data types, relationships, and the rules that stored data must follow.
A schema defines the structure of a database, not the data itself.
It covers tables, columns, data types, keys, and constraints.
Schema changes are usually applied through versioned migrations.
12Database Trigger: A database trigger is code stored in the database that runs automatically when a chosen event, such as an insert, update, or delete, happens on a table.
A trigger runs automatically when data in a table is inserted, updated, or deleted.
BEFORE triggers can check or change incoming data; AFTER triggers react to finished changes.
Row-level triggers can read both the old and the new values of a row.
13Database View: A database view is a saved SQL query that behaves like a virtual table, so you can select from it by name instead of repeating the underlying query each time.
A view is a named, saved query that you can select from like a table.
A regular view stores no data and always shows current results.
Views simplify complex joins and can limit which columns users see.
14Denormalization: Denormalization is the deliberate duplication of data across tables or documents so that frequent reads need fewer joins, at the cost of more complex writes.
Denormalization adds deliberate copies of data to speed up reads.
It reduces joins and repeated calculations on frequent queries.
Every copy must be kept in sync, which makes writes more complex.
15Document Database: A document database is a NoSQL database that stores each record as a self-contained document, usually JSON-like, whose fields can differ from record to record.
Each record is a self-contained, JSON-like document stored in a collection.
Documents can nest objects and arrays, and their fields can vary.
Any field, including nested ones, can be indexed and queried.
16DynamoDBAmazon DynamoDB: Amazon DynamoDB is a fully managed NoSQL database on AWS that stores items by key, scales automatically and answers lookups in single-digit milliseconds.
DynamoDB is a fully managed key-value and document database on AWS.
Items are found by a partition key, optionally with a sort key.
It scales horizontally by spreading partitions across many machines.
17Elasticsearch: Elasticsearch is a distributed search and analytics engine that indexes JSON documents for fast full-text search, filtering and aggregations over large data.
Elasticsearch is a distributed search and analytics engine built on Lucene.
An inverted index maps words to documents for fast full-text search.
It ranks by relevance, tolerates typos and computes aggregations.
18ETLExtract, Transform, Load: ETL is a data integration process that extracts data from source systems, transforms it into a clean, consistent shape, and loads it into a target store.
Extract pulls data from sources, transform cleans and reshapes it, and load writes it to a target.
Pipelines usually run as scheduled batch jobs managed by an orchestrator.
Incremental loads and change data capture avoid copying everything every time.
19Eventual Consistency: Eventual consistency is a guarantee that, if no new updates are made, all copies of a piece of data in a distributed system will become identical over time.
Replicas may briefly disagree, but they converge once updates stop.
Writes are acknowledged quickly and propagated to other replicas in the background.
Conflicting writes are resolved with rules such as last writer wins or CRDTs.
20Firebase: Firebase is Google's platform that gives web and mobile apps a hosted database, authentication, storage, hosting and functions without managing a backend.
Firebase is Google's backend platform for web and mobile apps.
Cloud Firestore is a NoSQL document database with real-time updates.
It also offers authentication, storage, hosting, functions and push messages.
21Foreign Key: A foreign key is a column in one database table that refers to the primary key of another table, linking related rows and keeping those references valid.
A foreign key references the primary key, or a unique column, of another table.
It enforces referential integrity: references must point to rows that exist.
ON DELETE rules such as CASCADE or RESTRICT decide what happens when a parent row is removed.
22Full-Text Search: Full-text search is a technique that finds documents containing given words or phrases by looking them up in a text index, then ranks the results by relevance.
Full-text search matches words inside text, not just exact field values.
It relies on an inverted index that maps each word to the documents containing it.
Tokenization, stop words, and stemming let different word forms match.
23Graph Database: A graph database stores data as nodes connected by relationships, which makes it fast to follow links such as friends of friends or dependencies between items.
Data is stored as nodes and relationships, both of which can have properties.
Following a relationship is a direct hop, so deep traversals stay fast.
Common query languages include Cypher, GQL, and SPARQL.
24Isolation Level: An isolation level is a database setting that controls how much concurrent transactions can see of each other's changes, trading strictness for speed.
The standard levels are Read Uncommitted, Read Committed, Repeatable Read, and Serializable.
Higher levels prevent more anomalies: dirty reads, non-repeatable reads, and phantom reads.
Serializable behaves as if transactions ran one after another.
25Key-Value Store: A key-value store is a NoSQL database that saves each piece of data under a unique key, so an application can read or write it by that key very quickly.
Each record is a unique key paired with a value.
Lookups by key are very fast, usually close to constant time.
Common uses include caching, sessions, counters, and feature flags.
26MongoDB: MongoDB is a document database that stores data as flexible JSON-like documents instead of table rows, so records in one collection can have different fields.
MongoDB stores JSON-like documents (BSON) in collections, not rows in tables.
Documents in one collection can have different fields.
Data read together is usually stored together, avoiding joins.
27MySQL: MySQL is a popular open-source relational database queried with SQL, long known as the database behind WordPress and the classic LAMP web stack.
MySQL is an open-source relational database that uses SQL.
It is the M in the classic LAMP stack and the default for WordPress.
The InnoDB engine provides transactions, row locking and foreign keys.
28N+1 Query Problem: The N+1 query problem is a performance bug where code runs one query to load a list and then one extra query per item, instead of fetching it all at once.
N+1 means one query for a list plus one query per item in that list.
ORM lazy loading is the most common hidden cause.
Each query is fast, but the round trips add up as the list grows.
29NoSQLNot Only SQL: NoSQL is a family of databases that store data in models other than relational tables, such as documents, key-value pairs, wide columns, or graphs.
NoSQL means 'not only SQL', not 'no SQL at all'.
The main types are document, key-value, wide-column, and graph databases.
Many NoSQL databases scale horizontally across many servers.
30OLAPOnline Analytical Processing: OLAP (online analytical processing) describes systems built to answer complex analytical questions over large amounts of historical data quickly.
OLAP systems run complex analytical queries over large historical data.
They usually store data by column for fast scans and compression.
BigQuery, Snowflake, Redshift and ClickHouse are OLAP systems.
31OLTPOnline Transaction Processing: OLTP (online transaction processing) describes databases built for many small, fast reads and writes from everyday operations, such as placing orders.
OLTP systems handle many small, fast transactions from daily operations.
They are usually relational databases with ACID transactions.
Schemas are normalized and data is stored by row.
32Optimistic Locking: Optimistic locking is a concurrency technique that lets transactions proceed without holding locks and checks a version number at save time to detect conflicts.
Each row carries a version number that is checked on every update.
An update that matches zero rows signals a conflict.
No lock is held while the user or program is working on the data.
33ORMObject-Relational Mapping: An ORM is a library that maps database tables to objects in your programming language, letting you read and write data with code instead of raw SQL.
An ORM maps database tables to classes and rows to objects.
It generates SQL for you, so you work in your programming language.
Most ORMs handle relationships, migrations, and parameterized queries.
34Partitioning: Partitioning splits a large table into smaller partitions by a rule such as date ranges, so queries can skip irrelevant data and old data is easy to remove.
Partitioning splits a large table into smaller partitions behind one name.
Range, list and hash partitioning use different rules on the partition key.
Partition pruning lets queries read only the relevant partitions.
35PostgreSQL: PostgreSQL is a free, open-source relational database known for reliability, strict standards support and extensions, and widely used for web applications.
PostgreSQL is a free, open-source relational database that uses SQL.
Transactions are ACID, and MVCC lets readers and writers work at the same time.
JSONB columns let it store and index document-style data.
36Primary Key: A primary key is a column, or set of columns, whose value uniquely identifies each row in a database table and can never be empty or duplicated.
A primary key uniquely identifies every row in a table.
Primary key values must be unique and cannot be NULL.
A table has only one primary key, but it can span several columns.
37RedisRemote Dictionary Server: Redis is an in-memory key-value store that reads and writes in well under a millisecond, which makes it a popular cache, session store and message broker.
Redis is an in-memory key-value store, so reads and writes are extremely fast.
Values can be strings, lists, sets, sorted sets, hashes and streams.
Keys can expire automatically, which suits caches and sessions.
38Relational Database: A relational database stores data in tables of rows and columns, links those tables through keys, and lets you query and combine the data with SQL.
Data lives in tables with typed columns and one row per record.
Primary keys identify rows, and foreign keys link rows across tables.
SQL is used to query the data, and joins combine tables at read time.
39Sharding: Sharding is a way of scaling a database by splitting its data across several servers, called shards, so each one stores and handles only part of the total.
Sharding splits a database's rows across multiple servers.
A shard key determines which shard stores each row.
Common strategies are range-based, hash-based, and directory-based sharding.
40SQLStructured Query Language: SQL is the standard language for working with relational databases, used to create tables and to insert, query, update, and delete the data stored in them.
SQL is the standard language for relational databases.
It is declarative: you describe the result, not the steps.
The core commands are SELECT, INSERT, UPDATE, and DELETE.
41SQL JOIN: A SQL JOIN is a query operation that combines rows from two or more tables into one result, matching them on related columns such as a foreign key.
A JOIN combines rows from multiple tables based on a matching condition.
INNER JOIN keeps only rows that match in both tables.
LEFT JOIN keeps every row from the left table, with NULL where nothing matches.
42SQL ServerMicrosoft SQL Server: Microsoft SQL Server is Microsoft's relational database, queried with the T-SQL dialect of SQL and widely used for business apps alongside .NET and Windows.
SQL Server is Microsoft's relational database, first released in 1989.
It is queried with T-SQL, Microsoft's extended SQL dialect.
Tools for ETL, reporting and analytics come with it.
43SQLite: SQLite is a small SQL database engine that runs inside your application and stores a whole database in a single file, with no separate server to manage.
SQLite is an embedded SQL database: a library, not a server.
A whole database is one ordinary file on disk.
It supports ACID transactions and survives crashes safely.
44Stored Procedure: A stored procedure is a named set of SQL statements saved inside the database, which applications can run with a single call instead of sending each query.
A stored procedure is named, reusable SQL code saved in the database.
It can take parameters and run many statements in one network round trip.
Each database uses its own procedural language, so procedures are rarely portable.
45Supabase: Supabase is an open-source backend platform built on PostgreSQL that gives an app a database, authentication, storage, real-time updates and instant APIs.
Supabase is an open-source backend platform built on PostgreSQL.
It adds auth, storage, real-time updates and edge functions.
APIs are generated automatically from the database schema.
46Time-Series Database: A time-series database is a database optimized for storing and querying timestamped measurements, such as sensor readings or server metrics, in time order.
Each data point has a timestamp, values, and identifying tags.
Data is mostly appended and queried by time range.
Time partitioning and compression keep huge volumes cheap to store.
47Transaction: A transaction is a group of database operations that succeed or fail as a single unit, so the data is never left in a half-finished, inconsistent state.
A transaction groups operations into one all-or-nothing unit.
COMMIT saves the changes; ROLLBACK undoes them.
ACID stands for atomicity, consistency, isolation, and durability.