Das Symptom
Die Datenbank wird langsam, ohne dass mehr Last darauf liegt. Eine Tabelle, die nach
Zeilenzahl kaum wächst, belegt jede Woche mehr Platz. Ein ALTER TABLE in der
Wartungsnacht bleibt minutenlang stehen und wartet auf eine Sperre. Irgendwann
meldet ein Kollege, der Deploy hänge, weil eine Migration nicht durchkommt.
Ein Blick in pg_stat_activity zeigt die Ursache, aber nicht den Schuldigen: Ein
Dutzend Verbindungen stehen auf idle in transaction, einige seit Stunden. Sie tun
nichts. Sie führen keine Abfrage aus. Sie sind nur nicht fertig. Die Anwendung, die
sie hält, meldet keinen Fehler, und der Verbindungspool hat alle seine Verbindungen
ordentlich vergeben.
Die „Layer 8“-Wahrheit
Eine untätige Verbindung ist harmlos. Eine untätige Verbindung in einer offenen
Transaktion ist es nicht. Die Dokumentation zu pg_stat_activity definiert den Zustand
knapp: „idle in transaction: The backend is in a transaction, but is not currently
executing a query.“ Jemand hat BEGIN gesagt, oder der Treiber hat es im Hintergrund
getan, und das Ende kam nie. Für PostgreSQL ist diese Transaktion so lebendig wie
jede andere: Ihre Sperren bleiben bestehen, und ihre Sicht auf die Daten muss
erhalten bleiben.
Das Zweite ist der eigentliche Schaden. PostgreSQL löscht Zeilen nicht sofort,
sondern markiert alte Versionen, die VACUUM später wegräumt. Solange eine Transaktion
offen ist, der diese alten Versionen noch sichtbar sein könnten, darf VACUUM sie nicht
anfassen. Die Dokumentation sagt es im Absatz zum Parameter
idle_in_transaction_session_timeout wörtlich: „Even when no significant locks are held,
an open transaction prevents vacuuming away recently-dead tuples that may be visible
only to this transaction; so remaining idle for a long time can contribute to table
bloat.“ Die stillstehende Verbindung bremst also nicht nur das ALTER TABLE, sie lässt
auch die Tabellen anschwellen, und zwar für alle, nicht nur für sich selbst.
Der naheliegende Reflex, PostgreSQL müsse so etwas doch von selbst aufräumen, scheitert
an der Voreinstellung. Der Parameter, der genau das tut, existiert, aber die
Dokumentation stellt klar: „A value of zero (the default) disables the timeout.“ Ab
Werk darf eine Sitzung beliebig lange in einer offenen Transaktion sitzen. Das ist
keine Nachlässigkeit, sondern Vorsicht: Der Server weiß nicht, ob die Anwendung in
einer Minute weitermacht oder nie. Erst der Administrator kann sagen, wie lange das
in seiner Umgebung tragbar ist, und ohne Einheit meint der Wert Millisekunden.
Wichtig ist die Abgrenzung zum Nachbarn idle_session_timeout. Der beendet Sitzungen,
die untätig sind, aber gerade keine Transaktion offen haben. Die Dokumentation stuft
das als weniger dringend ein, weil eine solche Sitzung dem Server keine großen Kosten
verursacht, und warnt zugleich, dass Verbindungspools auf unerwartete Abbrüche schlecht
reagieren können. Wer beide Timeouts in einen Topf wirft und den falschen setzt,
hat entweder weiter hängende Transaktionen oder einen Pool, der sich über
abgerissene Verbindungen beschwert.
Der Timeout ist am Ende ein Sicherheitsnetz. Die Dokumentation zu Routine Vacuuming
nennt die Lösung für lange offene Transaktionen ohne Umschweife: committen, zurückrollen
oder die Sitzung mit pg_terminate_backend beenden. Dauerhaft hilft nur die Anwendung,
die ihre Transaktionen so beendet, wie sie sie begonnen hat. Eine Transaktion
umschließt Datenbankarbeit, nicht die Mittagspause des Benutzers.
Die echte, verifizierte Lösung
1000 % faktenbasiert. Jeder Befehl ist real, jeder Pfad existiert. Schritt für Schritt:
1.Die hängenden Sitzungen sichtbar machen
pg_stat_activity zeigt je Sitzung den Zustand in der Spalte state und den Zeitpunkt des letzten Zustandswechsels in state_change. Die Abfrage listet alle Sitzungen, die gerade in einer offenen Transaktion untätig sind, mit Benutzer, Anwendung, Client-Adresse und der Dauer seit dem letzten Zustandswechsel. Die Spalte query enthält die zuletzt ausgeführte Anweisung, also den letzten Hinweis darauf, wo die Anwendung stehen geblieben ist. Für diese Sicht auf fremde Sitzungen braucht man ausreichende Rechte; die Dokumentation beschreibt die Spalten der Sicht im Kapitel zum Cumulative Statistics System.
SELECT pid, usename, application_name, client_addr,
now() - state_change AS untaetig_seit, state, left(query, 60) AS letzte_anweisung
FROM pg_stat_activity
WHERE state LIKE 'idle in transaction%'
ORDER BY state_change;
2.Den aktuellen Wert des Timeouts prüfen
SHOW zeigt den in dieser Sitzung wirksamen Wert. Steht dort 0, ist der Timeout abgeschaltet, und zwar nicht wegen eines Fehlers, sondern weil das laut Dokumentation die Voreinstellung ist. Der Wert kann auf mehreren Ebenen gesetzt sein: in postgresql.conf, pro Datenbank, pro Rolle oder in der Sitzung selbst. Die Dokumentation erklärt die Rangfolge im Kapitel Setting Parameters; Werte aus ALTER DATABASE und ALTER ROLE greifen dabei nur für neu gestartete Sitzungen.
SHOW idle_in_transaction_session_timeout;
SHOW idle_session_timeout;
3.Die akute Blockade lösen
Hält eine hängende Sitzung Sperren, die andere blockieren, oder ist sie der Grund, warum VACUUM nichts freigibt, kann man sie gezielt beenden. Die Dokumentation nennt dafür pg_terminate_backend mit der Prozess-ID aus pg_stat_activity. Das rollt die offene Transaktion zurück; was die Anwendung darin noch nicht bestätigt hatte, ist weg. Deshalb vorher die Spalte query lesen und mit dem Team klären, welche Anwendung dahintersteckt. Das ist Erste Hilfe, nicht die Reparatur: Ohne Änderung an der Anwendung hängt in einer Stunde die nächste Sitzung.
SELECT pg_terminate_backend(12345);
4.Den Timeout gezielt setzen, zuerst pro Rolle
Statt den Wert global in postgresql.conf zu setzen, lohnt der Anfang bei der Rolle, über die die auffällige Anwendung verbunden ist. ALTER ROLE erlaubt laut Dokumentation, globale und datenbankweite Einstellungen mit benutzerspezifischen Werten zu überschreiben, wirksam für neue Sitzungen. Der Wert darf Einheiten tragen; ohne Einheit sind Millisekunden gemeint, also wäre eine nackte 5 nicht fünf Minuten, sondern fünf Millisekunden. Ein zu knapper Wert beendet auch legitime Transaktionen, die auf eine Benutzereingabe warten. Der Timeout ist ein Sicherheitsnetz gegen vergessene Transaktionen, kein Ersatz für ein sauberes COMMIT.
ALTER ROLE berichtsdienst SET idle_in_transaction_session_timeout = '5min';
-- oder für alle Sitzungen einer Datenbank:
ALTER DATABASE shop SET idle_in_transaction_session_timeout = '10min';
5.Die Anwendung reparieren
Der Timeout sorgt nur dafür, dass vergessene Transaktionen nicht ewig leben. Die Ursache bleibt eine Anwendung, die BEGIN ohne Ende schickt. Typische Muster: Autocommit im Treiber abgeschaltet und nach reinen Lesezugriffen nie committet; Transaktion geöffnet, dann auf eine Netzwerkantwort oder Benutzereingabe gewartet; Ausnahme gefangen, aber weder ROLLBACK geschickt noch Verbindung zurückgegeben. Die Regel ist einfach: Eine Transaktion umschließt die Datenbankarbeit, nicht das Warten auf Menschen oder fremde Dienste. Nach der Korrektur zeigt die Abfrage aus Schritt 1 nur noch kurzlebige Einträge, und die Tabellen hören auf zu wachsen.