PostgreSQL Under High Write Load: HOT Updates, Fillfactor Tuning, and Eliminating Index Bloat

The Hidden Cost of PostgreSQL Updates: The Index Pointer Multiplier

In high-throughput relational systems, developers frequently treat UPDATE statements as localized field modifications. However, beneath PostgreSQL's Multiversion Concurrency Control (MVCC) architecture, an in-place update does not exist by default. Every time an update executes, PostgreSQL writes an entirely new row version (tuple) into an 8KB heap page, marks the prior version as expired by updating its t_xmax transaction identifier, and—critically—inserts a new index pointer into every single index defined on the table.

Consider an orders table with a primary key, three foreign keys (user_id, merchant_id, warehouse_id), an enum status column, and two timestamp tracking indexes. Even if an update modifies only the status or updated_at column, the database engine writes a new physical leaf entry into all seven B-Tree indexes. When processing 5,000 updates per second, this behavioral characteristic creates catastrophic write amplification, floods shared_buffers with dirty index blocks, drives up disk I/O, and causes exponential index bloat that forces aggressive autovacuum cycles.

1. Heap-Only Tuple (HOT) Mechanics: In-Page Chaining Without Index Churn

To mitigate this systemic write amplification, PostgreSQL introduced the Heap-Only Tuple (HOT) optimization in version 8.3. HOT allows the database engine to write a new tuple version into the exact same 8KB disk page as the previous version without touching or modifying any indexes on the table.

For an update to qualify for HOT optimization, two strict conditions must be satisfied simultaneously:

• No Indexed Columns Modified: None of the columns updated by the query can be referenced in any table index (including composite, partial, or expression indexes).
• Sufficient Page Free Space: The current 8KB heap page where the old tuple resides must possess adequate unallocated space to house the newly created tuple version.

When both conditions are met, the original tuple's line pointer in the page header remains the sole entry referenced by index roots. PostgreSQL creates a forward pointer chain directly inside the heap page: the old tuple's t_ctid header field points directly to the line pointer (ItemPointerData) of the new tuple on the same page:

-- Conceptual representation of HOT Line Pointer Chaining on an 8KB Page:
-- [Page Header]
-- LinePointer 1 (LP_NORMAL)   ---> Points to Old Tuple [t_xmax = 1050, t_ctid = (0, 2)]
-- LinePointer 2 (LP_HOT_REDIRECT) ---> Points to New Tuple [t_xmin = 1050, t_ctid = (0, 2)]
-- Index Scan: Index points ONLY to LinePointer 1.
-- Engine reads LinePointer 1 -> follows internal chain to LinePointer 2 -> retrieves current row!

Because the index leaf entry continues pointing exclusively to Line Pointer 1, zero index writes occur. Furthermore, when subsequent read queries or lightweight page prunings traverse this block, PostgreSQL executes micro-vacuum defragmentation in memory, converting Line Pointer 1 into an LP_REDIRECT pointer that points directly to the latest version, collapsing intermediate chain links.

2. Auditing HOT Efficiency in Production: The Metric That Matters

To determine whether your high-write production tables are leveraging HOT updates or suffering from silent index bloat, inspect the system view pg_stat_user_tables. The core metric is the ratio between n_tup_hot_upd (HOT updates) and n_tup_upd (total updates):

SELECT 
    schemaname,
    relname AS table_name,
    pg_size_pretty(pg_relation_size(relid)) AS table_size,
    pg_size_pretty(pg_indexes_size(relid)) AS indexes_size,
    n_tup_upd AS total_updates,
    n_tup_hot_upd AS hot_updates,
    CASE 
        WHEN n_tup_upd = 0 THEN 0.0
        ELSE ROUND((n_tup_hot_upd::numeric / n_tup_upd::numeric) * 100, 2)
    END AS hot_update_ratio_pct
FROM pg_stat_user_tables
WHERE n_tup_upd > 1000
ORDER BY n_tup_upd DESC
LIMIT 15;

In un-tuned production systems, high-frequency tables such as sessions, user_tokens, task_queues, or order_tracking routinely exhibit HOT update ratios below 10%. This indicates that 90% of updates are writing new entries across all associated indexes, creating massive table and index bloat.

3. Calibrating Table Fillfactor: Reserving Headroom for HOT Updates

Why do HOT updates fail even when non-indexed columns are updated? Because PostgreSQL tables default to fillfactor = 100. When initial INSERT operations populate the table, PostgreSQL packs every 8KB disk page to 100% capacity. When an UPDATE arrives, the engine inspects the page, discovers zero remaining bytes, and is forced to place the new tuple version onto a completely different page. The moment a tuple moves to another page, HOT optimization is broken, and all indexes must be updated.

By lowering a table's fillfactor, you instruct PostgreSQL to stop inserting new rows when the page reaches a specified percentage, reserving the remaining space specifically for subsequent in-page updates:

-- Set fillfactor to 75% for high-write tables (reserves 25% headroom per page)
ALTER TABLE orders SET (fillfactor = 75);

-- Inspect current table options
SELECT relname, reloptions 
FROM pg_class 
WHERE relname = 'orders';

Crucial Operational Detail: Modifying fillfactor via ALTER TABLE applies only to newly allocated pages. Existing, densely packed pages are untouched. To immediately apply the new fillfactor across historical data without downtime, use pg_repack (recommended for zero lock contention) or run an offline table rewrite during a maintenance window:

# Online zero-downtime table rebuild using pg_repack:
pg_repack -h 127.0.0.1 -U postgres -d production_db -t orders

4. Sizing Fillfactor: Mathematical Guidelines

Choosing the correct fillfactor requires balancing disk storage overhead against write frequency:

• Read-Heavy / Append-Only Tables (Logs, Analytics): Maintain fillfactor = 100. Rows are rarely or never updated; reserving headroom wastes RAM in shared_buffers.
• Moderate Update Rates (Accounts, Profiles): Set fillfactor = 85 to 90. Leaves 10–15% headroom, accommodating 1 to 2 updates per row lifecycle.
• High-Velocity Status / Queue Tables (Tasks, Delivery Status, Counters): Set fillfactor = 70 to 75. Provides ample space for repeated status transitions (e.g. PENDING -> PROCESSING -> COMPLETED) on the exact same page.

5. Production Benchmark: Default vs. Tuned Fillfactor (10,000 Updates/Sec)

In a synthetic pgbench stress test updating non-indexed status fields across 5,000,000 records on NVMe SSD storage:

By auditing your HOT update ratios and selectively calibrating fillfactor on write-intensive tables, you eliminate the single largest contributor to database index bloat and sustain predictable, sub-millisecond OLTP response times.