PostgreSQL-Optimierung: Alles über effektive Tabellen-Partitionierung!

Die Partitionierung von Tabellen ist eine äußerst effektive Technik, um die Leistung von sehr großen Datenbanktabellen zu verbessern. Durch die Aufteilung des Inhalts der Tabelle in kleinere Unter-Tabellen, bekannt als Partitionen, wird die Gesamtgröße der Tabelle reduziert, was zu erheblichen Leistungsverbesserungen führt.

Wann sollten Sie eine Tabelle partitionieren?

• Bei Tabellen, die den RAM des Servers übersteigen: Wenn eine Tabelle einige Gigabyte erreicht, ist es ratsam, sie in kleinere Segmente zu unterteilen.
• Wenn Sie mit enormen Datenmengen arbeiten, ist es ratsam, die Partitionierung erst in Erwägung zu ziehen, wenn Sie Millionen von Datensätzen erreicht haben.
• Wenn Ihre Tabelle logisch in kleinere Blöcke unterteilt werden kann. Zum Beispiel bei Serverprotokollen könnten Sie diese nach Datum trennen, um das Löschen alter Einträge zu erleichtern.

Verfügbare Partitionierungstypen

PostgreSQL bietet eingebaute Unterstützung für drei Arten von Partitionierung.

Partition nach Bereich:

Hier wird die Tabelle in "Bereiche" unterteilt, die durch eine Schlüsselspalte oder eine Gruppe von Spalten definiert sind.

In diesem Beispiel wird die Tabelle mitarbeiter nach dem Eintrittsdatum (eintrittsdatum) partitioniert:

CREATE TABLE mitarbeiter (
 mitarbeiter_id int not null,
 eintrittsdatum date not null,
 abteilung_code character(3) not null,
 vorname text
) PARTITION BY RANGE (eintrittsdatum);

CREATE TABLE mitarbeiter_2010 PARTITION OF mitarbeiter
 FOR VALUES FROM ('2010-01-01') TO ('2011-01-01');

CREATE TABLE mitarbeiter_2011 PARTITION OF mitarbeiter
 FOR VALUES FROM ('2011-01-01') TO ('2012-01-01');

CREATE TABLE mitarbeiter_2012 PARTITION OF mitarbeiter
 FOR VALUES FROM ('2012-01-01') TO ('2013-01-01');

Probieren wir es aus:

INSERT INTO mitarbeiter (mitarbeiter_id, eintrittsdatum, abteilung_code, vorname) VALUES
 (101, '2010-05-05', 'HR', 'Lukas'),
 (102, '2010-09-09', 'IT', 'Mia'),
 (103, '2011-06-06', 'MK', 'Finn');
> INSERT 0 3

SELECT schemaname, relname, n_live_tup 
 FROM pg_stat_user_tables 
 ORDER BY n_live_tup DESC;

schemaname | relname           | n_live_tup 
-----------+-------------------+-----------
 public    | mitarbeiter_2010  | 2
 public    | mitarbeiter_2011  | 1
 public    | mitarbeiter_2012  | 0

Wie Sie sehen können, haben wir drei Datensätze in die Haupttabelle mitarbeiter eingefügt. Da die Tabelle nach eintrittsdatum partitioniert ist, wurden zwei Datensätze zur Partition mitarbeiter_2010 hinzugefügt, einer zu mitarbeiter_2011, während mitarbeiter_2012 noch leer ist.

Partition nach Liste:

Die Tabelle wird partitioniert, indem explizit aufgelistet wird, welche Schlüsselwerte in jeder Partition erscheinen.

Unter Verwendung des vorherigen Beispiels fügen wir eine Spalte abteilung_code hinzu und verwenden sie als Partitionierungsschlüssel:

CREATE TABLE mitarbeiter (
 mitarbeiter_id int not null,
 eintrittsdatum date not null,
 abteilung_code character(3) not null,
 vorname text
) PARTITION BY LIST (abteilung_code);

