Zum Inhalt springen
·21 Min. Lesezeit

PostgreSQL-Abfrageoptimierung: Von langsam zu Sub-Millisekunde

Die PostgreSQL-Optimierungstechniken, die ich auf Produktionsdatenbanken verwende. EXPLAIN ANALYZE, Index-Strategien, Query-Rewrites und die genauen Änderungen, die Abfragezeiten von Sekunden auf Mikrosekunden reduzierten.

postgresqldatabaseperformancebackend
Teilen:X / TwitterLinkedIn

Letzten Monat bekam ich um 2 Uhr morgens eine Slack-Nachricht: „Das Dashboard hat ein Timeout." Ich habe mich per SSH auf die Produktionsmaschine eingewählt, pg_stat_activity geöffnet und eine einzelne Abfrage gefunden, die 14 Millionen Zeilen scannte, um 12 Ergebnisse zu liefern. Der Fix war ein Partial Index, der 30 Sekunden zum Erstellen brauchte. Die Abfrage ging von 4,2 Sekunden auf 0,3 Millisekunden.

Das ist das Ding mit PostgreSQL-Performance. Die Probleme sind fast nie exotisch. Es sind übersehene Indizes, veraltete Statistiken, Abfragen, die Sinn ergaben, als die Tabelle 10.000 Zeilen hatte, jetzt aber 10 Millionen hat. Die Fixes sind normalerweise einfach — sobald du weißt, wo du suchen musst.

Dieser Beitrag ist alles, was ich über PostgreSQL-Abfrageoptimierung beim Betrieb von Produktionsdatenbanken gelernt habe. Keine Theorie ohne Praxis. Jede Technik hier hat mir echte Zeit in echten Systemen gespart.

Die Debugging-Denkweise: Nicht raten, messen#

Der größte Fehler, den ich bei Entwicklern mit langsamen Abfragen sehe, ist Raten. „Vielleicht brauchen wir einen Index auf dieser Spalte." „Vielleicht ist der JOIN langsam." „Vielleicht sollten wir mehr RAM hinzufügen."

Hör auf zu raten. PostgreSQL hat einen der besten Abfrageanalyzer aller Datenbanken. Nutze ihn.

EXPLAIN — Der Bauplan#

Einfaches EXPLAIN zeigt dir, was PostgreSQL plant zu tun, ohne die Abfrage tatsächlich auszuführen:

sql
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;
                                    QUERY PLAN
---------------------------------------------------------------------------
 Index Scan using idx_orders_customer_id on orders  (cost=0.43..8.45 rows=1 width=128)
   Index Cond: (customer_id = 42)

Das zeigt dir den Plan — einen Index Scan — und die geschätzten Kosten. Aber es ist eine Vorhersage. PostgreSQL könnte bei der Zeilenanzahl komplett falsch liegen. Ich habe Schätzungen von „1 Zeile" gesehen, wenn das tatsächliche Ergebnis 50.000 Zeilen war. Diese Art von Fehleinschätzung kaskadiert zu furchtbaren Plan-Entscheidungen.

Verwende einfaches EXPLAIN, wenn du einen schnellen Blick auf den Plan werfen willst, ohne die Abfrage tatsächlich auszuführen. Das ist wichtig, wenn du ein DELETE analysierst, das Daten verändern würde, oder eine Abfrage, die 30 Minuten zum Ausführen braucht.

EXPLAIN ANALYZE — Die Wahrheit#

sql
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;
                                    QUERY PLAN
---------------------------------------------------------------------------
 Index Scan using idx_orders_customer_id on orders  (cost=0.43..8.45 rows=1 width=128)
                                                    (actual time=0.021..0.023 rows=1 loops=1)
   Index Cond: (customer_id = 42)
 Planning Time: 0.085 ms
 Execution Time: 0.041 ms

Jetzt siehst du die tatsächliche Ausführungszeit und die tatsächliche Zeilenanzahl. Hier passiert das echte Debugging. Vergleiche rows=1 (geschätzt) mit rows=1 (tatsächlich) — sie stimmen überein, also hat der Planner eine gute Entscheidung getroffen. Wenn sie nicht übereinstimmen, ist das dein erster Hinweis.

Warnung: EXPLAIN ANALYZE führt die Abfrage tatsächlich aus. Wenn du ein UPDATE oder DELETE analysierst, wickle es in eine Transaktion:

sql
BEGIN;
EXPLAIN ANALYZE DELETE FROM sessions WHERE expires_at < now();
ROLLBACK;

EXPLAIN (ANALYZE, BUFFERS) — Das vollständige Bild#

Das ist es, was ich tatsächlich zu 90% der Zeit verwende:

sql
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at > '2026-01-01'
  AND o.status = 'completed';
                                    QUERY PLAN
---------------------------------------------------------------------------
 Hash Join  (cost=425.00..1893.25 rows=2150 width=48)
            (actual time=2.341..15.892 rows=2347 loops=1)
   Hash Cond: (o.customer_id = c.id)
   Buffers: shared hit=312 read=45
   ->  Bitmap Heap Scan on orders o  (cost=12.50..1450.75 rows=2150 width=32)
                                     (actual time=0.512..12.105 rows=2347 loops=1)
         Recheck Cond: (created_at > '2026-01-01'::date)
         Filter: (status = 'completed')
         Rows Removed by Filter: 893
         Heap Blocks: exact=189
         Buffers: shared hit=195 read=45
         ->  Bitmap Index Scan on idx_orders_created_at  (cost=0.00..11.96 rows=3043 width=0)
                                                         (actual time=0.287..0.287 rows=3240 loops=1)
               Index Cond: (created_at > '2026-01-01'::date)
               Buffers: shared hit=12
   ->  Hash  (cost=287.50..287.50 rows=10000 width=24)
             (actual time=1.753..1.753 rows=10000 loops=1)
         Buffers: shared hit=117
         ->  Seq Scan on customers c  (cost=0.00..287.50 rows=10000 width=24)
                                      (actual time=0.008..0.892 rows=10000 loops=1)
               Buffers: shared hit=117
 Planning Time: 0.215 ms
 Execution Time: 16.147 ms

Die BUFFERS-Ausgabe ist kritisch. shared hit=312 bedeutet, 312 Seiten kamen aus dem Buffer Cache (RAM). read=45 bedeutet, 45 Seiten mussten von der Festplatte gelesen werden. Wenn du viel read relativ zu hit siehst, könnten deine shared_buffers zu klein sein, oder die Tabelle ist zu groß, um im Cache zu bleiben.

