PostgreSQL startet mit sehr konservativen Standardwerten, shared_buffers liegt zum Beispiel bei 128 MB. Für einen Produktionsserver lohnt sich deshalb ein gezieltes PostgreSQL-Performance-Tuning. Diese Anleitung richtet sich an Admins, die PostgreSQL selbst auf Debian oder Ubuntu betreiben. Sie behandelt die wichtigsten Parameter der postgresql.conf, Indizes, EXPLAIN, autovacuum und PgBouncer.

Voraussetzungen

Server: Debian 12/13 oder Ubuntu 24.04 LTS mit PostgreSQL 16 oder neuer, mindestens 2 CPU-Kerne und 4 GB RAM. Die Pfade nutzen 16; passen Sie die Versionsnummer an Ihre Installation an.
Zugriff: sudo-Rechte und Zugang per psql als Systembenutzer postgres.
RAM und Speichertyp bekannt: Die Faustregeln gelten für einen dedizierten DB-Server; läuft dort noch eine Web-App, rechnen Sie zurückhaltender. Ob SSD/NVMe oder HDD entscheidet über random_page_cost.
Wartungsfenster für Parameter, die einen Neustart brauchen, und eine aktuelle Sicherung der postgresql.conf.

Schritt 1: Konfigurationsdatei finden und Werte prüfen

Auf Debian und Ubuntu liegt die Datei unter /etc/postgresql/16/main/postgresql.conf. Den genauen Pfad fragen Sie bei PostgreSQL ab:

sudo -u postgres psql -c "SHOW config_file;"
# Ausgabe i.d.R.: /etc/postgresql/16/main/postgresql.conf (Versionsnummer anpassen)

Die wirksamen Werte zeigt SHOW einzeln oder pg_settings gesammelt mit Einheit:

SHOW shared_buffers;
SHOW work_mem;

SELECT name, setting, unit
FROM pg_settings
WHERE name IN ('shared_buffers','work_mem','maintenance_work_mem',
               'effective_cache_size','max_connections');

Defaults sind: shared_buffers 128 MB, work_mem 4 MB, maintenance_work_mem 64 MB, effective_cache_size 4 GB, max_connections 100.

Verifizieren: SHOW config_file; liefert einen existierenden Pfad, und die Abfrage auf pg_settings zeigt fünf Zeilen mit Werten. Notieren Sie diese als Ausgangsstand. Sichern Sie die Datei vor Änderungen: sudo cp /etc/postgresql/16/main/postgresql.conf{,.bak}.

Schritt 2: Speicher-Parameter mit Faustregeln setzen

Faustregeln für einen dedizierten DB-Server:

Parameter	Default	Faustregel	Wirkung
`shared_buffers`	128 MB	ca. 25 % RAM (nicht über 40 %)	von PostgreSQL selbst verwalteter Cache
`effective_cache_size`	4 GB	50–75 % RAM	nur Planner-Hinweis, reserviert keinen Speicher
`work_mem`	4 MB	16–64 MB (konservativ)	Speicher pro Sortier-/Hash-Operation und Verbindung
`maintenance_work_mem`	64 MB	256 MB bis 1 GB	für VACUUM, CREATE INDEX, FK-Anlage
`max_connections`	100	moderat, lieber PgBouncer	jede Verbindung kostet Speicher

work_mem gilt pro Sortier- oder Hash-Knoten und pro Verbindung, nicht global. Bei vielen parallelen Abfragen kann ein zu hoher Wert den RAM erschöpfen und den OOM-Killer auslösen. Halten Sie den globalen Wert niedrig und erhöhen Sie ihn nur für einzelne Sitzungen per SET.

Startpunkt für einen dedizierten Server mit 16 GB RAM und SSD in der postgresql.conf:

shared_buffers = 4GB                 # ~25% RAM
effective_cache_size = 12GB          # ~75% RAM (nur Planner-Hinweis)
work_mem = 32MB                      # pro Sort/Hash je Verbindung -> konservativ
maintenance_work_mem = 1GB           # schnelleres VACUUM/CREATE INDEX
max_connections = 100
random_page_cost = 1.1               # fuer SSD/NVMe

effective_cache_size reserviert keinen Speicher, sondern sagt dem Planner, wie viel Cache (PostgreSQL plus Betriebssystem) verfügbar ist. Ist der Wert zu niedrig, meidet der Planner Index Scans. random_page_cost steht per Default auf 4.0 (Annahme HDD); auf SSD/NVMe senken Sie ihn auf 1.1.

