Databases & SQL
13 guidesMVCC, isolation, optimizer behavior, storage trade-offs, and safe production tuning.
Relational Databases and SQL Foundations: Model the Invariant First
Learn tables, keys, constraints, joins, transactions, and query shape before choosing a specific database engine.
PostgreSQL MVCC and Indexes: Reads, Vacuum, and Query Plans
Connect PostgreSQL's multi-version concurrency control to transaction visibility, vacuum work, index choice, and EXPLAIN output.
MySQL InnoDB: Transactions, Indexes, and Reading EXPLAIN
Understand InnoDB's clustered primary key, secondary-index lookups, transaction behavior, and practical query-plan analysis.
Oracle SQL: Analytic Functions, Execution Plans, and Bind Variables
Use Oracle analytic SQL for ordered calculations, inspect plan operations, and understand why bind-variable behavior matters.
SQL Query Recipes: Joins, Grouping, Window Functions, and Safe Pagination
Build common analytics queries while avoiding duplicate rows, incorrect aggregates, unstable pages, and cross-dialect surprises.
Top-N and Third-Highest Salary Queries in PostgreSQL, MySQL, and Oracle
Choose between distinct salary ranking, row ranking, and pagination syntax while handling ties and NULLs explicitly.
Stored Functions, Procedures, and Triggers: Put Logic in the Right Layer
Compare database routines and triggers with application services, focusing on transaction ownership, portability, and operational visibility.
Database Locks and Isolation: Diagnose Blocking, Deadlocks, and Retries
Understand transaction conflicts, lock waits, isolation anomalies, and safe retry boundaries across relational databases.
Fix Slow SQL: N+1 Queries, Missing Indexes, and Bad Cardinality Estimates
A systematic approach to diagnosing ORM query explosions and slow database plans without guessing at indexes.
Database Internals Advanced: MVCC Anomalies, Cost Models, Sharding, and Storage Engines
Reason about isolation anomalies, query-plan mistakes, B-tree/LSM trade-offs, shard routing, and production-safe index changes.
SQL Event Gaps: LAG, Stable Ordering, and Explicit Window Frames
Investigate missing heartbeats with PostgreSQL interval arithmetic and a separate SQLite integer-millisecond contract.
SQL keyset pagination: continue an order without promising a snapshot
Use a unique ordering tuple and a deliberate visibility policy for reliable page traversal.
PostgreSQL concurrent indexes: reason about locks, retries, and invalid states
Use concurrent index builds without assuming they are lock-free or all-or-nothing.