Ich verwende auch FORMAT JSON, wenn ich den Plan in explain.dalibo.com zur Visualisierung einfügen muss. Die Baumansicht macht komplexe Pläne viel lesbarer:

sql
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT ...;

EXPLAIN-Optionen, die ich tatsächlich verwende#

Hier ist die vollständige Diagnose-Query, die ich zuerst bei jeder Untersuchung langsamer Abfragen ausführe:

sql
EXPLAIN (ANALYZE, BUFFERS, TIMING, VERBOSE)
SELECT ...;
  • TIMING — Timing pro Knoten (standardmäßig mit ANALYZE aktiv, aber ich bin explizit)
  • VERBOSE — Zeigt Ausgabe-Spaltenlisten und schema-qualifizierte Tabellennamen

Abfragepläne wie ein Detektiv lesen#

Jeder Abfrageplan ist ein Baum. PostgreSQL liest ihn von unten nach oben: die am tiefsten eingerückten Knoten werden zuerst ausgeführt und liefern Ergebnisse nach oben. Das Verständnis der Scan-Typen ist die Grundlage für alles andere.

Seq Scan — Der volle Tabellenscan#

Seq Scan on orders  (cost=0.00..25000.00 rows=1000000 width=128)

Sequential Scan liest jede einzelne Zeile in der Tabelle. Bei einer Tabelle mit einer Million Zeilen ist das jede Seite auf der Festplatte.

Aber hier ist die Nuance: Seq Scan ist nicht immer schlecht. Wenn du 30% oder mehr der Tabelle auswählst, ist ein Sequential Scan tatsächlich schneller als ein Index Scan, weil sequenzielle I/O viel schneller ist als Random I/O. PostgreSQL weiß das. Wenn es einen Seq Scan wählt, obwohl du einen Index hast, prüfe, wie selektiv deine WHERE-Klausel tatsächlich ist.

Wann es ein Problem ist: wenn du einen winzigen Bruchteil der Zeilen aus einer großen Tabelle auswählst und es keinen Index gibt.

Index Scan — Der gezielte Lookup#

Index Scan using idx_orders_customer_id on orders  (cost=0.43..8.45 rows=5 width=128)
  Index Cond: (customer_id = 42)

Geht zum B-Tree-Index, findet die passenden Einträge und ruft dann die tatsächlichen Zeilen aus dem Table Heap ab. Jeder Zeilenabruf ist eine Random-I/O-Operation auf den Heap. Das ist großartig für hochselektive Abfragen, aber jeder Heap-Fetch hat seinen Preis.

Index Only Scan — Der Heilige Gral#

Index Only Scan using idx_orders_customer_total on orders  (cost=0.43..4.15 rows=5 width=12)
  Index Cond: (customer_id = 42)
  Heap Fetches: 0

Das ist der schnellste Scan-Typ. PostgreSQL bekommt alles, was es braucht, allein aus dem Index, ohne den Table Heap zu berühren. Du siehst das, wenn alle Spalten, die du mit SELECT und WHERE verwendest, im Index enthalten sind.

Der Haken: Heap Fetches: 0 bedeutet, die Visibility Map ist aktuell. Wenn deine Tabelle viele tote Tupel hat (kürzlich nicht vacuumt), muss PostgreSQL trotzdem den Heap prüfen, um die Sichtbarkeit der Zeilen zu verifizieren. Das ist ein Grund, warum Autovacuum für Performance wichtig ist, nicht nur für Speicherplatz.

Bitmap Scan — Der Mittelweg#

Bitmap Heap Scan on orders  (cost=45.00..1250.00 rows=5000 width=128)
  Recheck Cond: (created_at > '2026-01-01')
  ->  Bitmap Index Scan on idx_orders_created_at  (cost=0.00..43.75 rows=5000 width=0)
        Index Cond: (created_at > '2026-01-01')

Bitmap Scans sind PostgreSQLs Antwort auf das Problem „zu viele Zeilen für einen Index Scan, zu wenige für einen Seq Scan". Es baut eine Bitmap auf, welche Seiten übereinstimmende Zeilen enthalten, sortiert sie nach physischer Position und ruft sie dann der Reihe nach ab. Das wandelt Random I/O in sequenzielle I/O um.

Du wirst oft Bitmap Scans sehen, wenn zwei oder mehr Indizes kombiniert werden:

Bitmap Heap Scan on orders
  Recheck Cond: ((status = 'completed') AND (created_at > '2026-01-01'))
  ->  BitmapAnd
        ->  Bitmap Index Scan on idx_orders_status
              Index Cond: (status = 'completed')
        ->  Bitmap Index Scan on idx_orders_created_at
              Index Cond: (created_at > '2026-01-01')

Dieses BitmapAnd kombiniert zwei separate Indizes. PostgreSQL sagt damit „Ich habe keinen Composite Index, aber ich kann diese beiden Einzel-Spalten-Indizes kombinieren." Es funktioniert, aber ein richtiger Composite Index wäre schneller.

Schlechte Schätzungen erkennen#

Die Nummer eins, nach der ich in einem Abfrageplan suche, sind fehlerhafte Schätzungen:

Nested Loop  (cost=0.43..50.12 rows=1 width=128)
             (actual time=0.025..4521.337 rows=48753 loops=1)

Der Planner hat 1 Zeile geschätzt. Das tatsächliche Ergebnis waren 48.753 Zeilen. Das ist eine Fehleinschätzung um fünf Größenordnungen. Der Planner wählte einen Nested Loop, weil er dachte, er würde gegen 1 Zeile joinen. Mit 48.753 Zeilen wäre ein Hash Join um Größenordnungen schneller gewesen.

Häufige Ursachen für schlechte Schätzungen:

  • Veraltete Statistiken: Führe ANALYZE auf der Tabelle aus
  • Korrelierte Spalten: Der Planner geht davon aus, dass Spaltenwerte unabhängig sind. Wenn status = 'shipped' und created_at > '2026-01-01' korreliert sind (die neuesten Bestellungen sind versandt), unterschätzt der Planner die kombinierte Selektivität
  • Benutzerdefinierte Funktionen in WHERE: Der Planner verwendet eine Standard-Selektivitätsschätzung (normalerweise 0,5% für Gleichheit, 33% für Bereich), wenn er eine Funktion nicht analysieren kann
  • Parametrisierte Abfragen mit generischen Plänen: Nach 5 Ausführungen kann PostgreSQL auf einen generischen Plan umschalten, der den tatsächlichen Parameterwert nicht berücksichtigt

