Verständnis und Implementierung deklarativer Tabellenpartitionierung in PostgreSQL 14+

Die PostgreSQL-Partitionierung ist eine Überlegung wert, wenn eine Tabelle umständlich zu durchsuchen, zu vakuumieren, zu archivieren oder daraus zu löschen ist. Das übliche Beispiel ist eine Ereignistabelle, die Millionen von Zeilen pro Tag erhält und fast immer nach Zeitbereichen abgefragt wird. Ohne Partitionierung können selbst gute Indizes zu einer Tabelle führen, die teuer zu warten und mühsam zu bereinigen ist.

Die deklarative Partitionierung ermöglicht es einer logischen Tabelle, Zeilen in kleinere physische Tabellen, sogenannte Partitionen, umzuleiten. PostgreSQL 10 führte die native Syntax ein, und spätere Versionen verbesserten das Planungs-, Bereinigungs-, Indexierungs- und Wartungsverhalten. PostgreSQL 14+ ist ausgereift genug, dass viele Teams Partitionierung ohne triggerbasierte Vererbungsschemata verwenden können, aber es belohnt dennoch ein sorgfältiges Design. Ein schlechter Partitionsschlüssel kann das System komplizierter machen, ohne es schneller zu machen.

Was ist deklarative Tabellenpartitionierung?

Die deklarative Partitionierung ist eine Datenbankfunktion, mit der Sie eine einzelne logische Tabelle (die übergeordnete oder partitionierte Tabelle) basierend auf einem definierten Satz von Regeln in mehrere physische Tabellen (untergeordnete oder Partitionstabellen) aufteilen können. Jede Partition enthält eine Teilmenge der Daten aus der übergeordneten Tabelle. Der Partitionsschlüssel bestimmt, zu welcher Partition eine Zeile gehört.

Zu den wichtigsten Vorteilen der deklarativen Partitionierung gehören:

• Verbesserte Abfrageleistung: Abfragen, die nach dem Partitionsschlüssel filtern, können schneller sein, da PostgreSQL Partitionen entfernen kann, die keine übereinstimmenden Zeilen enthalten können.
• Einfachere Datenverwaltung: Vorgänge wie das Löschen alter Daten oder das Archivieren können viel effizienter durchgeführt werden, indem einzelne Partitionen getrennt oder gelöscht werden, anstatt massive DELETE-Operationen für eine einzelne große Tabelle durchzuführen.
• Vereinfachte Wartung: Indizierung und Vakuumierung können auf Partitionsebene verwaltet werden, wodurch die Auswirkungen auf die gesamte Tabelle reduziert werden.
• Kleinere Wartungseinheiten: Indexierung auf Partitionsebene, Trennungsvorgänge und gezielte Vakuumarbeiten können den Umfang routinemäßiger Wartungsarbeiten reduzieren.

Arten der deklarativen Partitionierung

PostgreSQL unterstützt mehrere Methoden für die deklarative Partitionierung, die jeweils für unterschiedliche Datenverteilungsmuster geeignet sind:

1. Bereichspartitionierung

Die Bereichspartitionierung unterteilt Daten basierend auf einem kontinuierlichen Wertebereich in einer bestimmten Spalte (z. B. Daten, Zahlen).

Anwendungsfall: Ideal für Zeitreihendaten wie Protokolle, Ereignisdaten oder Verkaufsaufzeichnungen, bei denen Sie häufig Daten innerhalb bestimmter Datums- oder Zahlenbereiche abfragen.

Beispiel: Partitionieren einer sales-Tabelle nach der Spalte sale_date.

Erstellen einer bereichspartitionierten Tabelle

Erstellen Sie zunächst die übergeordnete Tabelle und geben Sie die Partitionierungsmethode und den Schlüssel an:

CREATE TABLE sales (
    sale_id SERIAL,
    product_name VARCHAR(100),
    sale_amount NUMERIC(10, 2),
    sale_date DATE NOT NULL
)
PARTITION BY RANGE (sale_date);

Erstellen Sie als nächstes die einzelnen Partitionen. Jede Partition wird mit einer FOR VALUES-Klausel definiert, die den Bereich angibt, den sie enthalten wird.

-- Partition für Verkäufe im Januar 2023.
-- Die Obergrenze ist exklusiv, daher ist der 31. Januar enthalten.
CREATE TABLE sales_2023_01
    PARTITION OF sales ()
    FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');

-- Partition für Verkäufe im Februar 2023
CREATE TABLE sales_2023_02
    PARTITION OF sales ()
    FOR VALUES FROM ('2023-02-01') TO ('2023-03-01');

