Geschichte der Frage
PostgreSQL führte vorbereitete Anweisungen ein, um die Parsing- und Planungsüberlastung bei wiederholt ausgeführten SQL-Abfragen zu eliminieren. Frühversionen generierten immer maßgeschneiderte Ausführungspläne, die auf spezifische Parameterwerte zugeschnitten waren, was jedoch bei komplexen Abfragen erhebliche CPU-Kosten verursachte. Um dies zu optimieren, implementierte PostgreSQL 9.2 das Plan-Caching mit einem generischen Planmechanismus, der eine einzige Planstruktur über mehrere Ausführungen hinweg wiederverwendet. Obwohl dieser Ansatz die Planungslatenz verringert, behandelt er alle Parameter während der anfänglichen Planungsphase als undurchsichtige Platzhalter.
Das Problem
Die Partitionierung in PostgreSQL funktioniert in zwei verschiedenen Phasen: der Planungszeit-Partitionierung, die während der Abfragplanung erfolgt, wenn der Planer die Partitionseinschränkungen gegen Literalwerte untersucht, und der Laufzeit-Partitionierung, die während der Ausführung die Partitionen mithilfe von partitionierten Joins oder der Filterung von Anhängen filtert. Generische Pläne, die für vorbereitete Anweisungen generiert werden, haben bei der Planungszeit keine konkreten Parameterwerte, was eine Planungszeit-Partitionierung unmöglich macht. Folglich generiert der Planer einen Scan über alle Partitionen, unabhängig von den tatsächlichen Parameterwerten, die während der Ausführung bereitgestellt werden, was zu katastrophalen Leistungsabfällen bei großen partitionierten Tabellen führt.
Die Lösung
Die Lösung besteht darin, PostgreSQL dazu zu zwingen, maßgeschneiderte Pläne zu erstellen, die während der Planungsphase tatsächliche Parameterwerte enthalten. Dies wird erreicht, indem der Konfigurationsparameter plan_cache_mode für die Sitzung oder spezifische Abfragen auf force_custom_plan gesetzt wird, wodurch der generische Plan-Cache vollständig umgangen wird. Alternativ beinhalten Protokollebene-Arbeitsabläufe die Verwendung des erweiterten Abfrageprotokolls mit der Bind-Nachricht, die Literalwerte anstelle von Parametern enthält, oder den Einsatz clientseitiger Abfrage-Builder, die Literalwerte für Partitionierungsschlüssel inline verwenden und andere Parameter gebunden lassen, um SQL-Injection zu verhindern.
-- Maßgeschneiderten Plan für diese Sitzung erzwingen SET plan_cache_mode = force_custom_plan; -- Oder verwenden Sie dynamisches SQL mit format(), um Literale sicher inline zu verwenden EXECUTE format('SELECT * FROM logs WHERE log_date >= %L', '2024-01-01');
Eine Hochfrequenz-Handelsanalyseplattform erlebte jeden Morgen erhebliche Latenzspitzen, als sie die Preisbewegungen innerhalb des Tages abfragte. Die Datenbank speicherte Tick-Daten in einer Tabelle, die nach Handels-Sitzungsdatum partitioniert war, und umfasste über 2.000 Partitionen über fünf Jahre. Die Anwendung verwendete vorbereitete JDBC-Anweisungen mit ?-Platzhaltern für den Datumsparameter, um SQL-Injection zu verhindern und die Parsing-Überlastung zu verringern.
Das Entwicklungsteam bemerkte zunächst, dass Abfragen, die nach „heutigen“ Daten suchten, historische Partitionen durchschnitten und 45 Sekunden benötigten statt der erwarteten 300 Millisekunden. Dieser Leistungsabfall trat auf, weil der generische Plan nicht in der Lage war, irrelevante Partitionen während der Planungsphase auszuschließen.
Ein Ansatz bestand darin, eine separate unprotokollierte Tabelle für heiße Daten zu erstellen und Datensätze über Nacht zu migrieren. Diese Strategie hätte die partitionierte Tabelle für aktuelle Abfragen vollständig umgangen, brachte jedoch komplexe ETL-Logik mit sich und riskierte Datenverlust bei Systemabstürzen.
Ein anderer Vorschlag bestand darin, vorbereitete Anweisungen global im JDBC-Verbindungspool zu deaktivieren. Obwohl dies die Planungszeit-Partitionierung wiederherstellen würde, indem Literale den Planer zugänglich gemacht wurden, ergaben Benchmarks einen Anstieg der CPU-Nutzung um 40 % auf dem Datenbankserver aufgrund wiederholter Parsing- und Planungsüberlastung.
Das Team evaluierte auch die Verwendung der Runtime-Partitionierungsfähigkeiten von PostgreSQL, die in Version 11 eingeführt wurden. Allerdings eliminiert die Laufzeit-Partitionierung nur Partitionen, nachdem der Executor mit dem Scannen begonnen hat, was bedeutet, dass der Planer immer noch Ressourcen für alle Partitionen zuweist und suboptimale Join-Reihenfolgen produziert, die die Partitionierungsgrenzen ignorieren.
Letztendlich entschied sich das Team, eine Verbindungsebene-Konfigurationsumschaltung zu implementieren. Sie konfigurierten den Verbindungspool so, dass er Abfragen, die auf partitionierte Tabellen abzielen, erkennt und SET plan_cache_mode = force_custom_plan ausführt, bevor diese spezifischen Anweisungen gesendet werden. Dies bewahrte die Sicherheitsvorteile parametrischer Abfragen für Benutzereingabefilter, während sichergestellt wurde, dass die Werte der Partitionierungsschlüssel dem Planer sichtbar waren.
Das Ergebnis reduzierte die Abfragelatenz auf 280 Millisekunden und verringerte die gesamte CPU-Nutzung der Datenbank um 15 %, da der Planer jetzt die Ausschlussbedingungen zur Eliminierung von 1.999 Partitionen vor der Ausführung nutzen konnte. Diese Optimierung ermöglichte es der Handelsplattform, ihre strengen morgendlichen Latenzanforderungen zu erfüllen, ohne die Datenintegrität oder Sicherheit zu gefährden.
Wie entscheidet PostgreSQL, ob generische oder maßgeschneiderte Pläne verwendet werden, wenn plan_cache_mode auf auto eingestellt ist?
Im auto-Modus plant und führt PostgreSQL die Abfrage in den ersten fünf Ausführungen mit einem maßgeschneiderten Plan aus und sammelt die Planungs-/Verarbeitungskosten. Nach der fünften Ausführung vergleicht es die durchschnittliche Ausführungszeit des generischen Plans (während der ersten Ausführung geschätzt) mit der durchschnittlichen Ausführungszeit der maßgeschneiderten Pläne zuzüglich ihrer Planungskosten. Wenn die geschätzten Kosten des generischen Plans niedriger sind als die durchschnittlichen Kosten des maßgeschneiderten Plans, wechselt das System dauerhaft zum generischen Plan für diese vorbereitete Aussage. Kandidaten übersehen oft, dass dieser Vergleich die Planungskosten, die durch die Wiederverwendung des generischen Plans eingespart werden, umfasst und dass die Entscheidung für die Dauer der Lebensdauer der vorbereiteten Anweisung dauerhaft ist, es sei denn, sie wird ausdrücklich neu geplant.
Was ist der Unterschied zwischen der Planungszeit- und der Laufzeit-Partitionierung im Kontext von vorbereiteten Anweisungen?
Die Planungszeit-Partitionierung erfolgt während der Planungsphase, wenn der Planer nachweisen kann, dass bestimmte Partitionen keine relevanten Zeilen aufgrund von Partitionseinschränkungen und Literalwerten in der Abfrage enthalten können. Die Laufzeit-Partitionierung erfolgt während der Ausführung, wenn der Executor die Partitionseinschränkungen gegen die tatsächlichen Parameterwerte mithilfe des Filtersystems des Executors überprüft. Generische vorbereitete Pläne unterstützen die Laufzeit-Partitionierung seit PostgreSQL 11, können jedoch keine Planungszeit-Partitionierung unterstützen, da die Parameterwerte unbekannt sind. Kandidaten verwechseln häufig diese Mechanismen und glauben, dass die Laufzeit-Partitionierung alle Probleme mit der Partitionierung vorbereiteter Anweisungen löst, ohne sich darüber bewusst zu sein, dass die Planungszeit-Partitionierung entscheidend für die effiziente Planungs- und Indizesauswahl ist.
Warum könnte force_custom_plan Partitionierungsprobleme in PostgreSQL-Versionen vor 10 nicht lösen?
Vor Version 10 hatte PostgreSQL keine nennenswerte Unterstützung für Laufzeit-Partitionierung und vorbereitete Anweisungen konnten nicht von Ausschlussbedingungen profitieren, selbst mit maßgeschneiderten Plänen, wenn die Parameter über das erweiterte Abfrageprotokoll mithilfe der Bind-Nachricht übergeben wurden. Der Planer behandelte alle gebundenen Parameter als extern zum Planungsprozess, sodass explizite Literalwerte im Abfragestring erforderlich waren, um den Ausschluss auszulösen. Diese historische Einschränkung bedeutet, dass in Altsystemen selbst maßgeschneiderte Pläne alle Partitionen durchsuchen würden, was die dynamische SQL-Generierung mit EXECUTE ... USING mit Literalen oder clientseitiger Zeichenfolgenverkettung mit ordnungsgemäßer Escape-Behandlung erforderte, anstelle der modernen Protokollebene-Parameterbindung.