10 Jahre Verbesserungen am PostgreSQL-Optimizer

Die Verbesserungen am PostgreSQL-Optimizer über 10 Jahre

• Als Forscher im Bereich Query-Optimierung habe ich in den vergangenen 10 Jahren den ausgefeilten Open-Source-Query-Optimizer von PostgreSQL für meine Forschung genutzt
• Seit ich mit Datenbankarbeit begonnen habe, habe ich mich gefragt, wie stark sich PostgreSQL in diesen 10 Jahren verbessert hat
• Es gab viele Changelogs und Meinungen, aber keinen belastbaren empirischen Vergleich, daher wurde beschlossen, den Join Order Benchmark (JOB) selbst auf PostgreSQL 8 bis 16 auszuführen
• Für jede Datenbankversion wurde die Latenz der Abfragen im 90. Perzentil erfasst

Aufbau der Testumgebung

• Jede PostgreSQL-Version wurde in einem Docker-Container unter Arch Linux mit GCC 13.2 gebaut
• Um die Qualität des Query-Optimizers zu messen, wurde shared_buffers auf 8 GB gesetzt (groß genug, um die gesamte Datenbank zu halten)
• Für alle Versionen wurde work_mem auf 8 MB gesetzt
• Jede Abfrage wurde einmal ausgeführt, um den Cache aufzuwärmen, und dann weitere fünfmal; erfasst wurde die Median-Latenz dieser Läufe

Allgemeine Performance-Verbesserungen

• Die Tail-Performance von PostgreSQL hat sich deutlich verbessert, aber die Versionen 13 bis 16 waren insgesamt eher stabil
• Vergleicht man Version 8 mit Version 16, hat der PostgreSQL-Optimizer die Tail-Latenz in den vergangenen 10 Jahren nahezu halbiert
• Die gesamte Verteilung der Abfragen kann untersucht werden (siehe logarithmische Skala)

Quantifizierung der Verbesserungen per Regressionsanalyse

• Mit einer Regressionsanalyse lässt sich prüfen, ob die sinkende Tendenz der Latenz signifikant ist, und quantifizieren, wie viel Verbesserung jede PostgreSQL-Version bringt
• Führt man eine Regressionsanalyse der PostgreSQL-Hauptversionsnummer gegen die Abfragelatenz durch, ergibt sich, dass jede neue Hauptversion von PostgreSQL im Join Order Benchmark im Durchschnitt eine Performance-Verbesserung von 15 % bringt
• Das lineare Modell ist jedoch arguably ein schlechter Maßstab, um Veränderungen zu messen

Weitere Überlegungen

• Natürlich sind nicht alle diese Verbesserungen dem Query-Optimizer zu verdanken. Auch Verbesserungen an der Execution Engine – von parallelen Workern bis hin zu Just-in-Time-(JIT-)Kompilierung – tragen dazu bei
• Es wäre ebenfalls interessant zu untersuchen, wie sich die Query-Pläne im JOB im Laufe der Jahre verändert haben

Zentrale Punkte

• Aktualisieren Sie Ihre Datenbank! Der Wechsel von PostgreSQL 8 auf 16 kann die Tail-Latenz Ihrer Workloads deutlich verbessern
• Forscher sollten beachten, dass PostgreSQL ein bewegliches Ziel ist
  • Forschung zu Learned Query Optimization hat PostgreSQL im Laufe der Zeit mit unterschiedlichen Versionen verglichen
  • Wenn eine frühere Technik PostgreSQL um 30 % verbessert und eine neuere Technik PostgreSQL um 25 % verbessert, kann es sein, dass die neuere Technik mit einem bereits stärkeren PostgreSQL verglichen wird

Meinung von GN⁺

• PostgreSQL hat seine Performance kontinuierlich verbessert, aber bei den neueren Versionen nimmt das Ausmaß der Verbesserungen ab. Das könnte daran liegen, dass bereits erhebliche Optimierungen erfolgt sind. Künftige Verbesserungen dürften sich eher auf Detailbereiche konzentrieren

• Nicht nur der Query-Optimizer, sondern auch Verbesserungen an der Execution Engine tragen zur Performance-Steigerung bei. Optimierungen erfolgen in verschiedenen Bereichen wie Parallelverarbeitung oder JIT-Kompilierung

• Dieses Experiment ist auf den Join Order Benchmark beschränkt; der Effekt von Performance-Verbesserungen in realen Anwendungen kann je nach Workload unterschiedlich ausfallen. Es ist sinnvoll, Benchmarks passend zu den eigenen Anforderungen durchzuführen

• Forscher sollten Versionsänderungen von PostgreSQL berücksichtigen. Denn selbst beim gleichen Algorithmus kann das relative Ausmaß der Performance-Steigerung je nach Vergleichsversion von PostgreSQL unterschiedlich ausfallen

• Wenn Sie noch eine alte PostgreSQL-Version verwenden, lohnt es sich, ein Upgrade aktiv in Betracht zu ziehen. Gegenüber einer Version von vor 10 Jahren bieten aktuelle Versionen deutlich bessere Performance. Fragen der Kompatibilität beim Upgrade müssen dabei natürlich mitbedacht werden

[GN⁺]

Hacker-News-Kommentar

Zusammenfassung:

• Um Optimierungsprobleme gut zu lösen, sind Daten über die Kosten entscheidend. Bei PostgreSQL gibt es noch viel Raum für Verbesserungen. Insbesondere fehlen Daten zur Syscall-Latenz und Foreign-Key-Statistiken.
• Bei großen Queries ist es nötig, Verfahren wie Deferred Planning einzuführen, bei denen der Plan während der Ausführung angepasst werden kann.
• Maschinelles Lernen eignet sich dafür, Modelle zur Kostenvorhersage zu verbessern. Mit maschinellem Lernen direkt Query-Pläne zu erstellen, ist nicht sinnvoll.
• Für Benchmarks den Shared Buffer so groß zu setzen, dass alle Daten im Speicher liegen, erschwert eine realistische Bewertung der tatsächlichen Leistung des Optimizers.
• JIT-Compiler führen derzeit oft eher zu Leistungseinbußen.
• Da sich das PostgreSQL-Versionsschema ab Version 10 geändert hat, wäre es auch interessant, die Versionen 8.x und 9.x als Major-Versionen zu betrachten und die Performance-Entwicklung zu analysieren.
• Allein anhand der gezeigten Grafiken lässt sich der Trend der Performance-Verbesserungen nicht klar erkennen. Die Tail Latency scheint sich verbessert zu haben, der Rest kann jedoch jeweils unterschiedlich ausfallen.
• Einen hervorragenden Optimizer zu bauen, ist eine ziemlich anspruchsvolle Aufgabe.
• Es ist fraglich, ob sich die Query-Optimierung auf SQL-Ebene oder auf Algorithmus-Ebene abspielt.