Postgres Produktionsoptimierung, die die Latenz reduziert

postgresql.confist im Standardzustand so konzipiert, dass eine Installation niemals fehlschlägt und Ihren Datenverkehr nicht absorbiert. Der historische Wert von shared_buffers, 128 MB, ermöglicht es Postgres, auf einer minimalen Maschine zu starten, ohne kritische Ressourcen zu reservieren. max_connections bei 100 passt auf einen kleinen gemeinsam genutzten Server. Keines von beiden wurde für Ihre tatsächliche Belastung ausgewählt.

Das Problem liegt also nicht darin, dass Postgres standardmäßig schlecht eingestellt ist, sondern darin, dass es nie für Sie eingestellt wurde. In den folgenden Abschnitten werden die Einstellungen in der Reihenfolge durchgegangen, in der sie sich am meisten auszahlen, vom häufigsten Engpass (den Verbindungen) bis zum am langsamsten auftretenden (WAL).

Beim ersten Projekt geht es nicht um Erinnerung, sondern um Verbindungen. Jede Postgres-Verbindung öffnet einen dedizierten Serverprozess, der RAM und Kontext-CPU-Zeit verbraucht, selbst wenn er inaktiv ist. Durch Erhöhen von max_connections zur Vermeidung von Fehlern vom Typ „zu viele Verbindungen“ verschiebt sich das Problem: Über eine bestimmte Anzahl gleichzeitig aktiver Verbindungen hinaus verringert sich durch CPU-Konkurrenz die Latenz aller Anforderungen, einschließlich der schnellsten.

Der richtige Ansatz kehrt die übliche Reihenfolge um: Skalieren Sie max_connections auf der tatsächlichen Server-Parallelität und absorbieren Sie dann die anwendungsseitige Parallelität mit einem Pooler wie PgBouncer in pool_mode=transaction. Der Pooler multiplext Hunderte von Client-Verbindungen auf eine Handvoll tatsächlich aktiver Serververbindungen. Unser spezieller Artikel beschreibt den Transaktionsmodusmechanismus und seine Grenzen (vorbereitete Anweisungen, LISTEN/NOTIFY) und einen Vergleich zwischen PgBouncer, Supavisor und PgCat zur Auswahl der Implementierung.

max_connections ist ein „Postmaster“-Kontextparameter: Für seine Änderung ist ein vollständiger Neustart des Servers erforderlich, kein einfaches Neuladen. Auf dedizierten Aurabase-Postgres-Instanzen (Pro- und Enterprise-Pläne, ein CNPG-Cluster pro Projekt) wird diese Einstellung pro Planstufe konfiguriert und nicht auf dem Standardwert belassen. Ein Neustart ist kein trivialer Vorgang, der in der Produktion wiederholt werden muss, was diese Auswahl in Stufen und nicht in einem festen Wert rechtfertigt. Die Formel zur Auswahl Ihres eigenen Werts mit seinen Grenzen ist Gegenstand eines separaten Artikels: size max_connections.

Vier Speichereinstellungen wiegen mehr als alle anderen zusammen: shared_buffers, effective_cache_size, work_mem und maintenance_work_mem. Die ersten drei bestimmen, wie viele Daten Postgres im Speicher behält, bevor sie auf die Festplatte zurückkehren. Der vierte bestimmt die Geschwindigkeit einer VACUUM- oder Indexerstellung.

shared_buffers legt den internen Cache fest, der von allen Verbindungen gemeinsam genutzt wird. Der vom PostgreSQL-Projekt häufig dokumentierte Benchmark liegt bei etwa 25 % des verfügbaren RAM auf einem dedizierten Datenbankserver. Darüber hinaus nehmen die Gewinne ab und der Festplatten-Cache des Betriebssystems übernimmt. effective_cache_size weist nichts zu: Es handelt sich um eine Schätzung des gesamten für den Cache verfügbaren Speichers (Postgres und Betriebssystem zusammen) an den Abfrageplaner. Eine Unterdimensionierung drängt den Scheduler zu sequentiellen Scans, wohingegen ein Index größtenteils zwischenspeichern würde; Der aktuelle Benchmark liegt zwischen 50 und 75 % des RAM.

