Postgres-Index-Audit mit pg_stat: langsame und ungenutzte Indizes finden

Was ein Postgres‑Index‑Audit ist (und was nicht)

Ein Postgres‑Index‑Audit ist ein Realitätscheck, basierend darauf, was Ihre Datenbank tatsächlich in Produktion tut. Es beantwortet praktische Fragen: Welche Abfragen sind langsam, welche Indizes werden genutzt, welche Indizes bleiben unangetastet und wohin die Zeit fließt.

Das Ziel ist nicht, „schön zu haben“‑Indizes zu sammeln. Es geht darum, die am stärksten belasteten Pfade zu beschleunigen und vermeidbare Kosten zu reduzieren.

In der Entwicklung sehen Indizes oft gut aus, weil der Dev‑Traffic aufgeräumt ist: kleine Tabellen, vorhersehbare Abfragen und nur wenige gängige Pfade. Produktion ist unordentlicher. Tabellen wachsen, Filter und Sortierungen variieren, Randfälle tauchen auf und viele Nutzer greifen gleichzeitig auf dieselben Endpunkte zu. Eine Abfrage, die bei 10.000 Zeilen „schnell genug“ wirkt, kann bei 10 Millionen zusammenbrechen, wenn sie Full‑Table‑Scans oder große Sorts ausführt.

Ein gutes Audit hilft Ihnen dabei:

• Konkrete langsame Abfragemuster unter realer Last zu identifizieren
• Zu bestätigen, welche Indizes heute helfen
• Indizes zu finden, die Speicher kosten und Schreibvorgänge verlangsamen, ohne Nutzen
• Zu entscheiden, was hinzuzufügen oder anzupassen ist und wie Sie beweisen, dass es geholfen hat

„Echte Abfragestatistiken“ heißt: gemessenes Verhalten aus Ihrem Live‑Workload — wie oft eine Abfrage läuft, wie lange sie braucht, wie viele Zeilen sie zurückgibt und ob sie spiky ist. Allein vom Schema zu raten ist riskant. Ein Schema kann andeuten, was indiziert werden könnte, aber nicht sagen, welche Filter Nutzer tatsächlich verwenden oder welche Abfragen die meiste Zeit verbrauchen.

Es hilft auch, Trade‑offs klar zu halten. Indizes sind nicht umsonst:

• Sie beschleunigen Lesevorgänge (SELECT), wenn sie zu Ihren Filtern, Joins und Sortierungen passen.
• Sie können Schreibzugriffe (INSERT/UPDATE/DELETE) verlangsamen, weil jede Änderung Indizes aktualisieren muss.
• Sie erhöhen Speicherbedarf und Wartungsaufwand (Vacuuming, Bloat, Backups).

Ein Audit heißt also nicht „überall Indizes hinzufügen“, sondern „die richtigen hinzufügen, Verschwendung entfernen und Ergebnisse mit Statistiken verifizieren“. Das sieht man oft in KI‑generierten Codebasen (zum Beispiel von Cursor, Replit oder v0), wo Abfragen ohne sorgfältige Indizierung ausgeliefert werden. In Demos läuft die App, in Produktion findet der Traffic schnell die langsamen Pfade.

Bevor Sie starten: Sorgen Sie dafür, dass Ihre Statistiken vertrauenswürdig sind

Ein Index‑Audit ist nur so gut wie die Statistiken, auf denen es basiert. Wenn Ihre Datenbank neu gestartet, ein Failover durchlaufen oder Statistiken zurückgesetzt wurden, können Sie am Ende den falschen Index hinzufügen oder einen nützlichen löschen.

Prüfen Sie zuerst, ob Sie die benötigten Views lesen können. Die meisten Signale stammen aus Systemviews wie pg_stat_*. Bei Managed Postgres sind Sie vielleicht kein Superuser, aber üblicherweise können Monitoring‑Rollen die gängigen pg_stat‑Views lesen.

