Neben verschiedenen Indexstrategien bietet PostgreSQL ein weiteres Feature, um mit sehr großen Datenmengen umzugehen: Das Splitten von Tabellen mit Vererbung. Dieses Aufteilen der Daten einer Tabelle auf Tochtertabellen wird bei PostgreSQL „Partitionierung“ genannt.
Im ersten Teil wurden die Vor- und Nachteile dieser Variante zur Performanzsteigerung dargestellt und die Bedingungen für einen sinnvollen Einsatz beschrieben. Der nachfolgende zweite Teil dieses Artikels beschreibt die einzelnen Konfigurationsschritte für eine funktionierende Partitionierung. Der vorgestellte Weg wurde in einer PostgreSQL Version 9.2 Datenbank produktiv eingesetzt. Ein dritter Post zeigt die Besonderheiten in einem Projekt mit Hibernate.
Vorbereitung
Für eine erfolgreiche Partitionierung sind einige Arbeitsschritte auszuführen. Vorausgesetzt, dass es bereits die Master-Tabelle als Modell gibt, ist der erste und einfachste Schritt das Erstellen der Tochtertabellen. Diese lassen sich mit der Vererbungsfunktion von PostgreSQL durch das Schlüsselwort INHERITS im CREATE TABLE Statement erzeugen. Dabei können die verschiedenen Tabellen in unterschiedlichen Schemata liegen, bei einer geclusterten Datenbank sogar auf verschiedenen Laufwerken. Vorsicht: In der Konfiguration der Datenbank muss der Parameter „sql_inheritance“ mit „on“ (default-Wert) belegt sein!
Die Unterscheidungskriterien der Tochtertabellen werden mit dem Befehl CHECK(Bedingung) festgelegt. Beide Schritte lassen sich beim CREATE TABLE auch verknüpfen:
[code language=”sql” gutter=”false” highlight=”4,5,7″ title=”DDL-Schema für den ersten Schritt einer Partitionierung”]
CREATE TABLE child01
(
CONSTRAINT child01_pkey PRIMARY KEY (id),
CONSTRAINT child01_selektionsfeld _check CHECK
(selektionsfeld >= 0 AND selektionsfeld < 1000)
)
INHERITS (master);
[/code]
Check-Bedingungen
Die Selektionsbedingungen dürfen sich nicht überschneiden und müssen eindeutig sein. Lücken können später Probleme verursachen. Eine Kombination der Bedingungen CHECK(PLZ BETWEEN 10000 AND 20000) und CHECK(PLZ BETWEEN 20000 AND 30000) ist ein Fehler, weil nicht klar ist, wo Werte mit 20000 einsortiert werden sollten.
Neben den sogenannten Ranges, also von-bis-Bedingungen, sind auch Listen erlaubt. Beispiel: CHECK ( county IN ( 'Hamburg', 'Schleswig-Holstein', 'Niedersachsen')) und CHECK ( county IN ( 'Mecklenburg-Vorpommern', 'Brandenburg', 'Berlin'))…
Jede Tochtertabelle sollte zumindest auf dem Schlüsselfeld indiziert sein. Das ist zwar nicht zwingend, aber sinnvoll. Ansonsten sollten der Primary Key und die Indizes wie auf der Master-Tabelle vorgesehen angelegt werden.
Vorsicht ist bei Konvertierungen geboten, denn für die CHECK-Bedingungen gelten Regeln wie in WHERE-Clauses: Wenn der Feldtyp gemappt wird, also das indizierte Feld etwa vom Typ String ist, aber ein Integer-Vergleich gemacht wird (Beispiel: Wenn PLZ varchar und CHECK(PLZ::Integer >10000)), funktioniert der Optimierer nicht. Eventuell muss dann ein funktionaler Index benutzt werden. Dasselbe gilt für Konvertierungen mit “upper” und “lower”. Ein typischer Fehler ist auch der Vergleich von einem Datum in einem Textfeld.
Trigger und Funktionen
Vererbung alleine genügt nicht, die Partitionen über den Master zu füllen. Hierzu sind Funktionen notwendig, welche die Daten nach den CHECK-Bedingungen auf die Tabellen verteilen. Die Funktionen werden durch einen Trigger auf dem Master gesteuert. Voraussetzung hierfür ist die Installation des Moduls plpgsql für die prozedurale Programmiersprache PL/pgSQL.
[code language=”sql” gutter=”false” title=”Beispiel Einfügefunktion”]
CREATE OR REPLACE FUNCTION master_to_child_insert_trigger()
RETURNS TRIGGER AS $BODY$
BEGIN
IF (NEW.selektionsfeld >= 0
AND NEW.selektionsfeld < 1000)
THEN
INSERT INTO child01 VALUES (NEW.*);
ELSIF (NEW.selektionsfeld >= 1000
AND NEW.selektionsfeld < 2000)
THEN
INSERT INTO child02 VALUES (NEW.*);
ELSE
RAISE EXCEPTION ‘ selektionsfeld out of range!’;
END IF;
RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql;
[/code]
Ein “RETURN NULL” ist notwendig, damit der zurückgegebene Wert nicht zusätzlich in die Master-Tabelle eingefügt wird! Die Ausnahmebehandlung “RAISE EXCEPTION” für nicht vorgesehene Inserts sorgt dafür, dass nicht unbemerkt Daten verschwinden statt eingefügt zu werden. Eine andere Möglichkeit, Daten “verschwinden” zu lassen, ist ein Update auf das Selektionsfeld: Sollte sich dabei die Zuordnung zu einer Partition ändern, wird der Datensatz nicht verschoben, sondern verbleibt, dann falsch zugeordnet, in der Tochtertabelle. Hier hilft nur, den Datensatz zu löschen und anschließend erneut einzufügen.
Um die Funktion im richtigen Moment aufzurufen, ist ein Trigger notwendig.
[code language=”sql” gutter=”false” title=”Beispiel Trigger”]
CREATE TRIGGER master_trigger
BEFORE INSERT ON master
FOR EACH ROW EXECUTE PROCEDURE master_to_child_insert_trigger();
[/code]
Damit ist die Partitionierung fertig eingerichtet. Statt Triggern können auch Regeln angewandt werden: „CREATE RULE… AS ON INSERT TO… WHERE… DO INSTEAD INSERT INTO… VALUES(NEW.*)“. Diese haben aber mehr Overhead und werden möglicherweise bei COPY-Operationen ausgehebelt.
Zum Managen einer Partitionierung gibt es ein PostgreSQL-Modul, die PostgreSQL Partition Manager Extension „pg_partman“, wenn fortlaufend nach Zeit oder ID partitioniert werden soll.
Fazit
Für viele Anwendungsfälle mit großen Datenmengen bietet die Partitionierung einen praktikablen Ansatzpunkt zur Optimierung der Abfragen. Dabei muss aber beachtet werden, dass die Einrichtung aufwändig und fehleranfällig ist. Die Einführung muss also mit entsprechender Umsicht und gründlicher Fehlerkontrolle erfolgen. Daneben gibt es Einschränkungen, wie das intransparente Constraint-Verhalten und die Festlegung auf ein zentrales Suchkriterium, welche die Nutzbarkeit einschränken. Der Einsatz der Partitionierung sollte also gut abgewogen und von erfahrenen Datenbank-Experten betreut werden.