PostgreSQL Performance und Sicherheit — Cheat Sheet

Das Blatt zum Seminar PostgreSQL Performance und Sicherheit in der Praxis — Parameter, Entscheidungsraster und Kurzformen zum Nachschlagen, wenn man vor einer Instanz sitzt und den Namen gerade nicht parat hat. Es bringt das Vorgehen nicht bei; wie man misst, deutet und daraus eine Maßnahme ableitet, ist der Inhalt der Module. Maßgeblich bleibt in jedem Zweifelsfall die PostgreSQL-Dokumentation zur eingesetzten Version.

Speicherparameter

Nur effective_cache_size reserviert keinen Speicher. work_mem ist der gefährlichste Wert: Er gilt je Sortierung, Hash und Aggregation — eine Abfrage mit mehreren solchen Schritten belegt ihn mehrfach, mal der Zahl aktiver Sitzungen.

Modul: Serverkonfiguration und Umgebung

Eine Einstellung ändern und nachweisen

ALTER SYSTEM SET work_mem = '32MB';
SELECT pg_reload_conf();
SHOW work_mem;                     -- neuer Wert steht

Steht nach dem Reload noch der alte Wert, verlangt der Parameter einen Neustart. Vorab sagt das die Spalte context in pg_settings. SHOW config_file zeigt, welche Datei die Instanz wirklich gelesen hat — postgresql.auto.conf überstimmt die von Hand gepflegte postgresql.conf. Zurückgenommen wird mit ALTER SYSTEM RESET.

Modul: Serverkonfiguration und Umgebung

Checkpoints und Write-Ahead-Logging

wal_level bestimmt zugleich, ob Replikation und Wiederherstellung auf einen Zeitpunkt möglich sind. Wer checkpoint_timeout erhöht, muss max_wal_size mitziehen, sonst greift weiter die WAL-Grenze.

SELECT num_timed, num_requested, write_time, sync_time
  FROM pg_stat_checkpointer;          -- ab Version 17
SELECT wal_records, wal_bytes FROM pg_stat_wal;

Viele num_requested gegenüber num_timed heißt: max_wal_size ist für diese Last zu knapp bemessen. Vor Version 17 standen die Checkpoint-Zähler in pg_stat_bgwriter.

Modul: Serverkonfiguration und Umgebung

Ausführungspläne lesen

Weichen Schätzung und Wirklichkeit stark ab, ist meist die Statistik das Thema, nicht der Index. Gemessen wird immer mit Puffern:

EXPLAIN (ANALYZE, BUFFERS) SELECT …;

ANALYZE führt die Abfrage wirklich aus — bei schreibenden Anweisungen also nur in einer Transaktion, die zurückgerollt wird.

Modul: Abfrage-Performance und Indizes

Welcher Index für welches Muster

CREATE INDEX buchung_bezahlt_idx ON buchung (gebucht_am)
       WHERE status = 'bezahlt';
CREATE INDEX buchung_uebersicht_idx ON buchung (gebucht_am DESC, status)
       INCLUDE (id, kunde_id);
CREATE INDEX veranstaltung_details_gin ON veranstaltung USING gin (details);

Die zuerst genannte Spalte eines zusammengesetzten Index muss in der Bedingung stehen. INCLUDE-Spalten sind nicht Teil des Suchschlüssels — wonach auch gefiltert wird, gehört in den Schlüssel. Nach dem Anlegen ANALYZE, im Produktivsystem CREATE INDEX CONCURRENTLY. Der Index-Only-Zugriff hängt an VACUUM: nur als sichtbar markierte Seiten erlauben ihn.

Modul: Abfrage-Performance und Indizes

Statistik und Umformulierung

SELECT relname, last_analyze, last_autoanalyze, n_mod_since_analyze
  FROM pg_stat_user_tables WHERE relname = 'buchung';
ANALYZE buchung;

ANALYZE ändert keine Daten, es aktualisiert nur das Bild, das der Planner von ihnen hat — nötig nach Massenimport, nach einem UPDATE über einen großen Teil der Tabelle und direkt nach einer Migration. Eine korrelierte Unterabfrage (SubPlan, eine Ausführung je äußerer Zeile) wird zur Verbund-Formulierung mit GROUP BY; das ist meist der größere Hebel als ein weiterer Index. Vorsicht bei der Semantik: Ein JOIN verwirft Zeilen ohne Posten.

Modul: Abfrage-Performance und Indizes

Autovacuum, MVCC und Wartungsmittel

Bei vielen großen Tabellen bremsen oft die Worker, nicht der Schwellwert. Für einzelne Vieländerungstabellen tabellenbezogen statt global stellen:

ALTER TABLE buchung SET (autovacuum_vacuum_scale_factor = 0.02,
                         autovacuum_vacuum_threshold = 500);

Rückstand erkennt man daran, dass der Anteil toter Zeilen über Tage steigt, statt nach jedem Lauf zu fallen, und dass das Transaktionsalter stetig wächst:

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
  FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;
SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2 DESC;

Module: Serverkonfiguration und Umgebung · Überwachung, Sperrverhalten und Wartung

Sperren und blockierende Sitzungen

SELECT a.pid, a.state, a.wait_event_type,
       pg_blocking_pids(a.pid) AS blockiert_von,
       now() - a.xact_start AS transaktionsalter
  FROM pg_stat_activity a
 WHERE cardinality(pg_blocking_pids(a.pid)) > 0;

