PostgreSQL Tabellenpartitionierung

Einführung

In diesem Lab lernen Sie, wie Sie Tabellenpartitionierung in PostgreSQL implementieren. Ziel ist es, eine große Tabelle in kleinere, besser handhabbare Teile zu unterteilen, was die Abfrageleistung erheblich verbessern und Aufgaben der Datenverwaltung wie Backups oder Archivierung vereinfachen kann.

Sie beginnen mit der Erstellung einer Haupt-"Eltern"-Tabelle, die für die Partitionierung konzipiert ist. Anschließend definieren Sie mehrere "Kind"-Tabellen oder Partitionen, die jeweils Daten für einen bestimmten Datumsbereich enthalten. Schließlich fügen Sie Daten in die Elterntabelle ein und beobachten, wie PostgreSQL diese automatisch der richtigen Partition zuordnet. Sie lernen auch, wie Sie die partitionierte Tabelle abfragen und wie PostgreSQL diese Abfragen optimiert, indem es nur auf die relevanten Partitionen zugreift.

Erstellen der übergeordneten partitionierten Tabelle

In diesem Schritt erstellen Sie die Haupttabelle sales, die als übergeordnete Tabelle für unsere Partitionen dient. Diese Tabelle definiert die Struktur für alle ihre Partitionen, speichert aber selbst keine Daten.

Zuerst müssen Sie sich mit der PostgreSQL-Datenbank verbinden. Öffnen Sie ein Terminal und verwenden Sie den folgenden Befehl, um die interaktive psql-Shell als Benutzer postgres zu starten:

sudo -u postgres psql

Sie sollten nun die PostgreSQL-Eingabeaufforderung sehen, die wie postgres=# aussieht. Alle nachfolgenden SQL-Befehle in diesem Lab werden von dieser Eingabeaufforderung aus ausgeführt.

Erstellen Sie als Nächstes die Tabelle sales. Diese Tabelle wird nach dem Bereich (RANGE) auf der Spalte sale_date partitioniert.

CREATE TABLE sales (
    sale_id SERIAL,
    sale_date DATE NOT NULL,
    product_id INTEGER,
    sale_amount DECIMAL(10, 2),
    PRIMARY KEY (sale_id, sale_date)
) PARTITION BY RANGE (sale_date);

Lassen Sie uns diesen Befehl aufschlüsseln:

• CREATE TABLE sales (...): Definiert die Spalten für unsere Verkaufsdaten.
• PRIMARY KEY (sale_id, sale_date): In einer partitionierten Tabelle muss der Primärschlüssel die Partitionierungsspalte (sale_date) enthalten.
• PARTITION BY RANGE (sale_date): Dies ist der entscheidende Teil. Er deklariert, dass diese Tabelle nach der RANGE-Methode auf der Spalte sale_date partitioniert wird.

Nachdem Sie den Befehl ausgeführt haben, sollten Sie eine Bestätigungsnachricht CREATE TABLE erhalten.

Um zu überprüfen, ob die Tabelle erstellt wurde, können Sie den Befehl \d in psql verwenden, um die Tabellenstruktur zu beschreiben.

\d sales

Die Ausgabe zeigt die Spalten der Tabelle und am Ende bestätigt sie, dass es sich um eine "Partitioned table" (Partitionierte Tabelle) handelt und listet den "Partition key" (Partitionsschlüssel) auf.

                                       Table "public.sales"
   Column    |     Type      | Collation | Nullable |                    Default
-------------+---------------+-----------+----------+------------------------------------------------
 sale_id     | integer       |           | not null | nextval('sales_sale_id_seq'::regclass)
 sale_date   | date          |           | not null |
 product_id  | integer       |           |          |
 sale_amount | numeric(10,2) |           |          |
Partition key: RANGE (sale_date)
Indexes:
    "sales_pkey" PRIMARY KEY, btree (sale_id, sale_date)
Number of partitions: 0

Beachten Sie, dass die "Number of partitions" (Anzahl der Partitionen) 0 ist. Die eigentlichen Partitionen werden Sie im nächsten Schritt erstellen.

Partitionen für Datumsbereiche definieren