-- Partition für Verkäufe im März 2023
CREATE TABLE sales_2023_03
    PARTITION OF sales ()
    FOR VALUES FROM ('2023-03-01') TO ('2023-04-01');

Tipp: Stellen Sie bei der Definition von Bereichen sicher, dass diese zusammenhängend sind und alle möglichen Werte abdecken. Vermeiden Sie überlappende Bereiche. Der TO-Wert ist exklusiv.

2. Listenpartitionierung

Die Listenpartitionierung unterteilt Daten basierend auf einer diskreten Liste von Werten in einer Spalte.

Anwendungsfall: Geeignet für Spalten mit einem festen, bekannten Satz von Werten, wie geografische Regionen, Statuscodes oder Produktkategorien.

Beispiel: Partitionieren einer orders-Tabelle nach der Spalte region.

Erstellen einer listenpartitionierten Tabelle

Definieren Sie die übergeordnete Tabelle mit PARTITION BY LIST:

CREATE TABLE orders (
    order_id SERIAL,
    customer_name VARCHAR(100),
    order_total NUMERIC(10, 2),
    region VARCHAR(50) NOT NULL
)
PARTITION BY LIST (region);

Erstellen Sie Partitionen für bestimmte Regionen:

-- Partition für Bestellungen aus 'Nordamerika'
CREATE TABLE orders_north_america
    PARTITION OF orders ()
    FOR VALUES IN ('North America');

-- Partition für Bestellungen aus 'Europa'
CREATE TABLE orders_europe
    PARTITION OF orders ()
    FOR VALUES IN ('Europe');

-- Partition für Bestellungen aus 'Asien'
CREATE TABLE orders_asia
    PARTITION OF orders ()
    FOR VALUES IN ('Asia');

Wichtig: Wenn Sie einen Wert für region einfügen, der keiner vorhandenen IN-Liste einer Partition entspricht und es keine DEFAULT-Partition gibt, schlägt das Einfügen fehl. Sie können eine DEFAULT-Partition erstellen, um alle anderen Werte abzufangen.

Erstellen einer Standardpartition

-- Standardpartition für jede nicht explizit aufgeführte Region
CREATE TABLE orders_other
    PARTITION OF orders ()
    DEFAULT;

3. Hash-Partitionierung

Die Hash-Partitionierung verteilt Daten basierend auf einem Hash-Wert des Partitionsschlüssels auf eine Anzahl von Partitionen.

Anwendungsfall: Nützlich, wenn Sie eine große Datenmenge haben und diese ohne klare Bereichs- oder listenbasierte Verteilung gleichmäßig auf Partitionen verteilen möchten. Es eignet sich gut für den Lastausgleich.

Beispiel: Partitionieren einer users-Tabelle nach user_id.

Erstellen einer hash-partitionierten Tabelle

Definieren Sie die übergeordnete Tabelle mit PARTITION BY HASH und geben Sie die Anzahl der Partitionen an:

CREATE TABLE users (
    user_id BIGSERIAL,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100)
)
PARTITION BY HASH (user_id);

PostgreSQL erstellt automatisch Partitionen für Sie, wenn Sie sie nicht angeben, aber es wird allgemein empfohlen, sie explizit zu erstellen, insbesondere wenn Sie die Kontrolle über die Anzahl und Benennung der Partitionen wünschen.

Erstellen expliziter Hash-Partitionen

-- Erstellen Sie 4 Hash-Partitionen
CREATE TABLE users_p0
    PARTITION OF users
    FOR VALUES WITH (modulus 4, remainder 0);

CREATE TABLE users_p1
    PARTITION OF users
    FOR VALUES WITH (modulus 4, remainder 1);

CREATE TABLE users_p2
    PARTITION OF users
    FOR VALUES WITH (modulus 4, remainder 2);

CREATE TABLE users_p3
    PARTITION OF users
    FOR VALUES WITH (modulus 4, remainder 3);

Hinweis: Bei Verwendung der Hash-Partitionierung müssen Sie den modulus (die Gesamtzahl der Partitionen) und den remainder (welche Partition dies ist) angeben.

Implementierung der deklarativen Partitionierung: Best Practices