work_mem ist die häufigste Falle. Dies ist keine globale Grenze. Jede Sortier- oder Hash-Operation in einer Abfrage kann ihren eigenen Anteil davon verbrauchen, und eine Abfrage mit mehreren Joins kann ihren eigenen Anteil mehrmals reservieren. Ein zu großzügiger Wert in Kombination mit einem hohen max_connections kann den Server-RAM bei gleichzeitiger Last erschöpfen, selbst wenn jede isolierte Anforderung sinnvoll erscheint. maintenance_work_memhingegen kann deutlich großzügiger bleiben: Es gilt nur für Wartungsvorgänge (VACUUM, CREATE INDEX), die selten gleichzeitig stattfinden.

Nur shared_buffers erfordert einen Neustart. Die anderen drei werden im laufenden Betrieb neu geladen, auch für eine isolierte Sitzung: SET work_mem = '64MB'; für die Dauer einer einzelnen Gier-Anfrage, ohne Auswirkungen auf die globale Einstellung.

Deaktivieren Sie niemals das automatische Vakuum in der Produktion, auch nicht vorübergehend, um während einer Spitzenlast „Ressourcen freizugeben“. Postgres verwendet MVCC: Jedes UPDATE und jedes DELETE hinterlässt eine Deadline, die nur durch Vakuum wiederhergestellt werden kann. Ohne sie blähen sich Tabellen auf, Indizes verschlechtern sich und Ausführungspläne verschlechtern sich allmählich, ohne dass Fehler sichtbar werden, bis es spät ist.

Der Standardwert vonautovacuum_vacuum_scale_factor (20 % tote Zeilen vor dem Auslösen) eignet sich für eine kleine Tabelle, nicht für eine schreibintensive Tabelle mit mehreren Millionen Zeilen. Bei einer Tabelle mit 10 Millionen Zeilen stellen diese 20 % 2 Millionen tote Zeilen dar, die vor dem ersten Durchgang angesammelt wurden. Senken Sie diesen Schwellenwert Tabelle für Tabelle, anstatt den Gesamtwert der gesamten Datenbank zu ändern.

-- Senken Sie den Schwellenwert für eine schreibintensive Tabelle, nicht insgesamt
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_vacuum_cost_limit = 2000
);

Der in Aurabase Studio integrierte Advisor überprüft diese Konfiguration bei jeder Projektanalyse, genauso wie Tabellen ohne Primärschlüssel oder nicht indizierte Fremdschlüssel. Dabei handelt es sich eher um ein deutliches Signal als um eine stille, zu spät entdeckte Verschlechterung.

Die meisten Latenzprobleme in der Produktion sind weder auf die CPU noch auf den RAM zurückzuführen, sondern auf einen fehlenden oder schlecht gewählten Index. Bevor ein einzelner Parameter von postgresql.confberührt wird, bleibt EXPLAIN (ANALYZE, BUFFERS) für die betreffende Abfrage die zuverlässigste Diagnose. Es reduziert die Latenz von Postgres-Anfragen deutlich vor dem Hinzufügen von Ressourcen.

-- Diagnostizieren Sie eine langsame Abfrage, bevor Sie die Konfiguration berühren
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.total
FROM orders o
WHERE o.customer_id = '…'
ORDER BY o.created_at DESC
LIMIT 20;

Ein Seq Scan in einer Tabelle mit mehreren Millionen Zeilen, in der ein Index Scan erwartet wurde, weist fast immer auf ein Indexproblem hin. Am häufigsten treten drei Ursachen auf: ein fehlender Index, ein mit dem vorhandenen Index inkompatibler Spaltentyp oder veraltete Statistiken nach einem Massenimport ohne ANALYZE. Durch Hinzufügen von RAM oder Erhöhen von work_mem wird dieses Symptom bei kleinen Datenmengen manchmal ausgeblendet. Das Problem tritt wieder auf, sobald die Tabelle wächst.

Um diese Anfragen zu finden, ohne sie einzeln zu durchsuchen, aggregiert pg_stat_statements die Ausführungsstatistiken aller Serveranfragen. Ein häufiger Fallstrick: Die Erweiterung muss zuerst in shared_preload_librariesaufgeführt werden, einem „Postmaster“-Kontextparameter, der einen Neustart erfordert. Ohne diesen Schritt ist CREATE EXTENSION pg_stat_statements; stillschweigend erfolgreich, sammelt aber nichts.

# Erfordert einen kompletten Neustart (Parameter „postmaster“)
shared_preload_libraries = 'pg_stat_statements'

-- Sobald der Server neu gestartet wurde, gilt in jeder zu prüfenden Datenbank Folgendes:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT query, calls, mean_exec_time, max_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

