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. Da das Partitionieren einer Tabelle einigen Aufwand erfordert und es auch bei der Nutzung der Tabellen Einschränkungen gibt, gilt es beim Erstellen der Partitionen auf die spezifischen Anforderungen und Einschränkungen zu achten.

Dieser erste Teil des Artikels stellt die Vor- und Nachteile, sowie die Bedingungen für einen sinnvollen Einsatz der Partitionierung zur Performanzsteigerung dar. Im zweiten Teil des Artikels beschreiben wir die einzelnen Konfigurationsschritte für eine funktionierende Partitionierung. Ein dritter Post zeigt die Besonderheiten in einem Projekt mit Hibernate.

Motivation

Ein wichtiges Symptom, das die Partitionierung überlegenswert macht, ist eine schlechte Performanz trotz wirksamer Indizierung. Ein Hinweis, dass die Abfragegeschwindigkeit wegen der Größe einer Tabelle sinkt, gibt die Menge der Datensätze: Je nach Indexaufbau kann es bei fünf bis acht Millionen Zeilen kritisch werden. Sobald die Indizes selbst zu umfangreich werden, um schnell durchsucht werden zu können oder an die Grenzen des Arbeitsspeichers stoßen, müssen andere Strategien gesucht werden. Eine Möglichkeit ist, mit dem STATISTICS-Parameter den Speicherbedarf eines Index zu verringern. Ein zu kleiner STATISTICS-Wert (gobal mit default_statistics_target, besser je Spalte mit ALTER TABLE ... ALTER COLUMN ... SET STATISTICS) verringert aber den Effekt des Index. Bei einer partitionierten Tabelle geht ein Index dagegen immer nur über eine Partition, also einen Teil der Datensätze.

Bedingungen

Für eine erfolgreiche Partitionierung ist die wichtigste Voraussetzung, ein Merkmal zu finden, mit dem sich die Daten gut und gleichmäßig segmentieren lassen. Denn den größten Effekt hat die Verteilung, wenn keine der Tochtertabellen übermäßig groß wird. Das sollte sich nach Möglichkeit auch in Zukunft nicht ändern. So ist es zum Beispiel meist nicht sinnvoll, die Jahreszahl oder ein von-bis-Datum zu verwenden, da in den Folgejahren die Partitionierung immer wieder angepasst werden muss oder sich die Gewichtung stark verschiebt. Günstig kann dagegen eine Einteilung nach Monaten sein, wenn sich die Daten gut über das Jahr verteilen und in einem prognostizierbaren Zeitraum kein Monat nah an die 5 Millionen Datensätze-Grenze kommt.
Allerdings liefert die partitionierte Tabelle die effizienteste Suche, wenn das Partitionierungsmerkmal zugleich das Suchkriterium ist. Denn sollte das Abgrenzungskriterium nicht zugleich Suchkriterium sein, dann muss die Datenbank jede einzelne Partition nach Treffern durchsuchen. Deshalb sollte für ein optimales Ergebnis unbedingt ein Merkmal benutzt werden, nach dem sich die Daten sinnvoll durchsuchen lassen. Statt dem Zeitbezug kann dann ein regionaler oder organisatorischer Bezug für die Teilung der Daten sinnvoller sein. Auch eine Kombination von Merkmalen ist denkbar.

Neben der Gleichverteilung bietet sich die Häufigkeit des Zugriffs auf eine bestimmte Kategorie von Daten einer Tabelle als Selektionsmotiv an. Die am meisten gemeinsam abgefragten Daten in einer Partition zusammenzufassen erhöht automatisch die Trefferwahrscheinlichkeit, selbst wenn diese Partition größer als der Durchschnitt sein sollte. Es macht also Sinn, auch auf logische, beziehungsweise inhaltliche Zusammenhänge beim Setzen der Grenzen zwischen Partitionierungen zu achten.

Gewinne

Auch große Datenmengen bleiben handhabbar, wenn sie sich in kleine Häppchen aufteilen lassen. Der Autor hat selbst bei einem Join über Tabellen mit 30 und 8 Millionen Datensätzen Resultate im Bruchteil einer Sekunde erhalten. Ein weiterer Vorteil gerade bei PostgreSQL zeigt sich im Bulk-Import oder Bulk-Löschen: Eine Partition als Ganzes kann wesentlich schneller eingefügt oder gelöscht werden, als ein Delete mit dem entsprechenden Selektionskriterium. Beim Löschen entfällt zudem das VACUUM.

Vorbehalte

Eine wichtige Eigenschaft verliert eine partitionierte Tabelle: Constraints sind nicht mehr über die gesamte Datenmenge gültig. Insbesondere Foreign keys, die ausschließlich auf die Master-Tabelle führen könnten, funktionieren nicht mehr. Ein „Durchleiten“ von Bedingungen von der führenden, aber leeren Tabelle auf die Tochtertabellen, die die Daten enthalten, ist nicht möglich. Constraints, aber auch Indizes der Master-Tabelle werden nicht vererbt, diese müssen für jede Partition gesondert angelegt werden.
Damit der Optimierer trotzdem mit der Partitionierung arbeiten kann, darf der Parameter „constraint_exclusion“ nicht auf „off“ stehen. Der Default-Wert in der postgresql.conf ist „partition“.
Eine besondere Situation entsteht, wenn sich der Wert des Merkmals ändert, mit dem die Partitionierung gesteuert wird. Wenn derselbe Datensatz mit einem Feldwert, der auf eine andere Partitionierung weist, eingefügt wird, dann gibt es diesen Datensatz zwei Mal in der Tabelle, ohne dass eine Primärschlüsselverletzung angezeigt wird. Bei einem geänderten Merkmalwert, bzw. einem Verschieben von einer Partition zu einer anderen, muss der Datensatz explizit gelöscht und neu eingefügt werden.

Teil 2: Arbeitsschritte zum Erstellen einer Partition