Technologie & Architektur

PostgreSQL Performance: 10 Jahre Learnings aus Produktionssystemen

Carola Schulte

Invalid Date

25 min

“Warum ist das Dashboard so langsam?” – Diese Frage höre ich oft, wenn ich Legacy-Systeme übernehme. Die Antwort ist fast immer: Nicht PostgreSQL ist langsam, sondern die Queries sind schlecht. Nach 10 Jahren PostgreSQL in Produktion (15+ Business-Apps, 50M+ Zeilen, 24/7-Betrieb) zeige ich Ihnen, welche Optimierungen wirklich wirken.

Executive Summary: ROI & Business Impact

Controlling-System-Optimierung (Real Case):

Dashboard-Ladezeit: 30s → 0,8s (37x schneller)
Monatsabschluss: 19 Tage → 2 Tage (90% Reduktion)
Server-Kosten: 3 Server → 1 Server (4.800€/Jahr gespart)
Mobile UX: App-Store-Reviews von “Zu langsam” → “Endlich schnell!”

Investition: 6 Wochen Optimierung (1 Senior-Dev), ROI nach 4 Monaten.

TL;DR – Technical Deep Dive

Materialized Views: 2.800ms → 8ms (350x schneller) für Controlling-Dashboard
Partitioning: INSERT-Performance +40%, Query-Performance +60% bei Zeitreihen
Index-Strategien: GIN/BRIN/Partial Indexes richtig einsetzen → 90% weniger Queries über 100ms
Query-Tuning: EXPLAIN ANALYZE ist Pflicht, nicht optional

Das Performance-Problem: Symptome erkennen

Typische Schmerzpunkte in Business-Apps

Dashboard lädt 30+ Sekunden

Controller-Team beschwert sich über “langsame Zahlen”
CFO will Live-KPIs, bekommt aber 5-min-Verzögerung
Report-Generierung blockiert andere Requests

Monatsabschluss dauert 19 Tage

Excel-Export läuft “über Nacht” (12h+)
Datenkonsolidierung statt 2h jetzt 8h
Geschäftsführung sieht Zahlen erst nach 3 Wochen

Mobile App fühlt sich träge an

REST-API antwortet in 300-800ms statt unter 50ms
User beschweren sich über “hängende” Screens
App-Store-Reviews: “Zu langsam, unbrauchbar”

Die echten Ursachen (nicht PostgreSQL selbst)

Fehlende Indexes: Jede Query macht Sequential Scan über Millionen Zeilen
N+1 Problem: 1 Query für Liste + 500 Queries für Details = 501 Roundtrips
Keine Materialized Views: Aggregationen jedes Mal live berechnet
Falsche Datentypen: VARCHAR statt INTEGER für IDs → kein Index Scan
Keine Partitionierung: 50M Zeilen in einer Tabelle → Sequential Scan unvermeidbar

Materialized Views: Der Performance-Game-Changer

Problem: Live-Aggregationen sind zu langsam

Controlling-Dashboard (15.000 Mitarbeiter, 12M Umsatz-Datensätze):

-- Naive Implementierung (wird bei jedem Page-Load ausgeführt)
SELECT
    DATE_TRUNC('month', u.datum) AS monat,
    a.name AS abteilung,
    SUM(u.umsatz) AS umsatz_gesamt,
    COUNT(DISTINCT u.kunde_id) AS anzahl_kunden,
    AVG(u.umsatz) AS avg_pro_transaktion
FROM umsatz u
JOIN kunden k ON u.kunde_id = k.id
JOIN abteilungen a ON k.abteilung_id = a.id
GROUP BY DATE_TRUNC('month', u.datum), a.id, a.name
ORDER BY monat DESC, abteilung;

Performance: 2.800ms (Live-Aggregation über 12M Zeilen)

Lösung: Materialized View

CREATE MATERIALIZED VIEW kpi_umsatz_monat AS
SELECT
    DATE_TRUNC('month', u.datum) AS monat,
    a.id AS abteilung_id,
    a.name AS abteilung,
    SUM(u.umsatz) AS umsatz_gesamt,
    COUNT(DISTINCT u.kunde_id) AS anzahl_kunden,
    AVG(u.umsatz) AS avg_umsatz_pro_transaktion,

    -- Window Functions für Vormonats-Vergleich
    LAG(SUM(u.umsatz)) OVER (
        PARTITION BY a.id ORDER BY DATE_TRUNC('month', u.datum)
    ) AS umsatz_vormonat,

    -- Wachstum in Prozent
    ROUND(
        (SUM(u.umsatz) - LAG(SUM(u.umsatz)) OVER (
            PARTITION BY a.id ORDER BY DATE_TRUNC('month', u.datum)
        )) / NULLIF(LAG(SUM(u.umsatz)) OVER (
            PARTITION BY a.id ORDER BY DATE_TRUNC('month', u.datum)
        ), 0) * 100,
        2
    ) AS wachstum_prozent
FROM umsatz u
JOIN kunden k ON u.kunde_id = k.id
JOIN abteilungen a ON k.abteilung_id = a.id
GROUP BY DATE_TRUNC('month', u.datum), a.id, a.name;

-- Index für schnellen Zugriff
CREATE UNIQUE INDEX idx_kpi_umsatz_monat_pk
    ON kpi_umsatz_monat(abteilung_id, monat);

CREATE INDEX idx_kpi_umsatz_monat_monat
    ON kpi_umsatz_monat(monat DESC);

Performance nach Materialized View:

Query: 8ms (Read from Index)
Refresh: 45 Sekunden (nachts um 03:00 Uhr)
Speedup: 350x schneller

Refresh-Strategien: Incremental statt Full

