The Index That Killed Write Performance: Losing PostgreSQL HOT Updates

Hot updates felt like a win until the index bloat showed up. “We added an index to improve performance, and writes became 10x slower.” The support ticket came from our session tracking team. They’d deployed a new index on Friday afternoon—standard practice for a read optimization—and by Monday morning, their database was in crisis. Write latency had gone from sub-millisecond to 5ms average, with spikes to 50ms. The table had bloated to 3x its normal size overnight. Autovacuum was running constantly but couldn’t keep up.

The counter-intuitive culprit: the new index on the last_seen column broke PostgreSQL’s HOT (Heap-Only Tuple) optimization. Every update to that timestamp column now required updating every index on the table, not just inserting a new tuple version in the same page. What had been a cheap in-place update became an expensive full-row rewrite with cascading index maintenance.

This experience taught me that indexes aren’t free on write-heavy workloads. The decision to add an index isn’t just “will this make queries faster?” It’s also “what columns does this table frequently update, and will this index interfere with HOT optimization?”

The most frustrating part was that nothing in the query explain plan told us this was happening. The writes looked normal—they just took 10x longer. The only visible symptoms were indirect: bloat, vacuum pressure, and latency. It took correlating the timing with the index deployment to find the root cause.

Environment: PostgreSQL 15, high-volume session tracking table, 50k updates/minute

The Problem

Before the Index

-- Original table
CREATE TABLE user_sessions (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    last_seen TIMESTAMP NOT NULL,
    session_data JSONB
);

-- Index on user_id for lookups
CREATE INDEX idx_sessions_user_id ON user_sessions(user_id);

-- Performance was fine
-- UPDATE user_sessions SET last_seen = now() WHERE id = ?
-- → 0.5ms average, no bloat issues

After Adding the Index

-- "Let's add an index to query by last_seen!"
CREATE INDEX idx_sessions_last_seen ON user_sessions(last_seen);

-- Suddenly:
-- UPDATE user_sessions SET last_seen = now() WHERE id = ?
-- → 5ms average (10x slower!)
-- Table bloat: 300% within hours
-- autovacuum constantly running but can't keep up

Root Cause

How HOT Updates Work

To understand why the index caused such dramatic degradation, you need to understand how PostgreSQL handles updates differently from other databases.

PostgreSQL uses Multi-Version Concurrency Control (MVCC), which means it never updates rows in place. Instead, every UPDATE creates a new row version and marks the old one as dead. This is great for concurrency—readers never block writers—but it creates a lot of dead tuples that need cleanup.

The HOT optimization is PostgreSQL’s clever solution to minimize the cost of updates. When you update a row and the modified columns aren’t indexed, PostgreSQL can insert the new row version in the same page as the old one and create a “chain” linking them. Existing index entries still point to the original location, and following the chain finds the current version. No index updates needed.

But when you update an indexed column, the optimization breaks. The index entry for the old value can’t just point to the new value—the index is sorted, and the new value might belong in a completely different place. PostgreSQL must insert a new index entry, which is much more expensive than the in-place tuple chain.

Normal PostgreSQL UPDATE (without HOT):

UPDATE users SET name = 'New' WHERE id = 1;

Step 1: Mark old row as dead
┌──────────────────────────────┐
│ Page 1                       │
│ ┌─────────────────────────┐  │
│ │ id=1, name='Old' [DEAD] │◄─┼── Mark as dead
│ └─────────────────────────┘  │
└──────────────────────────────┘

Step 2: Insert new row (possibly different page)
┌──────────────────────────────┐
│ Page 2                       │
│ ┌─────────────────────────┐  │
│ │ id=1, name='New'        │◄─┼── New row here
│ └─────────────────────────┘  │
└──────────────────────────────┘

Step 3: Update ALL indexes to point to new location
├── PRIMARY KEY: update pointer
├── idx_name: update pointer
└── idx_email: update pointer  <-- Even if unchanged!

Result: Expensive! Every index touched.

HOT Update (Heap-Only Tuple):

UPDATE users SET last_seen = now() WHERE id = 1;
(assuming last_seen is NOT indexed)

