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.
Search the library
17 matching resultsArticles and interview answers
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&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
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.
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&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.
security
6 minTenant authorization: carry trusted scope into every lookup
Bind resource access to authenticated tenant membership instead of treating object identifiers as permission.