Problem: Volle 36 Monate neu berechnen bei jedem Refresh (45s)

Lösung: Nur aktuelle + Vormonat refreshen (inkrementell)

-- Inkrementeller Refresh (nur letzte 2 Monate)
REFRESH MATERIALIZED VIEW CONCURRENTLY kpi_umsatz_monat
WHERE monat >= CURRENT_DATE - INTERVAL '2 months';

Achtung: PostgreSQL 14+ unterstützt das nicht nativ. Workaround:

-- Lösche alte Einträge, INSERT nur neue
BEGIN;
DELETE FROM kpi_umsatz_monat
WHERE monat >= CURRENT_DATE - INTERVAL '2 months';

INSERT INTO kpi_umsatz_monat
SELECT ...
FROM umsatz u
WHERE u.datum >= CURRENT_DATE - INTERVAL '2 months';
COMMIT;

Performance: Refresh-Zeit von 45s auf 6s reduziert (nur aktuelle Monate).

Governance: Refresh-Lock & Stale-Window

Problem: Parallele ETL-Prozesse + Refresh kollidieren → Deadlocks

Lösung: Advisory Lock vor Refresh

-- Refresh-Lock-Tabelle (einmalig erstellen)
CREATE TABLE refresh_lock (
    name TEXT PRIMARY KEY,
    locked_at TIMESTAMP
);
INSERT INTO refresh_lock (name) VALUES ('kpi_umsatz_monat');

-- Vor Refresh: Lock holen (SKIP LOCKED = kein Warten)
BEGIN;
SELECT 1 FROM refresh_lock
WHERE name = 'kpi_umsatz_monat'
FOR UPDATE SKIP LOCKED;

-- Wenn keine Zeile → andere Prozess hat Lock, abbrechen
-- Wenn Zeile → weitermachen mit Refresh
UPDATE refresh_lock SET locked_at = NOW()
WHERE name = 'kpi_umsatz_monat';

-- Refresh durchführen...
DELETE FROM kpi_umsatz_monat WHERE monat >= CURRENT_DATE - INTERVAL '2 months';
INSERT INTO kpi_umsatz_monat SELECT ...;

COMMIT;

Stale-Window transparent machen:

-- Letzte Refresh-Zeit speichern
CREATE TABLE materialized_view_meta (
    view_name TEXT PRIMARY KEY,
    last_refresh TIMESTAMP,
    data_until DATE
);

-- Nach Refresh updaten
UPDATE materialized_view_meta
SET last_refresh = NOW(),
    data_until = CURRENT_DATE
WHERE view_name = 'kpi_umsatz_monat';

-- Dashboard zeigt: "Datenstand: 2025-03-03 03:00"
SELECT data_until FROM materialized_view_meta WHERE view_name = 'kpi_umsatz_monat';

Vorteil: Reduziert Rückfragen “Warum sehe ich den heutigen Umsatz noch nicht?” → User wissen, dass Refresh nightly läuft.

Wann Materialized Views NICHT nutzen?

❌ Echtzeit-Daten: Wenn Sie sekündliche Updates brauchen (z.B. Live-Börsenkurse) ❌ Kleine Tabellen: Unter 10.000 Zeilen lohnt sich der Overhead nicht ❌ Ad-hoc-Analysen: Wenn jede Abfrage andere Gruppierungen braucht ❌ Write-Heavy-Workloads: Ständige Refreshes blockieren Resources

✅ Perfect für:

Dashboards mit täglichem/stündlichem Update
Aggregationen über Millionen Zeilen
Reporting-Abfragen (Monats-/Quartalsberichte)
Historische Analysen (YoY-Vergleiche)

Partitioning: Zeitreihen richtig strukturieren

Problem: 50M Zeilen in einer Tabelle

Umsatz-Tabelle ohne Partitionierung:

CREATE TABLE umsatz (
    id BIGSERIAL PRIMARY KEY,
    kunde_id INTEGER NOT NULL,
    umsatz NUMERIC(12,2) NOT NULL,
    datum DATE NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Nach 3 Jahren: 50M Zeilen
-- Query: WHERE datum >= '2024-01-01' → Sequential Scan (12 Sekunden)
-- INSERT: Langsam wegen riesiger Indexes

Lösung: Range Partitioning (monatlich)

-- Parent-Tabelle
CREATE TABLE umsatz (
    id BIGSERIAL,
    kunde_id INTEGER NOT NULL,
    produkt_id INTEGER NOT NULL,
    umsatz NUMERIC(12,2) NOT NULL,
    datum DATE NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    created_by INTEGER
) PARTITION BY RANGE (datum);

-- Partitionen pro Monat (automatisch via Cron oder pg_partman)
CREATE TABLE umsatz_2025_01 PARTITION OF umsatz
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

CREATE TABLE umsatz_2025_02 PARTITION OF umsatz
    FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

CREATE TABLE umsatz_2025_03 PARTITION OF umsatz
    FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');

-- Indexes nur auf aktuellen Partitionen
CREATE INDEX idx_umsatz_2025_03_kunde ON umsatz_2025_03(kunde_id);
CREATE INDEX idx_umsatz_2025_03_datum ON umsatz_2025_03(datum);

-- Alte Partitionen: Nur Datum-Index (Read-Only-Workload)
CREATE INDEX idx_umsatz_2024_01_datum ON umsatz_2024_01(datum);

Performance-Gewinn durch Partitioning

Vor Partitioning:

Query WHERE datum >= '2025-03-01': Sequential Scan über 50M Zeilen → 12.000ms
INSERT: 45ms (Indexes über 50M Zeilen updaten)

Nach Partitioning:

Query WHERE datum >= '2025-03-01': Index Scan nur auf Partition 2025_03 (1,5M Zeilen) → 180ms
INSERT in aktuelle Partition: 18ms (kleinere Indexes)
Speedup: 66x schneller für Queries, 2,5x für Inserts

Partitioning-Guardrails: Pruning absichern

Pruning greift nur mit plan-stable Constraints

-- ✅ Gut: Pruning funktioniert
SELECT * FROM umsatz
WHERE datum >= CURRENT_DATE - INTERVAL '1 month';

-- ❌ Schlecht: VOLATILE function → kein Pruning
CREATE FUNCTION get_last_month() RETURNS DATE AS $$
    SELECT CURRENT_DATE - INTERVAL '1 month';
$$ LANGUAGE SQL VOLATILE;  -- VOLATILE verhindert Pruning!

SELECT * FROM umsatz WHERE datum >= get_last_month();

-- ✅ Fix: STABLE statt VOLATILE
CREATE OR REPLACE FUNCTION get_last_month() RETURNS DATE AS $$
    SELECT CURRENT_DATE - INTERVAL '1 month';
$$ LANGUAGE SQL STABLE;  -- STABLE erlaubt Pruning

BRIN für sehr große Partitionen (PostgreSQL 15+)

-- Für Partitionen mit 10M+ Zeilen + Zeit-Korrelation
CREATE INDEX idx_umsatz_2025_03_datum_brin
    ON umsatz_2025_03 USING BRIN(datum minmax_multi_ops);

-- Trade-off: B-Tree 500MB vs. BRIN 5MB
-- Performance: B-Tree 15ms, BRIN 25ms
-- Wähle BRIN wenn: Disk-Space kritisch & 25ms akzeptabel

Automatische Partition-Erstellung mit pg_partman

-- Extension installieren
CREATE EXTENSION pg_partman;

-- Automatische monatliche Partitionen
SELECT partman.create_parent(
    p_parent_table => 'public.umsatz',
    p_control => 'datum',
    p_type => 'native',
    p_interval => '1 month',
    p_premake => 3  -- 3 Monate im Voraus erstellen
);

-- Cron-Job: Alte Partitionen droppen (nach 36 Monaten)
SELECT partman.run_maintenance('public.umsatz', p_retention => '36 months');

Wann Partitioning sinnvoll ist

✅ Zeitreihen-Daten: Logs, Sensordaten, Finanztransaktionen ✅ Große Tabellen: Über 10M Zeilen, besonders wenn ältere Daten selten abgefragt werden ✅ Archivierungs-Anforderungen: Alte Partitionen können einfach detached/dropped werden ✅ Write-Heavy-Workloads: INSERTs auf aktuelle Partition, alte Partitionen read-only

❌ Kleine Tabellen: Unter 1M Zeilen → Overhead lohnt sich nicht ❌ Gleiche Zugriffsmuster über alle Daten: Wenn Sie ständig über 5 Jahre aggregieren

Index-Strategien: Mehr als nur B-Tree

B-Tree Index (Standard)

Gut für:

Gleichheitsabfragen: WHERE kunde_id = 123
Range-Queries: WHERE datum BETWEEN '2025-01-01' AND '2025-12-31'
ORDER BY: ORDER BY created_at DESC

-- Standard B-Tree Index
CREATE INDEX idx_kunden_name ON kunden(name);
CREATE INDEX idx_umsatz_datum ON umsatz(datum);

-- Composite Index (Reihenfolge wichtig!)
CREATE INDEX idx_umsatz_kunde_datum ON umsatz(kunde_id, datum);
-- Nutzt Index: WHERE kunde_id = 123 AND datum > '2025-01-01'
-- Nutzt Index NICHT: WHERE datum > '2025-01-01' (kunde_id fehlt)

Composite-Index-Regel: Gleichheit vorne, Range hinten

Faustregel: Equality-Spalten zuerst, dann Range-Spalten.

-- ✅ Richtige Reihenfolge
CREATE INDEX idx_umsatz_kunde_datum ON umsatz(kunde_id, datum);

-- Query nutzt Index optimal
SELECT * FROM umsatz
WHERE kunde_id = 123 AND datum >= '2025-01-01' AND datum < '2025-02-01';

-- ❌ Falsche Reihenfolge
CREATE INDEX idx_umsatz_datum_kunde ON umsatz(datum, kunde_id);

-- Query nutzt Index NICHT optimal für obiges WHERE
-- Grund: datum hat Range-Condition → Index-Scan bricht nach erstem Match ab

-- Warum?
-- B-Tree-Struktur: (kunde_id=123, datum=2025-01-15) ist direkt neben (kunde_id=123, datum=2025-01-16)
-- Aber: (datum=2025-01-15, kunde_id=123) ist NICHT neben (datum=2025-01-15, kunde_id=124)

Praxis-Beispiel:

-- Business-Use-Case: Bestellungen eines Kunden im letzten Monat
-- ✅ Index: (kunde_id, datum) → perfekt
-- ❌ Index: (datum, kunde_id) → Sequential Scan auf filtered rows

GIN Index (Generalized Inverted Index)

Perfect für:

Volltext-Suche
JSONB-Queries
Array-Operationen
Tag-Systeme

-- Volltext-Suche
CREATE INDEX idx_produkte_beschreibung_fts
    ON produkte USING GIN(to_tsvector('german', beschreibung));

-- Query
SELECT * FROM produkte
WHERE to_tsvector('german', beschreibung) @@ to_tsquery('german', 'laptop & ssd');

-- JSONB
CREATE INDEX idx_metadaten_gin ON events USING GIN(metadaten jsonb_path_ops);

-- Query
SELECT * FROM events WHERE metadaten @> '{"user_id": 123}';

-- Array
CREATE INDEX idx_tags_gin ON artikel USING GIN(tags);

-- Query
SELECT * FROM artikel WHERE tags && ARRAY['postgresql', 'performance'];

Performance: Volltext-Suche über 5M Produkte: 12ms statt 8.000ms (Sequential Scan)

JSONB-Index: Welcher Operator-Class?

Entscheidungsmatrix:

Query-Pattern	Operator	Index-Type	Begründung
`@>` (containment)	`jsonb @> '{"key":"val"}'`	`jsonb_path_ops`	Kleinster Index (50% kleiner), schnellster Lookup
`?` (key exists)	`jsonb ? 'key'`	`jsonb_ops`	Nur `jsonb_ops` unterstützt `?`
`?	` (any key)	`jsonb ?	ARRAY[‘k1’,‘k2’]`
`->`, `->>` (access)	`jsonb->>'key' = 'val'`	Expression Index	Besser als GIN für simple Zugriffe

Code-Beispiele:

-- ✅ Containment: jsonb_path_ops (kleinster Index)
CREATE INDEX idx_events_meta_path ON events USING GIN(metadaten jsonb_path_ops);
SELECT * FROM events WHERE metadaten @> '{"user_id": 123}';

-- ✅ Key-Exists: jsonb_ops (einzige Option)
CREATE INDEX idx_events_meta_ops ON events USING GIN(metadaten jsonb_ops);
SELECT * FROM events WHERE metadaten ? 'error_code';

-- ✅ Nested-Access: Expression Index (besser als GIN für simple Zugriffe)
CREATE INDEX idx_events_user_id ON events((metadaten->>'user_id'));
SELECT * FROM events WHERE metadaten->>'user_id' = '123';

-- Trade-off:
-- jsonb_path_ops: 120 MB Index, 8ms Lookup
-- jsonb_ops: 240 MB Index, 12ms Lookup, aber mehr Query-Pattern
-- Expression Index: 80 MB, 5ms für exakt DIESEN Zugriff

BRIN Index (Block Range Index)

Perfect für:

Zeitreihen mit natürlicher Sortierung
Sehr große Tabellen (100M+ Zeilen)
Korrelation zwischen physischer Reihenfolge und Abfrage-Pattern

-- BRIN Index für Zeitreihen (minimal Space, gut für append-only)
CREATE INDEX idx_logs_created_at_brin ON logs USING BRIN(created_at);

-- Nur 1 MB Index-Größe statt 500 MB bei B-Tree
-- Performance: 90% so schnell wie B-Tree, aber 500x kleiner

Trade-off:

✅ Minimal Disk Space (1-5% von B-Tree)
✅ Schnelle INSERTs (wenig Index-Overhead)
⚠️ Nur gut bei physischer Sortierung (sonst langsamer als Sequential Scan)

Partial Index (Filtered Index)

Für spezifische Queries:

-- Nur aktive Mitarbeiter indexieren
CREATE INDEX idx_mitarbeiter_aktiv
    ON mitarbeiter(abteilung_id)
    WHERE status = 'aktiv';

-- Index-Größe: 80% kleiner (nur 20% der Mitarbeiter sind aktiv)
-- Query-Performance: Gleich schnell wie Full-Index

-- Nur ungültige Führerscheine (für Dashboard "Ablaufende Führerscheine")
CREATE INDEX idx_fuehrerschein_abgelaufen
    ON fuehrerscheine(mitarbeiter_id, gueltig_bis)
    WHERE gueltig_bis < CURRENT_DATE + INTERVAL '30 days';

Vorteil: Kleinere Indexes → schnellere Writes, gleiche Read-Performance für relevante Queries.

Partial Index: Selektivität prüfen

Nur sinnvoll bei hoher Selektivität (unter 20% der Zeilen).

-- Prüfen: Wie viele Zeilen betrifft der Filter?
SELECT
    COUNT(*) FILTER (WHERE status = 'aktiv') AS count_aktiv,
    COUNT(*) AS count_gesamt,
    ROUND(
        COUNT(*) FILTER (WHERE status = 'aktiv') * 100.0 / COUNT(*),
        2
    ) AS prozent_aktiv
FROM mitarbeiter;

-- Ergebnis: 2.400 von 12.000 = 20%
-- ✅ Partial Index lohnt sich (20% Threshold)

-- Ergebnis: 9.600 von 12.000 = 80%
-- ❌ Partial Index lohnt sich NICHT (zu wenig Filterung)
-- → Besser Full-Index oder gar kein Index (Sequential Scan schneller)

Trade-off-Tabelle:

Selektivität	Index-Typ	Begründung
unter 5%	Partial Index	Extrem platzsparend, perfekt für seltene Fälle
5-20%	Partial Index	Gut, reduziert Index-Größe signifikant
20-50%	Full Index	Partial spart zu wenig Platz, Full ist einfacher
mehr als 50%	Kein Index	Sequential Scan liest eh große Teile → Index-Overhead sinnlos

Expression Index (Functional Index)

Für berechnete Werte:

-- Index auf LOWER(email) für Case-Insensitive Suche
CREATE INDEX idx_benutzer_email_lower ON benutzer(LOWER(email));

-- Query nutzt Index
SELECT * FROM benutzer WHERE LOWER(email) = 'max@example.com';

-- Index auf Datum-Extraktion
CREATE INDEX idx_bestellungen_jahr_monat
    ON bestellungen(EXTRACT(YEAR FROM datum), EXTRACT(MONTH FROM datum));

Query-Tuning: EXPLAIN ANALYZE ist Pflicht

EXPLAIN ANALYZE: Alle wichtigen Flags nutzen

Minimum für Production-Debugging:

EXPLAIN (
    ANALYZE,        -- Echte Ausführungszeiten messen
    BUFFERS,        -- Shared Buffer Hits/Misses sichtbar
    VERBOSE,        -- Output-Spalten explizit zeigen
    TIMING,         -- Per-Node-Timing (Default: ON)
    WAL             -- PostgreSQL 15+: WAL-Volumen messen
)
SELECT m.name, COUNT(f.id) as anzahl_fuehrerscheine
FROM mitarbeiter m
LEFT JOIN fuehrerscheine f ON m.id = f.mitarbeiter_id
WHERE m.abteilung_id = 5
GROUP BY m.id, m.name;

Was die Flags bringen:

ANALYZE: Führt Query tatsächlich aus (Vorsicht bei UPDATE/DELETE!)
BUFFERS: Zeigt shared hit=X read=Y → Cache-Effizienz
VERBOSE: Output-Columns pro Node → hilft bei “warum wird Spalte X geladen?”
TIMING: Per-Node-Zeit (kann bei vielen Nodes Overhead haben, dann TIMING OFF)
WAL: Zeigt WAL-Bytes für DML-Queries → wichtig für Replikations-Lag

Output-Beispiel:

GroupAggregate  (cost=1234.56..1345.67 rows=100 width=42) (actual time=12.345..15.678 rows=98 loops=1)
  Group Key: m.id, m.name
  Buffers: shared hit=234 read=12
  ->  Nested Loop Left Join  (cost=0.56..1234.00 rows=500 width=38) (actual time=0.234..8.901 rows=450 loops=1)
        Buffers: shared hit=234 read=12
        ->  Index Scan using idx_mitarbeiter_abteilung on mitarbeiter m
            (cost=0.28..23.45 rows=100 width=34) (actual time=0.012..0.234 rows=98 loops=1)
              Index Cond: (abteilung_id = 5)
              Buffers: shared hit=12
        ->  Index Scan using idx_fuehrerscheine_mitarbeiter on fuehrerscheine f
            (cost=0.28..11.90 rows=5 width=8) (actual time=0.008..0.067 rows=5 loops=98)
              Index Cond: (mitarbeiter_id = m.id)
              Buffers: shared hit=222 read=12
Planning Time: 1.234 ms
Execution Time: 16.123 ms

Worauf achten:

actual time vs cost (estimate): Wenn weit auseinander → Statistics veraltet
rows estimate vs actual: Bei großer Abweichung → ANALYZE laufen lassen
Seq Scan bei großen Tabellen → Index fehlt
Buffers: read (Disk I/O) vs hit (RAM): Viele reads → Index fehlt oder Query zu breit

Typische Performance-Killer

1. N+1 Problem

// ❌ Schlecht: 1 Query für Abteilungen + 50 Queries für Mitarbeiter
$abteilungen = $pdo->query("SELECT * FROM abteilungen")->fetchAll();
foreach ($abteilungen as $abt) {
    $stmt = $pdo->prepare("SELECT COUNT(*) FROM mitarbeiter WHERE abteilung_id = ?");
    $stmt->execute([$abt['id']]);
    $abt['mitarbeiter_count'] = $stmt->fetchColumn();
}

// ✅ Gut: 1 Query mit JOIN
$stmt = $pdo->query("
    SELECT
        a.id,
        a.name,
        COUNT(m.id) as mitarbeiter_count
    FROM abteilungen a
    LEFT JOIN mitarbeiter m ON a.id = m.abteilung_id
    GROUP BY a.id, a.name
");

2. SELECT * statt spezifische Spalten

-- ❌ Schlecht: Lädt 20 Spalten, braucht aber nur 3
SELECT * FROM mitarbeiter WHERE abteilung_id = 5;

-- ✅ Gut: Nur benötigte Spalten
SELECT id, name, email FROM mitarbeiter WHERE abteilung_id = 5;

Impact: Bei 10.000 Zeilen: 8 MB Daten-Transfer statt 1,2 MB

3. Fehlende WHERE-Klausel

-- ❌ Schlecht: Lädt ALLE Zeilen, filtert in PHP
SELECT * FROM bestellungen ORDER BY datum DESC;
// PHP: array_filter($bestellungen, fn($b) => $b['status'] === 'offen')

-- ✅ Gut: Filter in SQL
SELECT * FROM bestellungen WHERE status = 'offen' ORDER BY datum DESC;

4. OR statt IN

-- ❌ Langsam: OR verhindert Index-Nutzung
SELECT * FROM kunden
WHERE abteilung_id = 1 OR abteilung_id = 5 OR abteilung_id = 8;

-- ✅ Schneller: IN nutzt Index
SELECT * FROM kunden WHERE abteilung_id IN (1, 5, 8);

work_mem: Der Multiplier-Effekt

Gefahr: work_mem wird PRO OPERATION allokiert, nicht pro Query!

-- work_mem = 64MB (postgresql.conf)

-- Query mit Hash Join + 2x Sort
EXPLAIN (ANALYZE, BUFFERS)
SELECT ...
FROM tabelle_a a
JOIN tabelle_b b ON ...
ORDER BY a.col1, b.col2;

-- Tatsächlicher RAM-Verbrauch:
-- 1x Hash Join: 64 MB
-- 2x Sort: 2 × 64 MB = 128 MB
-- GESAMT: 192 MB pro Query

-- Bei 50 parallelen Queries: 50 × 192 MB = 9,6 GB!

Lösung: Konservative Defaults, per-Query-Override für Big Queries.

-- postgresql.conf: Konservativ
work_mem = 16MB  -- Default für alle Sessions

-- Für Reports/ETL: Session-Level erhöhen
BEGIN;
SET LOCAL work_mem = '256MB';
SELECT ... -- Komplexe Aggregation mit vielen Sorts
COMMIT;
-- Nach COMMIT: work_mem wieder 16MB

EXPLAIN-Output lesen:

Sort  (cost=... rows=... width=...) (actual time=...)
  Sort Method: external merge  Disk: 12345kB
  -- ❌ "Disk" → work_mem zu klein, Temp-Files genutzt (langsam!)

Sort Method: quicksort  Memory: 8192kB
  -- ✅ "Memory" → work_mem ausreichend (schnell)

Faustregel: work_mem × max_connections × 4 sollte kleiner als RAM sein.

Connection Pooling: PgBouncer ist Pflicht

Problem: Connection Overhead

Ohne Pooling:

Jeder PHP-Request öffnet neue DB-Connection (10-20ms Overhead)
Bei 1.000 Requests/min: 1.000 Connections → PostgreSQL kollabiert bei ~500

Mit PgBouncer:

PHP verbindet zu PgBouncer (1ms)
PgBouncer pooled 20-50 Connections zu PostgreSQL
10.000 Requests/min möglich

PgBouncer Setup

# /etc/pgbouncer/pgbouncer.ini
[databases]
myapp = host=localhost port=5432 dbname=production_db

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

# Pool-Modi
pool_mode = transaction  # Beste Performance für Web-Apps
max_client_conn = 1000   # Max Connections von PHP
default_pool_size = 25   # Connections zu PostgreSQL
reserve_pool_size = 5    # Reserve bei Spitzen

PHP-Code:

// Verbindung zu PgBouncer statt direkt zu PostgreSQL
$pdo = new PDO('pgsql:host=127.0.0.1;port=6432;dbname=myapp', 'user', 'pass');

Performance-Gewinn:

Connection Time: 15ms → 1ms
Max Concurrent Requests: 500 → 10.000+

PgBouncer: Transaction Mode Pitfalls

Warnung: pool_mode = transaction ist schnell, aber bricht Session-Features!

Was NICHT funktioniert im Transaction Mode:

-- ❌ TEMP TABLES
CREATE TEMP TABLE tmp_calc AS SELECT ...;
-- Fehler: Connection wechselt nach COMMIT → Temp Table weg

-- ❌ PREPARED STATEMENTS (persistent)
PREPARE stmt AS SELECT * FROM mitarbeiter WHERE id = $1;
EXECUTE stmt(123);
-- Fehler: Nach COMMIT neue Connection → Prepared Statement unbekannt

-- ❌ ADVISORY LOCKS über Transaction hinweg
SELECT pg_advisory_lock(123);
-- Lock wird nach COMMIT freigegeben (Connection zurück im Pool)

-- ❌ LISTEN/NOTIFY
LISTEN channel_name;
-- Fehler: Neue Connection kennt LISTEN nicht

Lösungen:

# pgbouncer.ini: Session Mode für Features, Transaction Mode für Performance

[databases]
myapp_fast = host=localhost dbname=myapp pool_mode=transaction
myapp_session = host=localhost dbname=myapp pool_mode=session

# In PHP:
# Normale Queries: PDO('pgsql:host=127.0.0.1;port=6432;dbname=myapp_fast')
# ETL mit TEMP: PDO('pgsql:host=127.0.0.1;port=6432;dbname=myapp_session')

Oder: Advisory Lock via pg_try_advisory_lock() + Release in selber Transaktion

BEGIN;
SELECT pg_try_advisory_lock(123);
-- Work...
SELECT pg_advisory_unlock(123);
COMMIT;

Überwachung: Idle-in-Transaction erkennen

-- Queries die in Transaktion hängen (Connection-Leak-Kandidaten)
SELECT
    pid,
    state,
    NOW() - state_change AS idle_time,
    LEFT(query, 60) AS last_query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND NOW() - state_change > INTERVAL '5 minutes';

-- Sollte LEER sein. Wenn nicht: Connection-Leak in App-Code!

Vacuum & Statistics: Maintenance is Critical

VACUUM: Dead Tuples aufräumen

PostgreSQL nutzt MVCC (Multi-Version Concurrency Control) → UPDATEs/DELETEs erzeugen “dead tuples”.

-- Manuelles VACUUM (sollte automatisch via autovacuum laufen)
VACUUM ANALYZE mitarbeiter;

-- VACUUM FULL (rebuilt table, requires EXCLUSIVE LOCK)
VACUUM FULL mitarbeiter;  -- Nur offline!

-- Bloat checken
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size,
    n_dead_tup
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

ANALYZE: Statistics aktualisieren

-- Statistics für Query Planner aktualisieren
ANALYZE mitarbeiter;

-- Automatisch nach großen Imports
COPY umsatz FROM '/data/import.csv';
ANALYZE umsatz;  -- Sonst nutzt Planner alte Row-Estimates

Symptom: Query plötzlich langsam nach großem Import → Statistics veraltet.

Autovacuum: Hot-Table-Tuning

Problem: Defaults passen nicht für High-Traffic-Tabellen.

-- Default (postgresql.conf global)
autovacuum_vacuum_scale_factor = 0.2    -- 20% Dead Tuples
autovacuum_analyze_scale_factor = 0.1   -- 10% Modified Rows

-- Bei 10M-Zeilen-Tabelle:
-- Autovacuum startet erst bei: 10M × 0.2 = 2M Dead Tuples!
-- → Bloat explodiert, Queries werden langsam

Lösung: Per-Table-Tuning für Hot Tables.

-- High-Traffic-Tabelle: Orders/Umsatz
ALTER TABLE umsatz SET (
    autovacuum_vacuum_scale_factor = 0.01,  -- 1% statt 20%
    autovacuum_analyze_scale_factor = 0.005, -- 0,5% statt 10%
    autovacuum_vacuum_cost_limit = 2000     -- Schnelleres Vacuum (Default: 200)
);

-- Jetzt: Autovacuum bei 10M × 0.01 = 100k Dead Tuples (nicht 2M!)

-- Prüfen ob wirksam:
SELECT
    schemaname,
    relname,
    n_live_tup,
    n_dead_tup,
    last_autovacuum,
    ROUND(n_dead_tup * 100.0 / NULLIF(n_live_tup, 0), 2) AS dead_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

-- Wenn dead_pct dauerhaft mehr als 5% → Tuning nicht ausreichend

Faustregel:

Normal Tables: Defaults ok
High-Traffic-Tables (mehr als 100 UPDATEs/s): scale_factor = 0.01
Append-Only-Tables (nur INSERTs): Autovacuum fast deaktivieren (scale_factor = 0.5)

Monitoring: Was du messen solltest

pg_stat_statements: Slow Query Log

-- Extension aktivieren
CREATE EXTENSION pg_stat_statements;

-- Top 10 langsamste Queries
SELECT
    queryid,
    LEFT(query, 60) AS short_query,
    calls,
    total_exec_time / 1000 AS total_sec,
    mean_exec_time AS avg_ms,
    stddev_exec_time AS stddev_ms
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

Action: Queries über 100ms avg → Index fehlt oder Query optimieren.

Connection Monitoring

-- Aktive Connections
SELECT count(*) FROM pg_stat_activity WHERE state = 'active';

-- Idle Connections (potential leak)
SELECT count(*) FROM pg_stat_activity WHERE state = 'idle';

-- Blocking Queries
SELECT
    blocked.pid AS blocked_pid,
    blocking.pid AS blocking_pid,
    blocked.query AS blocked_query,
    blocking.query AS blocking_query
FROM pg_stat_activity AS blocked
JOIN pg_stat_activity AS blocking
    ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE blocked.state = 'active';

Cache Hit Ratio (sollte über 95% sein)

SELECT
    sum(heap_blks_read) AS heap_read,
    sum(heap_blks_hit) AS heap_hit,
    round(
        sum(heap_blks_hit) / NULLIF(sum(heap_blks_hit) + sum(heap_blks_read), 0) * 100,
        2
    ) AS cache_hit_ratio
FROM pg_statio_user_tables;

Wenn unter 95%: shared_buffers erhöhen oder mehr RAM.

Die 3 kritischen Alerts

1. Cache Hit Ratio unter 95%

-- Alert wenn unter 95%
SELECT
    ROUND(
        sum(heap_blks_hit) / NULLIF(sum(heap_blks_hit) + sum(heap_blks_read), 0) * 100,
        2
    ) AS cache_hit_ratio
FROM pg_statio_user_tables;

-- Wenn unter 95%: shared_buffers erhöhen ODER Queries filtern besser

2. Autovacuum-Lag (Dead Tuples mehr als 5%)

-- Alert wenn Dead-Tuple-Ratio mehr als 5%
SELECT
    schemaname,
    relname,
    n_live_tup,
    n_dead_tup,
    ROUND(n_dead_tup * 100.0 / NULLIF(n_live_tup, 0), 2) AS dead_pct,
    last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
  AND (n_dead_tup * 100.0 / NULLIF(n_live_tup, 0)) > 5
ORDER BY dead_pct DESC;

-- Wenn dauerhaft mehr als 5%: Autovacuum-Tuning (siehe oben)

3. Temp Files Growth (Disk-Spill wegen work_mem)

-- Alert wenn Temp-File-Volumen steigt
SELECT
    datname,
    temp_files AS temp_file_count,
    pg_size_pretty(temp_bytes) AS temp_bytes
FROM pg_stat_database
WHERE temp_files > 0
ORDER BY temp_bytes DESC;

-- Wenn temp_bytes dauerhaft mehr als 1GB/h: work_mem zu klein
-- Check einzelne Queries:
SELECT
    queryid,
    LEFT(query, 60) AS short_query,
    calls,
    temp_blks_written,
    pg_size_pretty(temp_blks_written * 8192::bigint) AS temp_written
FROM pg_stat_statements
WHERE temp_blks_written > 0
ORDER BY temp_blks_written DESC
LIMIT 10;

Praxis-Empfehlung: Alle 3 Queries in Monitoring-Tool (Grafana/Prometheus) als Alerts konfigurieren.

Production Checklist: Must-Have-Optimierungen

PostgreSQL Config (postgresql.conf)

# RAM (für 16GB Server)
shared_buffers = 4GB           # 25% des RAM
effective_cache_size = 12GB    # 75% des RAM
work_mem = 64MB                # Pro Query-Operation
maintenance_work_mem = 1GB     # VACUUM, CREATE INDEX

# Connections
max_connections = 100          # Mit PgBouncer: niedrig halten

# Checkpoints
checkpoint_completion_target = 0.9
wal_buffers = 16MB
max_wal_size = 4GB              # Default: 1GB (zu klein für Bulk-Loads)
checkpoint_timeout = 15min      # Default: 5min (zu häufig)

# Query Planner
random_page_cost = 1.1         # Für SSD (Default: 4.0 für HDD)
effective_io_concurrency = 200 # Für SSD

# Logging
log_min_duration_statement = 100  # Log Queries über 100ms
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on

Backup-Strategie

# Tägliches Backup (Cron 02:00 Uhr)
pg_dump -Fc -Z9 production_db > /backup/db_$(date +%Y%m%d).dump

# Point-in-Time-Recovery via WAL-Archivierung
archive_mode = on
archive_command = 'cp %p /archive/%f'

Index-Maintenance-Queries

-- Ungenutzte Indexes finden (löschen, um Writes zu beschleunigen)
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

-- Fehlende Indexes (Foreign Keys ohne Index)
SELECT
    tc.table_name,
    kcu.column_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
    ON tc.constraint_name = kcu.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND NOT EXISTS (
      SELECT 1 FROM pg_indexes
      WHERE tablename = tc.table_name
        AND indexdef LIKE '%' || kcu.column_name || '%'
  );

Real-World Case Study: Controlling-System Optimierung

Ausgangslage

Kunde: Mittelständisches Unternehmen, 15.000 MA Problem: Dashboard lädt 30+ Sekunden, Monatsabschluss 19 Tage Datenbank: PostgreSQL 12, 12M Umsatz-Zeilen, 50M Buchungszeilen

Maßnahmen

1. Materialized Views für KPIs (Woche 1)

5 Core-KPIs als Materialized Views
Nightly Refresh um 03:00 Uhr
Ergebnis: Dashboard 30s → 1,2s

2. Partitioning für Zeitreihen (Woche 2-3)

umsatz, buchungen partitioned (monatlich)
pg_partman für automatische Partition-Erstellung
Ergebnis: Report-Queries 8s → 0,8s

3. Index-Optimierung (Woche 4)

12 fehlende Indexes auf Foreign Keys
3 GIN-Indexes für Volltext-Suche
8 ungenutzte Indexes gelöscht
Ergebnis: INSERT-Performance +25%

4. Query-Refactoring (Woche 5)

N+1-Queries eliminiert (PHP-Code)
SELECT * durch spezifische Spalten ersetzt
Ergebnis: API-Response-Time 300ms → 45ms

5. PgBouncer Setup (Woche 6)

Connection Pooling aktiviert
Ergebnis: Max Concurrent Users 500 → 5.000+

Endergebnis

Dashboard: 30s → 0,8s (37x schneller)
Monatsabschluss: 19 Tage → 2 Tage (90% Reduktion)
Server-Kosten: 3 Server → 1 Server (4.800€/Jahr gespart)
Mobile UX: “Langsam, unbrauchbar” → “Endlich schnell!” (App-Store-Reviews)

Fazit: Performance ist kein Zufall

Die 3 wichtigsten Learnings

1. EXPLAIN ANALYZE ist Pflicht, nicht optional

Jede Query über 100ms → EXPLAIN ANALYZE → optimieren
Slow Query Log aktivieren → kontinuierlich monitoren

2. Materialized Views für Aggregationen

Live-Aggregationen über 1M+ Zeilen → Materialized View
Refresh-Strategie: Nightly oder inkrementell

3. Partitioning für Zeitreihen

Tabellen über 10M Zeilen mit Zeit-Dimension → partitionieren
pg_partman für automatische Verwaltung

Checkliste für neue Projekte

✅ Indexes:

Foreign Keys indexiert?
Composite Indexes in richtiger Reihenfolge?
GIN für JSONB/Arrays/Fulltext?
BRIN für Zeitreihen?

✅ Queries:

Keine SELECT *?
Keine N+1-Probleme?
EXPLAIN ANALYZE für alle Core-Queries?
WHERE-Filter in SQL, nicht in PHP?

✅ Infrastructure:

PgBouncer aktiv?
shared_buffers richtig konfiguriert?
Slow Query Log aktiviert?
Backup-Strategie vorhanden?

✅ Monitoring:

pg_stat_statements enabled?
Cache Hit Ratio über 95%?
Dead Tuples unter Kontrolle?
Unused Indexes regelmäßig prüfen?

Mein Angebot

Ich optimiere PostgreSQL-Systeme seit 2012 für Business-Apps:

Performance-Audit (2 Tage): Slow-Query-Analyse, Index-Strategie, Quick-Wins
Optimization-Sprint (2 Wochen): Materialized Views, Partitioning, Query-Refactoring
Production-Support (SLA): 24/7-Monitoring, Performance-Regressions beheben

Kostenlose Erstanalyse:

Senden Sie mir Ihre pg_stat_statements-Top-10 → Ich gebe Ihnen 3 konkrete Optimierungen (kostenlos, 30 Min Call).

📧 die@entwicklerin.net 🌐 www.entwicklerin.net 📍 Lüneburg & Remote

Über die Autorin: Carola Schulte ist Software-Architektin und optimiert seit 2012 PostgreSQL für Business-Apps. Schwerpunkte: Performance-Tuning, Query-Optimization, Schema-Design. 15+ Produktionssysteme, 50M+ Zeilen, 24/7-Betrieb.

Hat Ihnen dieser Artikel geholfen? Teilen Sie ihn mit Kollegen, die mit PostgreSQL-Performance kämpfen.

Weitere Artikel in dieser Serie:

PHP ohne Framework: Warum unsere Apps schneller sind (06.01.2025)
Controlling-Systeme entwickeln: Von Excel zu Echtzeit-Dashboard (20.01.2025)
Security-by-Design: DSGVO-konforme PHP-Apps (erscheint 17.03.2025)

Über Carola Schulte

Software-Architektin mit 25+ Jahren Erfahrung. Spezialisiert auf robuste Business-Apps mit PHP/PostgreSQL, Security-by-Design und DSGVO-konforme Systeme. 1,8M+ Lines of Code in Produktion.

die@entwicklerin.net entwicklerin.net

Projekt im Kopf?

Lassen Sie uns besprechen, wie ich Ihre Anforderungen umsetzen kann – kostenlos und unverbindlich.

Kostenloses Erstgespräch

Impressum Datenschutz

Teilen

Inhaltsverzeichnis