databases
10 minSQL 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.
Search the library
18 matching resultsArticles and interview answers
databases
10 minFix 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.
Q&A
Q&AWhy can’t a window-function rank usually be filtered in WHERE?
Window values are computed after WHERE in logical query processing.
Read the concise answer
WHERE filters source rows before window calculations are available. Put the window expression in a common table expression or derived table, then filter its rank in the outer query. This also makes the ranking input set explicit.
databases
10 minPostgreSQL 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.
Q&A
Q&AHow do you confirm and fix an ORM N+1 query?
Count repeated statements per request, then batch-load without changing result cardinality.
Read the concise answer
Trace or log normalized SQL for one request and look for the same child lookup repeated once per parent row. Replace the loop with an eager/batch load or a query at the correct grain. Verify query count and returned rows; a broad join can fix round trips while accidentally multiplying results.
databases
19 minDatabase 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.
databases
9 minMySQL InnoDB: Transactions, Indexes, and Reading EXPLAIN
Understand InnoDB's clustered primary key, secondary-index lookups, transaction behavior, and practical query-plan analysis.
databases
10 minRelational Databases and SQL Foundations: Model the Invariant First
Learn tables, keys, constraints, joins, transactions, and query shape before choosing a specific database engine.