Die Top 5 PostgreSQL-Fehler bei der Fehlerbehebung und wie man sie vermeidet

Die meisten PostgreSQL-Vorfälle beginnen nicht mit etwas Exotischem. Sie beginnen mit einem langsamen Endpunkt, einer Warteschlange blockierter Sitzungen, einer Tabelle, die schneller gewachsen ist als erwartet, oder einem Datenträgeralarm von der WAL-Partition zur ungünstigsten Zeit. Das Schwierige ist nicht zu wissen, dass PostgreSQL Indizes, Autovacuum, Speichereinstellungen, Sperren und WAL hat. Das Schwierige ist zu wissen, welche davon gerade wichtig ist und die Korrekturen zu vermeiden, die den nächsten Vorfall verschlimmern.

Die folgenden Fallstricke bei der PostgreSQL-Fehlerbehebung sind diejenigen, die ich in der täglichen Betriebsarbeit am häufigsten sehe. Es sind nicht nur Tipps wie "diesen Parameter einstellen". Jeder enthält das Symptom, die Falle und einen sichereren Weg, das Problem zu durchdenken, bevor die Produktion geändert wird.

Falle 1: Indexmangel und -fehlgebrauch

Eine der häufigsten Ursachen für langsame PostgreSQL-Leistung ist eine schlechte Indexierung. Viele DBAs verlassen sich ausschließlich auf automatisch erstellte Primärschlüssel-Indizes und berücksichtigen nicht die spezifischen Abfragemuster, was zu häufigen, teuren sequenziellen Scans anstelle effizienter Index-Scans führt.

Diagnose: Sequenzielle Scans

Wenn eine Abfrage schlecht läuft, beginnen Sie mit dem Ausführungsplan. Verwenden Sie zuerst einfaches EXPLAIN, wenn die Abfrage Daten ändert oder lange laufen könnte. Verwenden Sie EXPLAIN (ANALYZE, BUFFERS), wenn Sie sie sicher ausführen können und Echtzeit- sowie E/A-Verhalten benötigen.

EXPLAIN ANALYZE
SELECT * FROM user_data WHERE last_login > '2023-10-01' AND status = 'active';

Vermeidung der Falle: Zusammengesetzte und partielle Indizes

Wenn die Abfrage mehrere Spalten in der WHERE-Klausel verwendet, kann ein zusammengesetzter Index helfen, aber die Spaltenreihenfolge hängt von der Abfrageform ab. Gleichheitsfilter gehören normalerweise vor Bereichsfilter. Für eine Abfrage wie WHERE status = 'active' AND last_login > ... ist ein Index auf (status, last_login) oft nützlicher als (last_login, status), da PostgreSQL auf einen Status eingrenzen und dann den Datumsbereich scannen kann. Für ORDER BY last_login DESC LIMIT 50 ist der beste Index möglicherweise ein anderer.

Erwägen Sie außerdem partielle Indizes für Spalten, die nur indiziert werden müssen, wenn bestimmte Kriterien erfüllt sind. Dies reduziert die Indexgröße und beschleunigt die Indexerstellung und -wartung.

-- Erstellen Sie einen zusammengesetzten Index für die obige Beispielabfrage
CREATE INDEX idx_user_login_status ON user_data (status, last_login);

-- Erstellen Sie einen partiellen Index nur für aktive Benutzer
CREATE INDEX idx_active_users_email ON user_data (email) WHERE status = 'active';

Löschen Sie einen Index nicht nur, weil idx_scan heute Null ist. Die Statistiken werden nach Neustarts und manuellen Zurücksetzungen zurückgesetzt, und einige Indizes existieren für seltene, aber wichtige Aufgaben. Eine sicherere Überprüfung sieht so aus:

SELECT schemaname, relname, indexrelname, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

Wenn ein Index groß ist, über einen vollständigen Geschäftszyklus ungenutzt ist und keine Einschränkung unterstützt, ist er ein Kandidat für die Entfernung. Verwenden Sie in stark ausgelasteten Systemen DROP INDEX CONCURRENTLY, damit normale Lese- und Schreibvorgänge nicht für den gesamten Vorgang blockiert werden.

Falle 2: Vernachlässigung des Autovacuum-Daemons

