Core Code
Core Code Modern Dev Handbook
DATABASE-ADVANCED Contents
Storage & Index Internals
DB Page Layouts B-Tree Internals Hash, GIN, and GiST Indexes Selectivity & Cardinality Index Maintenance & Bloat Covering & Partial Indexes WAL & Write Amplification MVCC and HOT Updates
Query Planner & Execution
Query Lifecycle Cost-Based Optimizer Basics Reading EXPLAIN ANALYZE Join Strategies Index Scan vs Seq Scan Statistics & Misestimation Plan Regressions Query Optimization Checklist
Transactions & Isolation
ACID Realities Isolation Levels Deep Dive Phantoms, Lost Updates, and Anomalies Deadlocks: Detection and Retries Locking Mechanisms Long-Running Transactions Optimistic vs Pessimistic Concurrency Transaction Safety Checklist
Replication & High Availability
Replication Types Postgres Replication Model MySQL Replication Model Replica Lag and Read-After-Write Failover Strategies Backup Strategies Point-in-Time Recovery HA Architecture Patterns
Sharding & Horizontal Scale
Vertical vs Horizontal Scaling Sharding Patterns Shard Key Design Cross-Shard Queries Rebalancing and Resharding Multi-Region Data Architectures Global Consistency Tradeoffs Sharding Operational Checklist
Postgres vs MySQL Tradeoffs
Storage Engine Differences JSON Support Comparison Indexing Capability Differences Replication Differences Ecosystem and Extensions Interpreting Benchmarks Choosing Between Postgres and MySQL Migration Surface Area
Caching & Redis Strategies
Caching Patterns Redis Data Structures in Production Cache Invalidation Cache Stampede and Thundering Herd TTL Design Strategies Read-Heavy Optimization Cache Consistency Models Cache Observability Checklist
Data Modeling at Scale
Normalization vs Denormalization Soft Delete Patterns Event-Driven Modeling Time-Series Modeling Multi-Tenant Schema Design Audit Trail Design Schema Migration Strategies Data Modeling Checklist

MVCC and HOT Updates

MVCC visibility, HOT updates, and how update patterns change IO, bloat, and latency.
On this page

MVCC: Multiple Versions Are the Default

Many production-grade databases implement concurrency using MVCC (Multi-Version Concurrency Control). The key consequence is simple: an UPDATE often creates a new row version instead of editing the row in place.

This is why “update-heavy” workloads create bloat, why vacuum/cleanup matters, and why long-running transactions are dangerous even if they look harmless.

Row Versions: What UPDATE Really Does

In MVCC systems, each row has versioning metadata. When you update a row:

  • The old version remains (still visible to older transactions).
  • A new version is written (visible to newer transactions after commit).
  • Indexes may need to be updated to point to the new version.

So a logical UPDATE can behave like INSERT + DELETE internally. This is a core source of write amplification and bloat.

Visibility: Why Readers Do Not Block Writers (Usually)

MVCC aims to reduce blocking by allowing readers to see a consistent snapshot while writers create new versions. In practice:

  • Readers choose which versions are visible based on transaction snapshots.
  • Writers create new versions and commit them.
  • Cleanup happens later when it is safe (when no snapshot needs old versions).

This improves concurrency but shifts complexity to cleanup and storage management.

Dead Tuples and Why They Accumulate

Old row versions become “dead” only after no active transaction can still see them. Until then, they remain on disk, occupying pages and degrading cache density.

Dead versions cause:

  • More pages to scan (even with indexes)
  • Larger indexes (more pointers and churn)
  • More vacuum/cleanup work
  • Higher tail latency due to increased IO

HOT Updates: The Optimization Everyone Wants

HOT (Heap-Only Tuple) updates are an optimization where an UPDATE can avoid updating indexes if indexed columns do not change. The new row version stays on the same page, and index pointers remain valid.

When HOT updates happen, you get:

  • Lower index write cost
  • Less index bloat
  • Better write throughput

