Verwalten von PostgreSQL-Partitionen mit der Erweiterung pg_partman

Die PostgreSQL-Tabellenpartitionierung bietet ein Framework für den leistungsstarken Umgang mit Dateneingaben und Berichten. Verwenden Sie die Partitionierung für Datenbanken, die eine sehr schnelle Eingabe großer Datenmengen erfordern. Die Partitionierung ermöglicht auch schnellere Abfragen großer Tabellen. Die Partitionierung hilft bei der Verwaltung von Daten, ohne die Datenbank-Instance zu beeinträchtigen, da sie weniger I/O-Ressourcen benötigt.

Durch die Partitionierung können Sie Daten zur Verarbeitung in benutzerdefinierte Blöcke aufteilen. Sie können beispielsweise Zeitreihendaten für Bereiche wie stündlich, täglich, wöchentlich, monatlich, vierteljährlich, jährlich, benutzerdefiniert oder eine beliebige Kombination davon partitionieren. Wenn Sie für ein Beispiel für Zeitreihendaten die Tabelle nach Stunden partitionieren, enthält jede Partition die Daten einer Stunde. Wenn Sie die Zeitreihentabelle nach Tag partitionieren, enthalten die Partitionen die Daten eines Tages und so weiter. Der Partitionsschlüssel steuert die Größe einer Partition.

Wenn Sie den SQL-Befehl INSERT oder UPDATE für eine partitionierte Tabelle verwenden, leitet die Datenbank-Engine die Daten an die entsprechende Partition weiter. PostgreSQL-Tabellenpartitionen, die die Daten speichern, sind untergeordnete Tabellen der Haupttabelle.

Während der Lesevorgänge der Datenbankabfrage untersucht der PostgreSQL-Optimierer die WHERE-Klausel der Abfrage und leitet den Datenbank-Scan nach Möglichkeit nur an die relevanten Partitionen weiter.

Beginnend mit Version 10 verwendet PostgreSQL deklarative Partitionierung, um die Tabellenpartitionierung zu implementieren. Dies wird auch als native PostgreSQL-Partitionierung bezeichnet. Vor PostgreSQL Version 10 wurden Auslöser verwendet, um Partitionen zu implementieren.

Die PostgreSQL-Tabellenpartitionierung bietet die folgenden Funktionen:

• Erstellung neuer Partitionen zu jeder Zeit.

• Variable Partitionsbereiche.

• Entfernbare und wiederverwendbare Partitionen mit DDL-Anweisungen (Data Definition Language).

  Zum Beispiel sind entfernbare Partitionen nützlich, um Verlaufsdaten aus der Hauptpartition zu entfernen, aber Verlaufsdaten für die Analyse zu behalten.

• Neue Partitionen erben die Eigenschaften der übergeordneten Datenbanktabelle, einschließlich folgender Eigenschaften:

  • Indizes

  • Primärschlüssel, die die Partitionsschlüsselspalte enthalten müssen

  • Fremdschlüssel

  • Einschränkungen prüfen

  • Referenzen

• Erstellen von Indizes für die vollständige Tabelle oder jede spezifische Partition.

Sie können das Schema für eine einzelne Partition nicht ändern. Sie können jedoch die übergeordnete Tabelle ändern (z. B. das Hinzufügen einer neuen Spalte), die auf Partitionen übertragen wird.

Übersicht über die PostgreSQL-Erweiterung pg_partman

Sie können die PostgreSQL-Erweiterung pg_partman verwenden, um die Erstellung und Pflege von Tabellenpartitionen zu automatisieren. Weitere allgemeine Informationen finden Sie unter PG-Partitions-Manager in der pg_partman-Dokumentation.

Anmerkung

Die Erweiterung pg_partman wird auf den RDS-for-PostgreSQL-Versionen 12.5 und höher unterstützt.

Anstatt jede Partition manuell erstellen zu müssen, konfigurieren Sie pg_partman mit den folgenden Einstellungen:

• Zu partitionierende Tabelle

• Partitionstyp

• Partitionsschlüssel

• Granularität der Partition

• Optionen für die Erstellung und Verwaltung von Partitionen

Nachdem Sie eine partitionierte PostgreSQL-Tabelle erstellt haben, registrieren Sie diese mit pg_partman, indem Sie die Funktion create_parent aufrufen. Dadurch werden die erforderlichen Partitionen basierend auf den Parametern erstellt, die Sie an die Funktion übergeben.