Wenn du schlechte Schätzungen siehst, ist der Fix normalerweise einer von: ANALYZE ausführen, erweiterte Statistiken erstellen, die Abfrage umschreiben oder ein CTE als Optimierungszaun verwenden.

sql
-- Erweiterte Statistiken für korrelierte Spalten erstellen
CREATE STATISTICS orders_status_date (dependencies)
ON status, created_at FROM orders;
ANALYZE orders;

Index-Strategie: Der richtige Index für den richtigen Job#

Indizes sind nicht kostenlos. Jeder Index verlangsamt Schreibvorgänge, verbraucht Festplattenspeicher und muss gewartet werden. Das Ziel ist nicht „alles indizieren" — es ist „genau das indizieren, was du brauchst."

B-tree — Das Standard-Arbeitspferd#

B-tree ist der Standard und deckt die überwiegende Mehrheit der Fälle ab. Er unterstützt Gleichheits- und Bereichsoperatoren (=, <, >, <=, >=, BETWEEN, IN, IS NULL).

sql
CREATE INDEX idx_orders_created_at ON orders (created_at);

Die Spaltenreihenfolge im B-tree-Index ist bei Composite Indexes enorm wichtig. Die linkste Spalte ist die primäre Sortierung, dann die nächste, und so weiter. Ein Index auf (a, b, c) kann effizient beantworten:

  • WHERE a = 1
  • WHERE a = 1 AND b = 2
  • WHERE a = 1 AND b = 2 AND c = 3
  • WHERE a = 1 AND b > 5
  • WHERE a = 1 ORDER BY b

Er kann nicht effizient beantworten:

  • WHERE b = 2 (überspringt die erste Spalte)
  • WHERE c = 3 (überspringt die ersten zwei Spalten)
  • WHERE a = 1 AND c = 3 (Lücke in der Mitte — a-Bedingung wird genutzt, c-Bedingung erfordert Filter)

Stell dir das wie ein Telefonbuch vor, sortiert nach Nachname, dann Vorname. Du kannst schnell alle „Müller"-Einträge finden oder speziell „Müller, Hans". Aber du kannst nicht schnell alle „Hans"-Einträge finden, ohne das gesamte Buch zu durchsuchen.

Die Regel: Setze Gleichheitsbedingungen zuerst, dann Bereichsbedingungen, dann Sortierspalten.

sql
-- Abfrage: WHERE status = 'active' AND created_at > '2026-01-01' ORDER BY total DESC
 
-- Gut: Gleichheit zuerst, dann Bereich, dann Sortierung
CREATE INDEX idx_orders_optimized ON orders (status, created_at, total);
 
-- Schlecht: Bereich vor Gleichheit
CREATE INDEX idx_orders_bad ON orders (created_at, status, total);

Covering Indexes mit INCLUDE#

PostgreSQL 11+ erlaubt dir, Nicht-Schlüssel-Spalten mit INCLUDE zu einem Index hinzuzufügen. Diese Spalten werden in den Blattseiten gespeichert, sind aber nicht Teil der B-Tree-Struktur. Sie ermöglichen Index-Only-Scans, ohne den Baum aufzublähen:

sql
-- Wir führen häufig aus:
-- SELECT id, total, status FROM orders WHERE customer_id = 42;
 
CREATE INDEX idx_orders_customer_covering
ON orders (customer_id) INCLUDE (total, status);

Jetzt kann PostgreSQL die gesamte Abfrage allein aus dem Index beantworten. Ohne INCLUDE müsste es total und status aus dem Heap holen. Der Unterschied bei einem kalten Cache kann dramatisch sein — ich habe Abfragen gesehen, die von 50ms auf 0,2ms gingen, nur durch das Hinzufügen von INCLUDE-Spalten.

Partial Indexes — Indiziere nur, was wichtig ist#

Das ist mein einzelnes Lieblings-PostgreSQL-Feature und das, das die meisten Entwickler nicht kennen.

sql
-- Nur 2% der Bestellungen sind 'pending', aber wir fragen sie ständig ab
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';

Dieser Index ist winzig im Vergleich zu einem vollen Index auf created_at. Er enthält nur Zeilen, bei denen status = 'pending' ist. Abfragen, die zur WHERE-Klausel passen, nutzen diesen kleinen, schnellen Index. Abfragen, die nicht passen, ignorieren ihn.

Echtes Beispiel aus der Produktion: Ich hatte eine sessions-Tabelle mit 50 Millionen Zeilen. Nur etwa 200.000 waren aktiv (nicht abgelaufen). Ein voller Index auf user_id war 1,2 GB. Ein Partial Index WHERE expires_at > now() war 8 MB. Die Abfrage ging von 12ms auf 0,1ms, weil der gesamte Index in den Cache passte.

sql
-- Ein weiteres häufiges Muster: Soft Deletes
CREATE INDEX idx_users_active_email ON users (email)
WHERE deleted_at IS NULL;
 
-- Unique Constraint nur auf aktive Datensätze
CREATE UNIQUE INDEX idx_users_unique_email ON users (email)
WHERE deleted_at IS NULL;

Das letzte ist unglaublich nützlich. Es ermöglicht dir, ein Unique-Email-Constraint auf aktive Benutzer zu haben, während dieselbe E-Mail in gelöschten Datensätzen erscheinen darf.

GIN Indexes — Volltextsuche und JSONB#

GIN (Generalized Inverted Index) ist die Antwort, wenn du innerhalb von Werten suchen musst — Arrays, JSONB-Dokumente oder Volltext-Suchvektoren.

sql
-- JSONB-Containment-Abfragen
CREATE INDEX idx_products_metadata ON products USING GIN (metadata);
 
-- Jetzt ist das schnell:
SELECT * FROM products WHERE metadata @> '{"color": "red"}';
 
-- Volltextsuche
CREATE INDEX idx_articles_search ON articles USING GIN (to_tsvector('english', title || ' ' || body));

GIN-Indizes sind groß und langsam beim Erstellen, aber blitzschnell bei Abfragen. Sie lohnen sich für leselastige Workloads mit komplexen Containment-Abfragen.

Für JSONB gibt es auch jsonb_path_ops, das kleiner und schneller für @>-Containment-Abfragen ist, aber keine Existenz-Operatoren (?, ?|, ?&) unterstützt:

sql
CREATE INDEX idx_products_metadata_pathops
ON products USING GIN (metadata jsonb_path_ops);

GiST Indexes — Geometrische und Range-Typen#

GiST (Generalized Search Tree) behandelt überlappende Datentypen: geometrische Formen, Ranges, Volltextsuche (Alternative zu GIN, kleiner aber langsamer).

sql
-- IP-Range-Lookups
CREATE INDEX idx_ip_ranges ON ip_blocks USING GiST (ip_range);
 
SELECT * FROM ip_blocks WHERE ip_range @> '192.168.1.50'::inet;
 
-- Zeitliche Ranges (Terminüberschneidungen)
CREATE INDEX idx_bookings_period ON bookings USING GiST (
  tstzrange(start_time, end_time)
);
 
-- Überlappende Buchungen finden:
SELECT * FROM bookings
WHERE tstzrange(start_time, end_time) && tstzrange('2026-03-01 10:00', '2026-03-01 12:00');

Hash Indexes — Nische aber nützlich#

Hash-Indizes sind nur für Gleichheitsvergleiche nützlich. Seit PostgreSQL 10 sind sie WAL-geloggt und crash-sicher. Sie sind kleiner als B-tree für breite Spalten und etwas schneller für reine Gleichheit:

sql
-- Gut für reine Gleichheits-Lookups auf großen Text-Spalten
CREATE INDEX idx_sessions_token ON sessions USING HASH (session_token);
 
-- Das ist schnell:
SELECT * FROM sessions WHERE session_token = 'abc123def456...';
 
-- Das KANN den Hash-Index NICHT nutzen:
SELECT * FROM sessions WHERE session_token LIKE 'abc%';

In der Praxis verwende ich Hash-Indizes selten. B-tree handhabt Gleichheit genauso gut, und die Unfähigkeit, Range-Abfragen oder Sortierung zu machen, macht Hash-Indizes für die meisten realen Anwendungsfälle zu unflexibel.

Wann Indizes schaden#

Jeder Index hat Kosten:

  • Write Amplification: Jedes INSERT aktualisiert jeden Index der Tabelle. Eine Tabelle mit 8 Indizes bedeutet 8 zusätzliche Schreibvorgänge pro INSERT
  • Blockierte HOT-Updates: Heap-Only Tuple (HOT) Updates sind eine wichtige Optimierung, bei der PostgreSQL eine Zeile aktualisieren kann, ohne Indizes zu aktualisieren, aber nur wenn keine indizierte Spalte geändert wurde. Mehr Indizes = mehr Chancen, HOT-Updates zu blockieren
  • Vacuum-Overhead: Mehr Indizes bedeutet, Vacuum dauert länger
  • Planner-Overhead: Mehr Indizes bedeutet, der Planner hat mehr Optionen zu evaluieren

Ich überprüfe regelmäßig Indizes auf Produktionstabellen:

sql
-- Ungenutzte Indizes finden
SELECT
  schemaname, tablename, indexname,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelid NOT IN (
    SELECT conindid FROM pg_constraint
    WHERE contype IN ('p', 'u')  -- Primary Key und Unique Constraints ausschließen
  )
ORDER BY pg_relation_size(indexrelid) DESC;

Ich habe Multi-Gigabyte-Indizes gefunden, die während einer einmaligen Migration erstellt und nie wieder verwendet wurden. Das Löschen beschleunigte Schreibvorgänge spürbar.

sql
-- Doppelte Indizes finden (gleiche Spalten, gleiche Reihenfolge)
SELECT
  a.indexrelid::regclass AS index1,
  b.indexrelid::regclass AS index2,
  a.indrelid::regclass AS table_name
FROM pg_index a
JOIN pg_index b ON a.indrelid = b.indrelid
  AND a.indexrelid < b.indexrelid
  AND a.indkey = b.indkey;

Das N+1-Abfrageproblem#

Jeder ORM-Entwickler trifft irgendwann darauf. Es ist das häufigste Performance-Problem, das ich debugge.

Wie N+1 aussieht#

python
# Python / SQLAlchemy — die klassische Falle
orders = session.query(Order).filter_by(status='pending').all()
 
for order in orders:
    # Das löst eine separate Abfrage für JEDE Bestellung aus
    print(f"Customer: {order.customer.name}")

Das generiert:

sql
-- Abfrage 1: Alle ausstehenden Bestellungen holen
SELECT * FROM orders WHERE status = 'pending';
 
-- Abfrage 2: Kunde für Bestellung 1 holen
SELECT * FROM customers WHERE id = 101;
 
-- Abfrage 3: Kunde für Bestellung 2 holen
SELECT * FROM customers WHERE id = 102;
 
-- ...Abfrage 502: Kunde für Bestellung 500 holen
SELECT * FROM customers WHERE id = 600;

501 Abfragen statt 1 oder 2. Jede Abfrage ist einzeln schnell, vielleicht 0,5ms. Aber 501 davon summieren sich auf 250ms reine Datenbankzeit, plus Netzwerk-Roundtrip-Latenz für jede einzelne.

N+1 in Logs erkennen#

Der schnellste Weg, N+1-Abfragen zu entdecken, ist das vorübergehende Aktivieren des Statement-Logging:

sql
-- Alle Abfragen loggen, die mehr als 0ms dauern (also alle)
SET log_min_duration_statement = 0;
 
-- Besser für Produktion: nur langsame Abfragen loggen
ALTER SYSTEM SET log_min_duration_statement = 50;  -- 50ms Schwelle
SELECT pg_reload_conf();

Dann schau dir die Logs an. N+1 ist unverkennbar — du siehst Hunderte identischer Abfragen mit unterschiedlichen Parameterwerten in schneller Folge.

Ein gezielterer Ansatz für die Entwicklung:

sql
-- auto_explain für langsame Abfragen aktivieren
LOAD 'auto_explain';
SET auto_explain.log_min_duration = 100;  -- 100ms
SET auto_explain.log_nested_statements = ON;
SET auto_explain.log_analyze = ON;

Das loggt die vollständige EXPLAIN-ANALYZE-Ausgabe für jede Abfrage über 100ms, einschließlich Abfragen innerhalb von Funktionen.

Der Fix: Eager Loading oder JOINs#

ORM-Ansatz — dem ORM sagen, verwandte Daten vorab zu laden:

python
# SQLAlchemy — joinedload holt Kunden in derselben Abfrage
from sqlalchemy.orm import joinedload
 
orders = (
    session.query(Order)
    .options(joinedload(Order.customer))
    .filter_by(status='pending')
    .all()
)