Verifizieren: Nach dem Reload in Schritt 4 zeigt die pg_settings-Abfrage aus Schritt 1 die neuen Werte; shared_buffers steht bis zum Neustart auf pending_restart = t.

Schritt 3: Checkpoint- und WAL-Settings anpassen

Schreiblastige Systeme profitieren davon, Checkpoints seltener und gleichmäßiger auszuführen. Defaults: checkpoint_timeout = 5min, max_wal_size = 1GB, min_wal_size = 80MB, checkpoint_completion_target = 0.9. Für schreiblastige Datenbanken vergrößern Sie Zeitfenster und WAL-Spielraum.

checkpoint_timeout = 15min
max_wal_size = 4GB
min_wal_size = 1GB
checkpoint_completion_target = 0.9

Damit verteilen sich Schreibspitzen besser. Ein zu großer WAL-Spielraum verlängert die Wiederherstellung nach einem Absturz.

Verifizieren: Nach dem Reload zeigt SHOW max_wal_size; den neuen Wert. Mit log_checkpoints = on (Default seit PostgreSQL 15) erscheinen im Log unter /var/log/postgresql/ Checkpoints überwiegend als „checkpoint starting: time“ statt „wal“.

Schritt 4: Änderungen aktivieren – reload oder restart?

Viele Werte greifen mit einem Reload ohne Verbindungsabbruch; shared_buffers, max_connections und shared_preload_libraries brauchen einen Neustart.

sudo systemctl reload postgresql      # fuer reload-faehige Parameter
sudo systemctl restart postgresql     # fuer shared_buffers, max_connections

Ob ein Parameter Reload oder Neustart braucht, zeigt die Spalte context in pg_settings (postmaster = Neustart, sighup = Reload):

SELECT name, setting, context
FROM pg_settings
WHERE name IN ('shared_buffers','max_connections','work_mem','random_page_cost');

Verifizieren: systemctl status postgresql@16-main zeigt „active (running)“, SELECT name FROM pg_settings WHERE pending_restart; liefert keine Zeile mehr, und SELECT * FROM pg_file_settings WHERE error IS NOT NULL; meldet keine Tippfehler in der Konfiguration.

Schritt 5: Indizes verstehen und anlegen

Der Standard-Indextyp in PostgreSQL ist der B-Tree (USING btree). Er passt für Gleichheit (=), Vergleiche (<, >), BETWEEN, ORDER BY und IS NULL. Ein Index lohnt sich bei hoher Selektivität, wenn eine Abfrage nur einen kleinen Teil der Zeilen liefert; bei einem größeren Anteil wählt der Planner zu Recht einen Seq Scan.

Ein normales CREATE INDEX sperrt Schreibzugriffe auf die Tabelle, bis es fertig ist. In Produktion legen Sie Indizes deshalb mit CONCURRENTLY an:

CREATE INDEX CONCURRENTLY idx_kunden_email ON kunden (email);

CONCURRENTLY läuft nicht in einem Transaktionsblock und dauert länger. Bricht es ab, bleibt ein ungültiger Index zurück (DROP INDEX CONCURRENTLY). Jeder Index verlangsamt Schreibzugriffe und kostet Platz.

Verifizieren: \d kunden in psql listet idx_kunden_email ohne den Zusatz INVALID.

Schritt 6: EXPLAIN und EXPLAIN ANALYZE lesen

EXPLAIN zeigt den geschätzten Plan, ohne die Abfrage auszuführen. EXPLAIN ANALYZE führt sie aus und zeigt reale Laufzeiten und Zeilenzahlen.

EXPLAIN SELECT * FROM kunden WHERE email = 'a@b.de';
-- nur Schaetzung, fuehrt nichts aus

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM kunden WHERE email = 'a@b.de';
-- fuehrt aus, zeigt reale Zeiten und Buffer-Treffer

Die wichtigsten Angaben im Plan:

cost=Start..Gesamt – geschätzte Start- und Gesamtkosten in einer abstrakten Einheit, nicht in Sekunden.
rows – die vom Planner geschätzte Zeilenzahl, width die geschätzte Zeilenbreite in Bytes.
actual time und rows (nur bei ANALYZE) – gemessene Zeit und reale Zeilenzahl. Starke Abweichungen zur Schätzung deuten auf veraltete Statistiken.

