Index Ktorý Zabil Write Performance: Strata PostgreSQL HOT Updates

Hot updates vyzerali super, kym sa neukazal index bloat. “Pridali sme index pre zlepšenie výkonu a zápisy sa stali 10x pomalšie.” Vinník: nový index na často aktualizovanom stĺpci rozbil PostgreSQL HOT (Heap-Only Tuple) optimalizáciu, meniaci lacné in-place updates na drahé full-row rewrites.

Prostredie: PostgreSQL 15, vysokoobjemová tabuľka session tracking, 50k updates/minútu

Problém

Pred Indexom

-- Originálna tabuľka
CREATE TABLE user_sessions (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    last_seen TIMESTAMP NOT NULL,
    session_data JSONB
);

-- Index na user_id pre lookups
CREATE INDEX idx_sessions_user_id ON user_sessions(user_id);

-- Výkon bol v poriadku
-- UPDATE user_sessions SET last_seen = now() WHERE id = ?
-- → 0.5ms priemer, žiadne problémy s bloatom

Po Pridaní Indexu

-- "Pridajme index pre dotazovanie podľa last_seen!"
CREATE INDEX idx_sessions_last_seen ON user_sessions(last_seen);

-- Zrazu:
-- UPDATE user_sessions SET last_seen = now() WHERE id = ?
-- → 5ms priemer (10x pomalšie!)
-- Table bloat: 300% v priebehu hodín
-- autovacuum neustále beží ale nestíha

Príčina

Ako HOT Updates Fungujú

Normálny PostgreSQL UPDATE (bez HOT):

UPDATE users SET name = 'Nové' WHERE id = 1;

Krok 1: Označ starý riadok ako mŕtvy
┌──────────────────────────────┐
│ Page 1                       │
│ ┌─────────────────────────┐  │
│ │ id=1, name='Staré' [DEAD]│◄─┼── Označ ako mŕtvy
│ └─────────────────────────┘  │
└──────────────────────────────┘

Krok 2: Vlož nový riadok (možno iná page)
┌──────────────────────────────┐
│ Page 2                       │
│ ┌─────────────────────────┐  │
│ │ id=1, name='Nové'       │◄─┼── Nový riadok tu
│ └─────────────────────────┘  │
└──────────────────────────────┘

Krok 3: Aktualizuj VŠETKY indexy na novú lokáciu
├── PRIMARY KEY: update pointer
├── idx_name: update pointer
└── idx_email: update pointer  <-- Aj keď nezmenený!

Výsledok: Drahé! Každý index dotknutý.

HOT Update (Heap-Only Tuple):

UPDATE users SET last_seen = now() WHERE id = 1;
(za predpokladu že last_seen NIE JE indexovaný)

Krok 1: Označ starý riadok, vlož nový v ROVNAKEJ PAGE
┌──────────────────────────────────────────┐
│ Page 1                                   │
│ ┌─────────────────────────┐              │
│ │ id=1, last_seen='10:00' │──┐ reťaz     │
│ │ [DEAD, ukazuje na next] │  │           │
│ └─────────────────────────┘  │           │
│ ┌─────────────────────────┐  │           │
│ │ id=1, last_seen='10:01' │◄─┘           │
│ │ [CURRENT]               │              │
│ └─────────────────────────┘              │
└──────────────────────────────────────────┘

Krok 2: ŽIADNE INDEX UPDATES POTREBNÉ!
Všetky indexy stále ukazujú na originálnu lokáciu.
Reťaz sa sleduje pre nájdenie aktuálnej verzie.

Výsledok: Rýchle! Žiadna údržba indexov.

Kedy Sa HOT Rozbije

HOT požiadavky:
1. Nový riadok sa zmestí do rovnakej page ako starý
2. ŽIADNE INDEXOVANÉ STĹPCE nie sú modifikované

Pasca:
┌─────────────────────────────────────────┐
│ CREATE INDEX idx_last_seen              │
│   ON user_sessions(last_seen);          │
│                                         │
│ Teraz 'last_seen' je indexovaný!        │
│                                         │
│ UPDATE ... SET last_seen = now() ...    │
│                                         │
│ Modifikuje indexovaný stĺpec!           │
│ → HOT update NIE JE MOŽNÝ               │
│ → Full row rewrite + ALL indexes update │
└─────────────────────────────────────────┘

Diagnostika

Skontroluj HOT Update Pomer

-- Skontroluj efektivitu HOT updates
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';

-- Pred pridaním indexu:
-- relname      | n_tup_upd | n_tup_hot_upd | hot_update_percent
-- user_sessions| 1000000   | 980000        | 98.00

-- Po pridaní indexu:
-- relname      | n_tup_upd | n_tup_hot_upd | hot_update_percent
-- user_sessions| 1000000   | 50000         | 5.00  <-- PROBLÉM!

Skontroluj Table Bloat

-- Odhadni 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';

-- Alebo použi pgstattuple rozšírenie
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstattuple('user_sessions');
-- Pozri na dead_tuple_percent - malo by byť <10%

Nájdi Problematický Index

