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, um die Abfrageleistung zu verbessern und die Datenverwaltung zu vereinfachen.

Sie beginnen mit der Einrichtung einer bereichspartitionierten Tabelle namens sales, die nach der Spalte sale_date partitioniert ist. Dies beinhaltet das Erstellen der Haupttabelle sales und anschließend das Definieren von Partitionen für verschiedene Datumsbereiche. Anschließend fügen Sie Daten in die Tabelle sales ein, die automatisch an die richtigen Partitionen weitergeleitet werden, und führen Abfragen über die partitionierten Tabellen aus.

Skills Graph

Erstellen der Tabelle sales und der ersten Partitionen

In diesem Schritt erstellen Sie die Haupttabelle sales und die ersten Partitionen für das Jahr 2023. Die Partitionierung unterteilt eine große Tabelle in kleinere, besser handhabbare Teile, wodurch die Abfrageleistung verbessert wird.

Stellen Sie zunächst als Benutzer postgres eine Verbindung zur PostgreSQL-Datenbank her. Öffnen Sie ein Terminal und verwenden Sie den folgenden Befehl:

sudo -u postgres psql

Sie sollten sich nun im interaktiven PostgreSQL-Terminal befinden.

Erstellen Sie als Nächstes die Tabelle sales. Diese Tabelle speichert Verkaufsdaten, einschließlich Verkaufsdatum, Produkt-ID und Verkaufsbetrag.

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

Dieser Befehl erstellt die Tabelle sales und legt fest, dass sie nach dem Bereich (RANGE) in der Spalte sale_date partitioniert wird.

Erstellen Sie nun die Partitionen für das Jahr 2023 und unterteilen Sie das Jahr in Quartale:

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');

Diese Befehle erstellen vier Partitionen für die Tabelle sales, die jeweils ein Quartal des Jahres 2023 abdecken.

Um zu überprüfen, ob die Tabelle und die Partitionen erfolgreich erstellt wurden, listen Sie alle Tabellen auf:

\dt

Sie sollten die Tabelle sales und ihre Partitionen (sales_2023_q1, sales_2023_q2, sales_2023_q3, sales_2023_q4) in der Ausgabe sehen.

Partitionen für 2024 definieren

Nachdem Sie die Tabelle sales und die Partitionen für 2023 erstellt haben, fügen wir Partitionen für das Jahr 2024 hinzu. Dies stellt sicher, dass Sie Verkaufsdaten für das gesamte Jahr speichern können.

Sie sollten sich noch im interaktiven PostgreSQL-Terminal aus dem vorherigen Schritt befinden. Wenn nicht, stellen Sie die Verbindung erneut her mit:

sudo -u postgres psql

Erstellen Sie Partitionen für die Tabelle sales für das Jahr 2024, wobei Sie dasselbe vierteljährliche Partitionierungsschema verwenden:

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');

Diese Befehle erstellen vier neue Partitionen für die Tabelle sales, die jedes Quartal des Jahres 2024 abdecken.

Um zu überprüfen, ob die neuen Partitionen erfolgreich erstellt wurden, listen Sie alle Tabellen erneut auf:

\dt

Sie sollten nun die Tabelle sales, die Partitionen für 2023 und die neuen Partitionen für 2024 in der Ausgabe sehen.

Daten in die Tabelle sales einfügen

In diesem Schritt fügen Sie Beispieldaten in die Tabelle sales ein. PostgreSQL leitet die Daten basierend auf dem sale_date automatisch an die richtige Partition weiter.

Sie sollten sich noch im interaktiven PostgreSQL-Terminal befinden. Wenn nicht, stellen Sie die Verbindung erneut her mit:

sudo -u postgres psql

Fügen Sie die folgenden Beispieldaten in die Tabelle sales ein:

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);

Diese INSERT-Anweisungen fügen Verkaufsdatensätze für verschiedene Daten in den Jahren 2023 und 2024 hinzu. PostgreSQL leitet jeden Datensatz basierend auf dem sale_date automatisch an die entsprechende Partition weiter.

Um zu überprüfen, ob die Daten korrekt eingefügt wurden, fragen Sie die Tabelle sales ab, um die Gesamtzahl der Datensätze zu zählen:

SELECT COUNT(*) FROM sales;

Die Ausgabe sollte insgesamt 16 Datensätze anzeigen.

Abfragen der partitionierten sales-Tabelle

In diesem Schritt fragen Sie die partitionierte sales-Tabelle ab, um Daten abzurufen. Sie können die gesamte Tabelle abfragen, als wäre es eine einzelne Tabelle, und PostgreSQL optimiert die Abfrage, um nur auf die relevanten Partitionen zuzugreifen.

Sie sollten sich noch im interaktiven PostgreSQL-Terminal befinden. Wenn nicht, stellen Sie die Verbindung erneut her mit:

sudo -u postgres psql

Um alle Verkaufsdatensätze aus der gesamten sales-Tabelle abzurufen, verwenden Sie die folgende SELECT-Anweisung:

SELECT * FROM sales;

Diese Abfrage gibt alle Daten aus allen Partitionen der sales-Tabelle zurück.

Sie können auch WHERE-Klauseln verwenden, um die Daten zu filtern. Um beispielsweise alle Verkaufsdatensätze aus dem Jahr 2023 abzurufen:

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

PostgreSQL verwendet den Partitionsschlüssel (sale_date), um zu bestimmen, auf welche Partitionen zugegriffen werden soll, wodurch die Abfrageleistung verbessert wird.

Versuchen wir eine weitere Abfrage, um den Gesamtumsatz 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;

Diese Abfrage berechnet die Summe von sale_amount für jede product_id innerhalb des Jahres 2024.

Beenden Sie abschließend das interaktive PostgreSQL-Terminal:

\q

Zusammenfassung

In diesem Lab haben Sie gelernt, wie Sie Tabellenpartitionierung (table partitioning) in PostgreSQL implementieren. Sie haben eine sales-Tabelle erstellt, diese nach Datumsbereich partitioniert, Partitionen für die Jahre 2023 und 2024 definiert, Daten in die Tabelle eingefügt und die partitionierte Tabelle abgefragt. Die Partitionierung kann die Abfrageleistung erheblich verbessern und die Datenverwaltung für große Tabellen vereinfachen.