• Wählen Sie den richtigen Partitionsschlüssel: Der Partitionsschlüssel sollte mit Ihren häufigsten Abfragefiltern und Datenverwaltungsvorgängen übereinstimmen. Ein guter Schlüssel verbessert die Leistung erheblich.
• Berücksichtigen Sie die Anzahl der Partitionen: Zu wenige Partitionen bieten möglicherweise nicht genügend Vorteile, während zu viele den Overhead erhöhen können. Streben Sie eine Anzahl an, die Verwaltbarkeit und Leistung in Einklang bringt. Berücksichtigen Sie bei der Bereichspartitionierung Ihre Datenwachstumsrate und Aufbewahrungsrichtlinien.
• Verwenden Sie pg_partman zur Automatisierung: Erwägen Sie für die Bereichspartitionierung, insbesondere bei Zeitreihendaten, die Verwendung von Erweiterungen wie pg_partman. Es automatisiert die Erstellung neuer Partitionen und die Archivierung/Löschung alter Partitionen, wodurch der manuelle Aufwand erheblich reduziert wird.
• Strategisch indizieren: Indizes werden physisch pro Partition gespeichert. Das Erstellen eines Index für die übergeordnete Tabelle erstellt übereinstimmende Partitionsindizes, aber Sie sollten dennoch prüfen, ob jede Partition dasselbe Indexmuster benötigt.
• Partitionsbereinigung: Stellen Sie sicher, dass Ihre Abfragen so geschrieben sind, dass sie die Partitionsbereinigung nutzen, indem Sie den Partitionsschlüssel in WHERE-Klauseln einbeziehen. Der Befehl EXPLAIN kann anzeigen, ob eine Bereinigung stattfindet.
• DEFAULT-Partitionen: Für die Listenpartitionierung ist eine DEFAULT-Partition entscheidend, um Einfügefehler zu vermeiden, wenn unerwartet neue Werte auftauchen.
• Eindeutige Einschränkungen: Eine eindeutige Einschränkung oder ein Primärschlüssel für eine partitionierte Tabelle muss im Allgemeinen alle Partitionsschlüsselspalten enthalten. Dies überrascht viele erstmalige Designer.
• Datentypen: Stellen Sie sicher, dass der Datentyp des Partitionsschlüssels geeignet und in der übergeordneten und den untergeordneten Tabellen konsistent ist.

Verwalten von Partitionen

Anhängen und Trennen von Partitionen

Während Partitionen direkt über CREATE TABLE ... PARTITION OF ... erstellt werden, können Sie auch vorhandene Tabellen als Partitionen trennen und anhängen. Dies ist nützlich für die Migration von Daten oder die Verwaltung großer Datensätze.

Trennen einer Partition: Das Trennen wandelt die Partition in eine reguläre Tabelle um, während ihre Daten erhalten bleiben.

-- Trennen Sie die Partition sales_2023_01
ALTER TABLE sales DETACH PARTITION sales_2023_01;

Anhängen einer Tabelle als Partition: Sie können eine reguläre Tabelle anhängen, die dem Schema der übergeordneten Tabelle entspricht und Daten enthält, die in die Partitionsgrenzen passen.

-- Angenommen, sales_2022_12 ist eine reguläre Tabelle mit denselben Spalten wie sales
-- und nur Zeilen aus Dezember 2022.
ALTER TABLE sales ATTACH PARTITION sales_2022_12
    FOR VALUES FROM ('2022-12-01') TO ('2023-01-01');

Fügen Sie vor dem Anhängen einer großen Tabelle zuerst eine passende CHECK-Einschränkung hinzu. PostgreSQL kann diese Einschränkung verwenden, um das Scannen der gesamten Tabelle zu vermeiden, um zu beweisen, dass die Zeilen in die Partitionsgrenzen passen.

Löschen von Partitionen

Das Löschen einer Partition ist ein schneller Vorgang, da es nur die Partitionstabelle entfernt, nicht die darin enthaltenen Daten (sofern nicht explizit angegeben). Dies ist viel schneller als DELETE.

-- Um eine Partition zu löschen, können Sie einfach die untergeordnete Tabelle löschen
DROP TABLE sales_2023_01;

Beispiel: Verbesserung der Abfrageleistung mit Partitionsbereinigung

Betrachten Sie die sales-Tabelle, die wie zuvor gezeigt nach sale_date partitioniert ist.

Abfrage ohne Partitionsbereinigung (hypothetisch für eine nicht partitionierte Tabelle):

SELECT SUM(sale_amount)
FROM sales
WHERE sale_date >= '2023-01-15' AND sale_date < '2023-01-20';

Wenn sales eine massive, nicht partitionierte Tabelle wäre, würde diese Abfrage die gesamte Tabelle scannen. Mit deklarativer Partitionierung jedoch:

