Database Cheat Sheet

“Problem says X → think Y.” One line per need; click a row to open the lesson.

Indexing

Fast lookup by a column->B-tree index Fast lookup by two columns together->Composite index (equality col first)Filter on a rare value of a low-cardinality column->Partial index WHERE that value Case-insensitive / functional lookup->Expression index on lower(col)Avoid touching the table at all->Covering index (INCLUDE the selected columns)Search inside arrays / JSONB / full text->GIN index ORDER BY … LIMIT is slow->Index that provides the sort order Query ignores the index->Check sargability, leftmost prefix, selectivity, statistics The FK-child query is slow->Index the referencing column (not auto-created)

Modeling

A relationship between two entities->Foreign key on the many side Exactly one child per parent->UNIQUE foreign key Many-to-many->Junction table with the pair’s attributes A hierarchy / tree->Self-referencing parent_id; recursive CTE to walk it An identifier that might change->Surrogate PK + UNIQUE on the natural key Repeating columns (product_1, product_2)->Child table, one row each (1NF)Same fact stored in many rows->Normalize to 3NF A read that joins six tables on every view->Denormalize a maintained copy The price paid must not change with the catalogue->Snapshot column (history, not redundancy)Prevent overlapping ranges (bookings)->EXCLUDE constraint (GiST)Variable-shape data->JSONB column, GIN-indexed

SQL

Filter groups, not rows->HAVING (WHERE runs before aggregation)Find duplicates->GROUP BY … HAVING count(*) > 1 Anti-join (rows with no match)->NOT EXISTS (NULL-safe), not NOT IN Rank / running total / previous value->Window function Top N per group->row_number() OVER (PARTITION BY …) then WHERE rn <= N Percentage of a group total per row->sum(x) OVER (PARTITION BY g) / value Test a column for NULL->IS NULL / IS NOT NULL (never = NULL)sum() returns NULL on empty groups->coalesce(sum(x), 0)Deep pagination is slow->Keyset pagination (WHERE id > :last), not OFFSET Walk a graph or tree in SQL->Recursive CTE (with a depth guard)

Performance

Diagnose a slow query->EXPLAIN (ANALYZE, BUFFERS)One slow page, all queries fast->Look for N+1 (query count per request)SUM over a join is too high->Aggregate the child before joining (fan-out)A plan went bad after a bulk load->ANALYZE (stale statistics)SELECT * on a wide table is slow->Select only needed columns (enables index-only)Sort spills to disk->Index the sort key, or raise work_mem

Transactions

Several statements must succeed together->One transaction (BEGIN … COMMIT)Concurrent counter increments lose updates->SET x = x + 1, or FOR UPDATE, or version check Read the same data twice, consistently->REPEATABLE READ Act on a condition you checked but did not write->SERIALIZABLE + retry, or a constraint Read a row you are about to update->SELECT … FOR UPDATE A job queue in the database->FOR UPDATE SKIP LOCKED Avoid deadlocks->Lock rows in a consistent order; keep txns short A table bloats though rows are flat->Find the long transaction blocking VACUUM

Scaling

Scale reads->Read replicas (mind replication lag)Scale writes past one machine->Sharding (last resort)A huge single table is hard to operate->Partition by time (still one machine)User sees their own write revert->Read-your-writes routing / wait for LSN "Too many connections" at idle CPU->Connection pooler (PgBouncer)Choose CP or AP->Decide what happens during a partition Strong reads across replicas->Quorum W + R > N

Caching

Repeated expensive read->Cache-aside with a TTL A deploy stampedes the database->TTL jitter / single-flight / stale-while-revalidate One key is most of the traffic->Replicate the hot key (L1 cache)Cache stale after a direct SQL edit->Change data capture invalidation Leaderboard / rank at scale->Redis sorted set Rate limiting->Redis INCR+EXPIRE or sorted-set sliding window

NoSQL

Semantic / similarity search->Vector index (pgvector HNSW)Exact-token + semantic search->Hybrid: BM25 + vector Relevance-ranked text search->Full-text (tsvector + GIN), then a search engine Multi-hop traversal queries->Graph database Very high-volume time-ordered writes->Wide-column or time-series store Whole-object reads with variable shape->Document store (embed vs reference)

Internals

Why does the engine read 8 KB to fetch one 100-byte row?->The page is the unit of I/O and caching A record grew on UPDATE and the row "moved"->Slotted page: slots stay, offsets change; overflow to another page How does an index find a row in 3 reads among millions?->B+ tree: fanout ~200, height 3–4, linked leaves Why not a balanced binary tree for the index?->Fanout: 24 random page reads vs 3 Index on = only, never ranges->Hash index: bucket lookup, no ordering Second run of the query is 100× faster->Buffer pool hit vs miss A big scan evicted the working set->Replacement policy: Clock, ring buffers, old/young lists What does COMMIT wait for?->WAL fsync — not the data pages Crash after COMMIT — is the data safe?->Yes: recovery replays the WAL from the last checkpoint Two transactions want the same row->Lock manager: S/X compatibility, wait queue Deadlock detected, transaction aborted->Cycle in the waits-for graph; one victim chosen Readers never block writers — how?->MVCC: version chains + snapshot visibility rule Table keeps growing after DELETE->Dead tuples until VACUUM; long snapshots pin them Serialization failure, please retry->SSI detected a rw-dependency cycle Millions of writes per second, mostly inserts->LSM tree: WAL → memtable → SSTable → compaction Point read checks many files->Bloom filter per SSTable skips the definite misses Disk usage 3× the data on an LSM store->Space amplification; compaction backlog B+ tree or LSM for this workload?->Reads and ranges → B+; write-heavy ingest → LSM Planner picked Seq Scan although an index exists->Cost model: selectivity × random page cost > sequential Which join algorithm will it use?->Nested loop (small/indexed), hash (equality, fits memory), merge (sorted inputs)Why is the InnoDB secondary lookup two descents?->Secondary index → PK → clustered index Replica shows old data->Async replication lag: LSN on replica < primary Adding a node moved 80% of keys->Consistent hashing / virtual nodes instead of modulo Primary died — who is leader now?->Election with a majority quorum; fence the old primary Slow, and EXPLAIN is not enough->Count pages, buffer misses, sorts, WAL, contention