PostgreSQL Wartung — VACUUM, REINDEX, CLUSTER

Ziel: Die drei zentralen Wartungs-Operationen verstehen. Wann brauche ich was,
welche Parameter haben welche Bedeutung, welche locken die Tabelle, welche laufen
online, und wie stelle ich Autovacuum so ein dass es die Arbeit von allein macht.

Inhaltsverzeichnis

1. Warum PostgreSQL Wartung braucht — MVCC-Grundlagen
2. VACUUM — tote Tupel aufräumen
3. VACUUM FULL — Tabelle physisch verkleinern
4. VACUUM FREEZE — Transaction-ID-Wraparound verhindern
5. ANALYZE — Statistiken aktualisieren
6. Autovacuum — die tägliche Arbeit
7. REINDEX — Indexe neu bauen
8. CLUSTER — Tabelle physisch sortieren
9. pg_repack — Online-Alternative zu FULL/CLUSTER
10. Diagnose-Queries
11. Wartungs-Schedule — was wann?

1. Warum Wartung

PostgreSQL nutzt MVCC (Multi-Version Concurrency Control). Das bedeutet:

• UPDATE ändert eine Zeile nicht in-place — die alte Version bleibt bestehen, eine neue Version wird angehängt
• DELETE markiert die Zeile als gelöscht, entfernt sie aber nicht physisch
• Alte/gelöschte Versionen (Dead Tupel) müssen regelmäßig aufgeräumt werden
• Jede Transaktion bekommt eine XID (Transaction ID) — ein 32-Bit-Zähler, der irgendwann überläuft und Freeze braucht

Was passiert wenn man Wartung vernachlässigt

• Table-Bloat: Tabelle belegt 3× mehr Disk-Space als nötig
• Index-Bloat: Index-Scans werden langsamer (mehr Pages zu lesen)
• Schlechte Query-Pläne: Planner nutzt veraltete Statistiken → Seq Scans wo Index besser wäre
• Transaction-ID-Wraparound (im Extremfall): Datenbank geht in read-only Mode — Katastrophe

2. VACUUM

Was VACUUM macht (im Kern)

1. Durchläuft Tabelle und Indexe
2. Markiert Dead Tupel als wiederverwendbar (aber gibt keinen Disk-Space an OS zurück)
3. Aktualisiert Free Space Map (FSM) → neue INSERTs können die Lücken nutzen
4. Aktualisiert Visibility Map (VM) → erlaubt Index-Only-Scans
5. Pflegt Statistiken (bei VACUUM ANALYZE auch Sample-basierte)

Syntax-Varianten

-- Standard: aufräumen, tabelle nicht locken
VACUUM market.stock_price;

-- Mit ANALYZE (Statistiken aktualisieren)
VACUUM (ANALYZE) market.stock_price;

-- Mit Output
VACUUM (VERBOSE, ANALYZE) market.stock_price;

-- Ganze Datenbank
VACUUM (ANALYZE, VERBOSE);

-- Nur Indexe
VACUUM (INDEX_CLEANUP on, ANALYZE) market.stock_price;

-- Parallel (PG 13+)
VACUUM (PARALLEL 4) market.stock_price;

-- Mehr Detail (PG 13+)
VACUUM (VERBOSE, ANALYZE, SKIP_LOCKED) market.stock_price;

Wichtige VACUUM-Optionen

VACUUM-Log interpretieren

INFO:  vacuuming "market.stock_price"
INFO:  table "stock_price": found 125430 removable, 4892100 nonremovable row versions in 89453 pages
DETAIL:  1245 dead row versions cannot be removed yet, oldest xmin: 845729100
         ...
         CPU: user: 12.34 s, system: 2.10 s, elapsed: 18.45 s

Was das bedeutet:

• 125430 removable — aufgeräumte Dead Tupel
• 4892100 nonremovable — lebende Zeilen (normal)
• 1245 dead row versions cannot be removed yet — Problem: Eine lange Transaktion

(oldest xmin) blockiert VACUUM. Sucher: pg_stat_activity → xact_start älter als
die Laufzeit der langen Tx