Nachdem Sie die übergeordnete Tabelle sales erstellt haben, müssen Sie die eigentlichen Partitionen erstellen, in denen die Daten gespeichert werden. Jede Partition speichert Daten für einen bestimmten Datumsbereich. In diesem Schritt erstellen Sie vierteljährliche Partitionen für die Jahre 2023 und 2024.

Sie sollten sich immer noch im interaktiven psql-Terminal befinden.

Erstellen Sie zuerst die vier Partitionen für 2023. Jeder Befehl definiert eine neue Tabelle als Partition von sales und gibt den Datumsbereich an, den sie abdecken wird.

CREATE TABLE sales_2023_q1 PARTITION OF sales
    FOR VALUES FROM ('2023-01-01') TO ('2023-04-01');

CREATE TABLE sales_2023_q2 PARTITION OF sales
    FOR VALUES FROM ('2023-04-01') TO ('2023-07-01');

CREATE TABLE sales_2023_q3 PARTITION OF sales
    FOR VALUES FROM ('2023-07-01') TO ('2023-10-01');

CREATE TABLE sales_2023_q4 PARTITION OF sales
    FOR VALUES FROM ('2023-10-01') TO ('2024-01-01');

Die Klausel FOR VALUES FROM ... TO ... definiert den Bereich für jede Partition. Die untere Grenze ist inklusiv und die obere Grenze ist exklusiv. Zum Beispiel speichert sales_2023_q1 Datensätze, bei denen sale_date vom 2023-01-01 bis zum 2023-04-01 (ausschließlich) reicht.

Erstellen Sie als Nächstes die Partitionen für das Jahr 2024 nach demselben vierteljährlichen Schema:

CREATE TABLE sales_2024_q1 PARTITION OF sales
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

CREATE TABLE sales_2024_q2 PARTITION OF sales
    FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

CREATE TABLE sales_2024_q3 PARTITION OF sales
    FOR VALUES FROM ('2024-07-01') TO ('2024-10-01');

CREATE TABLE sales_2024_q4 PARTITION OF sales
    FOR VALUES FROM ('2024-10-01') TO ('2025-01-01');

Nachdem Sie jeden CREATE TABLE-Befehl ausgeführt haben, erhalten Sie eine Bestätigungsnachricht.

Um zu überprüfen, ob alle Partitionen erstellt wurden, können Sie die Tabellen in der Datenbank erneut auflisten.

\dt

In der Ausgabe sollten Sie nun die übergeordnete Tabelle sales und alle acht gerade erstellten Partitionen (sales_2023_q1, sales_2023_q2 usw.) sehen.

Daten einfügen und weiterleiten

In diesem Schritt fügen Sie Beispieldaten ein. Ein Hauptmerkmal der Partitionierung ist, dass Sie Daten direkt in die übergeordnete Tabelle (sales) einfügen, und PostgreSQL leitet jede Zeile automatisch an die richtige Partition weiter, basierend auf dem Wert des Partitionsschlüssels (sale_date).

Sie sollten sich immer noch im interaktiven psql-Terminal befinden.

Führen Sie die folgende INSERT-Anweisung aus, um 16 Beispieldatensätze für Verkäufe im Zeitraum 2023 und 2024 hinzuzufügen:

INSERT INTO sales (sale_date, product_id, sale_amount) VALUES
('2023-01-15', 101, 50.00),
('2023-02-20', 102, 75.50),
('2023-04-10', 103, 100.00),
('2023-05-25', 104, 60.25),
('2023-07-01', 105, 120.00),
('2023-08-12', 106, 80.75),
('2023-10-05', 107, 90.00),
('2023-11-18', 108, 110.50),
('2024-01-22', 109, 55.00),
('2024-03-01', 110, 70.00),
('2024-04-15', 111, 95.50),
('2024-06-10', 112, 65.00),
('2024-07-08', 113, 125.00),
('2024-09-20', 114, 85.25),
('2024-10-12', 115, 95.00),
('2024-12-01', 116, 115.75);

Nachdem der Befehl abgeschlossen ist, sehen Sie die Ausgabe INSERT 0 16, was bedeutet, dass 16 Zeilen erfolgreich eingefügt wurden.