Vor dem Zahlenziehen führen Sie ein paar schnelle Checks durch:

• Stellen Sie sicher, dass Sie pg_stat_database, pg_stat_user_tables und pg_stat_user_indexes ohne Fehler abfragen können.
• Prüfen Sie, ob die Statistiken kürzlich zurückgesetzt wurden (nach Neustart, Failover oder manuellem Reset).
• Bestätigen Sie, dass die Anfragen Ihrer App nicht größtenteils Postgres umgehen (ein Cache‑Layer kann die echte DB‑Last verbergen).
• Führen Sie das Audit auf derselben Datenbank aus, die echten Traffic bedient.

Wenn verfügbar, macht pg_stat_statements das Audit deutlich zuverlässiger, weil es reales Abfrageverhalten über die Zeit aggregiert (Aufrufe, Gesamtzeit, Mittelwert). Es muss zuvor installiert und aktiviert worden sein und zeichnet nur Abfragen auf, nachdem es zu sammeln begonnen hat.

Wählen Sie ein Beobachtungsfenster, das zur Nutzung der App passt. Ein guter Default sind ein paar echte Geschäftszyklen, inklusive Spitzenstunden.

Vermeiden Sie verzerrte Fenster, es sei denn, die Verzerrung ist genau das Problem, das Sie lösen wollen. Führen Sie kein Audit direkt nach:

• Einer größeren Deployment, die Abfragemuster verändert hat
• Einer Backfill‑Aktion oder Migration, die die DB geflutet hat
• Einem Bulk‑Import, der kein normaler Traffic ist
• Einem Zwischenfall, bei dem das System degradiert war

Eine häufige Falle: Ein frisch eingeführtes Feature löst versehentlich eine N+1‑Query‑Schleife aus. Ihre Statistiken schreien dann nach Indizes, obwohl die echte Lösung darin besteht, die Query‑Explosion zu stoppen.

Die pg_stat‑Views, auf die Sie sich verlassen werden (kurze Übersicht)

Ein praktisches Audit startet mit einem Prinzip: Vertrauen Sie dem, was Ihre Datenbank unter realem Traffic beobachtet hat.

Diese Views decken das meiste ab, was Sie brauchen:

• pg_stat_statements: Ein Scoreboard normalisierter Abfragen mit Summen und Durchschnitten für Zeit, Aufrufe und Zeilen. Hier finden Sie Abfragemuster, die die meiste Latenz verursachen.
• pg_stat_user_tables: Wie jede Tabelle angesprochen wird. Das wichtigste Signal ist, ob sie hauptsächlich über Indizes oder über sequenzielle (Full)‑Scans angesprochen wird.
• pg_stat_user_indexes: Wie oft jeder Index verwendet wird. Nützlich, um Indizes zu finden, die wichtig erscheinen, aber nahezu gar nicht genutzt werden.
• pg_statio_user_tables: Ob Tabellenlesungen aus Cache oder von der Platte kommen. Hohe Plattenzugriffe korrelieren oft mit für Nutzer sichtbarer Langsamkeit.
• pg_statio_user_indexes: Dasselbe Cache‑vs‑Platte‑Bild, aber für Indexseiten.

Ein häufiges Missverständnis: Nutzungszählungen und Impact sind nicht dasselbe.