• elapsed: 18.45 s — Laufzeit

Wann manuelles VACUUM?

Normalerweise nie — Autovacuum macht den Job. Manuelles VACUUM brauchst du bei:

• Massiven DELETE- oder UPDATE-Batches (hunderttausende Zeilen auf einmal)
• Nach großen Bulk-Imports (um Statistiken und VM zu aktualisieren)
• Bei Verdacht auf Freeze-Probleme (VACUUM FREEZE)
• Zur Problemanalyse (VACUUM VERBOSE)

3. VACUUM FULL

Unterschied zu normalem VACUUM

Wann VACUUM FULL?

• Tabelle hat > 50 % Bloat und Bloat wächst nicht weiter (z.B. nach einmaligem Massen-DELETE)
• Du brauchst Disk-Space zurück, weil Platte voll läuft
• Regelmäßige VACUUM haben nicht gereicht (Tabelle hatte dauerhafte Long-Running-Tx)

Wann nicht?

• Produktion ohne Maintenance-Fenster
• Wenn pg_repack verfügbar ist (siehe Abschnitt 9)
• Wenn Autovacuum seit Monaten nicht ordentlich lief → erst Ursache fixen

Disk-Space-Bedarf

VACUUM FULL schreibt die Tabelle komplett neu in eine neue Datei, dann wird die
alte gelöscht. Du brauchst kurzfristig doppelt so viel Disk-Space wie die
Tabelle (plus Indexe, die auch neu gebaut werden).

-- Prüfen ob genug Platz da ist
SELECT
    pg_size_pretty(pg_total_relation_size('market.stock_price')) AS current_size
  , pg_size_pretty(pg_total_relation_size('market.stock_price') * 2) AS needed_during_full;

4. VACUUM FREEZE

Das Problem: Transaction-ID-Wraparound

PostgreSQL-XIDs sind 32 Bit → nach ~2 Milliarden Transaktionen läuft der Zähler
über. Damit eine alte Zeile nicht plötzlich aus der Zukunft zu kommen scheint, muss
PG regelmäßig freezen: alte XIDs werden durch einen Spezialwert (FrozenXID)
ersetzt, der als „ewig sichtbar" zählt.

Wie Freeze passiert

Automatisch durch Autovacuum — konfiguriert über:

• autovacuum_freeze_max_age (Default: 200 Mio XIDs)