PostgreSQL verwendet Multi-Version Concurrency Control (MVCC). Updates und Löschungen hinterlassen alte Zeilenversionen, bis der Vacuum sie bereinigen kann. Autovacuum ist keine optionale Hausarbeit; es ist Teil des normalen Datenbankbetriebs. Es entfernt tote Tupel, aktualisiert Planungsstatistiken durch Autoanalyze und verhindert den Transaktions-ID-Wraparound.

Diagnose: Übermäßiger Bloat

Das Ignorieren von Autovacuum führt zu Tabellen-Bloat, bei dem Dateisysteme ungenutzten Speicherplatz behalten, was sequenzielle Scans erheblich verlangsamt. Wenn Autovacuum mit hohem Schreibverkehr nicht Schritt halten kann, beschleunigt sich der XID-Verbrauch.

Häufiges Symptom: Hohe E/A-Wartezeit, wachsende Tabellendateien, veraltete Zeilenschätzungen und Tabellengrößen, die stetig steigen, obwohl die Anzahl der Live-Zeilen weitgehend stabil ist.

Nützliche erste Überprüfungen:

SELECT schemaname, relname, n_live_tup, n_dead_tup,
       last_autovacuum, last_autoanalyze,
       vacuum_count, autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

Vermeidung der Falle: Autovacuum optimieren

Viele Teams belassen die Standardeinstellungen und stellen dann fest, dass die Standardeinstellungen für eine oder zwei Tabellen mit hohem Änderungsaufkommen nicht aggressiv genug sind. Optimieren Sie diese Tabellen direkt, anstatt den gesamten Cluster laut zu machen.

Die Einstellungen, die zuerst verstanden werden sollten, sind:

1. autovacuum_vacuum_scale_factor: Der Anteil der Tabelle, der sich ändern muss, bevor der Vacuum ausgelöst wird. Große Tabellen benötigen normalerweise einen niedrigeren Wert.
2. autovacuum_vacuum_threshold: Der feste Zeilenschwellenwert, der zur Berechnung des Skalierungsfaktors hinzugefügt wird.
3. autovacuum_vacuum_cost_delay und autovacuum_vacuum_cost_limit: Drosselungssteuerungen. Ein schnellerer Autovacuum kann den E/A-Druck erhöhen. Beobachten Sie daher das System, nachdem Sie sie geändert haben.

Optimieren Sie diese global in postgresql.conf oder pro Tabelle mithilfe der Speicherparameter, um sicherzustellen, dass Autovacuum aggressiv genug läuft, um Tabellen mit hohem Änderungsaufkommen zu verwalten.

ALTER TABLE high_churn_table SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold = 5000
);

Die Falle besteht darin, Autovacuum zu deaktivieren, weil es während eines Leistungsproblems aufgetreten ist. Wenn Autovacuum ständig sichtbar ist, bedeutet das normalerweise, dass es versucht, mit dem Schreib-Churn Schritt zu halten. Behandeln Sie dies als Kapazitäts- und Optimierungssignal, nicht als Beweis dafür, dass Autovacuum die Ursache ist.

Falle 3: Das shared_buffers- und work_mem-Dilemma

Die falsche Konfiguration der Speicherzuweisung ist eine häufige Falle, die sich direkt auf die Datenbank-E/A-Leistung auswirkt. Zwei Parameter dominieren diesen Bereich: shared_buffers (Caching von Datenblöcken) und work_mem (Speicher, der für Sortier- und Hash-Operationen innerhalb einer Sitzung verwendet wird).

Diagnose: Hohe Datenträger-E/A und Spills

Wenn shared_buffers für die Arbeitslast zu klein ist, verlässt sich PostgreSQL stärker auf den Betriebssystem-Cache und den Speicher. Wenn work_mem zu klein ist, spillen Sortier- und Hash-Operationen temporäre Dateien auf die Festplatte. Wenn work_mem global zu groß ist, kann ein Burst gleichzeitiger Abfragen den Speicher erschöpfen.

Um nach Datenträger-Spills zu suchen, verwenden Sie EXPLAIN ANALYZE. Achten Sie auf Zeilen, die Folgendes anzeigen:

Sort Method: external merge Disk: 1234kB

Vermeidung der Falle: Strategische Speicherzuweisung

1. shared_buffers

