PostgreSQL Performance-Tuning für Symfony-Anwendungen: Die Konfigurationsänderungen, die wirklich zählen

Die meisten Symfony-Anwendungen laufen auf einer PostgreSQL-Instanz, deren Konfiguration seit der Installation nie angefasst wurde. Die Standardwerte, mit denen postgresql.conf ausgeliefert wird, gehen von einer Maschine mit 128 MB RAM aus, weil das früher eine sichere Annahme für eine generische Installation war. Für einen dedizierten Datenbankserver, auf dem eine produktive SaaS-Anwendung läuft, ist das keine sichere Annahme, und genau in der Lücke zwischen den Standardeinstellungen und der tatsächlichen Hardware steckt ein Großteil der vermeidbaren Langsamkeit.

Das hier ist ein praktischer Rundgang zum PostgreSQL Performance-Tuning für Symfony: die konkreten Konfigurationsänderungen, die Begründung hinter jeder einzelnen und das Query-Muster, das sie unterstützt. Nichts davon erfordert Änderungen am Anwendungscode. Alles davon erfordert, dass man versteht, was die jeweilige Einstellung tatsächlich steuert, denn Zahlen aus einem Blogpost zu übernehmen, ohne sie zu verstehen, ist der sichere Weg zu einer Datenbank, der unter Last der Speicher ausgeht, statt zu einer, die schneller läuft.

PostgreSQL Performance-Tuning für Symfony: zuerst die Memory-Einstellungen

Drei Einstellungen steuern, wie PostgreSQL den RAM auf seinem Host nutzt, und sie beeinflussen sich gegenseitig, weshalb es selten hilft, nur eine davon zu ändern.

shared_buffers ist PostgreSQLs eigener Cache für Tabellen- und Indexdaten. Der Standardwert liegt bei 128 MB, unabhängig davon, wie viel RAM der Server hat. Auf einem dedizierten Datenbankserver setzt man ihn auf 25 % des gesamten RAM. Ein Server mit 16 GB RAM bekommt shared_buffers = 4GB. Deutlich über 25 % zu gehen, geht meist nach hinten los, weil PostgreSQL sich zusätzlich auf den Page-Cache des Betriebssystems verlässt, und wenn PostgreSQL zu viel vom RAM bekommt, wird diese zweite Cache-Schicht ausgehungert.

effective_cache_size reserviert keinen Speicher. Es teilt dem Query-Planer mit, wie viel Cache (PostgreSQLs Shared Buffers plus der OS-Page-Cache zusammen) er als verfügbar annehmen darf, wenn er entscheidet, ob ein Index-Scan günstig ist. Setze das auf 75 % des gesamten RAM. Auf demselben 16-GB-Server wäre das effective_cache_size = 12GB. Diesen Wert zu niedrig anzusetzen, ist eine häufige Ursache dafür, dass der Planer einen sequenziellen Scan statt eines Index-Scans wählt, obwohl eine Tabelle eindeutig vom Index profitieren sollte. Wer schon mal auf einen EXPLAIN ANALYZE-Output geschaut und sich gefragt hat, warum Postgres einen eigentlich guten Index ignoriert, findet die Ursache meist genau hier.

work_mem steuert den Speicher, der pro Sortier- oder Hash-Operation verfügbar ist, und diese Einstellung braucht die meiste Vorsicht, weil sie mit der gleichzeitigen Aktivität multipliziert wird statt einmal global gesetzt zu werden. Jede Query kann work_mem mehrfach nutzen, wenn sie mehrere Sortier- oder Hash-Schritte enthält, und jede gleichzeitige Verbindung, die eine solche Query ausführt, nutzt ihre eigene Zuweisung. Die Formel, die auf der sicheren Seite bleibt:

work_mem = (RAM * 0.25) / max_connections

Auf einem 16-GB-Server mit max_connections = 100 sind das ungefähr 40 MB. work_mem global zu hoch zu setzen, ist der Weg, auf dem ein Schwung gleichzeitiger Reporting-Queries einen Datenbankserver in die Knie zwingt, der auf dem Papier reichlich RAM hatte. Braucht eine bestimmte Query mehr (zum Beispiel ein Export-Job mit einer großen Sortierung), setzt man das für diese Session mit SET work_mem = '256MB', statt den globalen Default anzuheben.