Entscheidend ist der Scan-Typ. Ein Seq Scan liest die ganze Tabelle; bei kleinen Tabellen ist das in Ordnung, bei großen Tabellen mit selektivem Filter deutet es auf einen fehlenden Index. Ein Index Scan nutzt einen Index. Ein Bitmap Heap/Index Scan ist der Mittelweg bei mehr betroffenen Zeilen.

Vorsicht: EXPLAIN ANALYZE führt die Abfrage wirklich aus. Bei INSERT, UPDATE oder DELETE verändert es also Daten. Kapseln Sie solche Statements in BEGIN; ... ROLLBACK;.

Verifizieren: Nach dem Anlegen des Index aus Schritt 5 zeigt EXPLAIN (ANALYZE, BUFFERS) für die Beispielabfrage einen Index Scan using idx_kunden_email, und die Execution Time ist niedriger als vorher.

Schritt 7: Statistiken und autovacuum verstehen

Der Planner entscheidet anhand von Statistiken über die Daten. Sind diese veraltet, etwa nach einem großen Import, wählt er falsche Pläne. Aktualisieren Sie die Statistiken dann manuell:

ANALYZE kunden;            -- aktualisiert nur die Statistik
VACUUM (ANALYZE) kunden;   -- raeumt tote Zeilen auf und aktualisiert die Statistik

Im Normalbetrieb erledigt das autovacuum. VACUUM startet ab autovacuum_vacuum_scale_factor 0.2 (20 % der Tabelle) plus autovacuum_vacuum_threshold 50 Zeilen. Bei großen, häufig geänderten Tabellen senken Sie diese Werte pro Tabelle, damit autovacuum öfter in kleineren Schritten läuft:

ALTER TABLE kunden SET (autovacuum_vacuum_scale_factor = 0.05);

Verifizieren: SELECT relname, n_dead_tup, last_autovacuum, last_autoanalyze FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10; zeigt für aktive Tabellen aktuelle Zeitstempel und keine stetig wachsende Zahl toter Zeilen.

Schritt 8: Fehlende und ungenutzte Indizes finden

PostgreSQL zählt, wie oft Tabellen sequenziell gelesen und Indizes genutzt werden (track_counts, per Default an). Damit finden Sie Kandidaten für neue und überflüssige Indizes.

SELECT relname, seq_scan, seq_tup_read, idx_scan, n_live_tup
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC
LIMIT 20;

Ein hoher seq_scan bei einer großen Tabelle (n_live_tup) deutet auf einen fehlenden Index. Ungenutzte Indizes finden Sie über idx_scan = 0:

SELECT s.relname AS tabelle, s.indexrelname AS index, s.idx_scan,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS groesse
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0 AND NOT i.indisprimary AND NOT i.indisunique
ORDER BY pg_relation_size(s.indexrelid) DESC;

Lassen Sie die Statistiken einige Wochen laufen, bevor Sie löschen: Ein Index kann für seltene Abfragen (z. B. Monatsabschluss) nötig sein.

Langsamste Abfragen mit pg_stat_statements finden

Die teuersten Abfragen zeigt die Erweiterung pg_stat_statements. Tragen Sie sie in shared_preload_libraries ein (Neustart nötig) und legen Sie sie in der Datenbank an:

-- 1) in postgresql.conf: shared_preload_libraries = 'pg_stat_statements'  (Neustart noetig)
-- 2) in der Datenbank:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT query, calls, mean_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

Verifizieren: SHOW shared_preload_libraries; enthält pg_stat_statements, und die Abfrage liefert nach einiger Laufzeit Zeilen mit calls größer 0.

Schritt 9: Connection-Pooling mit PgBouncer

Jede Datenbankverbindung kostet Speicher; max_connections hochzudrehen ist kein Tuning. Bei vielen kurzlebigen Verbindungen, etwa von einer Web-Anwendung, verwendet der Connection-Pooler PgBouncer eine kleine Zahl echter DB-Verbindungen wieder.

sudo apt update && sudo apt install -y pgbouncer

Die Konfiguration liegt in /etc/pgbouncer/pgbouncer.ini. Meist passt der transaction-Modus: Eine Serververbindung wird nur für die Dauer einer Transaktion an einen Client vergeben. In userlist.txt stehen Benutzer und SCRAM-Hash, den Sie mit SELECT rolname, rolpassword FROM pg_authid; auslesen.

