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
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.
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
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 minTop-N and Third-Highest Salary Queries in PostgreSQL, MySQL, and Oracle
Choose between distinct salary ranking, row ranking, and pagination syntax while handling ties and NULLs explicitly.
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.
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.
Q&A
Q&AHow do you find the third-highest distinct salary in SQL?
Use DENSE_RANK for distinct values and state whether you want salary values or employee rows.
Read the concise answer
Filter out NULL salaries, compute DENSE_RANK() OVER (ORDER BY salary DESC), then select rank 3. DENSE_RANK treats equal salaries as the same rank. PostgreSQL, MySQL 8+, and Oracle support this window-function approach; if you instead mean the third row, use ROW_NUMBER with a deterministic tie-breaker.