Optimizing Postgres Write Throughput: B-Trees, HOT Updates, and Heap Fragmentation

The Anatomy of Postgres Writes

As applications scale, we often focus on read latency via indexing strategies. However, in write-heavy workloads, the bottleneck frequently shifts from CPU cycles to I/O operations caused by heap fragmentation and unnecessary index maintenance. Understanding how PostgreSQL manages physical storage at the tuple level is critical for any cloud architect.

Understanding Heap-Only Tuple (HOT) Updates

When you perform an UPDATE in PostgreSQL, it does not modify the data in-place. Instead, it creates a new version of the row (a new tuple). If the updated columns are indexed, Postgres must update the index as well, leading to index bloat and extra I/O. Enter HOT Updates.

A HOT update occurs when the new tuple is inserted into the same physical data page as the old tuple. Because the row's physical location (RID) remains effectively unchanged relative to the index, Postgres skips the index update. This drastically reduces write amplification.

The Cost of Index Bloat

Every non-HOT update creates a pointer record. Over time, frequent updates on indexed columns lead to massive index bloat. This increases the depth of your B-tree, meaning more page reads to fetch a single record. To monitor this, use the following query to inspect your fill factor efficiency:

SELECT relname, n_tup_upd, n_tup_hot_upd, 
  (n_tup_hot_upd::float / n_tup_upd) AS hot_ratio
FROM pg_stat_user_tables
WHERE n_tup_upd > 0;

Managing Heap Fragmentation

Fragmentation is a silent performance killer. When rows are updated or deleted, they leave "dead" space (bloat) that requires VACUUM to reclaim. In high-concurrency environments, autovacuum settings must be tuned to prevent the bloat from snowballing. If your hot_ratio is low, your application is likely performing full index updates on every change. Consider:

• Denormalizing sparingly: Moving frequently updated columns to a separate table can prevent fragmentation of your main data entity.
• Reducing Indexed Columns: Only index columns that are frequently used in WHERE clauses.
• Monitoring Autovacuum: Aggressively tune autovacuum_vacuum_scale_factor for large, high-update tables to keep the table size manageable.

By prioritizing the health of your physical page layout, you shift from reactive troubleshooting to proactive performance engineering. High-performance Postgres is not just about the query—it is about how the engine interacts with the disk storage engine.