SELECT pg_cancel_backend(4711);     -- sanft: Anweisung abbrechen
SELECT pg_terminate_backend(4711);  -- hart: Sitzung beenden

Steht der Blockierer auf idle in transaction, fehlt nicht Rechenleistung, sondern ein COMMIT. Beenden rollt die offene Transaktion zurück und lässt die Anwendung mit einem Verbindungsabbruch zurück — deshalb das letzte Mittel. Ein Anteil idle bei wenigen active in pg_stat_activity ist das klassische Bild für einen fehlenden Verbindungspool.

Module: Abfrage-Performance und Indizes · Überwachung, Sperrverhalten und Wartung

Überwachung: welches Werkzeug beantwortet was

Die teuersten Anweisungen sortiert man nach total_exec_time, nicht nach mean_exec_time — sonst verschwinden die häufig laufenden aus dem Bild. pg_stat_statements_reset() startet einen neuen Messzeitraum.

log_statement ist nicht log_min_duration_statement: Das eine schreibt alles mit, samt schützenswerter Werte.

Modul: Überwachung, Sperrverhalten und Wartung

Sicherheitsschichten

Die Reihenfolge ist zugleich der Weg einer Verbindung — jede Schicht kann sie beenden. Zum Nachweis, dass die Schichten wirklich greifen:

SHOW password_encryption;   -- soll scram-sha-256 sein
SELECT line_number, type, database, user_name, address, auth_method, error
  FROM pg_hba_file_rules ORDER BY line_number;
SELECT a.usename, a.client_addr, s.ssl, s.version
  FROM pg_stat_activity a JOIN pg_stat_ssl s ON s.pid = a.pid;

Die Spalte error in pg_hba_file_rules meldet Zeilen, die der Server nicht übernehmen konnte — sie wirken dann gar nicht. Der Server nimmt die erste passende Regel; eine großzügige Regel oben macht jede strengere darunter wirkungslos. Bestehende Kennwörter wandeln sich nicht von selbst auf SCRAM um, sie müssen einmal neu gesetzt werden.

Modul: Sicherheit im Produktivbetrieb

Rollen, Rechte und Row Level Security

Eigentum, Rechte und Anmeldung getrennt halten — die Anmelderolle nutzt die Objekte, besitzt sie aber nicht:

CREATE ROLE tonspur_owner NOLOGIN;
CREATE ROLE tonspur_readwrite NOLOGIN;
CREATE ROLE tonspur_app LOGIN PASSWORD '…';
GRANT tonspur_readwrite TO tonspur_app;
GRANT USAGE ON SCHEMA verkauf TO tonspur_readwrite;
GRANT SELECT, INSERT, UPDATE ON verkauf.buchung TO tonspur_readwrite;

ALTER DEFAULT PRIVILEGES FOR ROLE tonspur_owner IN SCHEMA verkauf
GRANT SELECT, INSERT, UPDATE ON TABLES TO tonspur_readwrite;

Standardrechte wirken nur für künftige Objekte dieser Rolle in diesem Schema, nie rückwirkend. \dp verkauf.buchung zeigt den Ist-Stand; nachsehen sollte man zuerst bei Rechten, die zur Fehlersuche vergeben wurden, bei der Rolle public und beim search_path.

ALTER TABLE verkauf.veranstaltung ENABLE ROW LEVEL SECURITY;
CREATE POLICY nur_eigene ON verkauf.veranstaltung
    FOR SELECT USING (veranstalter = current_user);

FORCE ROW LEVEL SECURITY erzwingt die Regeln auch für den Eigentümer. Eine Regel nur für SELECT lässt INSERT und UPDATE offen.

Modul: Sicherheit im Produktivbetrieb

Typische Fallen

• Einheiten übersehen — 16384 bei shared_buffers sind Blöcke zu 8 kB, nicht Bytes.
• Reload verbucht, Wert nicht geprüft — manche Parameter greifen erst nach einem Neustart, und der alte Wert bleibt kommentarlos stehen.
• Kosten für Millisekunden halten — cost ist einheitenlos, und EXPLAIN ohne ANALYZE liefert Schätzungen, keine Messwerte.
• Nach dem Indexanlegen kein ANALYZE und sich über den unveränderten Plan wundern.
• Für jede Spalte ein eigener Index, statt das Abfragemuster abzubilden — jeder Index kostet bei jeder Änderung, nicht nur beim Massenimport.
• Am Sortierknoten optimieren statt am teuren Durchlauf darunter.
• Autovacuum bei Rückstand abschalten — der Preis kommt später. Lange offene Transaktionen halten jedes Aufräumen auf, gleich wie scharf gestellt.
• VACUUM FULL im laufenden Betrieb — es sperrt die Tabelle vollständig; ein normales VACUUM verkleinert die Datei ohnehin nicht.
• Die wartende Sitzung abbrechen statt die blockierende, oder pg_terminate_backend einsetzen, wo ein COMMIT genügt hätte.
• trust aus der Entwicklung stehen lassen, die pg_hba.conf ändern und den Reload vergessen — oder sich ohne zweiten Zugangsweg selbst aussperren.
• Verschlüsselung annehmen, weil der Server sie anbietet — nachweisbar ist sie nur je Sitzung über pg_stat_ssl.
• Rechte direkt an Anmeldungen vergeben statt an eine Rechte-Rolle, oder die Anwendung mit der Eigentümerrolle verbinden lassen.
• RLS nur mit der administrativen Anmeldung testen und nichts bemerken — Eigentümer und Superuser gehen an jeder Regel vorbei.