Wenn die älteste XID einer Tabelle mehr als diesen Abstand vom aktuellen Zähler hat,
startet Autovacuum einen aggressiven Freeze-Run (auch wenn Tabelle sonst nicht
„dirty" genug ist).

• vacuum_freeze_min_age (Default: 50 Mio)

Wenn eine XID älter als das ist, wird sie während jedes VACUUMs gefreezed.

• vacuum_freeze_table_age (Default: 150 Mio)

Ab diesem Alter wird ein aggressives Scan der ganzen Tabelle gemacht (statt nur
VM-markierte Pages).

Manuelles Freeze

-- Aggressiv alles freezen (teuer, aber sicher)
VACUUM (FREEZE, VERBOSE) market.stock_price;

-- Ganze DB freezen (Wartungsfenster!)
VACUUM (FREEZE, VERBOSE);

XID-Alter überwachen

SELECT
    datname
  , age(datfrozenxid) AS xid_age
  , 2147483647 - age(datfrozenxid) AS xids_before_wraparound
  , round(100.0 * age(datfrozenxid) / 2147483647, 2) AS pct_used
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

Aktionsschwellen:

• < 10 % — alles gut
• 10-50 % — normal, Autovacuum wird arbeiten
• 50-80 % — Autovacuum-Performance prüfen (läuft er überhaupt durch?)
• > 80 % — Wartungsfenster planen, manuellen VACUUM FREEZE durchführen
• > 90 % — Notfall, autovacuum_freeze_max_age global runterstellen reicht nicht mehr

Pro Tabelle

SELECT
    schemaname
  , relname
  , age(c.relfrozenxid) AS xid_age
  , pg_size_pretty(pg_total_relation_size(c.oid)) AS size
  , last_vacuum
  , last_autovacuum
FROM pg_stat_user_tables s
JOIN pg_class c ON c.oid = s.relid
ORDER BY age(c.relfrozenxid) DESC
LIMIT 20;

Tabellen mit hohem xid_age und alter last_autovacuum → priorisieren.

5. ANALYZE

Was ANALYZE macht

Sampelt die Tabelle (Default: 300 × default_statistics_target = 30.000 Zeilen)
und schreibt Statistiken in pg_statistic:

• Histogramm der Werteverteilung
• Most-Common-Values-Liste
• NULL-Anteil
• Korrelation physische vs. logische Reihenfolge
• Distinct-Value-Schätzung

Der Query-Planner nutzt diese Statistiken, um Kardinalitäten zu schätzen und
Pläne zu wählen.

Syntax

-- Eine Tabelle
ANALYZE market.stock_price;

-- Nur bestimmte Spalte
ANALYZE market.stock_price (ticker);

-- Verbose
ANALYZE VERBOSE market.stock_price;

-- Ganze DB
ANALYZE;

Wann manuell?

• Nach großem Bulk-Import (Autovacuum kommt erst mit Verzögerung)
• Wenn Query-Pläne schlecht aussehen und last_analyze alt ist
• Nach Schema-Änderungen mit neuen Spalten
• Vor Performance-Tests (damit konsistente Ausgangslage)

Statistiken-Target pro Spalte erhöhen

Bei wichtigen Filter-Spalten (häufig im WHERE) kann feinere Statistik helfen:

-- Default ist 100, erhöhen auf 1000 für wichtige Spalte
ALTER TABLE market.stock_price ALTER COLUMN ticker SET STATISTICS 1000;
ANALYZE market.stock_price;

Mehr Samples = genauere MCV-Listen = bessere Pläne bei skewed Daten.

6. Autovacuum

Was Autovacuum tut

Ein Hintergrund-Prozess (launcher + N worker) prüft regelmäßig alle Tabellen.
Eine Tabelle wird ge-VACUUMed wenn:

n_dead_tup > autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor × n_live_tup)

Mit Defaults (threshold=50, scale_factor=0.2) passiert das wenn 20 % der Tabelle
sich geändert haben (plus 50 Zeilen).

Analog für ANALYZE: threshold=50, scale_factor=0.1 → 10 %.

Die wichtigsten Parameter

Pro Tabelle individuell tunen

Große Tabellen mit vielen Updates brauchen oft aggressiveres Autovacuum, nicht
das globale Default:

-- 100-Mio-Zeilen-Tabelle: 20% wäre 20 Mio Zeilen — viel zu viel
ALTER TABLE market.stock_price SET (
    autovacuum_vacuum_scale_factor = 0.02,   -- 2% statt 20%
    autovacuum_analyze_scale_factor = 0.01,  -- 1% statt 10%
    autovacuum_vacuum_cost_limit = 1000      -- schneller durchlaufen
);

Wann welche Einstellung?

• OLTP mit hohem Write-Volume: scale_factor kleiner (0.05-0.1) für Hot-Tables,

cost_delay niedrig (1-2 ms) für schnellen Durchlauf, max_workers höher (4-6)

• Big-Analytics-DB: Auf großen Tabellen Scale-Factor auf 0.02 setzen, sonst passiert

Autovacuum nur alle paar Wochen

• Shared Server: cost_delay höher (5-10 ms) damit VACUUM nicht alle IO frisst

Autovacuum-Aktivität prüfen

SELECT
    schemaname
  , relname
  , n_live_tup
  , n_dead_tup
  , round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct
  , last_vacuum
  , last_autovacuum
  , last_analyze
  , last_autoanalyze
  , vacuum_count
  , autovacuum_count
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC
LIMIT 30;

Tabellen wo dead_pct > 20% + last_autovacuum alt → Autovacuum kommt nicht hinterher.

Live-Autovacuum anschauen

SELECT
    pid
  , now() - xact_start AS duration
  , datname
  , query