Ein üblicher Ausgangspunkt für shared_buffers liegt bei etwa 25 % des Arbeitsspeichers, aber es ist keine universelle Regel. Kleinere Instanzen, Container-Speichergrenzen, gemischte Arbeitslasten und verwaltete Datenbankplattformen können den richtigen Wert ändern. PostgreSQL profitiert auch vom Betriebssystem-Seiten-Cache, daher ist es normalerweise ein Fehler, den gesamten Speicher shared_buffers zuzuweisen.

2. work_mem

Dieser Parameter ist sitzungsspezifisch. Eine häufige Falle ist die Einstellung eines hohen globalen work_mem, das, multipliziert mit Hunderten gleichzeitiger Verbindungen, schnell den Systemspeicher erschöpfen kann, was zu Swapping und Abstürzen führt. Legen Sie stattdessen einen konservativen globalen Standard fest und verwenden Sie SET work_mem, um ihn für bestimmte Sitzungen zu erhöhen, die komplexe Berichte oder Batch-Jobs ausführen.

# postgresql.conf Beispiel
shared_buffers = 12GB   # Angenommen 48 GB Gesamt-RAM
work_mem = 4MB          # Konservativer globaler Standard

Setzen Sie es für einen Berichts-Job nur für diese Sitzung oder Transaktion:

BEGIN;
SET LOCAL work_mem = '128MB';
-- die Berichtsabfrage ausführen
COMMIT;

Denken Sie daran, dass eine einzelne Abfrage work_mem mehr als einmal verwenden kann. Eine parallele Abfrage mit mehreren Sortier- oder Hash-Knoten kann den tatsächlich verwendeten Speicher multiplizieren.

Falle 4: Ignorieren von langlaufenden Abfragen und Sperren

Unbeschränkte, schlecht geschriebene Abfragen oder Anwendungsfehler können dazu führen, dass Verbindungen stundenlang aktiv bleiben, Ressourcen verbrauchen und, schlimmer noch, transaktionale Sperren halten, die andere Prozesse blockieren. Die Überwachung und Verwaltung dieser Abfragen zu unterlassen, ist ein großes Stabilitätsrisiko.

Diagnose: Überwachung aktiver Sitzungen

Verwenden Sie die Ansicht pg_stat_activity, um schnell langlaufende Abfragen, das spezifische SQL, das sie ausführen, und ihren aktuellen Status (z. B. Warten auf Sperre, aktiv) zu identifizieren.

SELECT pid, usename, client_addr, backend_start, state, query_start, query
FROM pg_stat_activity
WHERE state = 'active' AND now() - query_start > interval '5 minutes';

Fügen Sie für Sperrwartezustände blockierende PIDs hinzu:

SELECT a.pid,
       a.usename,
       a.state,
       now() - a.query_start AS age,
       pg_blocking_pids(a.pid) AS blocked_by,
       a.query
FROM pg_stat_activity a
WHERE cardinality(pg_blocking_pids(a.pid)) > 0
ORDER BY age DESC;

Vermeidung der Falle: Timeouts und Terminierung

Implementieren Sie Sitzungs- und Anweisungs-Timeouts, um ausufernde Prozesse automatisch zu beenden, bevor sie erheblichen Schaden anrichten.

1. statement_timeout: Die maximale Zeit, die eine einzelne Anweisung laufen kann, bevor sie abgebrochen wird. Dies sollte global oder pro Anwendungsverbindung festgelegt werden.
2. lock_timeout: Die maximale Zeit, die eine Anweisung auf eine Sperre wartet, bevor sie den Versuch aufgibt.

Für eine sofortige Schadensbegrenzung können Sie einen problematischen Prozess mit seiner in pg_stat_activity identifizierten Prozess-ID (PID) beenden:

-- Legen Sie ein globales Anweisungs-Timeout von 10 Minuten fest (600000 ms)
ALTER SYSTEM SET statement_timeout = '600s';

-- Beenden Sie eine bestimmte Abfrage mit ihrer PID
SELECT pg_terminate_backend(12345);

Bevorzugen Sie pg_cancel_backend(pid), wenn die Abfrage lediglich teuer ist. Es bricht die aktuelle Anweisung ab, lässt die Sitzung jedoch aktiv. Verwenden Sie pg_terminate_backend(pid), wenn die Sitzung im Leerlauf in einer Transaktion ist, Sperren hält oder nicht auf den Abbruch reagiert. Das Beenden des falschen Backends kann Arbeit rückgängig machen, deren Abschluss die Anwendung noch erwartet. Erfassen Sie daher vor dem Handeln die Abfrage, den Benutzer, die Client-Adresse und die Blockierungsbeziehung.