Um zu überprüfen, ob die Daten korrekt weitergeleitet wurden, können Sie die einzelnen Partitionen abfragen. Überprüfen wir zum Beispiel die Anzahl der Datensätze im ersten Quartal 2023:

SELECT COUNT(*) FROM sales_2023_q1;

Die Ausgabe sollte sein:

 count
-------
     2
(1 row)

Überprüfen Sie nun die Anzahl für das vierte Quartal 2024:

SELECT COUNT(*) FROM sales_2024_q4;

Die Ausgabe sollte ebenfalls 2 sein. Dies bestätigt, dass PostgreSQL die Daten in die richtigen zugrunde liegenden Partitionstabellen eingefügt hat.

Daten abfragen und Leistung analysieren

In diesem letzten Schritt werden Sie die partitionierte Tabelle sales abfragen. Der Hauptvorteil der Partitionierung, bekannt als "Partition Pruning", besteht darin, dass der Query Planner von PostgreSQL intelligent genug ist, nur die notwendigen Partitionen zu scannen und einen vollständigen Scan des gesamten Datensatzes zu vermeiden.

Sie sollten sich immer noch im interaktiven psql-Terminal befinden.

Führen Sie zuerst eine Abfrage aus, um alle Verkäufe aus dem ersten Quartal 2023 abzurufen.

SELECT * FROM sales WHERE sale_date >= '2023-01-01' AND sale_date < '2023-04-01';

Sie sehen die beiden Datensätze, die in diesen Datumsbereich fallen. Um zu sehen, wie PostgreSQL dies optimiert, können Sie den Befehl EXPLAIN verwenden, der den Abfrageausführungsplan anzeigt.

EXPLAIN SELECT * FROM sales WHERE sale_date >= '2023-01-01' AND sale_date < '2023-04-01';

Die Ausgabe wird ungefähr so aussehen:

                                     QUERY PLAN
------------------------------------------------------------------------------------
 Seq Scan on sales_2023_q1 sales  (cost=0.00..31.75 rows=7 width=28)
   Filter: ((sale_date >= '2023-01-01'::date) AND (sale_date < '2023-04-01'::date))
(2 rows)

Beachten Sie die Zeile Seq Scan on sales_2023_q1. Dies beweist, dass PostgreSQL nur die Partition sales_2023_q1 gescannt und die anderen sieben ignoriert hat, was die Abfrage bei einem großen Datensatz erheblich beschleunigt.

Lassen Sie uns nun eine komplexere Abfrage ausführen, um den gesamten Verkaufsbetrag für jedes Produkt im Jahr 2024 zu ermitteln.

SELECT product_id, SUM(sale_amount) AS total_sales
FROM sales
WHERE sale_date >= '2024-01-01' AND sale_date < '2025-01-01'
GROUP BY product_id
ORDER BY product_id;

Diese Abfrage scannt effizient nur die vier Partitionen für 2024, um das Ergebnis zu berechnen. Die Ausgabe zeigt die Gesamtverkäufe für jedes Produkt von 109 bis 116.

Schließlich können Sie das interaktive PostgreSQL-Terminal verlassen, indem Sie Folgendes eingeben:

\q

Sie werden zu Ihrer normalen Shell-Eingabeaufforderung zurückgeleitet.

Zusammenfassung

In diesem Lab haben Sie die Grundlagen der Tabellenpartitionierung in PostgreSQL kennengelernt. Sie haben erfolgreich eine übergeordnete Tabelle erstellt, die nach einem Datumsbereich partitioniert ist, spezifische Partitionen für verschiedene Zeiträume definiert und Daten eingefügt, die automatisch an die richtige Partition weitergeleitet wurden. Am wichtigsten ist, dass Sie den Befehl EXPLAIN verwendet haben, um das Partition Pruning in Aktion zu sehen, was zeigt, wie die Partitionierung die Abfrageleistung erheblich verbessern kann, indem sie der Datenbank ermöglicht, nur eine Teilmenge der Daten zu scannen. Dies ist eine leistungsstarke Technik zur effizienten Verwaltung großer Datensätze.