Step 1: Mark old row, insert new in SAME PAGE
┌──────────────────────────────────────────┐
│ Page 1                                   │
│ ┌─────────────────────────┐              │
│ │ id=1, last_seen='10:00' │──┐ chain     │
│ │ [DEAD, points to next]  │  │           │
│ └─────────────────────────┘  │           │
│ ┌─────────────────────────┐  │           │
│ │ id=1, last_seen='10:01' │◄─┘           │
│ │ [CURRENT]               │              │
│ └─────────────────────────┘              │
└──────────────────────────────────────────┘

Step 2: NO INDEX UPDATES NEEDED!
All indexes still point to original row location.
Chain is followed to find current version.

Result: Fast! No index maintenance.

When HOT Breaks

HOT requirements:
1. New row fits in same page as old row
2. NO INDEXED COLUMNS are modified

The trap:
┌─────────────────────────────────────────┐
│ CREATE INDEX idx_last_seen              │
│   ON user_sessions(last_seen);          │
│                                         │
│ Now 'last_seen' is indexed!             │
│                                         │
│ UPDATE ... SET last_seen = now() ...    │
│                                         │
│ Modifies indexed column!                │
│ → HOT update NOT POSSIBLE               │
│ → Full row rewrite + ALL indexes update │
└─────────────────────────────────────────┘

Diagnosis

Check HOT Update Ratio

-- Check HOT update effectiveness
SELECT
    relname,
    n_tup_upd,
    n_tup_hot_upd,
    CASE WHEN n_tup_upd > 0
         THEN round(100.0 * n_tup_hot_upd / n_tup_upd, 2)
         ELSE 0
    END AS hot_update_percent
FROM pg_stat_user_tables
WHERE relname = 'user_sessions';

-- Before adding index:
-- relname      | n_tup_upd | n_tup_hot_upd | hot_update_percent
-- user_sessions| 1000000   | 980000        | 98.00

-- After adding index:
-- relname      | n_tup_upd | n_tup_hot_upd | hot_update_percent
-- user_sessions| 1000000   | 50000         | 5.00  <-- PROBLEM!

Check Table Bloat

-- Estimate table bloat
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
    pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) AS table_size,
    pg_size_pretty(pg_indexes_size(schemaname || '.' || tablename)) AS index_size
FROM pg_tables
WHERE tablename = 'user_sessions';

-- Or use pgstattuple extension
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstattuple('user_sessions');
-- Look at dead_tuple_percent - should be <10%

Find the Problematic Index

-- List all indexes and their columns
SELECT
    i.relname AS index_name,
    a.attname AS column_name,
    ix.indisunique
FROM pg_index ix
JOIN pg_class t ON t.oid = ix.indrelid
JOIN pg_class i ON i.oid = ix.indexrelid
JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = ANY(ix.indkey)
WHERE t.relname = 'user_sessions'
ORDER BY i.relname, a.attnum;

-- Check which columns are frequently updated
SELECT
    attname,
    n_distinct,
    correlation
FROM pg_stats
WHERE tablename = 'user_sessions';

The Fix

Option 1: Remove the Problematic Index

-- If you don't really need to query by last_seen
DROP INDEX idx_sessions_last_seen;

-- HOT updates resume immediately
-- Bloat will be cleaned by autovacuum

Option 2: Redesign to Avoid Updating Indexed Column

-- Instead of updating last_seen, use a separate table
CREATE TABLE session_heartbeats (
    session_id BIGINT REFERENCES user_sessions(id),
    heartbeat_time TIMESTAMP NOT NULL DEFAULT now(),
    PRIMARY KEY (session_id, heartbeat_time)
);

-- Now you can index heartbeat_time
CREATE INDEX idx_heartbeats_time ON session_heartbeats(heartbeat_time);

-- Inserts (not updates) don't break HOT on main table
INSERT INTO session_heartbeats(session_id) VALUES (?);

Option 3: Use Partial Index

-- If you only query recent sessions
CREATE INDEX idx_sessions_recent
ON user_sessions(last_seen)
WHERE last_seen > now() - interval '1 hour';

-- Updates to old sessions don't trigger index update
-- (they fall outside the partial index condition)

Option 4: Increase fillfactor to Enable HOT

