Wichtige Monitoring-Kommandos in PostgreSQL — pg_stat_activity und pg_stat_user_tables

Datenbank-Monitoring ohne pg_stat_activity und pg_stat_user_tables ist wie die Gesundheit nur anhand der Temperatur zu checken. Du kannst nicht erkennen, wo das Problem liegt, wenn du nur auf das große Ganze schaust. Diese zwei Schlüsselkommandos in PostgreSQL helfen dir nicht nur beim Beobachten, sondern auch beim aktiven Analysieren, was in deiner Datenbank abgeht.

Was ist pg_stat_activity?

pg_stat_activity ist ein System-View in PostgreSQL, das dir Infos über alle Verbindungen zu deiner Datenbank zeigt. Es beantwortet Fragen wie: Wer ist gerade verbunden? Welche Queries laufen aktuell? Welche Verbindungen "hängen" im Idle-Zustand? Das ist dein Tool, um die aktuelle Aktivität auf dem Server zu analysieren.

Lass uns die wichtigsten Felder anschauen, die in pg_stat_activity verfügbar sind. Das Feld datname enthält den Namen der Datenbank, zu der der Client verbunden ist, und usename zeigt den Usernamen, der die Verbindung aufgebaut hat. application_name gibt den Namen der Anwendung an, die die Verbindung nutzt, client_addr enthält die IP-Adresse des Clients, der mit dem Server verbunden ist. backend_start zeigt, wann der Client sich verbunden hat, state spiegelt den aktuellen Verbindungsstatus wider (active, idle, idle in transaction), und query enthält das aktuell oder zuletzt ausgeführte Statement.

Beispiel 1: Alle aktiven Verbindungen anzeigen

Um die aktiven Verbindungen zu sehen, führe folgenden Query aus:

SELECT datname, usename, client_addr, state, query
FROM pg_stat_activity
WHERE state = 'active';

Achte besonders auf das Feld query. Es zeigt dir die Queries, die gerade laufen. Wenn ein Query zu lange dauert, stimmt da vielleicht was nicht.

Beispiel 2: Analyse des Transaktionsstatus

Manchmal "hängen" Verbindungen im Zustand idle in transaction. Das bedeutet, eine Transaktion wurde gestartet, aber nicht abgeschlossen – das kann zu Locks führen.

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

Wie kannst du das fixen? Wenn du eine "hängende" Transaktion findest, kannst du sie mit folgendem Befehl beenden:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction';

Manche Entwickler übertreiben es damit ein bisschen. Frag am besten erst mal im Team nach, ob du den Prozess "killen" darfst. Ups, sorry, ich meinte natürlich – die Verbindung beenden.

Tabellen-Monitoring: Das View pg_stat_user_tables

Wenn pg_stat_activity dir hilft, die Verbindungen zu beobachten, dann erzählt dir pg_stat_user_tables alles über die Performance deiner Tabellen. Damit findest du raus: Wie oft werden Daten gelesen oder geschrieben? Welche Tabellen sind am meisten im Einsatz? Wo gibt’s vielleicht Performance-Probleme?

Hier die wichtigsten Felder von pg_stat_user_tables, die dir beim Analysieren helfen. relname enthält den Tabellennamen, seq_scan zeigt die Anzahl der sequentiellen Scans, idx_scan — wie oft ein Index-Scan gemacht wurde. n_tup_ins gibt an, wie viele Zeilen eingefügt wurden, n_tup_upd — wie viele Zeilen aktualisiert wurden, und n_tup_del — wie viele Zeilen gelöscht wurden.

Beispiel 1: Vergleich von Index- und sequentiellen Scans

Wenn ein Index zu selten genutzt wird (idx_scan nahe Null), kannst du vermutlich die Queries für diese Tabelle optimieren.

SELECT relname, seq_scan, idx_scan
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;

Beispiel für ein Ergebnis:

Wenn du siehst, dass die Tabelle orders extrem viele sequentielle Scans (seq_scan) hat, denk mal über einen Index nach. Stell dir vor, die Tabelle orders hat 3500 sequentielle Scans und nur 100 Index-Scans, während employees 50 sequentielle und 1000 Index-Scans hat – das schreit nach Optimierung.

Beispiel 2: Analyse der Anzahl von Operationen auf Tabellen

Um zu sehen, wie "lebendig" die Daten in den Tabellen sind, hol dir Infos über eingefügte, aktualisierte und gelöschte Zeilen:

SELECT relname, n_tup_ins, n_tup_upd, n_tup_del
FROM pg_stat_user_tables
ORDER BY n_tup_ins DESC;

Was kannst du daraus lernen? Tabellen mit vielen Inserts (n_tup_ins) und Deletes (n_tup_del) können echte Hotspots in deiner DB sein. Das heißt, deren Performance solltest du besonders im Auge behalten.

Praktische Nutzung der Kommandos zur Performance-Analyse: Kombiniere pg_stat_activity und pg_stat_user_tables

Wenn du die Performance deiner Datenbank analysierst, kannst du die Infos aus beiden Views kombinieren. Erst identifizierst du lange laufende Queries mit pg_stat_activity, dann checkst du, welche Tabellen von diesen Queries genutzt werden – mit pg_stat_user_tables. Wenn die Queries auf Tabellen mit hohem seq_scan laufen, versuch die Queries zu optimieren oder einen Index zu setzen.

Beispiel-Query:

WITH active_queries AS (
    SELECT pid, query
    FROM pg_stat_activity
    WHERE state = 'active' AND query <> '<IDLE>'
)
SELECT a.pid, a.query, t.relname, t.seq_scan, t.idx_scan
FROM active_queries a
JOIN pg_stat_user_tables t ON a.query LIKE '%' || t.relname || '%';