FROM pg_stat_activity
WHERE backend_type = 'autovacuum worker'
ORDER BY xact_start;

Blocker identifizieren

Autovacuum kommt nicht hinterher? Oft ist eine lange offene Transaktion schuld:

SELECT
    pid
  , now() - xact_start AS tx_duration
  , state
  , wait_event_type
  , substring(query, 1, 100) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND now() - xact_start > interval '10 minutes'
ORDER BY xact_start;

Solange so eine Tx offen ist, kann VACUUM keine Tupel entfernen die nach ihrem
Start entstanden sind — n_dead_tup wächst und wächst.

7. REINDEX

Wann REINDEX?

• Index-Bloat > 30 % (siehe postgres-index-optimization.md)
• Nach Korruption (selten, aber möglich bei Kernel-Bugs)
• Index-Definition soll geändert werden (z.B. text_pattern_ops hinzufügen) → besser

neuen Index parallel anlegen, dann alten droppen

Syntax

-- Einzelnen Index
REINDEX INDEX market.idx_stock_ticker;

-- Alle Indexe einer Tabelle
REINDEX TABLE market.stock_price;

-- Alle Indexe eines Schemas
REINDEX SCHEMA market;

-- Alle Indexe der DB
REINDEX DATABASE marketinsider;

-- ONLINE-Variante (PG 12+) — kein Lock
REINDEX INDEX CONCURRENTLY market.idx_stock_ticker;
REINDEX TABLE CONCURRENTLY market.stock_price;
REINDEX DATABASE CONCURRENTLY marketinsider;

Lock-Verhalten

Auf Produktion immer CONCURRENTLY. Dauert länger, IO höher, aber keine Blockade.

Fortschritt beobachten (PG 12+)

SELECT
    p.pid
  , now() - a.query_start AS duration
  , p.phase
  , round(100.0 * p.blocks_done / nullif(p.blocks_total, 0), 1) AS pct_blocks
  , round(100.0 * p.tuples_done / nullif(p.tuples_total, 0), 1) AS pct_tuples
FROM pg_stat_progress_create_index p
JOIN pg_stat_activity a ON a.pid = p.pid;

Aufräumen nach abgebrochenem REINDEX CONCURRENTLY

-- Zombie-Indexe finden
SELECT
    n.nspname
  , c.relname
  , pg_size_pretty(pg_relation_size(c.oid)) AS size
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname LIKE '%_ccnew%' OR c.relname LIKE '%_ccold%';

-- Droppen
DROP INDEX CONCURRENTLY schemaname.idxname_ccnew;

8. CLUSTER

Was CLUSTER macht

CLUSTER schreibt die Tabelle physisch nach einem Index sortiert neu. Danach
liegen Zeilen, die der Index als benachbart kennzeichnet, auch auf Disk benachbart —
große Win bei Range-Scans und BRIN-Indexen.

Syntax

-- Erstmalig: Index für CLUSTER markieren
ALTER TABLE market.stock_price CLUSTER ON idx_price_date;

-- Clustering ausführen
CLUSTER market.stock_price;

-- Mit Index explizit
CLUSTER market.stock_price USING idx_price_date;

-- Alle zuvor markierten Tabellen
CLUSTER;

-- Verbose
CLUSTER VERBOSE market.stock_price;

Lock-Verhalten

CLUSTER nimmt ACCESS EXCLUSIVE LOCK — wie VACUUM FULL. Blockiert also alles
auf der Tabelle. Kein CONCURRENTLY-Modus eingebaut.

Alternative für Produktion: pg_repack (siehe Abschnitt 9).

Wann CLUSTER?

• BRIN-Indexe wirken nur bei physisch sortierten Daten → CLUSTER macht BRIN überhaupt

erst nützlich

• Range-Scans auf einer Spalte sind der Hot-Path der Anwendung
• Tabelle ist groß genug dass Index-Scans mit Random-IO spürbar sind (nicht komplett

im Buffer)

Wichtig: CLUSTER ist einmalig

