Hash Partitioning in PostgreSQL - wann und warum?

Neben der mittlerweile fast klassischen Frage

"I've got an Oracle background and just installed PostgreSQL for the first time. How do I create a tablespace?"

begegnen uns im PostgreSQL-Telegram-Channel  in letzter Zeit auch gelegentlich Fragen nach Partitionierung, um genau zu sein Hash-Partitionierung.

Und ähnlich wie bei der Frage nach Tablespaces haben wir häufig den Eindruck, dass die FragestellerInnen nicht wirklich verstanden haben, was genau sie da tun, bzw. dass es eine Gewohnheitssache (von Oracle) ist...

Was bedeutet Partitionierung überhaupt?

Partitionierung generell heißt, eine Datenbank-Relation (sprich: eine Tabelle) in klar definierte Teile zu zerteilen. Das bringt durchaus Vorteile mit sich:

• die einzelnen Partitionen sind für sich genommen kleiner, ebenso deren Indizes; damit ist die Wahrscheinlichkeit größer, dass sie in RAM Platz finden
• wenn der "aktive" Teil der Daten relativ (zur Gesamtmenge) klein ist (Messwerte z.B., die in einem Dashboard für die letzten Stunden, ggfs. Tage angezeigt werden, über die aber nur einmal im Jahr eine Komplettauswertung gefahren wird), brauche ich nur für einen Teil der Daten Indizes zu pflegen und vorzuhalten (RAM), bei Anfragen müssen weniger Daten "angeschaut"/berücksichtigt zu werden und ich kann alte Daten auf billigeren Speicherbereich auslagern
• die Statistiken werden (auch) pro Partition vorgehalten, dem Planer stehen also potentiell detailliertere Informationen zur Verfügung
• wenn regelmäßig alte Teile der Daten gelöscht (schöner Euphemismus dafür: "archiviert") werden, ist dies durch wegwerfen ("DROP") einer Partition wesentlich einfacher und schneller zu bewirken als durch entsprechende "DELETE"-Kommandos

Faktisch ist der letzte genannte Grund der Hauptvorteil (einen weiteren gibt es noch, dazu später mehr).

Zur Partitionierung stehen uns die drei Mechanismen "RANGE", "LIST" und "HASH" zur Verfügung. "HASH" kam allerdings erst mit PostgreSQL 11 dazu, ist also quasi der Nesthaken.

Nehmen wir eine einfache Messdaten-Tabelle:

CREATE TABLE should_i_partition_this (
        fk_measurepoint BIGINT REFERENCES measurepoints,
        measuredate TIMESTAMPTZ NOT NULL DEFAULT now(),
        measure_value float NOT NULL
);

Wenn ich jetzt vorhersehe, dass ich sehr viele Messdaten erhalten werde, kann ich diese Kundentabelle von vornherein als partitionierte Tabelle anlegen (hier sinnvollerweise "RANGE"):

CREATE TABLE i_partition_this_because_i_can (
    fk_measurepoint BIGINT REFERENCES measurepoints,
    measuredate TIMESTAMPTZ NOT NULL DEFAULT now(),
    measure_value float NOT NULL
)
PARTITION BY RANGE(measuredate);

und dann entsprechende Partitionen erzeugen:

CREATE TABLE measurements_2020_01
    PARTITION OF i_partition_this_because_i_can
    FOR VALUES FROM ( '2020-01-01 00:00:00' ) TO ( '2020-02-01 00:00:00' );
CREATE TABLE measurements_2020_02 
    PARTITION OF i_partition_this_because_i_can 
    FOR VALUES FROM ( '2020-02-01 00:00:00' ) TO ( '2020-03-01 00:00:00' );

usw.

Messdaten werden dann je nach "measuredate" in verschiedene Partitionen geschrieben, je nach Wert des "measuredate".

Wenn dann nach einem Jahr die Messdaten von Januar 2020 nicht mehr benötigt werden, genügt

ALTER TABLE i_partition_this_because_i_can 
    DETACH PARTITION measurements_2020_01;
DROP TABLE measurements_2020_01;

und ich bin die Daten los.

Und was heißt jetzt HASH-Partitionierung?

Eine Hashfunktion ist eine "Einweg"-Funktion, die z.B. aus "Gunnar" 12345, aus "Nick" 54321 und aus "Pro Open"  13579 macht. Und das reproduzierbar. Ein anderes Beispiel ist z.B. das Register eines Telefonbuchs.

Meine Messdaten könnte ich z.B. so partitionieren:

CREATE TABLE i_partition_this_because_i_can (
    fk_measurepoint BIGINT REFERENCES measurepoints,
    measuredate TIMESTAMPTZ NOT NULL DEFAULT now(),
    measure_value float NOT NULL
)
PARTITION BY HASH (measuredate);