Raw-SQL-Ansatz — einfach einen JOIN verwenden:

sql
SELECT o.*, c.name AS customer_name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending';

Eine Abfrage. Fertig.

JOINs vs mehrere Abfragen#

Hier gibt es eine berechtigte Debatte. Manchmal sind zwei Abfragen besser als ein JOIN:

sql
-- Ansatz 1: Einzelner JOIN (kann Duplikate erzeugen bei One-to-Many)
SELECT o.*, i.product_name, i.quantity
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.status = 'pending';
 
-- Ansatz 2: Zwei Abfragen (weniger Datentransfer wenn Bestellungen viele Spalten haben)
SELECT * FROM orders WHERE status = 'pending';
-- Anwendung sammelt order_ids = [1, 2, 3, ...]
SELECT * FROM order_items WHERE order_id = ANY($1);

Der JOIN-Ansatz dupliziert Bestellungsdaten für jeden Artikel. Wenn jede Bestellung 20 Artikel hat und die Bestellungszeile breit ist, ist das 20-facher Datentransfer. Der Zwei-Abfragen-Ansatz sendet jede Bestellung genau einmal.

Meine Regel: Verwende JOINs für Eins-zu-eins-Beziehungen, erwäge separate Abfragen für Eins-zu-viele, wenn die „Eins"-Seite breit ist. Aber benchmarke immer — der Netzwerk-Roundtrip einer zweiten Abfrage kostet oft mehr als die duplizierten Daten.

Häufige Abfrage-Rewrites#

Manche Abfragen sind nicht wegen fehlender Indizes langsam, sondern wegen ihrer Formulierung. PostgreSQLs Planner ist gut, aber kein Wundermittel.

Subquery vs JOIN vs CTE#

Diese drei Ansätze können sehr unterschiedliche Pläne erzeugen:

sql
-- Subquery in WHERE — oft okay, manchmal furchtbar
SELECT * FROM orders
WHERE customer_id IN (
  SELECT id FROM customers WHERE country = 'US'
);
 
-- JOIN — normalerweise die beste Wahl
SELECT o.* FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.country = 'US';
 
-- CTE — materialisiert in PG 11 und darunter, optimiert in PG 12+
WITH us_customers AS (
  SELECT id FROM customers WHERE country = 'US'
)
SELECT o.* FROM orders o
JOIN us_customers uc ON uc.id = o.customer_id;

In PostgreSQL 12+ werden CTEs normalerweise inlined (der Planner behandelt sie wie Subqueries), sodass die Performance identisch ist. Aber in PostgreSQL 11 und darunter sind CTEs Optimierungszäune — der Planner materialisiert sie und kann keine Prädikate durch sie hindurchschieben. Wenn du noch auf PG 11 bist, vermeide CTEs für performance-kritische Abfragen.

EXISTS vs IN vs JOIN#

Das kommt ständig vor:

sql
-- EXISTS — stoppt beim ersten Treffer, großartig zur Existenzprüfung
SELECT * FROM orders o
WHERE EXISTS (
  SELECT 1 FROM returns r WHERE r.order_id = o.id
);
 
-- IN mit Subquery — baut zuerst die vollständige Ergebnismenge auf
SELECT * FROM orders
WHERE id IN (SELECT order_id FROM returns);
 
-- JOIN — kann Duplikate erzeugen wenn returns mehrere Zeilen pro Bestellung hat
SELECT DISTINCT o.* FROM orders o
JOIN returns r ON r.order_id = o.id;

Bei großen Tabellen gewinnt EXISTS oft, weil es kurzschließt. Die IN-Version muss die gesamte Liste von Return-Order-IDs aufbauen, bevor sie filtert. Die JOIN-Version kann Duplikate erzeugen, was DISTINCT erfordert, das einen Sort- oder Hash-Schritt hinzufügt.

Mein Standard: Verwende EXISTS, wenn du die Existenz verwandter Zeilen prüfst. Es ist semantisch am klarsten und normalerweise am schnellsten.

Aber es gibt ein Gegenbeispiel. Wenn die Subquery-Ergebnismenge klein ist und du sie für mehrere Bedingungen brauchst:

sql
-- Wenn die Subquery wenige Zeilen zurückgibt, ist IN völlig in Ordnung
SELECT * FROM orders
WHERE customer_id IN (1, 2, 3, 4, 5);

SELECT * vermeiden#

Das ist nicht nur eine Code-Style-Vorliebe. Es hat echte Performance-Auswirkungen:

sql
-- Schlecht: holt alle 25 Spalten inklusive einer TEXT-Spalte mit Kilobytes an Daten
SELECT * FROM products WHERE category = 'electronics';
 
-- Gut: holt nur was du brauchst
SELECT id, name, price FROM products WHERE category = 'electronics';

Mit SELECT *:

  • Index-Only-Scans sind unmöglich (alle Spalten müssten im Index sein)
  • Mehr Daten werden über das Netzwerk übertragen
  • Mehr Speicher für Sortierung und Hashing
  • Wenn jemand später eine 10MB-BYTEA-Spalte hinzufügt, werden deine bestehenden Abfragen leise langsamer

Window Functions vs Subqueries#

Window Functions sind eines der mächtigsten Features von PostgreSQL und übertreffen fast immer korrelierte Subqueries:

sql
-- Langsam: korrelierte Subquery läuft einmal pro Zeile
SELECT
  o.*,
  (SELECT COUNT(*) FROM order_items i WHERE i.order_id = o.id) AS item_count,
  (SELECT SUM(amount) FROM payments p WHERE p.order_id = o.id) AS total_paid
FROM orders o
WHERE o.status = 'completed';
 
-- Schnell: Window Functions berechnen in einem einzigen Durchgang
SELECT
  o.*,
  COUNT(i.id) OVER (PARTITION BY o.id) AS item_count,
  SUM(p.amount) OVER (PARTITION BY o.id) AS total_paid
FROM orders o
LEFT JOIN order_items i ON i.order_id = o.id
LEFT JOIN payments p ON p.order_id = o.id
WHERE o.status = 'completed';

Ein weiteres häufiges Muster — die neueste Zeile pro Gruppe abrufen:

sql
-- Langsam: korrelierte Subquery
SELECT * FROM notifications n
WHERE n.created_at = (
  SELECT MAX(created_at) FROM notifications
  WHERE user_id = n.user_id
);
 