-- Partition für Mitarbeiter in der IT-Abteilung
CREATE TABLE mitarbeiter_IT PARTITION OF mitarbeiter
 FOR VALUES IN ('IT');

-- Partition für Mitarbeiter in der Marketing-Abteilung
CREATE TABLE mitarbeiter_MK PARTITION OF mitarbeiter
 FOR VALUES IN ('MK');

-- Partition für Mitarbeiter in der Personalabteilung
CREATE TABLE mitarbeiter_HR PARTITION OF mitarbeiter
 FOR VALUES IN ('HR');

Probieren wir es aus:

INSERT INTO mitarbeiter (mitarbeiter_id, eintrittsdatum, abteilung_code, vorname) VALUES
 (101, '2010-05-05', 'HR', 'Lukas'),
 (102, '2010-09-09', 'IT', 'Mia'),
 (103, '2011-06-06', 'MK', 'Finn');
> INSERT 0 3

SELECT schemaname, relname, n_live_tup 
 FROM pg_stat_user_tables 
 ORDER BY n_live_tup DESC;

schemaname | relname           | n_live_tup 
-----------+-------------------+-----------
 public    | mitarbeiter_IT    | 1
 public    | mitarbeiter_MK    | 1
 public    | mitarbeiter_HR    | 1

Wie Sie sehen können, hat PostgreSQL jede Zeile in die korrekte Partition verschoben.

Partition nach Hash:

Hier wird die Tabelle partitioniert, indem ein Modulus und ein Rest für jede Partition angegeben wird.

Dieser Typ ist nützlich, wenn wir unsere Daten nicht logisch unterteilen können, aber die Tabellengröße reduzieren möchten, indem wir Zeilen auf viele kleinere Partitionen verteilen.

Das folgende Beispiel teilt produkte in vier Tabellen auf, wobei jede Tabelle (fast) die gleiche Anzahl von Zeilen enthält:

CREATE TABLE produkte (
 produkt_id int not null,
 herstellungsdatum date not null,
 kategorie_code character(3) not null,
 produktname text
) PARTITION BY HASH (produkt_id);

CREATE TABLE produkte_1 PARTITION OF produkte
 FOR VALUES WITH (MODULUS 4, REMAINDER 0);

CREATE TABLE produkte_2 PARTITION OF produkte
 FOR VALUES WITH (MODULUS 4, REMAINDER 1);

CREATE TABLE produkte_3 PARTITION OF produkte
 FOR VALUES WITH (MODULUS 4, REMAINDER 2);

CREATE TABLE produkte_4 PARTITION OF produkte
 FOR VALUES WITH (MODULUS 4, REMAINDER 3);

Probieren wir es aus:

INSERT INTO produkte (produkt_id, herstellungsdatum, kategorie_code, produktname) VALUES
 (101, '2020-05-05', 'ELK', 'Laptop'),
 (102, '2020-09-09', 'MOB', 'Smartphone'),
 (103, '2021-06-06', 'BCH', 'Mixer');
> INSERT 0 3

SELECT schemaname, relname, n_live_tup 
 FROM pg_stat_user_tables 
 ORDER BY n_live_tup DESC;

schemaname | relname | n_live_tup 
------------+----------------+------------
 public | produkte_1 | 1
 public | produkte_2 | 1
 public | produkte_3 | 1
 public | produkte_4 | 0

Wie Sie sehen können, wurden die drei Datensätze gleichmäßig auf alle verfügbaren Partitionen verteilt.

Standardpartition in PostgreSQL

Was passiert, wenn Sie versuchen, einen Datensatz einzufügen, der in keine Partition passt? Hier kommt die "DEFAULT Partition" ins Spiel.

Lassen Sie uns zur Tabelle produkte zurückkehren, die im Kapitel "Partitionierung nach Liste" definiert wurde, und versuchen, ein Produkt namens "Kühlschrank" aus der Kategorie "HAU" hinzuzufügen:

INSERT INTO produkte (produkt_id, herstellungsdatum, kategorie_code, produktname) VALUES
 (104, '2021-07-07', 'HAU', 'Kühlschrank');

