Einführung in pg_stat_statements: Installation und Konfiguration der Extension

Die Extension pg_stat_statements in PostgreSQL ist ein Tool zum Sammeln von Query-Statistiken. Damit kannst du sehen, welche Queries am häufigsten ausgeführt werden, welche am meisten Zeit brauchen und wie effizient die Ressourcen der Datenbank genutzt werden. Anstatt jede Query einzeln mit EXPLAIN zu analysieren, bekommst du so einen Gesamtüberblick über die Performance deiner Datenbank.

Vorteile von pg_stat_statements:

Echtzeit-Monitoring: Du siehst direkt, welche Queries deine Datenbank gerade belasten.

Systemweite Performance-Analyse: Die Infos gelten für alle Queries in der Datenbank, nicht nur für die, die du manuell analysierst.

Langsame Queries finden: Du erkennst easy, welche Queries am meisten Zeit fressen.

Wiederkehrende Queries erkennen: Damit kannst du das Caching optimieren und für beliebte Queries Indexe hinzufügen.

Installation und Konfiguration von pg_stat_statements

Jetzt, wo du weißt, wofür pg_stat_statements gut ist, lass uns Schritt für Schritt anschauen, wie du es installierst und einrichtest.

1. Prüfen, ob PostgreSQL bereit ist. Stell sicher, dass dein PostgreSQL die Extension pg_stat_statements unterstützt. Die Extension ist ab PostgreSQL 9.2 standardmäßig dabei. Um zu checken, ob sie vorhanden ist, führe aus:

SELECT extname FROM pg_extension;

Wenn pg_stat_statements nicht in der Liste steht, heißt das wahrscheinlich, dass sie vom Admin nicht installiert wurde.

So sieht es aus, wenn die Extension installiert und aktiviert ist:

2. Extension hinzufügen.

Um pg_stat_statements zu aktivieren, musst du sie zu den preload-libraries von PostgreSQL hinzufügen. Das machst du in der Config-Datei postgresql.conf.

Schritte:

1. Such die Datei postgresql.conf. Die liegt normalerweise im Datenverzeichnis von PostgreSQL.
2. Öffne sie zum Editieren.
3. Füge diese Zeile hinzu oder ändere sie ab:

   shared_preload_libraries = 'pg_stat_statements'

Warum ist das nötig? Weil pg_stat_statements ein Preload braucht, um Queries auf Systemebene zu tracken.

4. Speichere die Änderungen und starte den PostgreSQL-Server neu, damit alles aktiv wird. Hier der Befehl für Linux:

   sudo systemctl restart postgresql
   

Wenn du lokal entwickelst oder testest, reicht auch ein einfacher Server-Restart.

3. Extension in der Datenbank anlegen. Nachdem der PostgreSQL-Server neu gestartet wurde, kannst du die Extension pg_stat_statements in der gewünschten Datenbank anlegen. Verbinde dich mit der DB per psql oder einem anderen Tool und führe aus:

CREATE EXTENSION pg_stat_statements;

Wenn alles klappt, gibt’s keine Fehlermeldung. Jetzt ist pg_stat_statements für deine Datenbank aktiv.

4. Parameter von pg_stat_statements einstellen.

Nach der Installation lohnt es sich, die Parameter für die Statistik-Erfassung zu konfigurieren. Die wichtigsten Einstellungen machst du in der postgresql.conf.

Wichtige Parameter

• pg_stat_statements.track
• Legt fest, welche Queries getrackt werden.
• Werte:
  • all — alle Queries tracken (empfohlen zum Debuggen und Analysieren).
  • top — nur Top-Level-Queries tracken.
  • none — Tracking ausschalten.
• Beispiel-Konfiguration:

pg_stat_statements.track = 'all'

• pg_stat_statements.max

  • Gibt an, wie viele Queries maximal in der Statistik gespeichert werden.
  • Standard: 5000.
  • Wenn du viele Queries hast, solltest du den Wert erhöhen, z.B.:

    pg_stat_statements.max = 10000
    

• pg_stat_statements.save

  • Legt fest, ob die Statistik zwischen Server-Neustarts gespeichert wird.
  • Werte: on oder off.
  • Empfehlung: on lassen:

    pg_stat_statements.save = on
    

Nach den Änderungen musst du den PostgreSQL-Server nochmal neu starten.

Checken, ob pg_stat_statements läuft

Jetzt, wo die Extension installiert und konfiguriert ist, lass uns checken, ob sie läuft. Um die gesammelten Query-Statistiken zu sehen, führe diesen Query aus:

SELECT
    queryid,        -- Eindeutige Query-ID
    query,          -- Query-Text
    calls,          -- Anzahl der Query-Aufrufe
    total_time,     -- Gesamtlaufzeit (in Millisekunden)
    rows            -- Anzahl der von der Query zurückgegebenen Zeilen
FROM pg_stat_statements
ORDER BY total_time DESC;

Was bedeuten die Spalten?

• queryid: Eindeutige Query-ID, praktisch um gleiche Queries mit unterschiedlichen Parametern zu finden.
• query: Der ausgeführte SQL-Query-Text.
• calls: Wie oft die Query ausgeführt wurde.
• total_time: Gesamtlaufzeit (Summe aller Aufrufe dieser Query).
• rows: Gesamtanzahl der von der Query zurückgegebenen Zeilen.

Wenn du z.B. siehst, dass eine Query mit calls = 100 und total_time = 50000 (50 Sekunden) den Großteil der Zeit frisst, ist das ein klares Zeichen, dass du sie optimieren solltest.

Typische Use-Cases für pg_stat_statements

1. Die langsamsten Queries finden. Um Queries zu finden, die am meisten Zeit brauchen, sortiere nach total_time:

SELECT query, total_time, calls
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 5;

2. Die aktivsten Queries finden. Um Queries zu finden, die am häufigsten laufen, sortiere nach calls:

SELECT query, calls, total_time
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 5;

3. Index-Nutzung analysieren. Wenn du viele langsame Queries siehst, check die Index-Nutzung. Bei Queries mit Filter (WHERE) ist fehlender Index oft der Grund für schlechte Performance.

Daten von pg_stat_statements zurücksetzen

Manchmal willst du die gesammelten Statistiken zurücksetzen, um die Analyse von vorne zu starten. Das geht mit diesem Befehl:

SELECT pg_stat_statements_reset();

Danach sind alle Statistiken gelöscht und das Sammeln startet neu.

Praxistipps

Begrenze die Menge der gesammelten Statistik: Wenn du in einem System mit Millionen von Queries arbeitest, lass pg_stat_statements.max auf einem sinnvollen Wert, damit es nicht zu viel Overhead gibt.

Setze die Statistik regelmäßig zurück: Das ist praktisch, bevor du eine Performance-Analyse startest, damit alte und neue Daten nicht vermischt werden.

Achte auf langsame Queries: Auch wenn sie selten laufen, kann eine einzelne langsame Query deine Datenbank stark belasten.

Jetzt weißt du, wie du die Extension pg_stat_statements installierst, konfigurierst und für die Query-Performance-Analyse nutzt. In der nächsten Vorlesung schauen wir uns genauer an, wie du mit dem Tool langsame Queries findest und sie optimierst.