Auswirkung: korrekt dimensionierte Memory-Einstellungen sind meist der größte Sprung bei der Query-Latenz, den man allein durch Konfiguration erreichen kann, und halbieren die Zeit für leselastige Dashboard- und Listing-Endpunkte oft oder mehr, weil die Daten, die diese Queries brauchen, tatsächlich zwischen Requests im Speicher bleiben, statt verdrängt und von der Platte neu gelesen zu werden.

Write-Ahead-Log-Einstellungen: Ausfallsicherheit gegen Performance

Das Write-Ahead-Log (WAL) ist der Mechanismus, mit dem PostgreSQL garantiert, dass committete Daten einen Absturz überleben, und es ist auch die Stelle, an der viel Schreiblatenz entsteht, wenn es nicht auf die tatsächlichen Anforderungen der Workload abgestimmt ist.

synchronous_commit ist die Einstellung, die man sich zuerst ansehen sollte. Der Standardwert on bedeutet, dass PostgreSQL wartet, bis der WAL-Eintrag auf die Platte geschrieben wurde, bevor es der Anwendung einen erfolgreichen Commit zurückmeldet. Das ist die sicherste Einstellung, und sie sollte für alles, was mit Finanztransaktionen, Abrechnungsdaten oder Daten zu tun hat, die man auch in einem seltenen Crash-Szenario nicht verlieren darf, auf on bleiben.

Für Workloads, bei denen der Verlust der letzten paar hundert Millisekunden an Schreiboperationen im Fall eines Absturzes akzeptabel ist (Activity-Logs, Analytics-Events, unkritische Background-Job-Datensätze), entfernt synchronous_commit = off diese Wartezeit und kann die Schreiblatenz unter Last spürbar senken. In PostgreSQL ist das eine Pro-Transaktion-Einstellung, man muss sich also nicht für die ganze Datenbank auf eine Policy festlegen:

BEGIN;
SET LOCAL synchronous_commit = OFF;
INSERT INTO activity_log (...) VALUES (...);
COMMIT;

Den globalen Default auf on belassen und einzelne Transaktionen gezielt davon ausnehmen, nicht umgekehrt.

wal_buffers sollte explizit gesetzt werden statt auf -1 (Autotune) zu bleiben, was den Wert auf größeren Servern zu knapp bemisst. Ein Wert von 16MB deckt die meisten Workloads ab und verhindert, dass das WAL bei schreiblastigen Lastspitzen zum Flaschenhals wird.

Auswirkung: das betrifft speziell die Schreiblatenz, nicht die Lese-Performance. Wenn die langsamen Endpunkte diejenigen sind, die Inserts und Updates machen, statt derjenigen, die lesen, ist das die Einstellungsgruppe, die man zuerst prüfen sollte.

Autovacuum für schreiblastiges Multi-Tenant-SaaS

Autovacuum ist der Prozess, der Speicherplatz von aktualisierten und gelöschten Zeilen zurückgewinnt und die Tabellenstatistiken für den Query-Planer aktuell hält. Die Standardkonfiguration geht von einer relativ niedrigen Schreibrate aus, und eine Multi-Tenant-SaaS-Anwendung mit häufigen Updates auf dieselben Zeilen (Abo-Status, Nutzungszähler, Session-State) überholt sie mühelos.

Der Standard-Trigger ist autovacuum_vacuum_scale_factor = 0.2, das heißt, eine Tabelle wird gevacuumt, sobald sich 20 % ihrer Zeilen geändert haben. Bei einer Tabelle mit 10 Millionen Zeilen sind das 2 Millionen geänderte Zeilen, bevor Vacuum läuft, und zu diesem Zeitpunkt hat sich die Query-Performance meist schon verschlechtert, während Vacuum selbst länger dauert, weil mehr Arbeit anfällt.

Für speziell schreiblastige Tabellen überschreibt man das besser auf Tabellenebene statt den globalen Default zu ändern, was unnötige Vacuum-Läufe auf Tabellen auslösen würde, die sich kaum ändern:

ALTER TABLE usage_counters SET (autovacuum_vacuum_scale_factor = 0.02);
ALTER TABLE subscription_status SET (autovacuum_vacuum_scale_factor = 0.02);

Das senkt den Trigger-Schwellenwert auf 2 % geänderter Zeilen, sodass Vacuum auf den Tabellen, die es brauchen, häufiger läuft, dabei aber jedes Mal weniger Arbeit verrichtet.

