PostgreSQL führte in Version 10 die deklarative Partitionierung ein, um das umständliche vererbungbasierte Partitionierungsmodell zu ersetzen. Der Abfrageplaner führt während der Planungsphase eine statische Partitionierung durch, indem er Abfrageprädikate mit Partitionierungsgrenzen vergleicht, aber dafür müssen Ausdrücke zur Planungszeit als Konstanten evaluiert werden, um festzustellen, welche Partitionen ausgeschlossen werden können.
Die grundlegende architektonische Einschränkung besteht darin, dass STABLE-Funktionen, wie now() oder current_timestamp, während der Planung nicht evaluiert werden, da ihre Ergebnisse zwischen Planung und Ausführung oder sogar während der Abfrageausführung variieren können. Folglich behandelt der Planer Prädikate, die diese Funktionen enthalten, als Black Boxes und kann nicht nachweisen, dass bestimmte Partitionen keine übereinstimmenden Zeilen enthalten können, was dazu führt, dass alle Partitionen durchsucht werden müssen.
Die Lösung besteht darin, entweder das Prädikat umzuschreiben, um IMMUTABLE-Funktionen oder literale Konstanten zu verwenden, oder sich auf das zur Laufzeit verfügbare Partition Pruning in PostgreSQL 11 und später zu verlassen. Indem enable_partition_pruning auf on gesetzt wird, evaluiert der Executor die Ergebnisse der STABLE-Funktionen gegen die Partitionierungsgrenzen zur Ausführungszeit und überspringt dynamisch irrelevante Partitionen nach der anfänglichen Planungsphase.
Ein Finanzanalyseunternehmen partitionierte eine Handelstabelle nach TIMESTAMPTZ in der Spalte execution_time mit täglichen Bereichen, um Terabytes an Tick-Daten zu verwalten. Analysten fragten häufig nach aktuellen Aktivitäten mit WHERE execution_time >= now() - interval '1 hour', stellten jedoch fest, dass diese Abfragen katastrophale Leistungsverschlechterungen erlitten, da alle 365 täglichen Partitionen durchsucht wurden, anstatt nur die neueste.
Der erste in Betracht gezogene Ansatz bestand darin, die Anwendungsschicht den Zeitstempelgrenzen berechnen zu lassen und ihn als literale Konstante einzufügen. Dies ermöglichte sofortiges statisches Pruning und reduzierte die Abfragezeit von 45 Sekunden auf 80 Millisekunden. Diese Vorgehensweise führte jedoch dazu, dass bestehende SQL-Abfragen, die in Drittanbieter-BI-Tools eingebettet waren, nicht einfach geändert werden konnten.
Der zweite Ansatz bestand darin, eine benutzerdefinierte immutable Funktion zu erstellen, die einen festen Zeitstempel basierend auf dem aktuellen Datum zurückgab. Dies wurde abgelehnt, da es zu inkorrekten Ergebnissen führen würde, wenn die Datenbanktransaktion über die Mitternachtsgrenze hinaus offen blieb und damit den STABLE-Vertrag, den now() innerhalb einer Transaktion bereitstellt, verletzte. Eine solche Verletzung könnte dazu führen, dass der Planer einen veralteten Partitionierungsbereich zwischenspeichert, was zu stillschweigenden inkorrekten Abfrageergebnissen führen könnte.
Die gewählte Lösung nutzte die Runtime-Partition-Pruning-Funktionen von PostgreSQL 12. Die Datenbankadministratoren stellten sicher, dass enable_partition_pruning aktiviert war, und refakturierten die Anwendung, um vorbereitete Anweisungen mit dem Zeitbereich als Parameter zu verwenden, anstatt in den SQL-String hineinzukombinieren. Dies ermöglichte es dem Executor, Partitionen dynamisch mit dem tatsächlichen Parameterwert zur Ausführungszeit zu prunen, was eine nahezu optimale Leistung erreichte, ohne Änderungen an der SQL-Textgenerierung in Legacy-Reporting-Tools zu erfordern.
Wie unterscheidet sich der Ausschlussmechanismus von PostgreSQL für vererbte Tabellen von der nativen Partitionierung und warum erfordert ersterer eine explizite GUC-Konfiguration, während letzterer dies nicht tut?
Der Ausschlussmechanismus war die Partitionierungsmethode, die vor der deklarativen Partitionierung verwendet wurde, und sich auf CHECK-Bedingungen in Kindtabellen stützte, um nachzuweisen, dass Tabellen keine relevanten Zeilen enthalten konnten. Da die Evaluierung dieser Bedingungen gegenüber jeder vererbten Tabelle während der Planung teuer ist, wenn Hunderte von Tabellen existieren, wird sie über den Parameter constraint_exclusion gesteuert, der standardmäßig auf partition gesetzt ist (nur prüfen, wenn über Vererbung abgefragt wird). Native Partitionierung verwendet spezialisierte Datenstrukturen im Planer, die die Partitionierungshierarchie direkt verstehen, was sie schneller macht und immer aktiviert ist, ohne dass eine GUC-Anpassung für eine korrekte Funktion erforderlich ist.
Warum führt PostgreSQL intern ein DELETE und INSERT aus, anstatt ein In-Place-Update vorzunehmen, wenn eine Zeile zwischen Partitionen verschoben wird, indem der Partition-Schlüssel geändert wird, und welche Auswirkungen hat das auf Trigger?
Da jede Partition eine separate Heap-Beziehung mit separatem physischem Speicher ist, erfordert das Ändern des Partition-Schlüssels das Verschieben des Tupels von einer Datei in eine andere. PostgreSQL implementiert diesen Übergang, indem es die Zeile aus der Quellpartition löscht und in die Zielpartition einfügt. Dies bedeutet, dass zeilenbasierte BEFORE DELETE, AFTER DELETE, BEFORE INSERT und AFTER INSERT-Trigger während dessen feuern, was wie eine einzelne UPDATE-Operation erscheint. Darüber hinaus streamt die logische Replikation dies als zwei separate WAL-Einträge (Löschen und Einfügen), was auf Abonnenten zu Konflikten führen kann, wenn die Replikationsidentität nicht ordnungsgemäß konfiguriert ist.
Welke spezifischen Sperr- und Validierungsüberkopf treten auf, wenn eine neue Partition an eine Tabelle angefügt wird, die eine DEFAULT-Partition hat, und wie kann man einen vollständigen Scan der Standardpartition vermeiden?
Wenn eine neue Partition an eine nach Bereich oder Liste partitionierte Tabelle mit einer DEFAULT-Partition angefügt wird, muss PostgreSQL die gesamte DEFAULT-Partition scannen, um zu überprüfen, dass keine Zeilen existieren, die in die neue, spezifischere Partition gehören sollten. Dieser Validierungs-Scan erwirbt eine ACCESS EXCLUSIVE-Sperre auf der partitionierten Tabelle und kann für große Standardpartitionen Stunden in Anspruch nehmen. Um dies zu vermeiden, sollte man die DEFAULT-Partition vor dem Anfügen der neuen Partition abtrennen und dann die DEFAULT-Partition erst wieder anfügen, nachdem sichergestellt wurde, dass keine widersprüchlichen Zeilen existieren, oder alternativ, CREATE TABLE ... PARTITION OF verwenden, um eine neue leere Partition zu erstellen und Daten mit INSERT ... SELECT unter Verwendung einer WHERE-Klausel zu migrieren, die den spezifischen Partitionbereich filtert, um den Validierungs-Scan des Inhalts der Standardpartition zu umgehen.