What is MVCC in PostgreSQL and how does it affect performance

MVCC — Multi-Version Concurrency Control

PostgreSQL uses MVCC to allow readers and writers to not block each other. Instead of locking rows during reads, it keeps multiple versions of each row.

How it works

Every row has hidden system columns: xmin (transaction that inserted it) and xmax (transaction that deleted/updated it). A query sees only rows where its transaction ID is within the valid visibility range.

SELECT xmin, xmax, * FROM users LIMIT 5;

Dead tuples and bloat

When a row is updated or deleted, the old version is not immediately removed — it becomes a dead tuple. Dead tuples waste space and slow down sequential scans.

VACUUM

VACUUM users;          -- reclaim dead tuples
VACUUM ANALYZE users;  -- reclaim + update statistics
VACUUM FULL users;     -- full rewrite (locks table, use carefully)

Autovacuum runs automatically based on thresholds. On high-churn tables, tune it more aggressively:

ALTER TABLE orders
  SET (autovacuum_vacuum_scale_factor = 0.01,
       autovacuum_analyze_scale_factor = 0.005);

HOT (Heap Only Tuple) updates avoid index updates when only non-indexed columns change, reducing write amplification significantly.