PostgreSQL Performance Tuning Linux 2025

postgresql.conf optimieren

# Konfigurationsdatei
sudo nano /etc/postgresql/16/main/postgresql.conf

# Grundlegende Parameter (für Server mit 16 GB RAM)

# Memory
shared_buffers = 4GB              # 25% des RAM
effective_cache_size = 12GB       # 75% des RAM
work_mem = 64MB                   # Pro Sortier-/Hash-Operation
maintenance_work_mem = 1GB        # VACUUM, CREATE INDEX

# Disk
random_page_cost = 1.1            # SSD: 1.1 (statt 4.0)
effective_io_concurrency = 200    # SSD: 200 (statt 2)

# WAL (Write-Ahead Log)
wal_buffers = 64MB
checkpoint_completion_target = 0.9
max_wal_size = 4GB

# Connections
max_connections = 200
# Mit PgBouncer: max_connections kann niedrig bleiben!

# Logging
log_min_duration_statement = 1000  # Queries > 1s loggen
log_checkpoints = on
log_connections = on
log_temp_files = 64MB

# Parallelismus
max_parallel_workers_per_gather = 4
max_parallel_workers = 8

# PostgreSQL neu laden
systemctl reload postgresql

Slow Query Log analysieren

-- pg_stat_statements aktivieren
-- /etc/postgresql/16/main/postgresql.conf:
-- shared_preload_libraries = 'pg_stat_statements'

-- In Datenbank:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Top 10 langsamste Queries
SELECT
    round(total_exec_time::numeric, 2) AS total_ms,
    calls,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct_total,
    left(query, 100) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Statistiken zurücksetzen
SELECT pg_stat_reset();
SELECT pg_stat_statements_reset();

Fehlende Indexe finden

-- Tabellen ohne ausreichende Indexe (Sequential Scans)
SELECT
    schemaname,
    tablename,
    seq_scan,
    seq_tup_read,
    idx_scan,
    idx_tup_fetch
FROM pg_stat_user_tables
WHERE seq_scan > 100
ORDER BY seq_scan DESC
LIMIT 10;

-- EXPLAIN ANALYZE für langsame Query
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM bestellungen WHERE kunden_id = 12345 ORDER BY erstellt_at DESC LIMIT 10;

-- Index erstellen (CONCURRENT = kein Lock!)
CREATE INDEX CONCURRENTLY idx_bestellungen_kunden
ON bestellungen(kunden_id, erstellt_at DESC);

Autovacuum überwachen

-- Tabellen mit aufgeblasenen (bloated) Dead Tuples
SELECT
    schemaname,
    tablename,
    n_dead_tup,
    n_live_tup,
    round(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
    last_autovacuum,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

-- Manuelles VACUUM FULL für sehr aufgeblasene Tabellen
VACUUM FULL ANALYZE bestellungen;

Connection Pooling (PgBouncer)

apt install -y pgbouncer

# /etc/pgbouncer/pgbouncer.ini
cat > /etc/pgbouncer/pgbouncer.ini << 'EOF'
[databases]
appdb = host=127.0.0.1 port=5432 dbname=appdb

[pgbouncer]
listen_port = 6432
listen_addr = 127.0.0.1
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 500
default_pool_size = 20
EOF

systemctl enable --now pgbouncer

FAQ

Was ist der Unterschied zwischen shared_buffers und effective_cache_size?
shared_buffers: tatsächlich von PostgreSQL belegter RAM. effective_cache_size: Schätzung des OS-Caches – beeinflusst den Query Planner aber reserviert kein RAM.

Fazit

PostgreSQL läuft aus der Box konservativ konfiguriert. Die shared_buffers, work_mem und effective_cache_size Anpassungen bringen sofort messbare Performance-Verbesserungen.

PostgreSQL und Datenbankoptimierung für KMU in Heidelberg, Mannheim und der Rhein-Neckar-Region. Beratung anfragen.

PostgreSQL Performance-Tuning unter Linux 2025