But HOT updates have strict conditions (engine-specific). Conceptually they require:

  • Indexed columns unchanged
  • Enough free space on the same page for the new version
  • Visibility rules allow linking versions without index updates

Update Patterns That Disable HOT

Even small changes can defeat HOT optimizations:

  • Updating any indexed column, including composite keys
  • Frequent page splits or insufficient free space (fillfactor too high)
  • Wide rows that rarely fit in-place after update
-- Example: small change but affects index usage
-- If status is indexed, updating it forces index maintenance
UPDATE orders
SET status = 'SHIPPED'
WHERE id = 123;

Vacuum/Cleanup Pressure: The “Garbage Collector” of MVCC

MVCC needs cleanup to reclaim dead versions. If cleanup cannot keep up, bloat grows. This manifests as:

  • Increasing table and index sizes
  • Worse cache hit rate
  • More IO per query
  • Longer maintenance operations

In production terms: vacuum/cleanup is not optional. It is your storage garbage collector.

Long-Running Transactions: The Most Common MVCC Incident

A long-running transaction holds an old snapshot. That prevents cleanup from removing dead versions that might still be visible to that snapshot. This creates a chain reaction:

  • Dead versions cannot be reclaimed
  • Table grows rapidly (bloat)
  • Indexes grow and degrade
  • IO increases, latency rises
  • Eventually: disk pressure, failovers, or outages

The worst part: the long transaction might be “idle in transaction” or a report query that someone forgot.

-- Typical dangerous pattern: transaction opened and left idle
BEGIN;
SELECT * FROM orders WHERE user_id = 42;
-- application forgets to COMMIT/ROLLBACK
-- vacuum cannot fully reclaim versions needed by this snapshot

Diagnosing MVCC Problems (Practical)

Symptoms that often indicate MVCC cleanup trouble:

  • Table size grows rapidly without row count growth
  • Vacuum time increases or falls behind
  • p95/p99 latency increases gradually
  • Index-only scans stop happening (visibility metadata not advancing)
-- Postgres: find long-running transactions (common incident trigger)
SELECT
  pid,
  usename,
  state,
  xact_start,
  now() - xact_start AS xact_age,
  query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start ASC;

Design Levers: Make MVCC Work for You

  • Keep transactions short and scoped
  • Avoid holding transactions open across network calls
  • Batch writes intelligently (but do not keep TX open too long)
  • Use fillfactor/free space to enable in-page updates where applicable
  • Minimize updates to indexed columns
  • Prefer append-only patterns for some workloads (event logs)

Failure Modes in Production

  • Idle in transaction: holds snapshot, blocks cleanup, causes bloat spiral.
  • Vacuum lag: cleanup cannot keep up with update churn.
  • Index bloat explosion: frequent index updates due to changing indexed columns.
  • HOT disabled: fillfactor too high or indexed columns updated; write cost spikes.
  • Disk pressure: dead versions accumulate until storage exhaustion.
  • Tail latency drift: more pages per query; p99 worsens first.

Operational Checklist

  • Alert on long-running transactions and “idle in transaction” sessions.
  • Track table size growth vs row count growth to detect bloat early.
  • Monitor vacuum/cleanup progress and backlog indicators.
  • Review update patterns: which indexed columns are updated frequently?
  • Use appropriate fillfactor/free space to reduce page splits and enable in-page updates.
  • Validate index-only scans are happening where expected; if not, investigate visibility/maintenance.
  • Keep batch jobs from holding snapshots for long periods.
  • After large churn events, plan maintenance (vacuum/analyze/rebuild) explicitly.
  • Have a runbook to kill or cancel offending long transactions safely.
  • Capacity plan for worst-case bloat during incidents; keep disk headroom.
  • Educate application teams: transaction scope is an availability concern, not just correctness.

Summary

MVCC improves concurrency by creating new versions instead of blocking reads. The cost is cleanup. When cleanup is blocked or falls behind, bloat grows and performance collapses slowly, then suddenly. HOT updates and healthy maintenance can keep MVCC fast. Long transactions can destroy it. Production database engineering is about controlling that lifecycle.