-- Schnell: DISTINCT ON (PostgreSQL-spezifisch, extrem nützlich)
SELECT DISTINCT ON (user_id) *
FROM notifications
ORDER BY user_id, created_at DESC;
 
-- Auch schnell: ROW_NUMBER Window Function
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM notifications
) sub
WHERE rn = 1;

DISTINCT ON ist mein Go-to für dieses Muster. Es ist prägnant, lesbar und PostgreSQL optimiert es gut mit dem richtigen Index:

sql
CREATE INDEX idx_notifications_user_latest
ON notifications (user_id, created_at DESC);

Pagination richtig machen#

OFFSET ist eine Falle für große Datensätze:

sql
-- Seite 1: schnell
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 0;
 
-- Seite 500: langsam — PostgreSQL muss 9.980 Zeilen scannen und verwerfen
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 9980;
 
-- Seite 5000: sehr langsam — scannt 99.980 Zeilen um 20 zurückzugeben
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 99980;

Der Fix ist Keyset Pagination (auch Cursor-basierte Pagination genannt):

sql
-- Erste Seite
SELECT * FROM products ORDER BY id LIMIT 20;
 
-- Nächste Seite: verwende die letzte ID von der vorherigen Seite
SELECT * FROM products WHERE id > 12345 ORDER BY id LIMIT 20;

Das ist immer schnell, weil es den Index nutzt, um direkt zur richtigen Position zu springen, egal auf welcher „Seite" du dich befindest. Der Kompromiss ist, dass du nicht zu einer beliebigen Seitennummer springen kannst, aber für Infinite Scroll oder „Nächste Seite"-UIs ist Keyset Pagination strikt überlegen.

Für komplexe Sortierreihenfolgen:

sql
-- Keyset Pagination mit mehreren Sortierspalten
SELECT * FROM products
WHERE (price, id) > (29.99, 12345)
ORDER BY price, id
LIMIT 20;

Das nutzt Zeilenwert-Vergleich, den PostgreSQL effizient mit einem Composite Index auf (price, id) handhabt.

Tabellenstatistiken und Vacuuming#

PostgreSQLs Abfrageplanner trifft Entscheidungen basierend auf Statistiken über deine Daten. Schlechte Statistiken führen zu schlechten Plänen. So einfach ist das.

ANALYZE: Statistiken aktualisieren#

sql
-- Eine einzelne Tabelle analysieren
ANALYZE orders;
 
-- Die gesamte Datenbank analysieren
ANALYZE;
 
-- Aktuelle Statistiken für eine Spalte anzeigen
SELECT
  tablename, attname, n_distinct, most_common_vals, correlation
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';

Der correlation-Wert ist besonders interessant. Er reicht von -1 bis 1 und misst, wie gut die physische Reihenfolge der Zeilen mit der logischen Reihenfolge der Spalte übereinstimmt. Eine Korrelation nahe 1 oder -1 bedeutet, die Daten sind physisch nach dieser Spalte sortiert, was Range-Scans sehr effizient macht (sequenzielle I/O). Eine Korrelation nahe 0 bedeutet Random I/O für Range-Abfragen.

sql
-- Statistikziel für Spalten mit schiefer Verteilung erhöhen
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;

Das Standard-Statistikziel ist 100, was bedeutet, PostgreSQL sampelt 300 * 100 = 30.000 Zeilen. Für Spalten mit vielen unterschiedlichen Werten oder schiefen Verteilungen gibt das Erhöhen auf 500 oder 1000 dem Planner bessere Daten auf Kosten geringfügig längerer ANALYZE-Zeiten.

Autovacuum-Tuning#

Autovacuum macht zwei Dinge: Platz von toten Tupeln (gelöschte oder aktualisierte Zeilen) zurückgewinnen und Statistiken aktualisieren. Bei stark frequentierten Tabellen sind die Standard-Autovacuum-Einstellungen oft zu konservativ.

sql
-- Autovacuum-Status prüfen
SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  last_vacuum,
  last_autovacuum,
  last_analyze,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

Wenn du Tabellen mit Millionen toter Tupel siehst und das letzte Vacuum Stunden her ist, hinkt dein Autovacuum hinterher.

Für Tabellen mit hoher Änderungsrate (wie Sessions, Job-Queues oder Metriken) setze ich tabellenspezifische Autovacuum-Einstellungen:

sql
-- Aggressiveres Autovacuum für schreibintensive Tabellen
ALTER TABLE sessions SET (
  autovacuum_vacuum_scale_factor = 0.01,     -- Vacuum bei 1% toten Tupeln (Standard 20%)
  autovacuum_analyze_scale_factor = 0.005,   -- Analyze bei 0,5% Änderungen (Standard 10%)
  autovacuum_vacuum_cost_delay = 2           -- Schnelleres Vacuum (Standard 2ms in neueren PG)
);

Table Bloat#

Wenn PostgreSQL eine Zeile aktualisiert, ändert es die Zeile nicht an Ort und Stelle — es erstellt eine neue Version und markiert die alte als tot. Vacuum beansprucht die toten Zeilen zurück, aber der Platz wird nur von dieser Tabelle wiederverwendet. Die Tabellendatei auf der Festplatte schrumpft nicht.

Im Laufe der Zeit kann eine stark aktualisierte Tabelle erhebliches Bloat haben — die Tabelle ist auf der Festplatte viel größer als die Live-Daten erfordern. Das bedeutet mehr Seiten zum Scannen, mehr I/O, mehr Cache-Druck.

sql
-- Tabellen-Bloat schätzen (vereinfachte Version)
SELECT
  current_database(),
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
  pg_size_pretty(
    pg_total_relation_size(schemaname || '.' || tablename) -
    pg_relation_size(schemaname || '.' || tablename)
  ) AS index_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC
LIMIT 20;

Für stark aufgeblähte Tabellen ist die nukleare Option VACUUM FULL — es schreibt die gesamte Tabelle neu. Aber es nimmt eine exklusive Sperre, sodass du es nicht auf einer laufenden Produktionstabelle ohne Downtime machen kannst. Der bessere Ansatz ist pg_repack, das dasselbe ohne Sperre macht:

bash
pg_repack --table orders --no-kill-backend -d mydb

Connection Pooling#

Hier ist etwas, das viele Entwickler überrascht: PostgreSQL-Verbindungen sind teuer. Jede Verbindung erzeugt einen neuen Prozess (keinen Thread), verbraucht etwa 5-10 MB Speicher und hat einen nicht-trivialen Fork-Overhead.

