PostgreSQL Performance-Settings — Referenz-Artikel

Ziel: Überblick aller performance-relevanten Settings. Für jedes:
Was es macht, Default, wann anfassen, typische Werte. Getrennt für
Linux und Windows wo es unterschiedlich ist.

Inhaltsverzeichnis

1. Grundprinzipien — wie PG Memory und CPU nutzt
2. Memory-Settings
3. Parallel Query
4. WAL & Checkpoint
5. Autovacuum
6. IO & Planner-Costs
7. Connections & Timeouts
8. Shared Memory (Low-Level)
9. Logging (für Analysen)
10. Linux-spezifisch
11. Windows-spezifisch
12. Empfohlene Anfangswerte je Server-Größe
13. Diagnose-Queries

1. Grundprinzipien

PostgreSQL nutzt drei Arten von Memory, die du getrennt konfigurierst:

Faustregel für RAM-Aufteilung

Bei einem dedizierten PG-Server (nichts anderes läuft groß):

Bei shared Servern (andere Services parallel): shared_buffers konservativer auf 10-15 % RAM.

2. Memory-Settings

shared_buffers

Was: Interner Buffer-Cache für Table-/Index-Pages. Jeder Read/Write geht erst durch diesen Cache.

Default: 128 MB (viel zu niedrig für moderne Server — immer anpassen)

Faustregel:

• Dedicated DB-Server: 25 % RAM
• Shared-Server: 10-15 % RAM
• Obere Grenze: ~40 % RAM (darüber bringt's nichts, OS-Cache ist effizienter)

Wann anfassen: Immer beim Setup.

Wichtig: shared_buffers ist fest reserviert beim Serverstart. Braucht Restart bei Änderung.

work_mem

Was: Memory-Budget pro Sort/Hash-Operation innerhalb einer Query. Eine komplexe Query kann 5-10 Sort/Hash-Ops haben → jede bekommt eigenes work_mem.

Default: 4 MB (absurd niedrig)

Faustregel:

• Konservativ: 16-32 MB
• Offensiv (wenig Connections): 64-128 MB
• Per-Session overrideable: SET work_mem = '256MB' für spezifische Jobs

Wann anfassen: Immer. Aber Vorsicht:

• Worst-Case-RAM = max_connections × work_mem × ~3 (Ops) × multi
• Bei 100 Connections × 32 MB × 3 = 9,6 GB potenziell

Signal dass zu klein: EXPLAIN zeigt Sort Method: external merge oder Hash Batches: N (N>1) → Spill-to-Disk.

maintenance_work_mem

Was: Memory für VACUUM, CREATE INDEX, ALTER TABLE. Pro parallel maintenance worker.

Default: 64 MB (oft zu klein)

Empfehlung: 256 MB - 2 GB, je nach RAM. Große maintenance_work_mem macht CREATE INDEX und VACUUM deutlich schneller.

Wann anfassen: Sofort beim Setup. Kann per Session override'd werden wenn gebraucht.

hash_mem_multiplier

Was: Multiplikator für work_mem nur bei Hash-Ops (Hash Join, Hash Aggregate). Hash-Spills sind teurer als Sort-Spills, darum separater Budget-Boost.

Default: 2.0 (seit PG 15), vorher 1.0

Faustregel: 2.0 passt meistens. Auf 3.0-4.0 hochdrehen wenn EXPLAIN viele Hash-Spills zeigt.

Wann anfassen: Selten. Bei stark analytischem Workload mit großen Aggregaten sinnvoll.

temp_buffers

Was: Memory für temporäre Tabellen pro Session.

Default: 8 MB (OK für die meisten)

Wann anfassen: Nur wenn massiv temporäre Tables genutzt werden (ETL-Workloads). Dann auf 64-256 MB.

effective_cache_size

Was: Schätzung wie viel RAM insgesamt für Disk-Caching zur Verfügung steht (inkl. OS-Cache). Der Query-Planner nutzt das um Index-Scans vs. Seq-Scans zu kalkulieren. Keine tatsächliche Reservierung — nur ein Hinweis an den Planner.

Default: 4 GB

Faustregel: 50-75 % des RAMs.

Wann anfassen: Immer. Wirkt nur auf Query-Pläne, nicht auf Memory-Verbrauch.

wal_buffers

Was: Shared-Memory-Puffer für WAL (Write-Ahead Log) bevor er auf Disk geht.

Default: -1 = 1/32 von shared_buffers, min 64 kB, max 16 MB (wird automatisch berechnet)

Wann anfassen: Selten. Bei schreibintensiven Workloads auf 64 MB fix setzen. Sonst Default lassen.

huge_pages (Linux) / large_pages (Windows)

Was: Aktiviert Kernel-HugePages für shared_buffers. Reduziert TLB-Misses um ~10-15 %.

Default: try (versucht HugePages, fällt still zurück)

Werte:

• off — deaktiviert
• try — nutzt HugePages wenn verfügbar (Standard)
• on — erzwingt, PG startet nicht ohne (Fehlerdetection)

Wann anfassen: Bei shared_buffers ≥ 4 GB lohnt HugePages klar. Linux-Setup siehe separater Artikel postgres-hugepages.md.

3. Parallel Query

max_worker_processes

Was: Harter Cap für alle Background-Worker (parallel queries, autovacuum, logical replication, Extensions wie pg_cron…).

Default: 8 (viel zu niedrig für moderne Server)

Faustregel: CPU-Cores × 2 oder mindestens CPU-Cores, minimum 16.

Wann anfassen: Immer. Braucht Restart.

Symptom wenn zu klein: Queries laufen effektiv seriell trotz max_parallel_workers_per_gather > 0. Du siehst in pg_stat_activity keine parallel worker-Backends trotz Plan-Aussicht.

max_parallel_workers

Was: Wie viele von max_worker_processes dürfen parallel queries fahren (Rest bleibt für autovacuum etc.).

Default: 8

Faustregel: ≤ CPU-Cores. Auf 16-Core-System: 12-16.

Braucht Restart.

max_parallel_workers_per_gather

Was: Maximum worker pro einzelner Query.

Default: 2

Faustregel:

• OLTP (viele Queries, klein): 2
• Analytisch (wenige, große): 4-8
• Reporting-Server: bis 16 möglich

Zur Laufzeit änderbar per Session: SET max_parallel_workers_per_gather = 8;

max_parallel_maintenance_workers

Was: Parallelität für CREATE INDEX, VACUUM.

Default: 2

Faustregel: 4 bei ≥ 8 Cores. Macht CREATE INDEX auf großen Tabellen signifikant schneller.

parallel_tuple_cost / parallel_setup_cost

Was: Kosten, die der Planner für Parallelisierung einrechnet. Hoch = weniger parallel.

Default: 0.1 / 1000

Wann anfassen: Wenn Planner zu selten parallel plant trotz config. Dann parallel_setup_cost = 100 (runter von 1000).

Effekt: Kleinere Queries dürfen parallel werden.

min_parallel_table_scan_size / min_parallel_index_scan_size

Was: Min-Größe einer Tabelle/Index damit Planner überhaupt parallel in Betracht zieht.

Default: 8 MB / 512 kB

Anfassen: Kleiner (z.B. 1MB) macht parallel für kleinere Tables verfügbar. Selten nötig.

4. WAL & Checkpoint

wal_level

Was: Wie viel Information in WAL geschrieben wird.

Werte:

• minimal — nur Recovery, kein Streaming Replication
• replica (Default seit PG 10) — unterstützt Standby-Replikation
• logical — plus Logical Replication

Anfassen: Nur bei aktiver Replikation.

checkpoint_timeout

Was: Max Zeit zwischen Checkpoints (dirty Pages werden auf Disk synced).

Default: 5 Min (zu oft bei schreibintensiven Workloads)

Faustregel: 15-30 Minuten. Mehr = weniger IO-Spikes, aber mehr WAL zu recovern nach Crash.

max_wal_size / min_wal_size

Was: WAL-Verzeichnis-Größenlimit. Wenn erreicht → Checkpoint-Trigger.

Default: 1 GB / 80 MB

Faustregel:

• Kleiner Server: 2-4 GB
• Schreibintensiv: 8-32 GB
• min_wal_size: 1-4 GB

Höher = weniger Checkpoint-Overhead, mehr Disk-Space.

checkpoint_completion_target

Was: Spread des Checkpoint-Writes (0.0 = sofort alles, 1.0 = bis zum nächsten Checkpoint).

Default: 0.9 (modern)

Wann anfassen: Selten. Default 0.9 ist gut.

wal_compression

Was: Komprimiert WAL-Einträge für full-page-writes.

Default: off (historisch)

Empfehlung: on oder lz4 (PG 15+) — reduziert WAL-Volumen deutlich, kostet minimale CPU.

wal_writer_delay / wal_writer_flush_after

Was: Timing des WAL-Writer-Prozesses.

Default: 200 ms / 1 MB

Selten anfassen. Defaults sind vernünftig.

5. Autovacuum

autovacuum

Was: Automatisches VACUUM und ANALYZE im Hintergrund.

Default: on

NIE ausschalten. Außer in speziellen Batch-Szenarien.

autovacuum_max_workers

Was: Wie viele autovacuum-Prozesse parallel laufen dürfen.

Default: 3

Faustregel: 3-6. Abhängig von DB-Größe und Tabellen-Anzahl.

autovacuum_vacuum_scale_factor / autovacuum_analyze_scale_factor

Was: Anteil veränderter Tabelle, bei dem autovacuum getriggert wird.

Default: 0.2 / 0.1 (20 % / 10 %)

Faustregel:

• Sehr große Tabellen: 0.05 / 0.025 (triggert früher — wichtig für Query-Pläne!)
• Kleine Tabellen: Default OK

Szenario: Eine 100-Mio-Row-Tabelle sollte nicht auf 20 Mio Änderungen warten bis ANALYZE läuft — das kostet Stunden an schlechten Query-Plänen.

Per Tabelle overridable:

ALTER TABLE big_table SET (autovacuum_analyze_scale_factor = 0.025);

autovacuum_vacuum_cost_delay

Was: Pause des autovacuum-Workers zwischen Operationen (drosselt IO-Impact).

Default: 2 ms

Faustregel: Bei stark IO-sensiblen Workloads auf 5-10 ms hoch. Sonst Default.

vacuum_cost_limit / autovacuum_vacuum_cost_limit

Was: Bevor autovacuum pausiert.

Default: 200 / -1 (inherit)

Faustregel: Bei schnellen SSDs auf 2000 hochsetzen → VACUUM macht mehr pro Runde → fertig schneller.

6. IO & Planner-Costs

random_page_cost / seq_page_cost

Was: Planner-Kosten für Random- bzw. Sequential-Disk-Access.

Default: 4.0 / 1.0 (basiert auf HDDs)

Faustregel:

• SSDs: random_page_cost = 1.1 (Random ist fast gleich schnell)
• NVMe: random_page_cost = 1.0
• HDDs: Default lassen

Effekt: Planner wählt Index-Scans aggressiver wenn random_page_cost niedriger ist.

effective_io_concurrency

Was: Wie viele parallele IO-Requests der Planner annimmt (für Bitmap-Heap-Scans etc.).

Default: 1 (HDD)

Faustregel:

• SSD: 200
• NVMe: 300-500
• RAID mit vielen Spindeln: N × Spindeln

maintenance_io_concurrency

Was: Gleicher Wert für VACUUM & ANALYZE.

Default: 10

Empfehlung: Doppelt so hoch wie effective_io_concurrency (für schnelleres VACUUM).

default_statistics_target

Was: Wie viele Statistik-Samples ANALYZE pro Spalte anlegt. Mehr = genauere Planner-Entscheidungen, aber längere ANALYZE-Zeiten.

Default: 100

Faustregel: 200-500 bei analytischem Workload, 100 bei OLTP.

Per Spalte overridable:

ALTER TABLE big_table ALTER COLUMN important_col SET STATISTICS 500;

7. Connections & Timeouts

max_connections

Was: Maximum Client-Connections.

Default: 100

Faustregel: So niedrig wie möglich. Jede Connection reserviert Memory (ca. 10 MB) und belegt work_mem × Ops. Besser Connection-Pooler (pgbouncer) davor.

Szenario-Werte:

• Webanwendung mit pgbouncer: 100-200
• Direkt-Connects Admin/Batch: 300-500
• Reporting-Server: 50-100

idle_in_transaction_session_timeout

Was: Killt Sessions die mit offener Transaktion idle sind.

Default: 0 (unlimited — gefährlich)

Empfehlung: 5-15 min. Verhindert „Zombie-Transaktionen" die autovacuum blockieren.

statement_timeout

Was: Killt Queries die länger laufen als X.

Default: 0 (unlimited)

Empfehlung: Pro Session/Rolle setzen — nicht global:

ALTER ROLE webapp SET statement_timeout = '30s';
ALTER ROLE batch SET statement_timeout = '1h';

lock_timeout

Was: Max Wartezeit auf Lock.

Default: 0 (unlimited)

Empfehlung: In kritischen Sessions 5-30s setzen. Verhindert dass DDL-Operationen ewig blockieren.

8. Shared Memory (Low-Level)

shared_memory_type

Was: Implementierung des shared-memory-Bereichs.

Werte: mmap (Linux-Default), sysv, windows

Anfassen: Fast nie. Nur bei Container-Constraints wo /dev/shm zu klein.

dynamic_shared_memory_type

Was: Implementierung für parallel-worker-Kommunikation.

Werte: posix (Linux-Default), sysv, mmap, windows, none

Anfassen: Fast nie. none deaktiviert parallel queries → nicht anfassen.

min_dynamic_shared_memory

Was: Prä-allokiert dynamic-shared-memory beim Serverstart.

Default: 0 (on-demand)

Anfassen: Bei sehr häufigen parallel queries vielleicht 64-256 MB. Effekt < 1%.

9. Logging (für Analysen)

log_min_duration_statement

Was: Loggt alle Queries, die länger als X laufen.

Default: -1 (aus)

Empfehlung: 1000 (1 Sek) auf Produktion. Zeigt dir schleichende langsame Queries.

log_checkpoints

Was: Loggt jeden Checkpoint.

Default: on (seit PG 15)

Anfassen: Lass on — wichtig für Performance-Analyse.

log_autovacuum_min_duration

Was: Loggt autovacuum-Läufe die länger dauern.

Default: 10 Min (PG 15+), vorher -1 (aus)

Empfehlung: 1000 (1s) — du siehst welche Tabellen autovacuum-Probleme haben.

log_lock_waits

Was: Loggt wenn Sessions > deadlock_timeout auf Locks warten.

Default: off

Empfehlung: on auf Produktion.

log_temp_files

Was: Loggt Queries die temporäre Dateien anlegen (= work_mem gespillt).

Default: -1 (aus)

Empfehlung: 0 (alle) oder 1024 (ab 1 MB). Zeigt dir wo work_mem zu klein ist.

10. Linux-spezifisch

/etc/sysctl.conf (Kernel-Seite)

# Swappen so lange wie möglich vermeiden (DB hat eigenen Cache)
vm.swappiness = 10

# Dirty-Pages früher flushen → weniger IO-Bursts
vm.dirty_background_ratio = 5
vm.dirty_ratio = 10

# HugePages für Postgres (siehe postgres-hugepages.md)
vm.nr_hugepages = 9100    # abhängig von shared_buffers

# IPv6 disablen wenn nicht genutzt (reduziert Syscall-Overhead minimal)
# net.ipv6.conf.all.disable_ipv6 = 1

# SHM-Limits hochdrehen (falls shared_memory_type=sysv)
# kernel.shmmax = 8589934592
# kernel.shmall = 2097152

Transparent HugePages (THP)

Für DB-Server deaktivieren — kann zu sporadischen Stalls führen. Siehe postgres-hugepages.md Abschnitt 8.

IO-Scheduler (SSD/NVMe)

# Prüfen
cat /sys/block/sda/queue/scheduler

# Auf NVMe optimal
echo none | sudo tee /sys/block/nvme0n1/queue/scheduler

# Auf SSD optimal
echo mq-deadline | sudo tee /sys/block/sda/queue/scheduler

Persistent via udev-Regeln oder GRUB-Parameter.

Filesystem für PG-Datadir

Empfehlung: ext4 oder xfs mit noatime,nodiratime gemountet. Vermeide nobarrier (Datensicherheit vor Perf).

11. Windows-spezifisch

large_pages

Was: Windows-Äquivalent zu Linux HugePages.

Werte: off, try, on

Vorher: Security-Policy setzen:

1. secpol.msc öffnen
2. Local Policies → User Rights Assignment → Lock pages in memory
3. Den PG-Service-User hinzufügen
4. Reboot oder Logout

Dann large_pages = try in postgresql.conf.

Kein huge_pages auf Windows

Windows hat keine nr_hugepages-Äquivalenz — die Large-Pages werden on-demand über VirtualAlloc(MEM_LARGE_PAGES) angefordert. Wenn Windows nicht genug zusammenhängenden Speicher hat, schlägt's fehl.

update_process_title

Windows: deaktivieren (off) — auf Windows kostet das wirklich Performance (ps-Titel setzen ist teuer).

Performance-Counter aktivieren

# Windows Performance Counter für Postgres 18
lodctr /R

Shared Memory Implementation

Auf Windows hast du keine Wahl — shared_memory_type ist fest windows, dynamic_shared_memory_type fest windows.

12. Empfohlene Anfangswerte

Kleiner Server (4 GB RAM, 2-4 Cores)

shared_buffers = 1GB
work_mem = 16MB
maintenance_work_mem = 128MB
effective_cache_size = 3GB
max_connections = 50
max_worker_processes = 4
max_parallel_workers = 4
max_parallel_workers_per_gather = 2
random_page_cost = 1.1           # SSD
effective_io_concurrency = 200
wal_compression = on
checkpoint_timeout = 15min
max_wal_size = 2GB
log_min_duration_statement = 1000

Mittlerer Server (32 GB RAM, 8 Cores, SSD)

shared_buffers = 8GB
work_mem = 32MB                   # bei max_connections = 100 → max 9-12 GB
maintenance_work_mem = 1GB
effective_cache_size = 20GB
huge_pages = try
max_connections = 100
max_worker_processes = 16
max_parallel_workers = 8
max_parallel_workers_per_gather = 4
max_parallel_maintenance_workers = 4
random_page_cost = 1.1
effective_io_concurrency = 200
maintenance_io_concurrency = 400
wal_compression = lz4
checkpoint_timeout = 30min
max_wal_size = 8GB
log_min_duration_statement = 1000
log_autovacuum_min_duration = 1000
log_temp_files = 1024
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.05

Großer Server (128 GB RAM, 16+ Cores, NVMe)

shared_buffers = 16GB             # oder 32GB wenn dedicated
work_mem = 64MB
maintenance_work_mem = 2GB
effective_cache_size = 96GB
huge_pages = on                   # Kernel: vm.nr_hugepages = 9100
max_connections = 200
max_worker_processes = 32
max_parallel_workers = 16
max_parallel_workers_per_gather = 4
max_parallel_maintenance_workers = 4
random_page_cost = 1.0
effective_io_concurrency = 300
maintenance_io_concurrency = 500
wal_compression = lz4
checkpoint_timeout = 30min
max_wal_size = 32GB
log_min_duration_statement = 500
log_autovacuum_min_duration = 1000
log_temp_files = 1024
autovacuum_max_workers = 6
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.02

13. Diagnose-Queries

Non-Default-Settings anzeigen

SELECT name, setting, unit, short_desc
FROM pg_settings
WHERE source != 'default'
ORDER BY name;

Buffer-Hit-Rate (Ziel: > 99 %)

SELECT
    round(100.0 * sum(heap_blks_hit) / nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0), 2) AS cache_hit_ratio
