databases
5 minSQL Event Gaps: LAG, Stable Ordering, and Explicit Window Frames
Investigate missing heartbeats with PostgreSQL interval arithmetic and a separate SQLite integer-millisecond contract.
Search the library
29 matching resultsArticles and interview answers
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 does top-N syntax differ across PostgreSQL, MySQL, and Oracle?
PostgreSQL and MySQL commonly use LIMIT; Oracle supports FETCH FIRST and legacy ROWNUM patterns.
Read the concise answer
PostgreSQL and MySQL support ORDER BY with LIMIT; standard FETCH FIRST syntax is also available in modern versions. Oracle supports FETCH FIRST in current versions; older ROWNUM queries must sort in an inner query before limiting in an outer query. Always specify deterministic ordering and test the exact target version.
databases
10 minOracle 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.
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&AWhy can a long PostgreSQL transaction delay vacuum cleanup?
An old transaction snapshot may still need row versions that newer transactions have replaced.
Read the concise answer
PostgreSQL MVCC keeps row versions visible to active snapshots. A long-running transaction can hold an old snapshot open, so vacuum cannot yet remove some dead versions. Bound transaction lifetimes and investigate idle-in-transaction sessions when table bloat grows.
databases
6 minSQL keyset pagination: continue an order without promising a snapshot
Use a unique ordering tuple and a deliberate visibility policy for reliable page traversal.
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.