Das Einfügen wird fehlschlagen, weil PostgreSQL nicht weiß, wohin dieser Datensatz hinzugefügt werden soll:

FEHLER: Für die Zeile wurde keine Partition der Relation "produkte" gefunden. 
DETAILS: Der Partitionsschlüssel der fehlerhaften Zeile enthält (kategorie_code) = (HAU).

Die offensichtlichste Lösung wäre, eine neue Partition hinzuzufügen. Wenn wir dies jedoch für jede Produktkategorie tun müssten, würden wir am Ende mit Hunderten von Tabellen mit einer sehr geringen Anzahl von Datensätzen enden. Das ist nicht wirklich ideal.

Glücklicherweise ist es möglich, eine DEFAULT-Partition zu definieren!

CREATE TABLE produkte_default PARTITION OF produkte DEFAULT;

Wenn wir dieselben Einträge erneut vornehmen, ergibt sich folgendes:

INSERT INTO produkte (produkt_id, herstellungsdatum, kategorie_code, produktname) VALUES
 (101, '2020-05-05', 'ELK', 'Laptop'),
 (102, '2020-09-09', 'MOB', 'Smartphone'),
 (103, '2021-06-06', 'BCH', 'Mixer'),
 (104, '2021-07-07', 'HAU', 'Kühlschrank');
> INSERT 0 4

SELECT schemaname, relname, n_live_tup 
 FROM pg_stat_user_tables 
 ORDER BY n_live_tup DESC;

schemaname | relname | n_live_tup 
------------+-------------------+------------
 public | produkte_IT | 1
 public | produkte_MK | 1
 public | produkte_HR | 1
 public | produkte_default | 1

Wie Sie sehen können, wurde "Kühlschrank" nun zu produkte_default hinzugefügt.

Subpartitionierung

Es ist möglich, dass eine Partition selbst eine partitionierte Tabelle ist.

Angenommen, die mitarbeiter_IT-Partition wird viele Datensätze enthalten, da die IT-Abteilung in einem großen Unternehmen arbeitet. Daher möchten wir sie möglicherweise nach Hash unterteilen, basierend auf der mitarbeiter_id:

-- Erweiterung der bestehenden mitarbeiter_IT Partition um eine Subpartitionierung
ALTER TABLE mitarbeiter_IT PARTITION BY HASH (mitarbeiter_id);

CREATE TABLE mitarbeiter_IT_1 PARTITION OF mitarbeiter_IT
 FOR VALUES WITH (MODULUS 3, REMAINDER 0);

CREATE TABLE mitarbeiter_IT_2 PARTITION OF mitarbeiter_IT
 FOR VALUES WITH (MODULUS 3, REMAINDER 1);

CREATE TABLE mitarbeiter_IT_3 PARTITION OF mitarbeiter_IT
 FOR VALUES WITH (MODULUS 3, REMAINDER 2);

Probieren wir es aus:

INSERT INTO mitarbeiter (mitarbeiter_id, eintrittsdatum, abteilung_code, vorname) VALUES
 (104, '2012-07-07', 'IT', 'Anna'),
 (105, '2013-08-08', 'IT', 'Tom'),
 (106, '2014-09-09', 'IT', 'Lara');
> INSERT 0 3

SELECT schemaname, relname, n_live_tup 
 FROM pg_stat_user_tables 
 ORDER BY n_live_tup DESC;

schemaname | relname | n_live_tup 
------------+-------------------+------------
 public | mitarbeiter_IT_1 | 1
 public | mitarbeiter_IT_2 | 1
 public | mitarbeiter_IT_3 | 1
 public | mitarbeiter_MK | 1
 public | mitarbeiter_HR | 1

Wie Sie sehen können, wurden die neuen IT-Mitarbeiterdatensätze gleichmäßig auf die Subpartitionen verteilt.

Partitionsvorgänge

Anhängen und Abtrennen von Partitionen:

Mit den Befehlen DETACH und ATTACH können Partitionen von der Haupttabelle getrennt bzw. angehängt werden.