[databases]
meinedb = host=127.0.0.1 port=5432 dbname=meinedb

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
reserve_pool_size = 5

Starten Sie den Dienst; die Anwendung verbindet sich danach über Port 6432 statt 5432.

sudo systemctl enable --now pgbouncer

Wichtig: Im transaction-Modus funktionieren Session-Variablen (SET), Session-Advisory-Locks und LISTEN/NOTIFY nicht zuverlässig. Prepared Statements auf Protokollebene unterstützt PgBouncer ab 1.21 über max_prepared_statements (ab 1.24 standardmäßig aktiv); Debian 12 liefert noch 1.18. Anwendungen, die auf Session-Funktionen bauen, brauchen den session-Modus.

Verifizieren: psql -h 127.0.0.1 -p 6432 -U appuser meinedb -c 'SELECT 1;' liefert 1, und ss -tlnp | grep 6432 zeigt PgBouncer nur auf 127.0.0.1.

Typische Fehler

work_mem zu hoch gesetzt: gilt pro Sortier-/Hash-Operation und Verbindung, siehe Schritt 2. Global niedrig halten, einzeln per SET erhöhen.
effective_cache_size missverstanden: reserviert keinen Speicher; zu niedrig lässt den Planner Index Scans meiden.
Reload statt Restart erwartet: shared_buffers und max_connections brauchen einen Neustart; Spalte context in pg_settings prüfen.
EXPLAIN ANALYZE auf schreibende Statements: verändert Daten; in BEGIN; ... ROLLBACK; kapseln.
Statistiken nach Import vergessen: manuell ANALYZE ausführen.
max_connections als Tuning: Bei vielen Verbindungen gehört PgBouncer davor.
PgBouncer transaction-Modus blind eingesetzt: Session-Variablen, Advisory Locks und LISTEN/NOTIFY brechen; bei PgBouncer vor 1.21 auch Prepared Statements. Anwendung vorher prüfen.
Zu viele oder ungenutzte Indizes: verlangsamen Schreibzugriffe; idx_scan = 0 regelmäßig prüfen.
CREATE INDEX in Produktion ohne CONCURRENTLY: sperrt Schreibzugriffe bis zum Ende; CREATE INDEX CONCURRENTLY nutzen.

Häufige Fragen

Wie viel RAM soll ich shared_buffers geben?

Auf einem dedizierten DB-Server etwa 25 % des RAM, nicht über 40 %. Die Änderung erfordert einen Neustart.

Warum nutzt der Planner meinen Index nicht?

Veraltete Statistiken (ANALYZE), geringe Selektivität oder zu hoher random_page_cost auf SSD.

Was ist der Unterschied zwischen EXPLAIN und EXPLAIN ANALYZE?

EXPLAIN schätzt nur, EXPLAIN ANALYZE führt die Abfrage aus und misst (siehe Schritt 6).

Muss ich autovacuum selbst starten?

Nein. Manuell greifen Sie nur nach großen Importen ein (ANALYZE).

Soll ich max_connections einfach erhöhen?

Besser nicht. Jede Verbindung belegt Speicher; bei vielen kurzlebigen Verbindungen schalten Sie PgBouncer davor (Schritt 9).

Brauche ich für SSD eine andere Konfiguration?

Ja: random_page_cost von 4.0 auf 1.1 senken, siehe Schritt 2.

Fazit

Angepasste Speicherwerte, passende Indizes, gepflegte Statistiken und PgBouncer bringen die größten Effekte. Ändern Sie immer nur eine Stellschraube und messen Sie vorher und nachher; so sehen Sie, was wirkt.

Weiterführende Anleitungen und Quellen

MariaDB/MySQL installieren und absichern
Grafana-Dashboards bauen: Datenquelle und Panel – Datenbank-Metriken dauerhaft sichtbar machen.
PostgreSQL nativ installieren und absichern – Installation aus dem PGDG-Repository und pg_hba.conf.
Weitere Anleitungen in der Kategorie Datenbanken.

Quellen: offizielle PostgreSQL-Dokumentation – Resource Consumption (shared_buffers, work_mem, maintenance_work_mem), Using EXPLAIN, WAL Configuration sowie die PgBouncer-Konfigurationsreferenz.