-- Vypíš všetky indexy a ich stĺpce
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;

-- Skontroluj ktoré stĺpce sú často aktualizované
SELECT
    attname,
    n_distinct,
    correlation
FROM pg_stats
WHERE tablename = 'user_sessions';

Riešenie

Možnosť 1: Odstráň Problematický Index

-- Ak naozaj nepotrebuješ query podľa last_seen
DROP INDEX idx_sessions_last_seen;

-- HOT updates sa okamžite obnovia
-- Bloat sa vyčistí autovacuumom

Možnosť 2: Redizajnuj Aby Si Nevyhol Update Indexovaného Stĺpca

-- Namiesto update last_seen, použi separátnu tabuľku
CREATE TABLE session_heartbeats (
    session_id BIGINT REFERENCES user_sessions(id),
    heartbeat_time TIMESTAMP NOT NULL DEFAULT now(),
    PRIMARY KEY (session_id, heartbeat_time)
);

-- Teraz môžeš indexovať heartbeat_time
CREATE INDEX idx_heartbeats_time ON session_heartbeats(heartbeat_time);

-- Inserts (nie updates) nerozbíjajú HOT na hlavnej tabuľke
INSERT INTO session_heartbeats(session_id) VALUES (?);

Možnosť 3: Použi Partial Index

-- Ak dotazuješ len nedávne sessions
CREATE INDEX idx_sessions_recent
ON user_sessions(last_seen)
WHERE last_seen > now() - interval '1 hour';

-- Updates starých sessions nespúšťajú index update
-- (spadajú mimo partial index podmienku)

Možnosť 4: Zvýš fillfactor Pre Povolenie HOT

-- Povol viac miesta pre HOT updates v každej page
ALTER TABLE user_sessions SET (fillfactor = 70);

-- Prebuduj tabuľku pre aplikovanie nového fillfactor
VACUUM FULL user_sessions;

-- Poznámka: Toto nepomôže ak je aktualizovaný indexovaný stĺpec
-- Ale pomáha ak page space bol limitujúci faktor

Možnosť 5: Použi BRIN Index Namiesto

-- BRIN index je oveľa ľahší pre time-series dáta
DROP INDEX idx_sessions_last_seen;

CREATE INDEX idx_sessions_last_seen_brin
ON user_sessions USING BRIN(last_seen);

-- BRIN ukladá len min/max per page range
-- Oveľa menší maintenance overhead
-- Ale: Menej presný pre point queries

Monitoring

PostgreSQL Metriky

# Prometheus postgresql_exporter queries

# HOT update pomer
pg_stat_user_tables_n_tup_hot_upd /
pg_stat_user_tables_n_tup_upd

# Dead tuple pomer
pg_stat_user_tables_n_dead_tup /
(pg_stat_user_tables_n_live_tup + pg_stat_user_tables_n_dead_tup)

# Autovacuum beží príliš často
rate(pg_stat_user_tables_autovacuum_count[1h])

Alert Pravidlá

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: "Nízky HOT update pomer na {{ $labels.relname }}"
          description: "Len {{ $value | humanizePercentage }} updates je HOT"

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

Checklist

## PostgreSQL HOT Update Problém

### Symptómy
- [ ] Write výkon degradoval po pridaní indexu
- [ ] Table bloat rýchlo rastie
- [ ] autovacuum neustále beží
- [ ] HOT update pomer dramaticky klesol

### Diagnostika
- [ ] Skontroluj pg_stat_user_tables pre HOT pomer
- [ ] Identifikuj ktoré indexované stĺpce sú často aktualizované
- [ ] Zmeraj table bloat s pgstattuple
- [ ] Skontroluj či bol nedávno pridaný nový index

### Riešenia
- [ ] Odstráň zbytočný index na aktualizovaných stĺpcoch
- [ ] Redizajnuj aby si vyhol update indexovaných stĺpcov
- [ ] Použi partial index pre obmedzený scope
- [ ] Zváž BRIN pre time-series dáta
- [ ] Uprav fillfactor ak page space je problém

### Prevencia
- [ ] Prehodnoť indexy pred pridaním na write-heavy tabuľky
- [ ] Monitoruj HOT update pomer kontinuálne
- [ ] Dokumentuj ktoré stĺpce sú často aktualizované

Záver

Toto je perfektný “kontra-intuitívny” príbeh:

1. “Pridaj index pre výkon” je štandardná rada
2. Ale na často aktualizovaných stĺpcoch, môže zničiť výkon
3. HOT je neviditeľný kým ho nezmeriaš
4. Symptóm (bloat, pomalé zápisy) zjavne neukazuje na indexovanie

Kľúčový insight: indexy nie sú zadarmo. Na PostgreSQL majú skrytý náklad pre zápisy—nie len insert overhead, ale potenciálne vypnutie HOT optimalizácie ktorá robí updates lacnými.

Súvisiace články

• PostgreSQL work_mem Parallel OOM - Ďalšia PostgreSQL resource pasca
• PostgreSQL Vacuum Tuning - Prevencia bloatu