• Nutzungszählungen (wie „wie oft ein Index gescannt wurde") zeigen Popularität, nicht die eingesparte Zeit.
• Zeit‑ und I/O‑Signale (aus pg_stat_statements und pg_statio_*) zeigen das tatsächliche Schmerzbild.

Ein guter Workflow ist: Finden Sie teure Abfrageformen in pg_stat_statements, dann nutzen Sie Tabellen‑ und Index‑Statistiken, um zu verstehen, warum diese Abfragen langsam sind (Table Scans, Plattenzugriffe, schlechte Selektivität usw.).

Denken Sie auch daran, dass Statistiken nicht permanent sind. Sie werden bei Neustart zurückgesetzt und können manuell gelöscht werden. „Unbenutzt“ heißt manchmal nur „seit dem letzten Reset unbenutzt“ — stellen Sie sicher, dass Ihr Fenster normalen Traffic abdeckt.

Schritt‑für‑Schritt: Ein einfaches Index‑Audit in 30–60 Minuten

Das ist ein schnelles Audit basierend auf realen Traffic‑Signalen. Wenn Sie eine App geerbt haben, bei der Seiten zufällig langsam werden oder timeouts auftreten, helfen diese Snapshots, sich auf das zu konzentrieren, was Nutzer tatsächlich treffen.

Schritt 1: Ziehen Sie Ihre langsamsten Abfragen (Gesamtzeit und Durchschnittszeit).

-- Requires pg_stat_statements
-- Postgres 13+ uses *_exec_time columns
SELECT
  queryid,
  calls,
  total_exec_time AS total_ms,
  mean_exec_time  AS mean_ms,
  rows,
  shared_blks_read,
  shared_blks_hit,
  left(query, 120) AS query_sample
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

SELECT
  queryid,
  calls,
  total_exec_time AS total_ms,
  mean_exec_time  AS mean_ms,
  rows,
  shared_blks_read,
  shared_blks_hit,
  left(query, 120) AS query_sample
FROM pg_stat_statements
WHERE calls >= 20
ORDER BY mean_exec_time DESC
LIMIT 20;

Verwenden Sie die erste Abfrage, um zu finden, was über den Tag hinweg die meiste Gesamtzeit verbraucht. Die zweite zeigt langsame Statements, die die Nutzerlatenz spiken können.

Schritt 2: Finden Sie Tabellen mit vielen sequenziellen Lesungen. Diese sind häufige Kandidaten für fehlende Indizes oder falsche Query‑Shapes.

SELECT
  schemaname,
  relname,
  seq_scan,
  seq_tup_read,
  idx_scan,
  n_live_tup,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
ORDER BY seq_tup_read DESC
LIMIT 20;

Schritt 3: Listen Sie Indizes auf, die ungenutzt oder kaum genutzt werden. Große Indizes mit niedrigem idx_scan sind verdächtig.

SELECT
  schemaname,
  relname      AS table_name,
  indexrelname AS index_name,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC, pg_relation_size(indexrelid) DESC
LIMIT 30;

Schritt 4: Erkennen Sie stark beschriebene Tabellen, bei denen zusätzliche Indizes die Schreibleistung verschlechtern können. Jeder zusätzliche Index macht Inserts und Updates teurer.

SELECT
  schemaname,
  relname,
  n_tup_ins,
  n_tup_upd,
  n_tup_del,
  n_tup_hot_upd,
  (n_tup_ins + n_tup_upd + n_tup_del) AS total_writes
FROM pg_stat_user_tables
ORDER BY total_writes DESC
LIMIT 20;

Schritt 5: Speichern Sie Snapshots mit Zeitstempeln, damit Sie Vorher‑/Nachher‑Vergleiche machen können. Legen Sie die Ergebnisse konsistent ab (CSV oder kleine Tabelle) und notieren Sie Änderungen.

CREATE SCHEMA IF NOT EXISTS audit;
CREATE TABLE IF NOT EXISTS audit.slow_queries_snap (
  captured_at timestamptz NOT NULL,
  queryid     bigint,
  calls       bigint,
  total_ms    double precision,
  mean_ms     double precision,
  query_sample text
);

INSERT INTO audit.slow_queries_snap
SELECT
  now(), queryid, calls,
  total_exec_time, mean_exec_time,
  left(query, 200)
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 50;

Wie man fehlende Indizes mit realen Traffic‑Signalen erkennt

Ein fehlender Index zeigt sich meist durch zwei gleichzeitige Beobachtungen:

• Eine große Tabelle wird wiederholt gescannt
• Eine kleine Menge Abfragemuster taucht wiederholt als teuer auf

Starten Sie mit Tabellen‑Signalen. In pg_stat_user_tables ist ein hoher seq_scan (besonders zusammen mit hohem seq_tup_read) auf einer großen Tabelle ein starkes Indiz dafür, dass Abfragen ohne passenden Index filtern oder joinen. Es ist kein Beweis, aber ein klarer Hinweis, wo Sie nachsehen sollten.

Verbinden Sie das dann mit Abfragemustern in pg_stat_statements. Sie suchen nach wiederkehrenden Zugriffsmustern:

• Häufige WHERE‑Filter (z. B. user_id, created_at, tenant_id)
• JOIN‑Schlüssel (Fremdschlüssel sind oft der Auslöser)
• ORDER BY‑Spalten, die auf großen Ergebnismengen zu teuren Sorts führen
• LIMIT‑Abfragen, die trotzdem viel lesen, weil kein Index effizient genutzt werden kann

Eine Faustregel: Indexieren Sie zuerst selektive Spalten. Eine selektive Spalte hat viele verschiedene Werte und grenzt die Suche schnell ein. WHERE user_id = 42 ist meist selektiv. WHERE status = 'active' oft nicht.

Mehrspaltenindizes können die richtige Lösung sein, aber die Reihenfolge zählt, weil Postgres den linken Teil eines B‑Tree‑Index am effektivsten nutzt. Wenn Ihre typische Abfrage ist:

SELECT * FROM orders
WHERE user_id = $1 AND created_at >= now() - interval '30 days'
ORDER BY created_at DESC
LIMIT 50;

passt ein Index wie (user_id, created_at) gut. Die umgekehrte Reihenfolge (created_at, user_id) hilft oft weniger, weil sie nicht früh genug auf einen einzelnen Benutzer einschränkt.

Wie man ungenutzte Indizes findet (und wann man sie nicht löschen sollte)

Ungenutzte Indizes sind tückisch: Sie verursachen Schreibkosten, belegen Platz und vergrößern Vacuum‑ und Backup‑Aufwand. Ziel ist nicht, Indizes aggressiv zu löschen, sondern die zu entfernen, die Ihrem realen Workload nie helfen.

Starten Sie mit pg_stat_user_indexes. Ein einfacher erster Check:

• idx_scan = 0 seit dem letzten Stat‑Reset (Kandidat)
• Sehr wenige Scans im Verhältnis zur Schreiblast der Tabelle (Kandidat)

Dann validieren Sie mit Kontext. Manche Indizes dienen der Korrektheit, nicht der Performance:

• Indizes, die PRIMARY KEY oder UNIQUE‑Constraints stützen
• Indizes, die für Fremdschlüsselprüfungen kritisch sind
• Indizes, die App‑Regeln durchsetzen (z. B. „eine aktive Subscription pro Nutzer“)
• Indizes, die für seltene, aber wichtige Jobs gebraucht werden (Reports, Exporte, Forensik)

Ein klassischer Fehler: Sie sehen idx_scan = 0 für orders_created_at_idx, löschen ihn und stellen später fest, dass die Monatsend‑Abstimmung darauf angewiesen war. Wenn Sie nur eine Woche an Statistiken betrachten, können Sie die falsche Entscheidung treffen.

Ein vorsichtiger Prozess, der gut funktioniert:

1. Erstellen Sie eine kurze Kandidatenliste (Tabelle, Index, warum er ungenutzt erscheint).
2. Prüfen Sie erneut nach einem vollen Geschäftszzyklus (oft 2–4 Wochen).
3. Wenn er weiterhin ungenutzt ist, löschen Sie ihn in einem geplanten Fenster und beobachten Sie Latenz und Schreibzeiten.
4. Bewahren Sie die Indexdefinition auf, damit Sie ihn schnell wiederherstellen können.

Finden der größten Latenz‑Verursacher über Abfrage‑Statistiken

Wenn Sie schnell den größten Gewinn wollen, ordnen Sie reale Abfragen nach der insgesamt verbrannten Zeit. pg_stat_statements zeigt, was über den Tag teuer ist, nicht nur was einmal langsam war.

Zwei Muster zum Vergleich:

• Eine sehr langsame Abfrage (2 Sekunden), die zweimal am Tag läuft
• Eine „mittlere“ Abfrage (40 ms), die 10.000‑mal am Tag läuft

Das zweite Muster schadet meist mehr.

Ein einfacher Startpunkt. Spaltennamen variieren je nach Postgres‑Version (manchmal total_time/mean_time statt total_exec_time/mean_exec_time):

SELECT
  queryid,
  calls,
  total_exec_time,
  mean_exec_time,
  rows,
  shared_blks_hit,
  shared_blks_read,
  query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Dann nach mittlerer Zeit sortieren, um „Einzelereignisse“ zu finden:

SELECT
  queryid,
  calls,
  total_exec_time,
  mean_exec_time,
  rows,
  shared_blks_hit,
  shared_blks_read,
  query
FROM pg_stat_statements
WHERE calls > 50
ORDER BY mean_exec_time DESC
LIMIT 20;

Für jede Kandidaten‑Abfrage erfassen Sie vor Indexänderungen einige Fakten:

• Abfragetext und queryid
• Aufrufe, Gesamtzeit, Mittelwert
• Zurückgegebene Zeilen (gibt die App oft mehr als nötig zurück?)
• shared_blks_hit vs shared_blks_read (meist aus Cache oder viele Plattenzugriffe?)
• Ein kurzes EXPLAIN (ANALYZE, BUFFERS) in einer sicheren Umgebung

Indizes sind nicht immer die Antwort. Hohe Latenz kommt auch von schlechten Joins, die Zeilen multiplizieren, fehlender Paginierung, dem Laden großer JSON‑Blobs oder zu viel Arbeit innerhalb von SQL.

Validieren und den richtigen Index entwerfen (ohne zu raten)

Nachdem Sie eine langsame Abfrage identifiziert haben, legen Sie nicht sofort einen Index an. Bestätigen Sie zuerst, was die Datenbank für die Produktions‑Abfrageform wirklich tut. Der schnellste Weg ist, die exakte Abfrage (mit realistischen Parametern) mit EXPLAIN (ANALYZE, BUFFERS) zu testen.

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE user_id = 42
  AND status = 'open'
ORDER BY created_at DESC
LIMIT 20;

Lesen Sie das wie eine Geschichte:

• Seq Scan bedeutet, Postgres liest viele Zeilen der Tabelle und filtert danach. Auf kleinen Tabellen ist das in Ordnung, auf großen oft langsam.
• Index Scan oder Index Only Scan bedeutet, Postgres kann direkt zu den benötigten Zeilen springen.
• BUFFERS zeigt, wie viel in Memory vs. auf Platte gearbeitet wurde — oft erklärt das, warum eine Abfrage manchmal schnell und bei Peak langsam ist.

Beim Index‑Design stimmen Sie den Index auf Filter und Sortierung ab:

• Einspaltiger Index, wenn eine Spalte den Großteil der Filterarbeit leistet (z. B. user_id).
• Kombinierter Index, wenn Sie auf mehreren Spalten zusammen filtern (z. B. (user_id, status)). Die Reihenfolge ist wichtig.
• Partieller Index, wenn Sie immer eine Teilmenge abfragen (z. B. nur status = 'open'). Kleinere Indizes, geringere Schreibkosten.
• Sortierspalte aufnehmen, wenn so ein großer Sort vermieden wird (z. B. (user_id, status, created_at)).

Änderungen klein und reversibel halten:

1. Fügen Sie einen Index für genau eine hochlatente Abfrage hinzu.
2. Führen Sie EXPLAIN (ANALYZE, BUFFERS) erneut aus, um Planverbesserung zu bestätigen.
3. Messen Sie wieder über ein vergleichbares Zeitfenster.
4. Wenn Schreibzeiten oder Lock‑Zeit schlechter werden, rollen Sie zurück.

Wenn Sie eine KI‑generierte Codebasis geerbt haben, ist oft die Abfrage selbst das eigentliche Problem (fehlende Filter, zu viele geladene Zeilen, riesige Sorts). Beheben Sie die Abfrage zuerst, wenn das der Fall ist.

Häufige Fehler, die Index‑Audits schiefgehen lassen

Audits gehen schief, wenn Sie eine einzige Statistik als die „Wahrheit“ behandeln und zu schnell handeln.

Häufige Fehler:

• Einen Index löschen, der Korrektheit schützt. Indizes, die PRIMARY KEY oder UNIQUE stützen, sind wichtig, auch wenn sie „unbenutzt“ aussehen. Manche Indizes sind für Fremdschlüsselprüfungen erforderlich.
• Nur idx_scan jagen, ohne Zeit und Zeilen zu betrachten. Niedrige Scan‑Zahlen sind nicht automatisch „sicher zu löschen“; hohe Scan‑Zahlen sind nicht automatisch „unbedingt behalten“.
• Überlappende Indizes für jede kleine Abweichung anlegen. Das führt zu mehrfachen Duplikaten, höherer Schreiblast und mehr Wartung.
• Indizes beschuldigen, wenn das eigentliche Problem Bloat oder veraltete Statistiken sind. Wenn Autovacuum und ANALYZE hinterherhinken, verschlechtern sich Pläne und Scans.
• Während ruhiger Stunden testen und annehmen, dass Peak besser wird. Cache‑Wärme, Konkurrenz und Schreibdruck ändern Planverhalten.

Machen Sie jeweils nur eine Änderung, messen Sie vor und nach unter ähnlicher Last und haben Sie immer einen Rollback‑Plan.

Schnelle Checkliste und nächste Schritte

Ein Audit zahlt sich nur aus, wenn Sie ihn wiederholen können. Halten Sie einen einfachen Bericht bereit, den Sie heute ausführen und nach jeder Änderung wiederholen können, um Gewinne zu beweisen (oder Regressionen zu erkennen).

Ihr 30‑Minuten wiederholbarer Bericht

• Erfassen Sie Top‑Abfragen nach Gesamtzeit und nach Durchschnittszeit (klein halten, z. B. 20 jeweils).
• Notieren Sie große Tabellen mit hohen sequenziellen Lesungen.
• Führen Sie eine kurze Prüfliste ungenutzter Indizes mit einer Ein‑Zeilen‑Begründung.
• Schlagen Sie bis zu drei Änderungen vor (hinzufügen, entfernen, anpassen) und verknüpfen Sie jede mit einer spezifischen Abfrage oder Tabelle.
• Führen Sie denselben Bericht nach Deployment erneut aus und vergleichen Sie Zeit, Aufrufe, Zeilen und Scans.

Bevor Sie etwas löschen, prüfen Sie kurz: bildet der Index einen Primary Key oder Unique‑Constraint ab, unterstützt er einen Foreign Key, oder wird er für einen seltenen, aber wichtigen Job benötigt?

Wenn Sie ein zweites Paar Augen brauchen

Wenn Ihre App von Tools wie Replit, Cursor oder v0 generiert wurde, kommen Performance‑Probleme häufig von fehlenden Indizes, ineffizienten Abfragen und unsicheren Mustern. FixMyMess (fixmymess.ai) konzentriert sich auf Diagnose und Reparatur KI‑generierter Codebasen, inklusive Query‑ und Schema‑Problemen, die erst unter Produktionslast sichtbar werden. Wenn Sie nicht weiterkommen, kann ein gezieltes Audit helfen, die kleinste Menge an Änderungen zu wählen, die die Latenz tatsächlich senkt.