Das ist genau der Fehler, den das Aurabase-Backend zurückgibt, wenn diese Erweiterung fehlt: eine explizite Meldung statt einer stillen leeren Liste, die mit „keine langsame Abfrage“ verwechselt werden könnte. Der Studio Advisor geht noch einen Schritt weiter: Er klassifiziert automatisch jede Anfrage mit einer durchschnittlichen Zeit von mehr als 150 ms als Warnung und über 500 ms als kritisch. Diese Schwellenwerte basieren auf denselben pg_stat_statements-Statistiken.

Ein Prüfpunkt zwingt Postgres dazu, alle Seiten, die seit der vorherigen im Speicher geändert wurden, auf die Festplatte zu schreiben. Standardmäßig konzentriert sich dieses Schreiben möglicherweise auf ein zu kurzes Zeitfenster. Das Ergebnis ist ein spürbarer Anstieg der Festplattenlatenz auf der Anwendungsseite, eine Art periodischer Verlangsamung, die nur schwer einer bestimmten Anfrage zugeordnet werden kann.

checkpoint_completion_target steuert die Verteilung dieses Schreibens über das Intervall zwischen zwei Prüfpunkten. Ein Detail, das in älteren Checklisten fehlt: PostgreSQL 14, veröffentlicht im Jahr 2021, hat seinen Standardwert von 0,5 auf 0,9 geändert. Auf einer PostgreSQL 16-Instanz, beispielsweise dedizierten Aurabase-Mandantenclustern, ist diese Einstellung daher standardmäßig bereits korrekt; Eine manuelle Anpassung ist nur bei einer Version vor 14 sinnvoll. Weitere Versionsänderungen, die sich auf die Optimierung auswirken, finden Sie in unserem Vergleich PostgreSQL 16 vs. 17 vs. 18.

max_wal_size wirkt in die gleiche Richtung: Ein zu niedriger Wert löst häufiger Checkpoints aus als erwartet, auch wenn checkpoint_timeout noch nicht erreicht ist. Durch die Erhöhung wird die Häufigkeit von Prüfpunkten verringert, auf Kosten einer längeren Wiederherstellungszeit nach einem Absturz, da mehr WALs wiedergegeben werden müssen. Ein Kompromiss, der entsprechend Ihrer Toleranz gegenüber Nichtverfügbarkeit entschieden werden muss, kein universeller Wert.

Bei dieser Checkliste handelt es sich nicht um ein einmaliges Audit, das vor Produktionsbeginn einmalig abgehakt werden muss. Eine Basis, deren Volumen sich verdoppelt, oder Datenverkehr, die sich verdreifacht, macht die beim Start gewählten Benchmarks überflüssig, oft ohne explizite Fehler, nur eine fortschreitende Verschlechterung der p95-Latenz.

Drei Signale verdienen eine kontinuierliche Überwachung. pg_stat_statements identifiziert Anforderungen, die sich im Laufe der Zeit verschlechtern. pg_stat_activity meldet blockierte oder ungewöhnlich lange laufende Abfragen, und das Verhältnis der aktiven Verbindungen zu max_connections lässt eine Sättigung erwarten, bevor es zu anwendungsseitigen Fehlern kommt.

Die Registerkarte „Beobachtbarkeit“ von Studio deckt einen Teil dieser Grundlage für jedes Aurabase-Projekt ab, ohne dass Tools von Drittanbietern installiert werden müssen. Es listet langsame Anfragen auf, ermöglicht Ihnen das Abbrechen oder Beenden einer aktiven Anfrage per PID und zeigt eine Pool-Sättigungsanzeige an, die bei einer Auslastung von 80 % eine Warnung ausgibt. Auf einer selbst gehosteten Instanz wird dieselbe Überwachung manuell erstellt, wobei pg_stat_statements aktiviert und ein externes Überwachungstool angeschlossen ist.

Acht Einstellungen, in der Reihenfolge, in der sie sich am meisten auszahlen, mit allem, was Sie wissen müssen, bevor Sie sie anfassen.

Die hier genannten Schwellenwerte, wie z. B. 150 ms und 80 % Poolsättigung, die der Aurabase Studio Advisor überwacht, sind ein vom Code verifizierter Ausgangspunkt und keine universelle Wahrheit. Ihr tatsächlicher Vorwurf bleibt der alleinige endgültige Richter. Um die Größe von max_connections genau zu bestimmen, anstatt einer allgemeinen Regel zu folgen, werden im entsprechenden Artikel die Formel und ihre Grenzen detailliert beschrieben.

Drei Fragen, die bei der ersten Anwendung der Checkliste immer wieder auftauchen.