Deep EVM #27: PostgreSQL-Performance im grossen Massstab — Indizes, VACUUM und Abfrageoptimierung

Die drei Saeulen der PostgreSQL-Performance

PostgreSQL ist von Haus aus bemerkenswert schnell, aber im grossen Massstab — Dutzende Millionen Zeilen, Tausende Abfragen pro Sekunde — muessen Sie seine Interna verstehen. Dieser Artikel behandelt die drei Saeulen: Indizes, VACUUM und Abfrageoptimierung.

Index-Typen

B-Tree (Standard)

Der Standard-Indextyp. Unterstuetzt Gleichheits- und Bereichsabfragen:

CREATE INDEX idx_transactions_block ON transactions (block_number);

Partielle Indizes

Indizieren Sie nur Zeilen, die eine Bedingung erfuellen:

CREATE INDEX idx_transactions_pending
    ON transactions (created_at)
    WHERE status = 'pending';

Ein partieller Index auf 0,1% einer 34M-Zeilen-Tabelle ist 170KB statt 800MB.

Covering-Indizes (INCLUDE)

CREATE INDEX idx_tx_from_covering
    ON transactions (from_addr)
    INCLUDE (value_wei, block_number, tx_hash);

Ermoeglicht Index-Only-Scans ohne Heap-Zugriff.

BRIN-Indizes

Fuer Zeitreihen oder monoton steigende Daten:

CREATE INDEX idx_transactions_block_brin
    ON transactions USING BRIN (block_number)
    WITH (pages_per_range = 128);

~100KB fuer 34M Zeilen (vs. ~800MB B-Tree).

VACUUM: Der stille Performance-Killer

PostgreSQL verwendet MVCC. Bei UPDATE oder DELETE wird die alte Version nicht entfernt — sie wird als tot markiert. VACUUM gibt den Platz toter Tupel zurueck.

Autovacuum-Konfiguration

ALTER TABLE transactions SET (
    autovacuum_vacuum_threshold = 10000,
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_analyze_threshold = 5000,
    autovacuum_vacuum_cost_delay = 2
);

Standard-Skalierungsfaktor von 20% fuer eine 34M-Zeilen-Tabelle bedeutet: Autovacuum wartet bis 6,8 Millionen tote Tupel. Das ist katastrophal.

EXPLAIN ANALYZE

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM transactions
WHERE block_number BETWEEN 18000000 AND 18100000
ORDER BY value_wei DESC
LIMIT 100;

Wichtige Metriken:

• actual time: Echte Ausfuehrungszeit in Millisekunden
• rows: Tatsaechlich verarbeitete Zeilen
• Buffers: shared hit: Seiten aus dem Cache (gut)
• Buffers: shared read: Seiten von Disk (langsam)

Haeufige Anti-Patterns

1. Sequential Scan auf grosser Tabelle — Fehlender Index
2. Nested Loop ohne Index — N+1-Abfragemuster
3. Uebermaeassiger Sort-Speicher — work_mem erhoehen
4. Ungenaue Zeilenschaetzungen — ANALYZE ausfuehren

Fazit

PostgreSQL-Performance im grossen Massstab steht auf drei Saeulen: die richtigen Indizes fuer Ihre Abfragemuster, aggressives Autovacuum-Tuning und systematische Abfrageoptimierung mit EXPLAIN ANALYZE. Raten Sie nicht — messen Sie.