CREATE TABLE measurements_2020_01
    PARTITION OF i_partition_this_because_i_can
    FOR VALUES WITH ( MODULUS 12, REMAINDER 1 );
CREATE TABLE measurements_2020_01
    PARTITION OF i_partition_this_because_i_can
    FOR VALUES WITH ( MODULUS 12, REMAINDER 2 );

usw.

Aber wäre das sinnvoll?!? Wohl kaum, was gewinne ich, wenn ich die Messdaten quasi unverhersagbar auf verschiedene Partitionen verteile?

Hash-Partitionierung funktioniert aber z.B. ganz gut mit Benutzernamen, User-IDs etc., die ich nur wiederfinden, aber nie (massenhaft) löschen will.

Warum wird Hash-Partitionierung überhaupt gewünscht?

Offen gestanden fallen mir nur wenige sinnvolle lokale Anwendungen ein.

Aber:

"Partitions can also be foreign tables, although they have some limitations that normal tables do not; see CREATE FOREIGN TABLE for more information."

Ich kann also auch Tabellen auf anderen Servern als Partitionen einer lokalen Tabelle einbinden!

Also "alle Fotos von Artikeln der Warengruppe X auf Server Y" (PARTITION BY LIST), "alle Messwerte von 2010 bis 2019 auf Server Z" (PARTITION BY RANGE).

Oder aber "alle Benutzer, bei denen der Hashwert der Userid - modulo 8 - gleich eins ist, liegen auf Server A, die mit zwei auf Server B" usw. usf. (PARTITION BY HASH).

Und da reden wir dann plötzlich über Sharding! Und bei Sharding geht es genau darum, die Daten möglichst gleichmäßig zu verteilen. Also quasi das Gegenteil davon, woran man bei "Partitionierung" spontan denkt.

Warum allerdings Leute, die gerade ihre ersten Gehversuche mit PostgreSQL vornehmen, ausgerechnet nach Hash-Partitionierung fragen, erschließt sich mir auch nicht... wenn man nach Gründen fragt, kommt entweder nichts oder heiße Luft.

Evtl. haben da die Marketing-Abteilungen der kommerziellen DBMS-Anbieter was mit zu tun? Dass die ihren Kunden Hash-Partitionierung als "Lösung all ihrer Performance-Probleme!" (aka. "snake oil") verkauft haben, als ihre Systeme es bereits konnten (wofür eigentlich?), PostgreSQL aber noch nicht?

Als Hash-Partitionierung mit PostgreSQL 11 eingeführt wurde, war es zumindest ein offenes Geheimnis, dass es dabei (auch) um das berühmte Häkchen auf "tick-lists" von Interessenten ging.
Gerade in großen Organisationen werden gerne mal Gründe an den Haaren herbeigezogen. Da kommt vom lustlosen DBA oder Entwickler auch schon mal "Aaaalso, solange das keine HASH-Partitionierung kann, brauchen wir uns das gar nicht erst anzuschauen!" Sie kennen das! (In Zukunft ist das dieses vermeintliche "Killerfeature" von Oracle dann wahrscheinlich "Blockchain"... ich bin mir aber ziemlich sicher, dass die PostgreSQL-Community davon Abstand halten wird ;-).

Von Oracle habe ich wiederum schon öfter gehört, dass Partitionierung dort so einige (Performance-)Probleme löst. Wenn es keinen sinnvollen Partitionierungsschlüssel (wie z.B. den Zeitstempel eines Messdatums) gibt, man aber partitionieren will (bzw. muss), kann hashen des Primärschlüssels dann ja durchaus sinnvoll sein...

PostgreSQL wiederum hat auch mit sehr großen Relationen erstmal kein Problem. Erst bei wirklich riesigen Datenmengen, ab dem Terabyte-Bereich, verschieben sich so einige Dinge. Aber das ist ein Thema für einen eigener Blogpost...
Und viele typische Szenarien, bei denen sonst Partitionierung angebracht wäre (Klassiker: Messwerte), konnen mit BRIN-Indizes elegant gelöst werden. Nur beim Thema "Archivierung" helfen diese nicht.

Damit bleibt - bei PostgreSQL - also neben dem schnellen Wegwerfen von alten Daten (typischer Fall: RANGE auf Zeitstempel) als großer Pluspunkt der Partitionierung das Sharding (LIST, HASH und durchaus auch RANGE) per FOREIGN SERVER (FDW).

Wobei hierbei HASH eine von drei möglichen, aber nicht "die" Option ist!

Fazit: "PARTITION BY HASH" ist und bleibt eine Nische.
Und ein Häkchen... ;-)

P.S.: Kommentare willkommen! Vielleicht kennt jemand den wahren Grund für die Popularität von HASH-Partitionierung im Oracle-Umfeld?