-- Diese Abfrage scannt nur die Partition sales_2023_01
SELECT SUM(sale_amount)
FROM sales
WHERE sale_date >= '2023-01-15' AND sale_date < '2023-01-20';

Der Abfrageplaner von PostgreSQL erkennt, dass sale_date der Partitionsschlüssel ist und dass der angegebene Bereich vollständig innerhalb der Partition sales_2023_01 liegt. Er wird daher nur diese Partition scannen, was die E/A drastisch reduziert und die Leistung verbessert.

Um dies zu überprüfen, verwenden Sie EXPLAIN:

EXPLAIN SELECT SUM(sale_amount) FROM sales WHERE sale_date >= '2023-01-15' AND sale_date < '2023-01-20';

Die Ausgabe sollte nur die relevante Partition anzeigen, oder sie kann je nach PostgreSQL-Version und Planform entfernte Unterpläne anzeigen. Das wichtige Zeichen ist, dass nicht verwandte Partitionen nicht gescannt werden.

Eine praktische Design-Checkliste

Partitionieren Sie nur, wenn Sie den betrieblichen Vorteil benennen können. "Die Tabelle ist groß" ist allein nicht genug. Eine große Tabelle mit gut indizierten Punktabfragen kann in Ordnung sein. Die Partitionierung ist sinnvoller, wenn die meisten Abfragen den Partitionsschlüssel enthalten, wenn alte Daten regelmäßig archiviert oder gelöscht werden oder wenn die Wartung einer einzigen großen Tabelle bereits Schmerzen verursacht.

Wählen Sie für Zeitreihentabellen Partitionsgrößen, die Ihren Abfrage- und Aufbewahrungsmustern entsprechen. Tägliche Partitionen sind nützlich für sehr hohe Aufnahmeraten und kurze Aufbewahrungszeiten. Monatliche Partitionen sind bei moderatem Ereignisvolumen oft einfacher zu verwalten. Zu viele winzige Partitionen können die Planung verlangsamen und die Wartung laut machen; zu wenige riesige Partitionen lösen möglicherweise nicht das ursprüngliche Problem.

Planen Sie Einfügungen, bevor Sie ausliefern. Wenn Zeilen verspätet eintreffen können, halten Sie ältere Partitionen lange genug verfügbar, um sie zu empfangen. Wenn der Partitionsschlüssel unerwartete Werte enthalten kann, erstellen Sie eine DEFAULT-Partition und überwachen Sie diese. Eine Standardpartition sollte ein Sicherheitsnetz sein, kein Ort, an dem vergessene Daten monatelang still vor sich hin sammeln.

Testen Sie schließlich mit echten Abfrageformen. Die Partitionsbereinigung funktioniert am besten, wenn die WHERE-Klausel den Partitionsschlüssel klar angibt, wie z. B. sale_date >= '2023-01-01' AND sale_date < '2023-02-01'. Das Verpacken des Schlüssels in Funktionen kann die Bereinigung erschweren:

-- Weniger bereinigungsfreundlich
WHERE date_trunc('month', sale_date) = DATE '2023-01-01';

-- Einfacher für den Planer
WHERE sale_date >= DATE '2023-01-01'
  AND sale_date <  DATE '2023-02-01';

Die deklarative Partitionierung ist ebenso ein Wartungswerkzeug wie ein Abfragewerkzeug. Gut eingesetzt macht sie alte Daten billig zu entfernen und heiße Daten leichter zu scannen. Leichtfertig eingesetzt fügt sie mehr Tabellen, mehr Indizes und mehr Randfälle hinzu. Beginnen Sie mit dem Zugriffsmuster, wählen Sie den Partitionsschlüssel aus diesem Muster und überprüfen Sie den Plan, bevor Sie das Design als abgeschlossen bezeichnen.

Planen Sie für eine vorhandene große Tabelle keine riskante einmalige Konvertierung während der Hauptverkehrszeit. Ein üblicher Migrationspfad besteht darin, eine neue partitionierte Tabelle zu erstellen, Daten in Blöcken zu kopieren, neue Schreibvorgänge über Anwendungslogik oder einen sorgfältig getesteten Trigger fließen zu lassen und dann die Namen während eines kurzen Wartungsfensters zu tauschen. Der genaue Ansatz hängt vom Schreibvolumen und der Ausfalltoleranz ab, aber das Prinzip ist dasselbe: Beweisen Sie die Kopie, beweisen Sie die Einschränkungen und proben Sie die Umstellung, bevor Sie die Produktion berühren.