EXPLAIN & EXPLAIN ANALYZE — Reading Query Plans in Practice
Reading intent against reality: the node anatomy, costs being cumulative upward and actual time/rows being per-loop averages that must be multiplied by loops, the diagnostic order (find the LOWEST node where estimated and actual rows diverge), the scan-node vocabulary including Bitmap Heap Scan and Heap Fetches, the BUFFERS/SETTINGS/GENERIC_PLAN options, and a worked plan traced from a 4000x top-level gap down to the composite index that actually fixes it.
Query Planning & Optimization — How the Database Picks a Plan
The planner as a search engine with a cost function, not a translator: the parser/rewriter/planner/executor path (and why a view is a pre-planning substitution), paths vs plans, the startup..total cost pair that explains LIMIT flips, cost as arithmetic on top of cardinality, the System R bottom-up search and the geqo threshold, the transformations it applies for you, the six things it cannot do, and the estimate-vs-cost-model split that decides every fix.
SQL Fundamentals — Joins, Aggregations, Subqueries & CTEs
Logical evaluation order as the master key; the cross-product join model with the ON-vs-WHERE silent-inner-join and fan-out double-counting traps, LATERAL top-N; GROUP BY grain change, WHERE-vs-HAVING, aggregate NULL skipping and FILTER; the NOT IN NULL trap vs NOT EXISTS; and CTEs with the Postgres 12 inlining rule, the recursive working-table algorithm, and one-snapshot data-modifying CTEs.
Join Algorithms — Nested Loop, Hash Join & Merge Join
Three answers to one lookup question: nested loop (O(N*M), but O(N*log M) and unbeatable with a small indexed inner side — and structurally the N+1 in the right place), hash join (O(N+M), equality only, with the work_mem x hash_mem_multiplier cliff and Batches>1 spill signal), and merge join (free when an index supplies the order, and the only other option for range joins); plus the row-estimate cliff that makes a wrong nested loop degrade without bound, the enable_* switches as diagnostics, and geqo join ordering.
Window Functions — RANK, ROW_NUMBER, LAG/LEAD, Partitions & Frames
Aggregate without collapsing the row: why window functions can't appear in WHERE (they run after it) and the CTE-wrapper that follows; ROW_NUMBER/RANK/DENSE_RANK tie behaviour and top-N-per-group; LAG/LEAD with the flag-then-running-SUM sessionization and gaps-and-islands idioms; and frames — the RANGE default, peer groups, the stalled running total, the LAST_VALUE trap, and ROWS-vs-RANGE as 'last 7 rows' vs 'last 7 days'.
Optimistic vs Pessimistic Locking — Choosing by Conflict Rate and Window Length
Two answers to the application-level lost update: lock-first vs validate-at-write; Kung & Robinson's read/validate/write phases, why the version column is a compare-and-swap built on EvalPlanQual, the READ COMMITTED (0 rows) vs REPEATABLE READ (40001) split, the atomic-in-place third option, and the per-row blind spot (write skew, phantoms).
SELECT FOR UPDATE & SKIP LOCKED — Row Locks and the Postgres Job Queue
Read-and-reserve via row-level exclusive locks: the lost-update race MVCC leaves open, the four lock strengths + conflict matrix, wait/NOWAIT/SKIP LOCKED policies, the CTE job-queue claim, and READ COMMITTED's EvalPlanQual re-check.
Advisory Locks — Application-Level Coordination Through the Database
Locks on application-invented integers that guard code, not data: the sign-up-sheet model, the singleton-cron case row locks can't solve, session-vs-transaction scope, the try/shared function matrix, key-space collisions, and the LIMIT dangling-lock trap.
Buffer Pool / Page Cache — How a DB Manages Memory
8KB pages cached in shared_buffers; buffer table/descriptors, clock-sweep eviction (approx LRU), WAL-flush-before-dirty-write, ring buffers, and double buffering vs the OS cache.
Write-Ahead Log (WAL) — Crash Recovery & the Durability Guarantee
Log-first rule, LSN/pd_lsn recovery replay (idempotent), checkpoints & redo point, full-page writes vs torn pages, synchronous_commit, redo-only WAL, and PITR/replication.
Table Bloat & Autovacuum — Reclaiming What MVCC Leaves Behind
Dead tuples → bloat; plain VACUUM (reusable, FSM) vs VACUUM FULL/pg_repack (shrink), the VM's two bits, freezing & XID wraparound, and autovacuum triggers/tuning.
MVCC — How Postgres Implements Multi-Version Concurrency Control
Version-stamped tuples (xmin/xmax/ctid chain), the xmin:xmax:xip snapshot, visibility rules + clog, hint bits (reads that write), HOT updates, and the OldestXmin horizon.
How Data Is Stored on Disk — Heap Files, Pages & Slots
Heap files → 8 KB pages → line-pointer slots; TID indirection (logical slot vs physical bytes), relfilenode rewrites, forks/TOAST, and the torn-page → full-page-write hazard.
Deadlocks — Detection, Prevention & the Coffman Conditions
The four Coffman conditions as the master key, the four handling strategies, Postgres detect-and-abort (deadlock_timeout, 40P01, consistent lock ordering), and livelock vs deadlock.
Two-Phase Locking (2PL)
The growing/shrinking phases and lock-point serializability, S/X compatibility, the Basic→Strict→SS2PL→Conservative variants with a lock-timing trace, and Postgres's MVCC+strict-2PL hybrid.
Isolation Levels & Read Anomalies
The anomaly×level table, MVCC snapshot timing per level, Postgres-vs-ANSI divergence, write skew, SSI/predicate locks, and the 40001 retry contract.
ACID Properties
The four transaction guarantees: WAL-backed atomicity (undo) & durability (redo), why C is the application's job, isolation as a tunable dial, and the wedding-ceremony model.
Partial & Expression Indexes
Index a subset of rows (partial) or a transformed value (expression); partial UNIQUE for scoped constraints, case-insensitive uniqueness, and combining both.
Clustered vs Non-clustered Indexes
Rows-in-the-leaf vs pointer-to-heap; the secondary-index double-lookup, Postgres CLUSTER (one-time, decays) vs InnoDB permanent PK clustering, and the clustering factor.
Composite Indexes — Column Order & the Leftmost-Prefix Rule
The phone-book sort, the leftmost-prefix rule, equality-before-range column ordering, why one composite ≠ two singles, and bitmap/skip scans.
Covering Indexes & Index-Only Scans
Answering a query from the index alone: INCLUDE payload columns, eliminating heap fetches, and why Postgres's visibility map / MVCC can still force heap access.
Schema Design — ER Diagrams & Normalization (1NF → BCNF)
ER modeling and normalization 1NF→BCNF: functional dependencies, partial/transitive dependencies, the three anomalies, and BCNF's edge case.
Denormalization — When and Why to Break Normalization Rules
Trading write complexity for read speed: counter columns, redundant copies, materialized views, consistency strategies, and the decision framework.
B-tree Indexes — Structure & How Range/Equality Queries Use Them
How a B-tree turns O(n) scans into O(log n) on-disk lookups; equality vs range traversal, ORDER BY for free, write tax, and why f(column) defeats the index.
Relational Data Model
Tables as sets of tuples; keys (super/candidate/primary/foreign), constraints, and 1:N/M:N/1:1 relationships — the foundation of SQL.
Data Models Overview — Relational vs Document vs Key-Value vs Columnar vs Graph
The five data models, how to pick by access pattern, relational-vs-document decision tests, and what Mongoose populate / $lookup really do.
No entries match. Try a different search or clear filters.