Wie wir gesehen haben, können einzelne Partitionen erstellt und gelöscht werden, wann immer wir wollen. Aber was, wenn wir einige Datensätze aus der Haupttabelle ausschließen möchten, ohne sie zu löschen?
Die Antwort lautet: DETACH

ALTER TABLE mitarbeiter DETACH PARTITION mitarbeiter_IT;

Eine abgetrennte Partition verhält sich wie eine normale Tabelle. Daher ist es möglich, Datensätze einzufügen, die die Partitionierungsbeschränkungen verletzen würden.

Die umgekehrte Operation, ATTACH, ist genauso einfach:

ALTER TABLE mitarbeiter ATTACH PARTITION mitarbeiter_IT FOR VALUES IN ('IT');

Nachdem die Partition mitarbeiter_IT wieder angehängt wurde, werden alle zukünftigen Einfügungen, die den Wert 'IT' für abteilung_code haben, wieder in diese Partition eingefügt.

Indexierung:

Leider erlaubt PostgreSQL nicht die Erstellung eines einzelnen Index, der jede Partition der Tabelle abdeckt. Jede Partition muss ihren eigenen Index haben.

Die schlechte Nachricht dabei ist, dass der Primärschlüssel oder ein anderer eindeutiger Index die Spalten enthalten muss, die in der Partitionierungsanweisung verwendet werden.

-- DIES WIRD NICHT FUNKTIONIEREN

CREATE UNIQUE INDEX idx_uniq ON mitarbeiter (mitarbeiter_id);
> ERROR: insufficient columns in UNIQUE constraint definition
> DETAILS: UNIQUE constraint on table "mitarbeiter" lacks column "abteilung_code" which is part of the partition key.


-- DIES FUNKTIONIERT!

CREATE UNIQUE INDEX idx_uniq ON mitarbeiter (mitarbeiter_id, abteilung_code);
> CREATE INDEX

Der Grund dafür ist, dass jede Partition als unabhängige Tabelle behandelt wird. Daher ist das Hinzufügen des Partitionsschlüssels zum Index der einzige Weg, die Eindeutigkeit eines Datensatzes in der gesamten Tabelle zu gewährleisten.

Beachten Sie, dass das Erstellen eines Index auf der Haupttabelle diesen automatisch auf jede angehängte Partition repliziert:

CREATE UNIQUE INDEX idx_uniq ON mitarbeiter (mitarbeiter_id, abteilung_code);

-- Überprüfen der erstellten Indizes

SELECT tablename, indexname FROM pg_indexes
WHERE schemaname = 'public' ORDER BY tablename, indexname;

tablename | indexname
----------------+-------------------------------------
mitarbeiter | idx_uniq
mitarbeiter_IT | mitarbeiter_IT_mitarbeiter_id_abteilung_code_idx
mitarbeiter_MK | mitarbeiter_MK_mitarbeiter_id_abteilung_code_idx
mitarbeiter_HR | mitarbeiter_HR_mitarbeiter_id_abteilung_code_idx

Schlussfolgerung

Die richtige Anwendung der Partitionierung in PostgreSQL kann erhebliche Leistungssteigerungen für große Datenbanken bieten. Es handelt sich dabei um ein mächtiges Werkzeug, das, wenn es richtig eingesetzt wird, die Effizienz und Geschwindigkeit Ihrer Datenbank erheblich verbessern kann. Denken Sie jedoch daran, dass die Partitionierung nur ein Aspekt der Datenbankoptimierung ist und am besten in Kombination mit anderen Best Practices eingesetzt wird.

Wenn Sie Fragen zur Optimierung Ihrer PostgreSQL-Datenbank haben oder Unterstützung bei der Implementierung der Partitionierung suchen, zögern Sie nicht, uns zu kontaktieren.

Unser Expertenteam steht Ihnen gerne zur Verfügung. Kontaktieren Sie uns hier.

Links zu relevanten Dokumentationen:

• PostgreSQL Partitionierung