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.
Search the library
11 matching resultsArticles and interview answers
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.
Q&A
Q&ACan an index on (a, b, c) efficiently serve a query filtering only b and c?
Usually the leading-column prefix matters; validate any skip-scan behavior on the target engine.
Read the concise answer
A composite B-tree is generally organized by its leading column, so omitting a can prevent efficient use of the index for b/c-only predicates. Some engines and versions may use skip scans under particular statistics, but do not assume it. Compare a candidate (b, c) index with the existing index using representative plans and write-cost measurements.
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.
devops
5 minDevOps Foundations: Safe Changes, Health Signals, and Rollback Boundaries
Design a release as a controlled change to code, configuration, and data, with explicit health checks and a recovery plan that matches what can actually be reversed.
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.
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.