Die Erweiterung pg_partman bietet auch die Funktion run_maintenance_proc, die Sie planmäßig aufrufen können, um Partitionen automatisch zu verwalten. Planen Sie, dass diese Funktion regelmäßig (z. B. stündlich) ausgeführt wird, um sicherzustellen, dass die richtigen Partitionen nach Bedarf erstellt werden. Sie können auch sicherstellen, dass Partitionen automatisch gelöscht werden.

Aktivieren der Erweiterung pg_partman

Wenn Sie mehrere Datenbanken innerhalb derselben PostgreSQL-DB-Instance haben, für die Sie Partitionen verwalten möchten, aktivieren Sie die Erweiterung pg_partman für jede Datenbank separat. Um die Erweiterung pg_partman für eine bestimmte Datenbank zu aktivieren, erstellen Sie das Partitionswartungsschema und dann die Erweiterung pg_partman wie folgt:

CREATE SCHEMA partman;
CREATE EXTENSION pg_partman WITH SCHEMA partman;

Anmerkung

Um die Erweiterung pg_partman zu erstellen, müssen Sie sicherstellen, dass Sie über rds_superuser-Berechtigungen verfügen.

Wenn Sie einen Fehler wie den folgenden erhalten, erteilen Sie dem Konto die Berechtigungen rds_superuser oder verwenden Sie Ihr Superuser-Konto.

ERROR: permission denied to create extension "pg_partman"
HINT: Must be superuser to create this extension.

Um die Berechtigungen rds_superuser zu erteilen, verbinden Sie sich mit Ihrem Superuser-Konto und führen Sie den folgenden Befehl aus.

GRANT rds_superuser TO user-or-role;

Für die Beispiele, die die Verwendung der Erweiterung pg_partman zeigen, verwenden wir die folgende Beispieldatenbanktabelle und Partition. Diese Datenbank verwendet eine partitionierte Tabelle basierend auf einem Zeitstempel. Ein data_mart-Schema enthält eine Tabelle mit dem Namen events mit einer Spalte namens created_at. Die folgenden Einstellungen sind in der events-Tabelle enthalten:

• Primärschlüssel event_id und created_at, die die Spalte zur Führung der Partition verwenden müssen.

• Eine CHECK-Beschränkung ck_valid_operation zum Durchsetzen von Werten für eine operation-Tabellenspalte.

• Zwei Fremdschlüssel, wobei einer fk_orga_membership) auf die externe Tabelle organization verweist und der andere (fk_parent_event_id) ein selbst referenzierter Fremdschlüssel ist.

• Zwei Indizes, wobei einer (idx_org_id) für den Fremdschlüssel und der andere (idx_event_type) für den Ereignistyp steht.

Die folgenden DDL-Anweisungen erstellen diese Objekte, die automatisch auf jeder Partition enthalten sind.

CREATE SCHEMA data_mart;
CREATE TABLE data_mart.organization ( org_id BIGSERIAL,
        org_name TEXT,
        CONSTRAINT pk_organization PRIMARY KEY (org_id)  
    );

CREATE TABLE data_mart.events(
        event_id        BIGSERIAL, 
        operation       CHAR(1), 
        value           FLOAT(24), 
        parent_event_id BIGINT, 
        event_type      VARCHAR(25), 
        org_id          BIGSERIAL, 
        created_at      timestamp, 
        CONSTRAINT pk_data_mart_event PRIMARY KEY (event_id, created_at), 
        CONSTRAINT ck_valid_operation CHECK (operation = 'C' OR operation = 'D'), 
        CONSTRAINT fk_orga_membership 
            FOREIGN KEY(org_id) 
            REFERENCES data_mart.organization (org_id),
        CONSTRAINT fk_parent_event_id 
            FOREIGN KEY(parent_event_id, created_at) 
            REFERENCES data_mart.events (event_id,created_at)
    ) PARTITION BY RANGE (created_at);

CREATE INDEX idx_org_id     ON  data_mart.events(org_id);
CREATE INDEX idx_event_type ON  data_mart.events(event_type);

Konfigurieren von Partitionen mit der create_parent-Funktion

Nachdem Sie die Erweiterung pg_partman aktiviert haben, verwenden Sie die create_parent-Funktion, um Partitionen innerhalb des Partitionswartungsschemas zu konfigurieren. Im folgenden Beispiel wird das events-Tabellenbeispiel verwendet, das in Aktivieren der Erweiterung pg_partman erstellt wurde. Rufen Sie die create_parent-Funktion wie folgt auf.

SELECT partman.create_parent( p_parent_table => 'data_mart.events',
 p_control => 'created_at',
 p_type => 'native',
 p_interval=> 'daily',
 p_premake => 30);