-- Allow more room for HOT updates in each page
ALTER TABLE user_sessions SET (fillfactor = 70);

-- Rebuild table to apply new fillfactor
VACUUM FULL user_sessions;

-- Note: This won't help if indexed column is updated
-- But helps if page space was the limiting factor

Option 5: Use BRIN Index Instead

-- BRIN index is much lighter for time-series data
DROP INDEX idx_sessions_last_seen;

CREATE INDEX idx_sessions_last_seen_brin
ON user_sessions USING BRIN(last_seen);

-- BRIN only stores min/max per page range
-- Much less maintenance overhead
-- But: Less precise for point queries

Monitoring

PostgreSQL Metrics

# Prometheus postgresql_exporter queries

# HOT update ratio
pg_stat_user_tables_n_tup_hot_upd /
pg_stat_user_tables_n_tup_upd

# Dead tuple ratio
pg_stat_user_tables_n_dead_tup /
(pg_stat_user_tables_n_live_tup + pg_stat_user_tables_n_dead_tup)

# Autovacuum running too often
rate(pg_stat_user_tables_autovacuum_count[1h])

Alert Rules

groups:
  - name: postgresql-hot
    rules:
      - alert: LowHOTUpdateRatio
        expr: |
          (pg_stat_user_tables_n_tup_hot_upd /
           pg_stat_user_tables_n_tup_upd) < 0.5
        for: 30m
        labels:
          severity: warning
        annotations:
          summary: "Low HOT update ratio on {{ $labels.relname }}"
          description: "Only {{ $value | humanizePercentage }} of updates are HOT"

      - alert: HighTableBloat
        expr: |
          pg_stat_user_tables_n_dead_tup > 100000
        for: 1h
        labels:
          severity: warning
        annotations:
          summary: "High dead tuple count on {{ $labels.relname }}"

Checklist

## PostgreSQL HOT Update Issue

### Symptoms
- [ ] Write performance degraded after adding index
- [ ] Table bloat increasing rapidly
- [ ] autovacuum running constantly
- [ ] HOT update ratio dropped dramatically

### Diagnosis
- [ ] Check pg_stat_user_tables for HOT ratio
- [ ] Identify which indexed columns are frequently updated
- [ ] Measure table bloat with pgstattuple
- [ ] Check if new index was added recently

### Fixes
- [ ] Remove unnecessary index on updated columns
- [ ] Redesign to avoid updating indexed columns
- [ ] Use partial index for limited scope
- [ ] Consider BRIN for time-series data
- [ ] Adjust fillfactor if page space is issue

### Prevention
- [ ] Review indexes before adding to write-heavy tables
- [ ] Monitor HOT update ratio continuously
- [ ] Document which columns are frequently updated

Conclusion

This is a perfect “counter-intuitive” story that challenges the common advice of “just add an index.”

The fundamental insight is that PostgreSQL indexes have a hidden cost for writes—not just the obvious insert overhead, but potentially disabling the HOT optimization that makes updates cheap. A table with 50k updates per minute and 98% HOT rate is barely breaking a sweat. The same table with 5% HOT rate is doing 50x more index maintenance, generating 50x more dead tuples, and requiring 50x more vacuum work.

The diagnosis is surprisingly simple once you know to look: check the hot_update_percent in pg_stat_user_tables. If it dropped dramatically after adding an index, you’ve found your culprit. The fix is usually either removing the index, redesigning to avoid updating the indexed column, or using a partial index that covers fewer rows.

Key principles to remember:

1. “Add index for performance” is incomplete advice—consider write impact
2. Check HOT ratio before and after adding indexes to write-heavy tables
3. Frequently-updated columns are dangerous to index
4. Bloat and vacuum pressure are symptoms, not root causes
5. Monitor HOT ratio continuously on production tables

The lesson I took from this incident: before adding any index to a table with significant write volume, I now check which columns are frequently updated and ensure the new index doesn’t cover them. A few minutes of analysis prevents a weekend of crisis.

Related Articles

• PostgreSQL work_mem Parallel OOM - Another PostgreSQL resource trap
• PostgreSQL Vacuum Tuning - Preventing bloat