Ebenfalls einen Blick wert: autovacuum_max_workers (Standard 3) kann zum Flaschenhals werden, wenn mehr als drei Tabellen gleichzeitig häufiges Vacuuming brauchen, da sie sich dann gegenseitig blockieren. Auf einem Server mit genug CPU-Reserve auf 5 oder 6 zu erhöhen, lässt Vacuum über mehr Tabellen gleichzeitig Schritt halten.

Auswirkung: unzureichend gevacuumte Tabellen zeigen sich durch allmählich langsamer werdende Queries auf Tabellen, die früher schnell waren, plus Table-Bloat, der Speicherplatz und Backup-Größe über die Zeit aufbläht. Autovacuum-Tuning auf Tabellenebene zielt genau auf die Tabellen, die das Problem verursachen, ohne überall sonst zusätzlichen Vacuum-Overhead zu erzeugen.

Connection-Konfiguration und PgBouncer für Symfony

max_connections in postgresql.conf setzt eine harte Obergrenze, und die Zahl lässt sich leicht in beide Richtungen falsch wählen. Jede Verbindung reserviert Speicher, egal ob sie gerade aktiv etwas tut (grob abhängig von der eigenen work_mem-Einstellung, wie oben gezeigt), zu hoch angesetzt verschwendet das also RAM, das sonst für Caching genutzt werden könnte. Zu niedrig angesetzt beginnen Symfony-Worker unter Last, Connection-Fehler zu bekommen.

Ein vernünftiger Startpunkt ist max_connections = 100 bis 200 für eine mittelgroße Anwendung, berechnet aus der tatsächlichen Nebenläufigkeit: PHP-FPM- bzw. Worker-Pool-Größe, multipliziert mit der Anzahl der App-Server, plus Background-Worker und eventuelle Admin- oder Reporting-Verbindungen, mit etwas Puffer.

Symfonys Standardverhalten für Doctrine-Verbindungen öffnet pro Request eine neue Datenbankverbindung und schließt sie am Ende wieder. Unter echtem Traffic entsteht dadurch deutlich mehr Connection-Churn, als PostgreSQL effizient handhabt, und das ist der Grund, warum die meisten produktiven Symfony-Deployments PgBouncer vor PostgreSQL schalten, statt sich direkt zu verbinden.

PgBouncer-Pool-Dimensionierung für dieses Muster:

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

[pgbouncer]
pool_mode = transaction
default_pool_size = 25
max_client_conn = 500

pool_mode = transaction ist die Einstellung, die für Symfony am wichtigsten ist: sie gibt die zugrunde liegende PostgreSQL-Verbindung zurück in den Pool, sobald eine Transaktion committet wird, statt sie für die Lebensdauer der Client-Verbindung zu halten. Dadurch können sich 500 gleichzeitige PHP-FPM-Worker einen deutlich kleineren Pool an tatsächlichen PostgreSQL-Verbindungen teilen (hier default_pool_size = 25), sodass PostgreSQLs eigenes max_connections niedrig bleibt, während auf Anwendungsebene trotzdem hohe Nebenläufigkeit bedient wird.

Ein Vorbehalt bei Transaction-Pooling: Session-Level-Features wie Prepared Statements und Advisory Locks, die sich über mehrere Transaktionen erstrecken, funktionieren über Verbindungen hinweg nicht zuverlässig. Doctrines Standardverhalten ist mit Transaction-Pooling kompatibel, aber wer an anderer Stelle im Code rohe Prepared Statements oder session-gebundene Features nutzt, sollte das vor der Umstellung gegen diesen Pooling-Modus prüfen.

Auswirkung: das ist die Änderung, die "too many connections"-Fehler bei Traffic-Spitzen verhindert und einem einzelnen PostgreSQL-Server erlaubt, deutlich mehr gleichzeitige Symfony-Worker zu bedienen, als direkte Verbindungen zulassen würden.

Index-Strategie für typische Symfony-Query-Muster

Konfigurationsänderungen helfen jeder Query proportional, aber die größten Gewinne bei einzelnen Queries kommen meist von Indizes, die zu dem passen, wie Doctrine tatsächlich Queries generiert.

Composite-Indizes für WHERE-Klauseln über mehrere Spalten sind wichtig, weil ein Single-Column-Index einer Query nicht hilft, die auf zwei oder drei Spalten gleichzeitig filtert. Filtert eine Query auf tenant_id und status, bedient ein Composite-Index auf (tenant_id, status) diesen Filter direkt, während separate Indizes auf jeder Spalte PostgreSQL zwingen, entweder einen davon zu wählen und den Rest im Speicher zu filtern oder einen langsameren Bitmap-Merge durchzuführen:

