VD
Vicktor Lambok Desrony Senior Backend & Systems Architect
← Back to Digital Garden / Database Internals Oct 12, 2026 ⏱ 2 min read (312 words)

Inside PostgreSQL MVCC, HOT Updates, and a 42k TPS Sub-Millisecond Ledger

Deconstructing 8KB heap page internals, write amplification from secondary B-Tree indexes, and how Heap-Only Tuples (HOT) + append-only partitioning unlock extreme OLTP throughput.

#postgresql #database #mvcc #storage-engine #fintech
THROUGHPUT: 42,600 TPS (ACID)
P99 LATENCY: 1.9 ms Commit
Verbosity
ARCHITECTURAL BREAKDOWN

PostgreSQL implements Multi-Version Concurrency Control (MVCC) by never overwriting live row data in place. While this guarantees non-blocking reads (readers never block writers), high-frequency UPDATE workloads suffer severe Write Amplification if 8KB heap pages run out of room for in-page HOT chains.
01MVCC Tuple Anatomy & Write Amplification
Stage 01 // Storage Internals

Why UPDATE Is Secretly an INSERT + Tombstone

When your backend executes UPDATE ledger_accounts SET balance_cents = balance_cents + 500 WHERE account_id = $1, PostgreSQL does not overwrite the existing 8-byte integer in place. Instead:

  1. It stamps the current transaction ID (txid) into the old tuple’s xmax field.
  2. It allocates a brand-new physical tuple with xmin = txid and appends it to an 8KB heap page.
  3. If the new tuple lands on a different 8KB page, PostgreSQL must insert a new index entry into every single B-Tree index on that table—even if none of the indexed columns changed!
1 non-indexed column UPDATE on a table with 6 indexes = 1 Heap Write + 6 B-Tree Leaf Writes

The Silent IOPS Killer: Index write amplification is the #1 reason high-write PostgreSQL clusters saturate NVMe bandwidth long before CPU limits are reached.

Inline Engineering Pulse #pg-mvcc-write-amp
Progressive Concept Unlocking

PostgreSQL Write Path & Heap-Only Tuple (HOT) Mechanics

Scroll to trace a transaction from XLogInsert WAL append to Autovacuum visibility map

1 / 4 Unlocked
1. XLogInsert & WAL Buffer Append
Sequential NVMe Fsync0.18ms

Tuple changes are first written as XLOG records into shared WAL buffers and flushed sequentially.

2. Shared Buffer Page Dirtying (8KB)
xmin / xmax MVCC Header Tagging0.05ms
3. Heap-Only Tuple (HOT) Traversal
Zero B-Tree Index Write Amplification0.02ms
4. Visibility Map & Dead Tuple Reclamation
Index-Only Scan ReadyAsync
02Engineering 98.5% Heap-Only Tuple (HOT) Updates
Stage 02 // Page Tuning

Leaving Headroom in 8KB Pages with fillfactor

By default, PostgreSQL packs INSERTed tuples into 8KB pages up to fillfactor = 100. That means the very first UPDATE on a full page is forced onto a different page—breaking the HOT chain and dirtying every index.

By lowering fillfactor = 75 on high-churn tables and tuning per-table autovacuum_vacuum_scale_factor = 0.01, 2KB of every 8KB page is reserved exclusively for same-page tuple versions. In our production telemetry, this single change elevated HOT update efficiency from 14% to 98.5% and cut NVMe write IOPS by 4.1x.
03Eliminating Hot-Row Lock Contention at 42k TPS
Stage 03 // Ledger Architecture

From Row-Level Locks to Append-Only Hash Partitions

Even with 98.5% HOT updates, a single viral merchant account receiving 2,500 settlements per second serializes on row-level FOR NO KEY UPDATE locks.

To scale past 40,000 TPS with sub-2ms P99 latency, we separate Write Ingestion from Balance Materialization:

  • Write Path: Pure INSERT into a 32-way hash-partitioned ledger_entries journal (zero row-lock contention).
  • Rollup Path: Micro-batched workers claim 500 entries via FOR UPDATE SKIP LOCKED, folding deltas into account_balances in a single vectorized upsert.
FOR UPDATE SKIP LOCKED LIMIT 500 -- Collapses 500 row locks into 1 batched upsert

Check Stage 03 in the sticky right-hand Code Spec and Telemetry tabs to compare row-lock vs append-rollup benchmarks!

Inline Engineering Pulse #pg-skip-locked-rollup