PostgreSQL-Index-Nutzung zuverlässig überwachen: So finden Sie tote Indizes

PostgreSQL bietet mit pg_stat_user_indexes einen detaillierten Einblick in die Nutzung von Datenbankindizes. Doch die integrierten Statistiken liefern nur unvollständige Antworten auf entscheidende Fragen: Wird der neu erstellte Index für eine langsame Abfrage überhaupt verwendet? Welche Indizes fressen wertvollen Speicherplatz, ohne jemals genutzt zu werden? Diese Lücken können zu ineffizienten Datenbanken führen – und zu unnötigen Kosten.

Warum PostgreSQLs Standardstatistiken nicht ausreichen

Die Abfrage

SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch 
FROM pg_stat_user_indexes 
WHERE schemaname = 'public';

liefert zwar Daten wie die Anzahl der Index-Scans (idx_scan) oder die gelesenen Tupel (idx_tup_read). Doch entscheidende Informationen fehlen:

• Welche konkreten Abfragen einen bestimmten Index nutzen
• Ob eine langsame Abfrage tatsächlich den neu erstellten Index verwendet
• Welche Indizes durch INSERT-Operationen unnötig belastet werden
• Ob der Abfrageplaner einen zusammengesetzten Index gegenüber einem einzelnen Spaltenindex bevorzugt – und damit Letzteren redundant macht
• Wie sich der Abfrageplan ändert, wenn dieselbe Abfrage in Zukunft einen anderen Index nutzt

Für kleine Projekte mag dies akzeptabel sein. Doch in produktiven Umgebungen mit mehreren Tabellen und Hunderten von Indizes wird die Analyse zur Ratespielerei. Ein Entwickler eines KI-gestützten Chatbots für Vertriebsteams erlebte dies am eigenen Leib: 51 Indizes verteilten sich auf verschiedene Tabellen wie stores, products und conversations. Einige waren absichtlich angelegt, andere durch Migrationen entstanden oder längst in Vergessenheit geraten. Die Frage, welche Indizes tatsächlich genutzt wurden, blieb unbeantwortet.

Eine maßgeschneiderte Lösung: Drei Dateien, ein Nachmittag

Um die Lücken in den Standardstatistiken zu schließen, entwickelte der Entwickler eine eigene Überwachungslösung. Das Kernkonzept: Alle Datenbankabfragen werden nicht direkt ausgeführt, sondern durch einen Wrapper geleitet. Dieser erfasst den Abfrageplan, misst die Ausführungszeit und protokolliert die Daten asynchron. Die Lösung besteht aus drei Komponenten:

1. Der Wrapper: query-logger.ts

Der Wrapper query-logger.ts führt Abfragen nicht direkt aus, sondern umhüllt sie mit zusätzlicher Logik. Hier die zentralen Funktionen:

• Abfrageplan ohne Ausführung: Mit EXPLAIN (FORMAT JSON) wird der geplante Ausführungsplan der Abfrage erstellt – ohne die Abfrage tatsächlich auszuführen. Dies ist besonders effizient, da die Kosten für den meisten Abfragen unter einer Millisekunde liegen. Der Plan enthält Informationen wie den verwendeten Index oder ob ein sequenzieller Scan (Seq Scan) durchgeführt wird.

async function getPlan(sql: string, params: unknown[]) {
  const result = await db.query(`EXPLAIN (FORMAT JSON) ${sql}`, params);
  return result.rows[0]["QUERY PLAN"][0];
}

• Extraktion der genutzten Indizes: Die Funktion extractIndexes durchsucht den Abfrageplan nach genutzten Indizes und sammelt diese in einer Liste.

function extractIndexes(plan: any): string[] {
  const indexes: string[] = [];
  const walk = (node: any) => {
    if (node["Index Name"]) indexes.push(node["Index Name"]);
    (node["Plans"] ?? []).forEach(walk);
  };
  walk(plan["Plan"]);
  return indexes;
}

• Erkennung sequenzieller Scans: Die Funktion planContainsSeqScan prüft, ob der Abfrageplan einen sequenziellen Scan enthält – ein Indikator für ineffiziente Abfragen.

function planContainsSeqScan(plan: any): boolean {
  let found = false;
  const walk = (node: any) => {
    if (node["Node Type"] === "Seq Scan") found = true;
    (node["Plans"] ?? []).forEach(walk);
  };
  walk(plan["Plan"]);
  return found;
}

• Asynchrone Protokollierung: Der Wrapper protokolliert die Abfrage, den verwendeten Index, die Ausführungszeit und weitere Metriken in einer separaten Tabelle (query_logs). Die Protokollierung erfolgt asynchron, um die Performance der Hauptabfrage nicht zu beeinträchtigen.