Nach CLUSTER werden neue INSERTs/UPDATEs wieder an beliebigen freien Stellen
eingefügt. Die Sortierung degradiert mit der Zeit. Muss regelmäßig wiederholt werden
(oder die Tabelle ist append-only / wird extern sortiert geladen).

Korrelation messen (wie gut sortiert?)

SELECT
    tablename
  , attname
  , correlation
FROM pg_stats
WHERE schemaname = 'market'
  AND tablename = 'stock_price'
ORDER BY abs(correlation) DESC;

correlation nahe ±1.0 → perfekt sortiert. Nahe 0 → wild gemischt, CLUSTER würde
viel bringen.

9. pg_repack

Was pg_repack ist

Extension, nicht Teil des Core-PostgreSQL. Macht VACUUM FULL und CLUSTER
ohne Access Exclusive Lock — stattdessen wird die Tabelle im Hintergrund in eine
Schatten-Tabelle kopiert, Änderungen werden via Trigger+Log synchron gehalten, am
Ende wird getauscht (kurzer Lock für den Swap).

# Installation (Package)
sudo apt install postgresql-17-repack

# In DB aktivieren
psql -c "CREATE EXTENSION pg_repack;"

Benutzung

# Tabelle online repacken (Bloat entfernen)
pg_repack -h localhost -U postgres -d marketinsider -t market.stock_price

# Mit Clustering nach Index
pg_repack -h localhost -U postgres -d marketinsider -t market.stock_price -I idx_price_date

# Nur Indexe einer Tabelle
pg_repack -h localhost -U postgres -d marketinsider -t market.stock_price --only-indexes

# Einzelnen Index
pg_repack -h localhost -U postgres -d marketinsider -i market.idx_price_date

# Parallel
pg_repack -h localhost -U postgres -d marketinsider -t market.stock_price --jobs=4

# Dry-Run
pg_repack -h localhost -U postgres -d marketinsider -t market.stock_price --dry-run

Voraussetzungen

• Tabelle braucht Primary Key oder Unique-Constraint mit NOT NULL
• Freier Disk-Space ≥ Tabellen-Größe
• Tabelle muss zu anderen Schemas kompatibel sein (keine exotischen Inherits)

Trade-offs vs. VACUUM FULL / CLUSTER

Für MarketInsider / 24/7-Betrieb ist pg_repack der Standard-Weg für große
Bloat-Bereinigungen.

10. Diagnose-Queries

Bloat je Tabelle (Heuristik, kein pgstattuple nötig)

WITH constants AS (
    SELECT current_setting('block_size')::numeric AS bs, 23 AS hdr, 8 AS ma
), bloat_info AS (
    SELECT
        ma, bs, schemaname, tablename
      , (datawidth + (hdr + ma - case when hdr%ma=0 THEN ma ELSE hdr%ma END))::numeric AS datahdr
      , (maxfracsum * (nullhdr + ma - case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END)) AS nullhdr2
    FROM (
        SELECT
            schemaname, tablename, hdr, ma, bs
          , SUM((1-null_frac)*avg_width) AS datawidth
          , MAX(null_frac) AS maxfracsum
          , hdr + (
              SELECT 1 + count(*)/8
              FROM pg_stats s2
              WHERE null_frac <> 0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename
          ) AS nullhdr
        FROM pg_stats s, constants
        GROUP BY 1,2,3,4,5
    ) foo
), table_bloat AS (
    SELECT
        schemaname, tablename, cc.relpages, bs
      , CEIL((cc.reltuples * ((datahdr + ma - (case when datahdr%ma=0 THEN ma ELSE datahdr%ma END)) + nullhdr2 + 4)) / (bs-20::float)) AS otta
    FROM bloat_info
    JOIN pg_class cc ON cc.relname = bloat_info.tablename
    JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = bloat_info.schemaname AND nn.nspname <> 'information_schema'
)
SELECT
    schemaname || '.' || tablename AS table_name
  , pg_size_pretty((bs * (relpages - otta))::bigint) AS bloat_size
  , round(100 * (relpages - otta)::numeric / nullif(relpages, 0), 1) AS bloat_pct
  , pg_size_pretty((bs * relpages)::bigint) AS total_size
