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.
Search the library
4 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&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.
Q&A
Q&AHow can write skew occur under snapshot or repeatable-read isolation?
Two transactions can read the same invariant and update disjoint rows, so row-level conflicts do not detect the violation.
Read the concise answer
If each transaction sees a snapshot where another row satisfies a rule, they may update different rows and both commit, leaving the combined invariant false. Use a constraint, lock a shared coordination row, or use serializable isolation with whole-transaction retry. Exact Repeatable Read semantics differ by engine.