Das Standard-max_connections ist 100. Wenn du einen Anwendungsserver mit 20 Workern hast, die jeweils 5 Verbindungen öffnen, bist du bereits am Limit. Füge einen Background-Job-Prozessor hinzu, ein Monitoring-Tool und eine laufende Migration, und du hast ein Problem.

Warum du einen Connection Pooler brauchst#

Ohne Pooler brauchst du bei 500 gleichzeitigen Anfragen 500 PostgreSQL-Verbindungen. Das sind 5 GB Speicher allein für den Verbindungs-Overhead, und PostgreSQLs Performance verschlechtert sich signifikant jenseits einiger hundert Verbindungen aufgrund von Prozessmanagement-Overhead.

Mit PgBouncer vor PostgreSQL werden diese 500 Anwendungsverbindungen auf vielleicht 20 tatsächliche PostgreSQL-Verbindungen gemappt. Der Pooler stellt Anfragen in eine Warteschlange, wenn alle Datenbankverbindungen belegt sind.

PgBouncer-Konfiguration#

ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb
 
[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
 
; Pool-Modus
pool_mode = transaction
 
; Pool-Sizing
default_pool_size = 20
max_client_conn = 500
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3
 
; Timeouts
server_idle_timeout = 300
client_idle_timeout = 0
query_timeout = 30

Transaction vs Session Mode#

  • Transaction Mode (pool_mode = transaction): Verbindungen werden nach jeder Transaktion an den Pool zurückgegeben. Das ist es, was du in 95% der Fälle willst. Maximale Verbindungswiederverwendung.
  • Session Mode (pool_mode = session): Verbindungen werden für die gesamte Client-Session gehalten. Verwende das, wenn du Prepared Statements, SET-Befehle, LISTEN/NOTIFY oder andere Session-Level-Features brauchst.
  • Statement Mode (pool_mode = statement): Verbindungen werden nach jedem Statement zurückgegeben. Zu restriktiv für die meisten Anwendungen — du kannst nicht einmal explizite Transaktionen verwenden.

Der Haken beim Transaction Mode: du kannst keine Prepared Statements verwenden (die sind Session-Level-State), du kannst SET nicht für Session-Variablen verwenden, und LISTEN/NOTIFY funktioniert nicht wie erwartet. Die meisten ORMs haben eine Möglichkeit, Prepared Statements zu deaktivieren.

Für Node.js mit dem pg-Treiber:

javascript
const pool = new Pool({
  host: '127.0.0.1',
  port: 6432,  // PgBouncer-Port, nicht PostgreSQL-Port
  database: 'mydb',
  max: 20,     // entspricht PgBouncer default_pool_size
  statement_timeout: 30000,
  // Prepared Statements für PgBouncer Transaction Mode deaktivieren
  prepare: false,
});

Pool-Sizing-Formel#

Es gibt eine gängige Formel für die optimale PostgreSQL-Connection-Pool-Größe:

optimal_connections = (core_count * 2) + effective_spindle_count

Für einen modernen Server mit 4 Kernen und einer SSD (1 effektive Spindel):

optimal = (4 * 2) + 1 = 9

Das scheint kontraintuitiv niedrig. Aber PostgreSQL ist auf den meisten modernen Hardware-Systemen CPU-gebunden (SSDs sind schnell genug, dass I/O selten der Flaschenhals ist). Mehr Verbindungen als das führt zu Context-Switching-Overhead, der die Dinge tatsächlich verlangsamt.

In der Praxis setze ich default_pool_size normalerweise auf das 2-3-fache dieses Wertes, um Burst-Traffic zu handhaben, mit dem Verständnis, dass bei Spitzenlast einige Abfragen in der PgBouncer-Warteschlange warten werden, anstatt alle gleichzeitig auf PostgreSQL einzuprasseln.

Praktische Checkliste: Die genauen Schritte für jede langsame Abfrage#

Hier ist mein tatsächlicher Prozess, wenn ich einen „diese Abfrage ist langsam"-Bericht bekomme. Ich folge diesen Schritten in dieser Reihenfolge, jedes Mal.

Schritt 1: Die tatsächliche Abfrage besorgen#

Nicht „der Endpoint ist langsam" — das tatsächliche SQL. Wenn du ein ORM verwendest, aktiviere das Query-Logging:

sql
-- Vorübergehend alle Abfragen über 100ms loggen
ALTER SYSTEM SET log_min_duration_statement = 100;
SELECT pg_reload_conf();

Oder prüfe pg_stat_statements für die Top-Abfragen nach Gesamtzeit:

sql
-- Top 10 Abfragen nach gesamter Ausführungszeit
SELECT
  queryid,
  calls,
  round(total_exec_time::numeric, 2) AS total_time_ms,
  round(mean_exec_time::numeric, 2) AS avg_time_ms,
  round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct,
  query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

pg_stat_statements ist die wertvollste Extension für PostgreSQL-Performance. Wenn du sie nicht verwendest, installiere sie jetzt:

sql
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Schritt 2: EXPLAIN (ANALYZE, BUFFERS) ausführen#

sql
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) <die langsame Abfrage>;

Achte auf:

  1. Schlechte Zeilenschätzungen — tatsächliche Zeilen weichen stark von geschätzten ab
  2. Seq Scans auf großen Tabellen — potenziell fehlender Index
  3. Nested Loops mit vielen Zeilen — sollte ein Hash Join oder Merge Join sein
  4. Hohe Buffer-Reads — kalter Cache oder zu große Tabelle
  5. Sort-Operationen, die auf Festplatte überlaufen — work_mem erhöhen oder Index für Sort hinzufügen

Schritt 3: Tabellenstatistiken prüfen#

sql
-- Wann wurden Statistiken zuletzt aktualisiert?
SELECT
  relname,
  last_analyze,
  last_autoanalyze,
  n_live_tup,
  n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'orders';

Wenn last_analyze alt ist oder n_dead_tup hoch im Verhältnis zu n_live_tup, führe aus:

sql
ANALYZE orders;

Dann führe EXPLAIN ANALYZE erneut aus. Wenn sich der Plan ändert, waren veraltete Statistiken das Problem.

Schritt 4: Vorhandene Indizes prüfen#

sql
-- Welche Indizes gibt es auf dieser Tabelle?
SELECT
  indexname,
  indexdef,
  idx_scan,
  idx_tup_read,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
JOIN pg_indexes ON pg_stat_user_indexes.indexrelname = pg_indexes.indexname
WHERE tablename = 'orders'
ORDER BY idx_scan DESC;