Falle 5: Schlechtes WAL-Management und Datenträger-Kapazitätsplanung

PostgreSQL ist für Dauerhaftigkeit und Replikation auf Write-Ahead Logging (WAL) angewiesen. WAL-Segmente sammeln sich bei starkem Schreibverkehr schnell an. Eine häufige betriebliche Falle ist es, die Überwachung des Datenträger-Speicherplatzes in Bezug auf WAL-Archive zu vernachlässigen oder aggressive WAL-Parameter ohne angemessene Speicherplanung festzulegen.

Diagnose: Datenbankstopp

Das schwerwiegendste Symptom eines schlechten WAL-Managements ist, dass der Datenbank der Speicherplatz auf der Partition ausgeht, die pg_wal enthält. Dies passiert oft, wenn die Archivierung fehlschlägt, ein Standby ausgefallen ist oder ein Replikations-Slot WAL für einen Verbraucher zurückhält, der nicht mehr existiert.

Vermeidung der Falle: Größenbestimmung und Archivierung

1. Kontrolle der WAL-Größe

Der Parameter max_wal_size ist ein Checkpoint-Ziel, kein festes Datenträger-Kontingent. PostgreSQL kann ihn überschreiten, wenn WAL für Archivierung, Replikation oder Wiederherstellung aufbewahrt werden muss. Eine zu niedrige Einstellung führt zu häufigen Checkpoints und zusätzlicher E/A. Eine höhere Einstellung kann den Checkpoint-Druck glätten, aber Sie benötigen dennoch eine Datenträger- und Archivüberwachung.

# postgresql.conf Beispiel
# Erhöhen, um die Checkpoint-Häufigkeit bei hoher Last zu reduzieren
max_wal_size = 4GB 
min_wal_size = 512MB

2. Archivierungsstrategie

Wenn die WAL-Archivierung (archive_mode = on) für die Point-in-Time-Wiederherstellung (PITR) oder Replikation aktiviert ist, muss der Archivierungsprozess zuverlässig sein. Wenn das Archivierungsziel (z. B. Netzwerkspeicher) nicht zugänglich wird, hält PostgreSQL weiterhin an den Segmenten fest und füllt schließlich die lokale Festplatte. Stellen Sie sicher, dass eine Überwachung vorhanden ist, um DBAs zu warnen, wenn archive_command-Fehler anhalten.

Überprüfen Sie auch die Replikations-Slots:

SELECT slot_name, slot_type, active, restart_lsn,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC NULLS LAST;

Ein inaktiver Slot mit wachsendem zurückgehaltenem WAL ist einer der schnellsten Wege, einen Primary zu füllen.

Eine praktische Reihenfolge für die Fehlerbehebung

Wenn Sie unter Druck stehen, verwenden Sie eine feste Reihenfolge, damit Sie nicht zufällig Symptomen nachjagen:

1. Überprüfen Sie den Datenträger-Speicherplatz, insbesondere das Datenverzeichnis, pg_wal und temporäre Dateispeicherorte.
2. Überprüfen Sie aktive Sitzungen und Blocker in pg_stat_activity.
3. Überprüfen Sie mit EXPLAIN (ANALYZE, BUFFERS), ob der langsame Abfrageplan tatsächlich das tut, was Sie denken.
4. Überprüfen Sie Tabellen-Churn, tote Tupel und den Autovacuum-Verlauf.
5. Überprüfen Sie die WAL-Archivierung, den Replikations-Lag und die Slot-Aufbewahrung.
6. Ändern Sie jeweils eine Sache und bewahren Sie die Vorher-/Nachher-Beweise auf.

Der größte Fehler bei der PostgreSQL-Fehlerbehebung ist, jeden Vorfall als Optimierungsproblem zu behandeln. Manchmal ist die richtige Lösung ein Index. Manchmal ist es ein fehlendes Timeout. Manchmal ist es ein festsitzender Replikations-Slot. Die Datenbank liefert Ihnen normalerweise genügend Beweise; die Disziplin besteht darin, diese Beweise zu lesen, bevor Sie an den Stellschrauben drehen.