Echtzeit-Überwachung aktiver Transaktionen mit pg_stat_activity

pg_stat_activity ist im Grunde ein Echtzeit-Fenster, das dir zeigt, was gerade in deiner Datenbank abgeht. In der letzten Vorlesung haben wir die Basics angeschaut, jetzt gehen wir tiefer und schauen uns an, wie du dieses mächtige Tool richtig einsetzt.

Beispiel für eine einfache Query auf pg_stat_activity:

SELECT * 
FROM pg_stat_activity;

Mit diesem Query bekommst du alle aktiven Verbindungen und die aktuellen Queries angezeigt. Nice! Aber das sind meistens zu viele Daten, und du könntest ewig brauchen, um alles durchzuschauen. Deshalb ist es sinnvoll, die wichtigsten Infos rauszufiltern.

Wichtige Felder in pg_stat_activity

Lass uns die wichtigsten Felder anschauen, die du zusätzlich zu den bekannten brauchst. query_start zeigt dir, wann ein Query gestartet wurde – superwichtig, um lange laufende Operationen zu erkennen. pid enthält die Prozess-ID der Verbindung – brauchst du zum Steuern (z.B. Beenden) der Verbindung. state_change zeigt, wann der aktuelle Status gesetzt wurde – das hilft besonders, um langanhaltende Problemzustände zu analysieren.

Beispiel für die Auswahl aktiver Prozesse:

SELECT pid, usename, state, query, query_start 
FROM pg_stat_activity
WHERE state = 'active';

Wie findet man lange laufende Queries?

Stell dir vor, du bist der Datenbank-Admin und plötzlich geht die Last auf dem Server durch die Decke. Was tun? Erstmal rausfinden, welcher Query alle Ressourcen frisst. Wir nutzen pg_stat_activity, um solche „hungrigen“ Queries zu finden.

SELECT pid, usename, query, state, now() - query_start AS dauer
FROM pg_stat_activity
WHERE state = 'active'
  AND (now() - query_start) > interval '10 seconds';

Mit diesem Query siehst du alle Queries, die länger als 10 Sekunden laufen. Passe das Intervall einfach an deine Anforderungen an.

Problematische Queries beenden

Schauen wir uns an, wie du Queries loswirst, die schon viel zu lange laufen und die Datenbank blockieren. Nutze die Funktion pg_terminate_backend(), um einen Prozess hart zu beenden.

Beispiel zum Beenden eines Prozesses mit einer bestimmten PID:

SELECT pg_terminate_backend(12345);

Hier ist 12345 die Prozess-ID (Feld pid) aus pg_stat_activity.

Wichtig: Das Beenden eines Prozesses kann zu einem Rollback der nicht sauber abgeschlossenen Transaktion führen – also Vorsicht!

Wenn du jetzt automatisch alle „hängenden“ Prozesse killen willst, zum Beispiel idle-Transaktionen, kannst du folgenden PL/pgSQL-Block ausführen. Da du schon mal programmiert hast, kennst du das Konzept von Schleifen (engl. loop) – das ist einfach eine Konstruktion, die Anweisungen wiederholt, solange eine Bedingung erfüllt ist oder bis alle Daten durch sind:

DO $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN 
        SELECT pid 
        FROM pg_stat_activity 
        WHERE state = 'idle in transaction' 
          AND (now() - state_change) > interval '5 minutes'
    LOOP
        PERFORM pg_terminate_backend(r.pid);
    END LOOP;
END $$;

Das ist eine dynamische Lösung, um das System von problematischen Transaktionen zu säubern. Die FOR-Schleife geht jede Zeile aus dem Query durch und beendet für jede gefundene PID den Prozess.

Bald geht’s los mit PL/pgSQL, halt noch kurz durch :P

Filtern nach Transaktionsstatus

Manchmal willst du nicht nur aktive Queries finden, sondern auch sehen, welche Verbindungen in einem speziellen Zustand sind, zum Beispiel idle oder idle in transaction. Das hilft dir, potenzielle Probleme zu erkennen, bevor sie kritisch werden.

Beispiel-Query, um Transaktionen im Zustand idle in transaction zu finden:

SELECT pid, usename, query, state, state_change
FROM pg_stat_activity
WHERE state = 'idle in transaction';

Das Feld state_change zeigt, wann dieser Zustand gesetzt wurde. So findest du lang laufende Transaktionen, die eigentlich nichts tun, aber trotzdem Ressourcen blockieren können.

Praxiseinsatz

Monitoring langer Queries im Produktivbetrieb: Du kannst ein regelmäßiges Monitoring für Queries einrichten, die eine bestimmte Zeit überschreiten, und dich dann z.B. per Slack, Telegram oder einem anderen Notification-Tool warnen lassen. So kannst du schnell auf Performance-Probleme reagieren.

Analyse von Queries bei Vorfällen: Wenn der Server langsam wird, schau als Erstes in pg_stat_activity, um die Ursache zu finden. Das sollte dein Standard-Protokoll bei Performance-Problemen sein.

Datenbankpflege: Durch regelmäßige Analyse von pg_stat_activity kannst du ineffiziente Queries erkennen und optimieren (z.B. durch Indexe oder Query-Optimierung).

Beim Monitoring können Fehler passieren, wenn du falsch filterst oder die Daten falsch interpretierst. Zum Beispiel: Wenn du nur nach active filterst, übersiehst du vielleicht Queries im Zustand idle in transaction, die aber auch Ressourcen blockieren können. Ein weiterer Fehler ist zu aggressives Beenden von Prozessen – das kann zu unerwünschten Rollbacks und Datenverlust führen. Analysiere immer den Kontext, bevor du harte Maßnahmen ergreifst.

Weitere Monitoring-Techniken

Für fortgeschrittenes Monitoring kannst du komplexere Queries bauen, die dir Statistiken pro User, Datenbank oder Query-Typ zeigen. Zum Beispiel kannst du herausfinden, wie viel Zeit jeder User im Schnitt für Queries braucht oder welche Datenbanken die meisten aktiven Verbindungen haben.

Außerdem ist es sinnvoll, automatisches Logging von langen Queries in die PostgreSQL-Logfiles zu aktivieren – mit den Konfigurationsparametern log_min_duration_statement und log_statement. Damit kannst du Performance-Probleme nachträglich analysieren und Muster im Verhalten deiner Anwendungen erkennen.