FROM table_bloat
WHERE relpages > 100
ORDER BY bs * (relpages - otta) DESC
LIMIT 20;

Dead-Tupel-Report

SELECT
    schemaname
  , relname
  , n_live_tup
  , n_dead_tup
  , round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct
  , pg_size_pretty(pg_relation_size(relid)) AS size
  , last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC
LIMIT 20;

XID-Wraparound-Risiko

SELECT
    schemaname || '.' || relname AS table_name
  , age(relfrozenxid) AS xid_age
  , round(100.0 * age(relfrozenxid) / 2000000000, 1) AS wrap_pct
  , pg_size_pretty(pg_total_relation_size(c.oid)) AS size
  , last_autovacuum
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_stat_user_tables s ON s.relid = c.oid
WHERE c.relkind = 'r'
  AND n.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
ORDER BY age(c.relfrozenxid) DESC
LIMIT 20;

Blocker für Autovacuum (Long-Running-Tx)

SELECT
    pid
  , now() - xact_start AS tx_duration
  , state
  , wait_event_type
  , wait_event
  , backend_xmin
  , substring(query, 1, 100) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND now() - xact_start > interval '5 minutes'
ORDER BY xact_start;

Autovacuum-Log-Analyse

In der postgresql.conf:

log_autovacuum_min_duration = 1000   # alles >1s loggen

Im Log dann:

automatic vacuum of table "market.stock_price":
  index scans: 1
  pages: 45 removed, 87234 remain, 0 skipped due to pins, 12453 skipped frozen
  tuples: 45892 removed, 8923412 remain, 1245 are dead but not yet removable
  buffer usage: 234567 hits, 4521 misses, 123 dirtied
  avg read rate: 12.3 MB/s, avg write rate: 0.3 MB/s
  system usage: CPU 3.21 s, elapsed 18.45 s

tuples: X are dead but not yet removable > 0 ist das Warnsignal → es gibt eine
blockierende Long-Running-Tx.

11. Wartungs-Schedule

Default-Schedule für MarketInsider-ähnliche OLTP+Analytics-DBs

Nie ohne Grund

• VACUUM FULL auf Produktion ohne Downtime-Fenster
• CLUSTER auf Produktion (pg_repack --cluster stattdessen)
• REINDEX ohne CONCURRENTLY
• Autovacuum global aggressiver machen wenn nur eine Tabelle Probleme hat

(besser pro-Tabelle tunen)

Minimales Monitoring

Setze diese Alerts:

1. Dead-Tupel-Ratio > 30 % auf Tabellen > 1 GB
2. XID-Age > 50 % auf einer DB
3. Autovacuum-Worker nicht aktiv trotz n_dead_tup > 1 Mio
4. Offene Transaktion > 1 Stunde
5. Disk-Usage wachsend ohne INSERT-Anstieg (typisch Bloat)

Checkliste — gesunde Wartung

• [ ] Autovacuum läuft regelmäßig (siehe last_autovacuum auf allen großen Tabellen)
• [ ] log_autovacuum_min_duration = 1000 aktiv, Logs werden reviewed
• [ ] Große Tabellen haben individuelle autovacuum_*_scale_factor
• [ ] XID-Age aller DBs < 50 %
• [ ] Keine Long-Running-Transaktionen > 1h in Produktion
• [ ] pg_repack installiert und dokumentiert
• [ ] Bloat-Report läuft wöchentlich
• [ ] Maintenance-Fenster definiert für seltene harte Operationen
• [ ] idle_in_transaction_session_timeout gesetzt (z.B. 15 min)
• [ ] pg_stat_statements aktiviert für Query-Pattern-Analyse

Weiterführend

• PostgreSQL Docs — Routine Vacuuming
• PostgreSQL Docs — VACUUM
• pg_repack Documentation
• Verwandt: PostgreSQL Indexe optimieren — Wartung, Monitoring, Einrichtung, PostgreSQL Performance-Settings, PostgreSQL HugePages aktivieren