Vielleicht existiert der Index, wird aber nicht verwendet. Vielleicht ist die Spaltenreihenfolge falsch für deine Abfrage.

Schritt 5: Indizes erstellen oder ändern#

Basierend auf dem Abfrageplan den passenden Index erstellen. Teste mit EXPLAIN ANALYZE vorher und nachher.

sql
-- Index concurrent erstellen (sperrt die Tabelle nicht)
CREATE INDEX CONCURRENTLY idx_orders_customer_status
ON orders (customer_id, status)
WHERE status IN ('pending', 'processing');
 
-- Verifiziere, dass der Index genutzt wird
EXPLAIN (ANALYZE, BUFFERS) <die langsame Abfrage>;

Verwende immer CONCURRENTLY auf Produktionstabellen. Ein normales CREATE INDEX nimmt eine volle Tabellensperre, die alle Schreibvorgänge blockiert.

Schritt 6: Abfrage-Rewrites erwägen#

Wenn der Index existiert und die Statistiken frisch sind, die Abfrage aber immer noch langsam ist, schau dir die Abfrage selbst an:

  • Kann eine Subquery als JOIN umgeschrieben werden?
  • Verursacht OFFSET Probleme? Wechsle zu Keyset Pagination
  • Wählst du mehr Spalten als nötig?
  • Kann eine korrelierte Subquery eine Window Function werden?
  • Verhindert ein CTE die Optimierung durch den Planner?

Schritt 7: Server-Einstellungen prüfen#

Für bestimmte Abfragemuster sind Server-Einstellungen wichtig:

sql
-- work_mem: Speicher für Sorts und Hash Joins (pro Operation, nicht pro Verbindung)
-- Standard ist 4MB, das ist zu niedrig für komplexe Abfragen
SET work_mem = '64MB';  -- probiere das und führe EXPLAIN ANALYZE erneut aus
 
-- effective_cache_size: teilt dem Planner mit, wie viel Disk-Cache zu erwarten ist
-- Setze auf ~75% des gesamten RAM
SHOW effective_cache_size;
 
-- random_page_cost: Verhältnis von Random I/O zu sequenzieller I/O
-- Standard ist 4.0, setze auf 1.1-1.5 für SSDs
SHOW random_page_cost;

Wenn das Ändern von work_mem ein Sort-to-Disk-Problem behebt, erwäge eine globale Erhöhung. Aber sei vorsichtig — es ist pro Operation, nicht pro Verbindung. Eine komplexe Abfrage mit 10 Sort-Operationen und work_mem = 256MB könnte 2,5 GB für eine einzelne Abfrage verwenden.

Schritt 8: Nach dem Fix überwachen#

Nicht einfach fixen und vergessen. Verifiziere, dass der Fix standhält:

sql
-- pg_stat_statements zurücksetzen für frische Daten nach deinen Änderungen
SELECT pg_stat_statements_reset();
 
-- In einer Stunde/einem Tag nachschauen
SELECT
  queryid,
  calls,
  round(mean_exec_time::numeric, 2) AS avg_time_ms,
  round(max_exec_time::numeric, 2) AS max_time_ms,
  query
FROM pg_stat_statements
WHERE query LIKE '%orders%'
ORDER BY mean_exec_time DESC;

Bonus: Quick Wins, die ich auf jede neue Datenbank anwende#

Das sind Einstellungen und Praktiken, die ich auf jede PostgreSQL-Datenbank anwende, die ich aufsetze, bevor irgendwelche Performance-Probleme auftreten:

sql
-- 1. pg_stat_statements installieren
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
 
-- 2. Slow Query Logging aktivieren (50ms ist meine Schwelle)
ALTER SYSTEM SET log_min_duration_statement = 50;
 
-- 3. SSD-geeignete Einstellungen
ALTER SYSTEM SET random_page_cost = 1.1;
ALTER SYSTEM SET effective_io_concurrency = 200;
 
-- 4. work_mem vom mageren Standard erhöhen
ALTER SYSTEM SET work_mem = '32MB';
 
-- 5. effective_cache_size auf 75% des RAM setzen
-- Für einen 16GB-Server:
ALTER SYSTEM SET effective_cache_size = '12GB';
 
-- 6. shared_buffers auf 25% des RAM setzen
-- Für einen 16GB-Server:
ALTER SYSTEM SET shared_buffers = '4GB';
 
-- 7. Autovacuum-Reaktionsfähigkeit verbessern
ALTER SYSTEM SET autovacuum_naptime = '10s';
 
-- Änderungen anwenden
SELECT pg_reload_conf();
-- Hinweis: shared_buffers erfordert einen Neustart

Und eine Monitoring-Abfrage, die ich wöchentlich ausführe:

sql
-- Tabellen, die Aufmerksamkeit brauchen
SELECT
  schemaname || '.' || relname AS table,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
  n_live_tup,
  n_dead_tup,
  CASE WHEN n_live_tup > 0
    THEN round(100.0 * n_dead_tup / n_live_tup, 1)
    ELSE 0
  END AS dead_pct,
  last_autovacuum,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

Abschließende Gedanken#

PostgreSQL-Abfrageoptimierung ist keine dunkle Kunst. Es ist ein systematischer Prozess:

  1. Messen — nicht raten. EXPLAIN (ANALYZE, BUFFERS) ist dein bester Freund.
  2. Den Plan verstehen — lerne, Scan-Typen, Join-Typen und Zeilenschätzungen zu lesen.
  3. Strategisch indizieren — der richtige Index auf den richtigen Spalten, in der richtigen Reihenfolge. Partial Indexes und Covering Indexes sind unterschätzte Superkräfte.
  4. Bessere Abfragen schreiben — EXISTS statt IN für Existenzprüfungen, Keyset Pagination statt OFFSET, Window Functions statt korrelierter Subqueries.
  5. Die Datenbank pflegen — Autovacuum-Tuning, Statistik-Updates, Connection Pooling.
  6. Kontinuierlich überwachen — pg_stat_statements sagt dir, wo deine Datenbank ihre Zeit verbringt. Prüfe es regelmäßig.

Der Unterschied zwischen einer Abfrage, die 4 Sekunden dauert, und einer, die 0,3 Millisekunden braucht, ist selten die Hardware. Es ist fast immer Wissen — zu wissen, wo man suchen und was man ändern muss. Und jetzt weißt du, wo du suchen musst.

Ähnliche Beiträge