export async function loggedQuery(
  sql: string,
  params: unknown[],
  meta: QueryMeta
) {
  const plan = await getPlan(sql, params);
  const start = Date.now();
  const result = await db.query(sql, params);
  const duration = Date.now() - start;

  supabaseAdmin
    .from("query_logs")
    .insert({
      query_hash: hash(sql),
      sql_preview: sql.slice(0, 200),
      table_name: meta.tableName,
      endpoint: meta.endpoint,
      store_id: meta.storeId,
      indexes_used: extractIndexes(plan),
      seq_scan: planContainsSeqScan(plan),
      planning_ms: plan["Planning Time"],
      execution_ms: duration,
      rows_returned: result.rowCount,
    })
    .then(() => {})
    .catch(() => {});

  return result;
}

Wichtig: Die Protokollierung wird nicht abgewartet (then/catch). Dies stellt sicher, dass die Hauptabfrage nicht durch Monitoring-Operationen verlangsamt wird. In Tests lag die Dauer der Protokollierung zwischen 8 und 25 Millisekunden, während die eigentliche Abfrage zwischen 5 und 800 Millisekunden dauerte. Würde die Protokollierung abgewartet, könnte sich die Latenz verdoppeln – ohne messbaren Nutzen für den Endbenutzer.

Ein weiterer Kostenfaktor: EXPLAIN plant die Abfrage einmal, während die eigentliche Ausführung erneut geplant wird. Für die meisten Abfragen sind dies nur Mikrosekunden, bei komplexen Abfragen mit vielen Joins kann sich dies jedoch summieren. Eine Lösung ist die Stichprobenprotokollierung: Nur jede zehnte Abfrage wird protokolliert (gesteuert durch eine Umgebungsvariable). Dies liefert ausreichend Daten bei minimalem Overhead.

2. Die Tabelle: query_logs

Die Tabelle query_logs speichert die protokollierten Daten. Sie enthält Felder wie:

• query_hash: Ein Hash-Wert der Abfrage (ohne Parameter), um ähnliche Abfragen zu gruppieren
• sql_preview: Eine gekürzte Version der Abfrage
• table_name: Die betroffene Tabelle
• endpoint: Die API- oder Anwendungsendpunkt, der die Abfrage auslöste
• store_id: Ein optionaler Bezeichner für den Mandanten (falls relevant)
• indexes_used: Die Liste der genutzten Indizes
• seq_scan: Ein boolescher Wert, der angibt, ob ein sequenzieller Scan durchgeführt wurde
• planning_ms: Die Zeit, die für die Planung der Abfrage benötigt wurde
• execution_ms: Die tatsächliche Ausführungszeit
• rows_returned: Die Anzahl der zurückgegebe Zeilen

Die Tabelle ermöglicht es, Indizes zu identifizieren, die nie genutzt werden, oder Abfragen zu erkennen, die ineffizient über sequenzielle Scans laufen.

Praktische Erkenntnisse: 20 von 51 Indizes ungenutzt

Als der Entwickler die Lösung erstmals auf seine Produktionsdatenbank anwandte, offenbarte sich ein überraschendes Bild: 20 von 51 Indizes waren in den letzten Wochen nie genutzt worden. 78 % des gesamten Speicherplatzes, der für Indizes reserviert war, wurden für nichts genutzt. Dies war ein klares Zeichen für ineffiziente Datenbankwartung.

Die Lösung zeigt nicht nur, welche Indizes verschwendet werden, sondern auch, welche Abfragen ineffizient laufen. Durch die Kombination der Abfragepläne mit den tatsächlichen Ausführungszeiten lassen sich gezielt Optimierungen vornehmen – sei es durch das Löschen ungenutzter Indizes oder das Anpassen von Abfragen.

Fazit: Monitoring als Grundlage für effiziente Datenbanken

PostgreSQLs Standardstatistiken bieten einen ersten Einblick in die Indexnutzung, doch sie reichen nicht aus, um fundierte Entscheidungen zu treffen. Eine maßgeschneiderte Überwachungslösung wie der hier beschriebene Wrapper kann die Lücken schließen und wertvolle Einblicke liefern. Besonders wichtig ist dabei, dass das Monitoring die Performance der Hauptabfragen nicht beeinträchtigt. Durch asynchrone Protokollierung und Stichprobenverfahren lässt sich ein ausgewogenes Verhältnis zwischen Datenqualität und Overhead erreichen.

Die Investition in eine solche Lösung zahlt sich schnell aus: Unnötige Indizes können gelöscht, Abfragen optimiert und die Datenbankperformance insgesamt verbessert werden. In einer Zeit, in der Datenbanken immer größer und komplexer werden, ist solch eine Transparenz unverzichtbar – um sicherzustellen, dass jede Ressource optimal genutzt wird.