CREATE INDEX idx_orders_tenant_status ON orders (tenant_id, status);

Die Spaltenreihenfolge ist entscheidend. Die Spalte, die in Gleichheitsfiltern genutzt wird, zuerst, die Spalte für Bereichsfilter oder Sortierung danach.

Partielle Indizes für soft-gelöschte Zeilen helfen in Codebasen, die Doctrines Soft-Delete-Muster nutzen (eine deleted_at-Spalte, die bei jeder Query geprüft wird). Ein regulärer Index auf einer häufig abgefragten Spalte enthält weiterhin die soft-gelöschten Zeilen, was den Index mit Zeilen aufbläht, die nie tatsächlich zurückgegeben werden. Ein partieller Index schließt sie aus:

CREATE INDEX idx_orders_active_tenant ON orders (tenant_id) WHERE deleted_at IS NULL;

Das hält den Index kleiner und schneller zu scannen und passt zu der WHERE deleted_at IS NULL-Klausel, die Doctrines Soft-Delete-Filter fast jeder Query automatisch hinzufügt.

Covering-Indizes adressieren eine bestimmte Variante des N+1-Problems: eine Query, die ein paar zusätzliche Spalten über das hinaus braucht, worauf sie filtert, wodurch PostgreSQL die vollständige Zeile in der Tabelle nachschlagen muss, obwohl ein Index den Filter bereits erfüllt hat. Die benötigten Spalten mit INCLUDE hinzuzufügen, erlaubt PostgreSQL, die Query allein aus dem Index zu beantworten:

CREATE INDEX idx_orders_tenant_lookup ON orders (tenant_id, status) INCLUDE (total_amount, created_at);

Das ersetzt nicht die Behebung echter N+1-Query-Muster in Doctrine (Eager Loading mit JOIN oder fetch: EAGER wo sinnvoll bleibt der erste Fix). Es reduziert die Kosten der einzelnen Lookups, die übrig bleiben, was relevant wird, sobald die Query-Anzahl selbst schon unter Kontrolle ist.

Auswirkung: Index-Änderungen sind das workload-spezifischste Tuning in dieser Liste. EXPLAIN ANALYZE auf den tatsächlich langsamsten Queries vor und nach dem Hinzufügen eines Index laufen lassen. Ein Composite- oder Covering-Index kann einen sequenziellen Scan über eine Tabelle mit mehreren Millionen Zeilen in einen Index-Only-Scan verwandeln, der in Millisekunden zurückkommt, aber nur, wenn er zu den Queries passt, die die eigene Anwendung tatsächlich ausführt.

Alles zusammen

Keine dieser Änderungen ist für sich genommen spektakulär, aber sie summieren sich. Memory-Einstellungen bestimmen, wie viele der Arbeitsdaten im Cache bleiben. WAL-Einstellungen bestimmen die untere Grenze der Schreiblatenz. Autovacuum bestimmt, ob Tabellen schnell bleiben oder sich langsam verschlechtern. Connection-Pooling bestimmt, wie viel Nebenläufigkeit der Server aufnehmen kann, ohne zusammenzubrechen. Indizes bestimmen, ob der Query-Planer überhaupt einen schnellen Weg zur Verfügung hat.

Jede Änderung unter realistischer Last testen, statt anzunehmen, dass die obigen Zahlen für die eigene Hardware und das eigene Traffic-Muster exakt passen. pgbench oder ein Replay der produktiven Query-Logs gegen eine Staging-Kopie der Datenbank zeigt den tatsächlichen Vorher-Nachher-Unterschied, den man vor einer Änderung an der Produktion kennen sollte.

Wenn sich das nach mehr Datenbankarbeit anfühlt, als das eigene Team neben dem Ausliefern des Produkts leisten kann, ist das eine verbreitete Situation, und genau diese Art von Performance-Arbeit übernehmen wir für Kunden im Rahmen von Code-Qualitätsberatung, einschließlich Reviews produktiver PostgreSQL- und Symfony-Konfigurationen. Für individuelle Symfony-Anwendungen, bei denen dieses Tuning von Anfang an mitgedacht wird, siehe individuelle Softwareentwicklung.

Fragen zu einer bestimmten langsamen Query oder einer Konfigurationsentscheidung für die eigene Umgebung sind willkommen unter hello@wolf-tech.io, oder schau dir an, was wir sonst noch auf wolf-tech.io abdecken.