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.
Search the library
15 matching resultsArticles and interview answers
databases
8 minPostgreSQL concurrent indexes: reason about locks, retries, and invalid states
Use concurrent index builds without assuming they are lock-free or all-or-nothing.
Q&A
Q&AHow does the InnoDB primary key affect secondary indexes?
InnoDB secondary-index entries carry the primary-key value used to find the clustered row.
Read the concise answer
InnoDB organizes table rows by the clustered primary key. Secondary indexes store their indexed columns plus the primary-key value, so a query requesting columns absent from the secondary index may need a second lookup. Wide primary keys therefore increase the footprint of every secondary index.
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.
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&AWhat should you inspect after CREATE INDEX CONCURRENTLY fails?
Check active work and catalog validity before dropping or retrying.
Read the concise answer
Concurrent builds use multiple phases and can leave an invalid index. Inspect the server error and pg_index readiness/validity, waits, and transactions; do not assume the operation was atomic or load-free.
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.
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.