The Production Incident: An Innocent Index Kills Write Throughput

Imagine this scenario: your engineering team notices that an admin dashboard query sorting by updated_at on a high-throughput orders table is performing slow sequential scans. To fix it, a developer files a quick pull request to add an index on updated_at:

CREATE INDEX CONCURRENTLY idx_orders_updated_at ON orders (updated_at);

The pull request passes code review, lands in production, and the dashboard query latency drops from 4 seconds to 3 milliseconds. Success? Not quite. Within two hours, disk I/O utilization on the primary PostgreSQL node spikes to 100%, replication lag on read replicas begins to climb linearly, and average API update latency degrades from 8ms to over 600ms. Autovacuum workers start running continuously, struggling to keep up with massive table and index bloat.

What caused this operational failure? The newly added index destroyed one of PostgreSQL's most critical internal optimizations: Heap-Only Tuple (HOT) updates.

Under the Hood: PostgreSQL MVCC vs. Heap-Only Tuples

To understand why this happens, we must look at how PostgreSQL implements Multi-Version Concurrency Control (MVCC). Unlike storage engines like MySQL InnoDB—which update rows in-place and log previous versions in UNDO logs—PostgreSQL creates a completely new row version (tuple) in the heap table file whenever an UPDATE statement runs.

In standard MVCC execution:

  • PostgreSQL writes the new tuple to any heap page with available free space.
  • It inserts new index pointers into every single index defined on that table, even for indexes whose indexed columns were not modified by the update.

If a table has 10 indexes, a single row update produces 1 write to the heap page and 10 separate writes across 10 B-Tree index pages. This phenomenon is known as write amplification, and it severely degrades write throughput while accelerating index bloat.

Enter Heap-Only Tuples (HOT)

To combat write amplification, PostgreSQL 8.3 introduced the Heap-Only Tuple (HOT) optimization. An update is performed as a HOT update if two mandatory conditions are met:

  1. No Indexed Columns Change: The UPDATE statement does not modify any column that is referenced in any index definition on the table.
  2. Sufficient Page Space: The target heap page containing the original tuple has enough free space to host the new tuple version.

When a HOT update occurs, PostgreSQL places the new tuple version on the exact same heap page as the old tuple. It links the old tuple to the new tuple using a light intra-page line pointer chain. Crucially, zero index entries are modified or added. Existing index pointers continue pointing to the root tuple, and PostgreSQL traverses the local HOT chain in memory to find the latest valid version.

The Root Cause: Breaking the HOT Chain

In high-throughput write systems, many tables feature an automated trigger or application logic that updates an updated_at timestamp on every write:

CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_orders_updated_at
BEFORE UPDATE ON orders
FOR EACH ROW
EXECUTE FUNCTION update_timestamp();

Before adding idx_orders_updated_at, updating fields like status or total_amount qualified as HOT updates (assuming sufficient page space). However, as soon as an index is placed on updated_at, every single UPDATE statement modifies an indexed column.

This permanently breaks HOT optimization. PostgreSQL is forced to fall back to standard MVCC updates, writing new entries across all B-Tree indexes for every update execution. The result is catastrophic write amplification, massive index bloat, and IOPS exhaustion.

Diagnosing HOT Failures in Production

You can verify whether your PostgreSQL cluster is suffering from broken HOT updates by querying the system statistics view pg_stat_user_tables. Look closely at the ratio between total updates (n_tup_upd) and successful HOT updates (n_tup_hot_upd):

SELECT 
    schemaname,
    relname,
    n_tup_upd,
    n_tup_hot_upd,
    ROUND((n_tup_hot_upd::numeric / NULLIF(n_tup_upd, 0)) * 100, 2) AS hot_update_ratio
FROM 
    pg_stat_user_tables
WHERE 
    n_tup_upd > 1000
ORDER BY 
    n_tup_upd DESC;

In a healthy write-heavy system, the hot_update_ratio should ideally hover between 80% and 95%. If your ratio drops to 0% on a table receiving hundreds of updates per second, HOT optimization is completely disabled.

Production-Grade Remediation Strategies

1. Tuning Table Fillfactor

Even if no indexed columns are updated, HOT updates will fail if heap pages are 100% full. By default, PostgreSQL uses a fillfactor of 100 (packing pages completely during initial INSERTs). Lowering fillfactor reserves space on each page specifically for future HOT updates:

-- Set fillfactor to 80% to reserve 20% page space for HOT updates
ALTER TABLE orders SET (fillfactor = 80);

-- Note: Fillfactor changes only apply to newly inserted pages.
-- To apply it to existing pages without full table locks, use pg_repack:
-- pg_repack --table=orders --dbname=production_db

2. Replacing Global Indexes with Partial Indexes

If queries against updated_at only target active or incomplete records (such as pending processing jobs), replace full index coverage with a partial index:

-- Drop the global index that broke HOT updates
DROP INDEX CONCURRENTLY idx_orders_updated_at;

-- Create a partial index targeting only active processing rows
CREATE INDEX CONCURRENTLY idx_orders_pending_updated_at 
ON orders (updated_at) 
WHERE status IN ('PENDING', 'PROCESSING');

With this partial index, updating completed orders (status = 'COMPLETED') will no longer modify an indexed column, allowing PostgreSQL to resume HOT updates for the vast majority of historical data modifications.

3. Decoupling High-Frequency Operational Columns

If a table contains rapidly mutating operational state (e.g., last_heartbeat_at, retry_count, status), decouple these columns into a dedicated 1:1 state table that carries minimal or zero indexes:

CREATE TABLE order_state (
    order_id BIGINT PRIMARY KEY REFERENCES orders(id),
    status VARCHAR(32) NOT NULL,
    updated_at TIMESTAMPTZ NOT NULL
) WITH (fillfactor = 70);

By keeping the lightweight order_state table free of extra secondary indexes, updates remain extremely fast, page-local, and HOT-friendly.

Key Takeaways

  • PostgreSQL MVCC out-of-place writes: Standard updates write to all indexes on a table, causing severe write amplification and bloat.
  • HOT updates save performance: HOT updates keep writes confined to a single heap page and avoid index updates entirely, provided no indexed columns change and page headroom exists.
  • Beware of updated_at indexes: Placing an index on auto-updating timestamp columns disables HOT updates across all write operations on that table.
  • Monitor stats regularly: Track n_tup_hot_upd / n_tup_upd via pg_stat_user_tables to ensure your high-volume tables maintain a healthy HOT ratio above 80%.
  • Apply fillfactor and partial indexes: Tune fillfactor between 70–80 for write-heavy tables and use partial indexes to limit index update overhead.