FROM pg_statio_user_tables;

Unter 95 % → shared_buffers zu klein.

Queries mit Disk-Spill (work_mem zu klein)

SELECT query, calls, total_exec_time, temp_blks_written
FROM pg_stat_statements
WHERE temp_blks_written > 0
ORDER BY temp_blks_written DESC
LIMIT 20;

(Braucht Extension pg_stat_statements.)

Lange laufende Queries jetzt

SELECT pid, state, wait_event_type, wait_event,
       NOW() - query_start AS duration,
       substring(query, 1, 100) AS q
FROM pg_stat_activity
WHERE state = 'active' AND backend_type = 'client backend'
ORDER BY query_start;

Tabellen die autovacuum brauchen

SELECT schemaname, relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC
LIMIT 20;

Welche Queries sind teuer? (pg_stat_statements)

SELECT query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Index-Nutzung

SELECT schemaname, relname, indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

Zeigt unused indexes — sind Write-Overhead ohne Read-Nutzen.

Checkpoint-Verhalten

SELECT checkpoints_timed, checkpoints_req, checkpoint_write_time, buffers_written
FROM pg_stat_bgwriter;

checkpoints_req ≫ checkpoints_timed → max_wal_size zu klein.

Abschluss

Die 5 wichtigsten Settings die du fast immer anfassen musst:

1. shared_buffers (25% RAM dedicated, 10-15% shared)
2. work_mem (32-64 MB)
3. effective_cache_size (50-75% RAM)
4. max_worker_processes (≥ CPU-Cores × 2)
5. random_page_cost = 1.1 (SSD-Anpassung)

Der Rest ist Feintuning — nur dann sinnvoll wenn du vorher gemessen hast (EXPLAIN ANALYZE, pg_stat_statements, pg_stat_bgwriter).

Goldene Regel: Defaults sind meistens OK. Jede Änderung mit Messung vorher/nachher validieren. Nicht blind aus Blogposts übernehmen.

Weiterführend

• PostgreSQL Tuning Guide
• pgtune.leopard.in.ua — Online-Konfigurator mit Ausgangswerten
• pg_stat_statements aktivieren für Query-Statistik
• pg_stat_activity für Live-Analyse