Dabei werden die folgenden Parameter verwendet:

• p_parent_table – Die übergeordnete partitionierte Tabelle. Diese Tabelle muss bereits existieren und einschließlich des Schemas vollständig qualifiziert sein.

• p_control – Die Spalte, auf der die Partitionierung basieren soll. Der Datentyp muss ganzzahlig oder zeitbasiert sein.

• p_type – Der Typ ist entweder 'native' oder 'partman'. In der Regel verwenden Sie den native Typ für seine Leistungsverbesserungen und Flexibilität. Der partman-Typ beruht auf Vererbung.

• p_interval – Das Zeitintervall oder der Ganzzahlbereich für jede Partition. Beispielwerte sind daily, stündlich und so weiter.

• p_premake – Die Anzahl der Partitionen, die im Voraus erstellt werden müssen, um neue Inserts zu unterstützen.

Eine vollständige Beschreibung der Funktion create_parent finden Sie in der pg_partman-Dokumentation unter Creation Functions (Erstellungsfunktionen).

Konfigurieren der Partitionspflege mit der run_maintenance_proc-Funktion

Sie können Partitionswartungsvorgänge ausführen, um automatisch neue Partitionen zu erstellen, Partitionen zu trennen oder alte Partitionen zu entfernen. Die Partitionspflege beruht auf der Funktion run_maintenance_proc von der pg_partman-Erweiterung und der Erweiterung pg_cron, die einen internen Scheduler initiiert. Der pg_cron-Scheduler führt automatisch SQL-Anweisungen, -Funktionen und -Prozesse aus, die in Ihren Datenbanken definiert sind.

Im folgenden Beispiel wird das events-Tabellenbeispiel verwendet, das in Aktivieren der Erweiterung pg_partman erstellt wurde, um die automatische Ausführung der Partitionswartung festzulegen. Fügen Sie als Voraussetzung pg_cron zum Parameter shared_preload_libraries in der Parametergruppe der DB-Instance hinzu.

CREATE EXTENSION pg_cron;

UPDATE partman.part_config 
SET infinite_time_partitions = true,
    retention = '3 months', 
    retention_keep_table=true 
WHERE parent_table = 'data_mart.events';
SELECT cron.schedule('@hourly', $$CALL partman.run_maintenance_proc()$$);

Nachfolgend finden Sie eine Schritt-für-Schritt-Erklärung für das vorangehende Beispiel:

1. Ändern Sie die mit Ihrer DB-Instance verknüpfte Parametergruppe und fügen Sie pg_cron dem shared_preload_libraries-Parameterwert hinzu. Diese Änderung erfordert einen Neustart der DB-Instance, damit sie wirksam wird. Weitere Informationen finden Sie unter Ändern von Parametern in einer DB-Parametergruppe.

2. Führen Sie den Befehl CREATE EXTENSION pg_cron; mit einem Konto aus, das die rds_superuser-Berechtigungen besitzt. Dadurch wird die Erweiterung pg_cron aktiviert. Weitere Informationen finden Sie unter Planen der Wartung mit der PostgreSQL-Erweiterung pg_cron.

3. Führen Sie den Befehl UPDATE partman.part_config aus, um die pg_partman-Einstellungen für die Tabelle data_mart.events anzupassen.

4. Führen Sie den Befehl aus SET . . . um die data_mart.events-Tabelle mit den folgenden Klauseln zu konfigurieren:

   1. infinite_time_partitions = true, – Konfiguriert die Tabelle so, dass automatisch neue Partitionen ohne Begrenzung erstellt werden können.

   2. retention = '3 months', – Konfiguriert die Tabelle so, dass sie eine maximale Beibehaltung von drei Monaten hat.

   3. retention_keep_table=true – Konfiguriert die Tabelle so, dass die Tabelle bei Fälligkeit der Aufbewahrungsfrist nicht automatisch gelöscht wird. Stattdessen werden Partitionen, die älter als die Aufbewahrungsfrist sind, nur von der übergeordneten Tabelle getrennt.

5. Führen Sie den Befehl aus SELECT cron.schedule . . . um einen pg_cron-Funktionsaufruf zu machen. Dieser Aufruf definiert, wie oft der Scheduler das pg_partman-Wartungsverfahren partman.run_maintenance_proc ausführt. In diesem Beispiel wird der Prozess stündlich ausgeführt.

Eine vollständige Beschreibung der run_maintenance_proc-Funktion finden Sie in der pg_partman-Dokumentation unter Maintenance Functions (Wartungsfunktionen).