In diesem Artikel werden die Funktionen erläutert, die durch die integrierte oder deklarative Partitionierung in Version 12 von PostgreSQL bereitgestellt werden. Die Demonstration wurde für die gleichnamige
Vorlesung auf der HighLoad ++ Siberia 2019-Konferenz vorbereitet (Update: Mit der Vorlesung erschien ein
Video ).
Alle Beispiele werden in der kürzlich erschienenen Beta-Version ausgeführt:
=> SELECT version();
version ------------------------------------------------------------------------------------------------------------------ PostgreSQL 12beta1 on i686-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.10) 5.4.0 20160609, 32-bit (1 row)
Die Beispiele verwenden die Buchungs- und Ticket-Tabellen der Demo-Datenbank. Die Reservierungstabelle enthält Einträge für drei Monate von Juni bis August 2017 und hat folgende Struktur:
=> \d bookings
Table "bookings.bookings" Column | Type | Collation | Nullable | Default --------------+--------------------------+-----------+----------+--------- book_ref | character(6) | | not null | book_date | timestamp with time zone | | not null | total_amount | numeric(10,2) | | not null | Indexes: "bookings_pkey" PRIMARY KEY, btree (book_ref) Referenced by: TABLE "tickets" CONSTRAINT "tickets_book_ref_fkey" FOREIGN KEY (book_ref) REFERENCES bookings(book_ref)
Eine Reservierung kann mehrere Tickets enthalten. Die Struktur des Tisches mit Tickets:
=> \d tickets
Table "bookings.tickets" Column | Type | Collation | Nullable | Default ----------------+-----------------------+-----------+----------+--------- ticket_no | character(13) | | not null | book_ref | character(6) | | not null | passenger_id | character varying(20) | | not null | passenger_name | text | | not null | contact_data | jsonb | | | Indexes: "tickets_pkey" PRIMARY KEY, btree (ticket_no) Foreign-key constraints: "tickets_book_ref_fkey" FOREIGN KEY (book_ref) REFERENCES bookings(book_ref) Referenced by: TABLE "ticket_flights" CONSTRAINT "ticket_flights_ticket_no_fkey" FOREIGN KEY (ticket_no) REFERENCES tickets(ticket_no)
Diese Informationen sollten ausreichen, um Beispiele zu verstehen, in denen versucht wird, Tabellen partitioniert zu machen.
→ Erfahren Sie hier mehr über die Demo-Basis
.Bereichspartitionierung
Versuchen Sie zunächst, die Buchungstabelle nach Datumsbereich zu unterteilen. In diesem Fall würde die Tabelle folgendermaßen erstellt:
=> CREATE TABLE bookings_range ( book_ref character(6), book_date timestamptz, total_amount numeric(10,2) ) PARTITION BY RANGE(book_date);
Separate Abschnitte für jeden Monat:
=> CREATE TABLE bookings_range_201706 PARTITION OF bookings_range FOR VALUES FROM ('2017-06-01'::timestamptz) TO ('2017-07-01'::timestamptz); => CREATE TABLE bookings_range_201707 PARTITION OF bookings_range FOR VALUES FROM ('2017-07-01'::timestamptz) TO ('2017-08-01'::timestamptz);
Um die Grenzen eines Abschnitts anzugeben, können Sie nicht nur Konstanten, sondern auch Ausdrücke verwenden, z. B. einen Funktionsaufruf. Der Wert des Ausdrucks wird zum Zeitpunkt der Erstellung und Speicherung des Abschnitts im Systemverzeichnis berechnet:
=> CREATE TABLE bookings_range_201708 PARTITION OF bookings_range FOR VALUES FROM (to_timestamp('01.08.2017','DD.MM.YYYY')) TO (to_timestamp('01.09.2017','DD.MM.YYYY'));
Beschreibung der Tabelle:
=> \d+ bookings_range
Partitioned table "bookings.bookings_range" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------------+--------------------------+-----------+----------+---------+----------+--------------+------------- book_ref | character(6) | | | | extended | | book_date | timestamp with time zone | | | | plain | | total_amount | numeric(10,2) | | | | main | | Partition key: RANGE (book_date) Partitions: bookings_range_201706 FOR VALUES FROM ('2017-06-01 00:00:00+03') TO ('2017-07-01 00:00:00+03'), bookings_range_201707 FOR VALUES FROM ('2017-07-01 00:00:00+03') TO ('2017-08-01 00:00:00+03'), bookings_range_201708 FOR VALUES FROM ('2017-08-01 00:00:00+03') TO ('2017-09-01 00:00:00+03')
Das ist genug. Kein Auslöser zum Einfügen von Datensätzen, keine CHECK-Einschränkungen erforderlich. Der Parameter CONSTRAINT_EXCLUSION wird ebenfalls nicht benötigt, Sie können ihn sogar deaktivieren:
=> SET constraint_exclusion = OFF;
Füllen mit automatischem Layout in Abschnitten:
=> INSERT INTO bookings_range SELECT * FROM bookings;
INSERT 0 262788
Die deklarative Syntax verbirgt weiterhin geerbte Tabellen, sodass Sie die Verteilung der Zeilen in Abschnitten nach Abfrage anzeigen können:
=> SELECT tableoid::regclass, count(*) FROM bookings_range GROUP BY tableoid;
tableoid | count -----------------------+-------- bookings_range_201706 | 7303 bookings_range_201707 | 167062 bookings_range_201708 | 88423 (3 rows)
Die übergeordnete Tabelle enthält jedoch keine Daten:
=> SELECT * FROM ONLY bookings_range;
book_ref | book_date | total_amount ----------+-----------+-------------- (0 rows)
Überprüfen Sie den Ausschluss von Abschnitten im Abfrageplan:
=> EXPLAIN (COSTS OFF) SELECT * FROM bookings_range WHERE book_date = '2017-07-01'::timestamptz;
QUERY PLAN ---------------------------------------------------------------------------- Seq Scan on bookings_range_201707 Filter: (book_date = '2017-07-01 00:00:00+03'::timestamp with time zone) (2 rows)
Scannen wie erwartet nur einen Abschnitt.
Im folgenden Beispiel wird die Funktion to_timestamp mit der Variabilitätskategorie STABLE anstelle einer Konstante verwendet:
=> EXPLAIN (COSTS OFF) SELECT * FROM bookings_range WHERE book_date = to_timestamp('01.07.2017','DD.MM.YYYY');
QUERY PLAN ------------------------------------------------------------------------------------ Append Subplans Removed: 2 -> Seq Scan on bookings_range_201707 Filter: (book_date = to_timestamp('01.07.2017'::text, 'DD.MM.YYYY'::text)) (4 rows)
Der Wert der Funktion wird berechnet, wenn der Abfrageplan initialisiert wird und ein Teil der Abschnitte von der Anzeige ausgeschlossen wird (Zeile Unterpläne entfernt).
Dies funktioniert jedoch nur für SELECT. Beim Ändern von Daten ist der Abschnittsausschluss basierend auf den Werten der STABLE-Funktionen noch nicht implementiert:
=> EXPLAIN (COSTS OFF) DELETE FROM bookings_range WHERE book_date = to_timestamp('01.07.2017','DD.MM.YYYY');
QUERY PLAN ------------------------------------------------------------------------------------ Delete on bookings_range Delete on bookings_range_201706 Delete on bookings_range_201707 Delete on bookings_range_201708 -> Seq Scan on bookings_range_201706 Filter: (book_date = to_timestamp('01.07.2017'::text, 'DD.MM.YYYY'::text)) -> Seq Scan on bookings_range_201707 Filter: (book_date = to_timestamp('01.07.2017'::text, 'DD.MM.YYYY'::text)) -> Seq Scan on bookings_range_201708 Filter: (book_date = to_timestamp('01.07.2017'::text, 'DD.MM.YYYY'::text)) (10 rows)
Daher sollten Sie Konstanten verwenden:
=> EXPLAIN (COSTS OFF) DELETE FROM bookings_range WHERE book_date = '2017-07-01'::timestamptz;
QUERY PLAN ---------------------------------------------------------------------------------- Delete on bookings_range Delete on bookings_range_201707 -> Seq Scan on bookings_range_201707 Filter: (book_date = '2017-07-01 00:00:00+03'::timestamp with time zone) (4 rows)
Indexsortierung
Um die folgende Abfrage durchzuführen, müssen die Ergebnisse aus verschiedenen Abschnitten sortiert werden. Daher sehen wir im Abfrageplan den SORT-Knoten und die hohen Anfangskosten des Plans:
=> EXPLAIN SELECT * FROM bookings_range ORDER BY book_date;
QUERY PLAN ------------------------------------------------------------------------------------------ Sort (cost=24649.77..25077.15 rows=170952 width=52) Sort Key: bookings_range_201706.book_date -> Append (cost=0.00..4240.28 rows=170952 width=52) -> Seq Scan on bookings_range_201706 (cost=0.00..94.94 rows=4794 width=52) -> Seq Scan on bookings_range_201707 (cost=0.00..2151.30 rows=108630 width=52) -> Seq Scan on bookings_range_201708 (cost=0.00..1139.28 rows=57528 width=52) (6 rows)
Erstellen Sie einen Index für book_date. Anstelle eines einzelnen globalen Index werden in jedem Abschnitt Indizes erstellt:
=> CREATE INDEX book_date_idx ON bookings_range(book_date);
=> \di bookings_range*
List of relations Schema | Name | Type | Owner | Table ----------+-------------------------------------+-------+---------+----------------------- bookings | bookings_range_201706_book_date_idx | index | student | bookings_range_201706 bookings | bookings_range_201707_book_date_idx | index | student | bookings_range_201707 bookings | bookings_range_201708_book_date_idx | index | student | bookings_range_201708 (3 rows)
Die vorherige Abfrage mit Sortierung kann jetzt den Index für den Partitionsschlüssel verwenden und das Ergebnis aus verschiedenen Abschnitten sofort in sortierter Form zurückgeben. Der SORT-Knoten wird nicht benötigt und die Mindestkosten sind erforderlich, um die erste Zeile des Ergebnisses zu erstellen:
=> EXPLAIN SELECT * FROM bookings_range ORDER BY book_date;
QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------- Append (cost=1.12..14880.88 rows=262788 width=52) -> Index Scan using bookings_range_201706_book_date_idx on bookings_range_201706 (cost=0.28..385.83 rows=7303 width=52) -> Index Scan using bookings_range_201707_book_date_idx on bookings_range_201707 (cost=0.42..8614.35 rows=167062 width=52) -> Index Scan using bookings_range_201708_book_date_idx on bookings_range_201708 (cost=0.42..4566.76 rows=88423 width=52) (4 rows)
Auf diese Weise erstellte partitionierte Indizes werden zentral unterstützt. Beim Hinzufügen eines neuen Abschnitts wird automatisch ein Index erstellt. Und Sie können den Index nur eines Abschnitts nicht entfernen:
=> DROP INDEX bookings_range_201706_book_date_idx;
ERROR: cannot drop index bookings_range_201706_book_date_idx because index book_date_idx requires it HINT: You can drop index book_date_idx instead.
Nur im Ganzen:
=> DROP INDEX book_date_idx;
DROP INDEX
INDEX ERSTELLEN ... AKTUELL
Wenn Sie einen Index für eine partitionierte Tabelle erstellen, können Sie CONCURRENTLY nicht angeben.
Sie können jedoch Folgendes tun. Zuerst erstellen wir den Index nur für die Haupttabelle, er erhält den ungültigen Status:
=> CREATE INDEX book_date_idx ON ONLY bookings_range(book_date);
=> SELECT indisvalid FROM pg_index WHERE indexrelid::regclass::text = 'book_date_idx';
indisvalid ------------ f (1 row)
Erstellen Sie dann mit der Option CONCURRENTLY Indizes für alle Abschnitte:
=> CREATE INDEX CONCURRENTLY book_date_201706_idx ON bookings_range_201706 (book_date); => CREATE INDEX CONCURRENTLY book_date_201707_idx ON bookings_range_201707 (book_date); => CREATE INDEX CONCURRENTLY book_date_201708_idx ON bookings_range_201708 (book_date);
Jetzt verbinden wir lokale Indizes mit globalen:
=> ALTER INDEX book_date_idx ATTACH PARTITION book_date_201706_idx; => ALTER INDEX book_date_idx ATTACH PARTITION book_date_201707_idx; => ALTER INDEX book_date_idx ATTACH PARTITION book_date_201708_idx;
Dies ähnelt dem Verbinden von Partitionstabellen, auf die wir später noch eingehen werden. Sobald alle Indexabschnitte verbunden sind, ändert der Hauptindex seinen Status:
=> SELECT indisvalid FROM pg_index WHERE indexrelid::regclass::text = 'book_date_idx';
indisvalid ------------ t (1 row)
Teile verbinden und trennen
Die automatische Erstellung von Abschnitten ist nicht vorgesehen. Daher müssen sie im Voraus erstellt werden, bevor Datensätze mit neuen Werten des Partitionierungsschlüssels zur Tabelle hinzugefügt werden.
Wir werden einen neuen Abschnitt erstellen, während andere Transaktionen mit der Tabelle arbeiten. Gleichzeitig werden wir uns die Sperren ansehen:
=> BEGIN; => SELECT count(*) FROM bookings_range WHERE book_date = to_timestamp('01.07.2017','DD.MM.YYYY');
count ------- 5 (1 row)
=> SELECT relation::regclass::text, mode FROM pg_locks WHERE pid = pg_backend_pid() AND relation::regclass::text LIKE 'bookings%';
relation | mode -----------------------+----------------- bookings_range_201708 | AccessShareLock bookings_range_201707 | AccessShareLock bookings_range_201706 | AccessShareLock bookings_range | AccessShareLock (4 rows)
Die AccessShareLock-Sperre wird für die Haupttabelle, alle Abschnitte und Indizes am Anfang der Anweisung festgelegt. Die Berechnung der Funktion to_timestamp und der Ausschluss von Abschnitten erfolgt später. Wenn anstelle einer Funktion eine Konstante verwendet würde, wären nur die Haupttabelle und der Abschnitt bookings_range_201707 gesperrt. Geben Sie daher nach Möglichkeit Konstanten in der Anforderung an. Dies sollte erfolgen, da sonst die Anzahl der Zeilen in pg_locks proportional zur Anzahl der Abschnitte zunimmt, was dazu führen kann, dass max_locks_per_transaction erhöht werden muss.
Erstellen Sie in einer neuen Sitzung den folgenden Abschnitt für September, ohne die vorherige Transaktion abzuschließen:
|| => CREATE TABLE bookings_range_201709 (LIKE bookings_range); || => BEGIN; || => ALTER TABLE bookings_range ATTACH PARTITION bookings_range_201709 FOR VALUES FROM ('2017-09-01'::timestamptz) TO ('2017-10-01'::timestamptz); || => SELECT relation::regclass::text, mode FROM pg_locks WHERE pid = pg_backend_pid() AND relation::regclass::text LIKE 'bookings%';
relation | mode -------------------------------------+-------------------------- bookings_range_201709_book_date_idx | AccessExclusiveLock bookings_range | ShareUpdateExclusiveLock bookings_range_201709 | ShareLock bookings_range_201709 | AccessExclusiveLock (4 rows)
Beim Erstellen eines neuen Abschnitts wird die mit AccessShareLock kompatible ShareUpdateExclusiveLock-Sperre für die Haupttabelle festgelegt. Daher stehen die Operationen zum Hinzufügen von Partitionen nicht in Konflikt mit Abfragen für eine partitionierte Tabelle.
=> COMMIT;
|| => COMMIT;
Die Partitionierung erfolgt mit dem Befehl ALTER TABLE ... DETACH PARTITION. Der Abschnitt selbst wird nicht gelöscht, sondern zu einer unabhängigen Tabelle. Daten können von dort heruntergeladen, gelöscht und bei Bedarf erneut verbunden werden (ATTACH PARTITION).
Eine weitere Option zum Deaktivieren besteht darin, den Abschnitt mit dem Befehl DROP TABLE zu löschen.
Leider verwenden beide Optionen, DROP TABLE und DETACH PARTITION, die AccessExclusiveLock-Sperre für die Haupttabelle.
Standardabschnitt
Wenn Sie versuchen, einen Datensatz hinzuzufügen, für den noch kein Abschnitt erstellt wurde, tritt ein Fehler auf. Wenn dieses Verhalten nicht gewünscht ist, können Sie einen Standardabschnitt erstellen:
=> CREATE TABLE bookings_range_default PARTITION OF bookings_range DEFAULT;
Angenommen, sie haben beim Hinzufügen von Datensätzen das Datum verwechselt, ohne ein Jahrtausend anzugeben:
=> INSERT INTO bookings_range VALUES('XX0000', '0017-09-01'::timestamptz, 0) RETURNING tableoid::regclass, *;
tableoid | book_ref | book_date | total_amount ------------------------+----------+------------------------------+-------------- bookings_range_default | XX0000 | 0017-09-01 00:00:00+02:30:17 | 0.00 (1 row) INSERT 0 1
Wir stellen fest, dass der Ausdruck RETURNING eine neue Zeile zurückgibt, die in den Standardabschnitt fällt.
Nach dem Einstellen des aktuellen Datums (Ändern des Partitionierungsschlüssels) wird der Datensatz automatisch in den gewünschten Abschnitt verschoben. Trigger werden nicht benötigt:
=> UPDATE bookings_range SET book_date = '2017-09-01'::timestamptz WHERE book_ref = 'XX0000' RETURNING tableoid::regclass, *;
tableoid | book_ref | book_date | total_amount -----------------------+----------+------------------------+-------------- bookings_range_201709 | XX0000 | 2017-09-01 00:00:00+03 | 0.00 (1 row) UPDATE 1
Wertelistenaufteilung
In der Demo-Datenbank muss die Spalte book_ref der Primärschlüssel der Buchungstabelle sein. Das ausgewählte Partitionierungsschema erlaubt jedoch nicht das Erstellen eines solchen Schlüssels:
=> ALTER TABLE bookings_range ADD PRIMARY KEY(book_ref);
ERROR: insufficient columns in PRIMARY KEY constraint definition DETAIL: PRIMARY KEY constraint on table "bookings_range" lacks column "book_date" which is part of the partition key.
Der Partitionierungsschlüssel muss im Primärschlüssel enthalten sein.
Um nach Monaten aufzuschlüsseln und trotzdem book_ref in den Primärschlüssel aufzunehmen, versuchen wir ein anderes Schema zum Partitionieren der Buchungstabelle - entsprechend der Werteliste. Fügen Sie dazu die redundante Spalte book_month als Partitionsschlüssel hinzu:
=> CREATE TABLE bookings_list ( book_ref character(6), book_month character(6), book_date timestamptz NOT NULL, total_amount numeric(10,2), PRIMARY KEY (book_ref, book_month) ) PARTITION BY LIST(book_month);
Wir werden Abschnitte dynamisch basierend auf den Daten der Buchungstabelle bilden:
=> WITH dates AS ( SELECT date_trunc('month',min(book_date)) min_date, date_trunc('month',max(book_date)) max_date FROM bookings ), partition AS ( SELECT to_char(g.month, 'YYYYMM') AS book_month FROM dates, generate_series(dates.min_date, dates.max_date, '1 month'::interval) AS g(month) ) SELECT format('CREATE TABLE %I PARTITION OF bookings_list FOR VALUES IN (%L)', 'bookings_list_' || partition.book_month, partition.book_month) FROM partition\gexec
CREATE TABLE CREATE TABLE CREATE TABLE
Folgendes ist passiert:
=> \d+ bookings_list
Partitioned table "bookings.bookings_list" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------------+--------------------------+-----------+----------+---------+----------+--------------+------------- book_ref | character(6) | | not null | | extended | | book_month | character(6) | | not null | | extended | | book_date | timestamp with time zone | | not null | | plain | | total_amount | numeric(10,2) | | | | main | | Partition key: LIST (book_month) Indexes: "bookings_list_pkey" PRIMARY KEY, btree (book_ref, book_month) Partitions: bookings_list_201706 FOR VALUES IN ('201706'), bookings_list_201707 FOR VALUES IN ('201707'), bookings_list_201708 FOR VALUES IN ('201708')
Füllen mit Layout in Abschnitten:
=> INSERT INTO bookings_list(book_ref,book_month,book_date,total_amount) SELECT book_ref,to_char(book_date, 'YYYYMM'),book_date,total_amount FROM bookings;
INSERT 0 262788
Als Rückzugsort. Um book_month automatisch zu füllen, ist es verlockend, die neue Funktionalität der Spalten der Version 12 - GENERATED ALWAYS zu verwenden. Leider können sie nicht als Partitionsschlüssel verwendet werden. Daher sollte die Aufgabe, den Monat zu füllen, auf andere Weise gelöst werden.
Integritätsbeschränkungen wie CHECK und NOT NULL können für eine partitionierte Tabelle erstellt werden. Wie bei der Vererbung gibt die Angabe von INHERIT / NOINHERIT an, ob die Einschränkung für alle Partitionstabellen vererbt werden soll. Standard INHERIT:
=> ALTER TABLE bookings_range ALTER COLUMN book_date SET NOT NULL;
=> \d bookings_range
Partitioned table "bookings.bookings_range" Column | Type | Collation | Nullable | Default --------------+--------------------------+-----------+----------+--------- book_ref | character(6) | | | book_date | timestamp with time zone | | not null | total_amount | numeric(10,2) | | | Partition key: RANGE (book_date) Indexes: "book_date_idx" btree (book_date) Number of partitions: 5 (Use \d+ to list them.)
=> \d bookings_range_201706
Table "bookings.bookings_range_201706" Column | Type | Collation | Nullable | Default --------------+--------------------------+-----------+----------+--------- book_ref | character(6) | | | book_date | timestamp with time zone | | not null | total_amount | numeric(10,2) | | | Partition of: bookings_range FOR VALUES FROM ('2017-06-01 00:00:00+03') TO ('2017-07-01 00:00:00+03') Indexes: "book_date_201706_idx" btree (book_date)
Eine EXCLUDE-Einschränkung kann nur lokal auf Partitionen erstellt werden.
Eine Suche in book_ref wird in allen Abschnitten angezeigt, jedoch nach Index, da book_ref zuerst aufgeführt wird:
=> EXPLAIN (COSTS OFF) SELECT * FROM bookings_list WHERE book_ref = '00000F';
QUERY PLAN -------------------------------------------------------------------------- Append -> Index Scan using bookings_list_201706_pkey on bookings_list_201706 Index Cond: (book_ref = '00000F'::bpchar) -> Index Scan using bookings_list_201707_pkey on bookings_list_201707 Index Cond: (book_ref = '00000F'::bpchar) -> Index Scan using bookings_list_201708_pkey on bookings_list_201708 Index Cond: (book_ref = '00000F'::bpchar) (7 rows)
Eine Suche in book_ref und einer Reihe von Abschnitten sollte nur im angegebenen Bereich erfolgen:
=> EXPLAIN (COSTS OFF) SELECT * FROM bookings_list WHERE book_ref = '00000F' AND book_month = '201707';
QUERY PLAN ----------------------------------------------------------------------------------- Index Scan using bookings_list_201707_pkey on bookings_list_201707 Index Cond: ((book_ref = '00000F'::bpchar) AND (book_month = '201707'::bpchar)) (2 rows)
Der Befehl INSERT ... ON CONFLICT findet den gewünschten Abschnitt korrekt und führt die Aktualisierung durch:
=> INSERT INTO bookings_list VALUES ('XX0001','201708','2017-08-01',0) RETURNING tableoid::regclass, *;
tableoid | book_ref | book_month | book_date | total_amount ----------------------+----------+------------+------------------------+-------------- bookings_list_201708 | XX0001 | 201708 | 2017-08-01 00:00:00+03 | 0.00 (1 row) INSERT 0 1
=> INSERT INTO bookings_list VALUES ('XX0001','201708','2017-08-01',100) ON CONFLICT(book_ref,book_month) DO UPDATE SET total_amount = 100 RETURNING tableoid::regclass, *;
tableoid | book_ref | book_month | book_date | total_amount ----------------------+----------+------------+------------------------+-------------- bookings_list_201708 | XX0001 | 201708 | 2017-08-01 00:00:00+03 | 100.00 (1 row) INSERT 0 1
Fremdschlüssel
In der Demo-Datenbank bezieht sich die Tickets-Tabelle auf Buchungen.
Um den Fremdschlüssel zu ermöglichen, fügen Sie die Spalte book_month hinzu und teilen Sie sie gleichzeitig wie bookings_list monatlich in Abschnitte auf.
=> CREATE TABLE tickets_list ( ticket_no character(13), book_month character(6), book_ref character(6) NOT NULL, passenger_id varchar(20) NOT NULL, passenger_name text NOT NULL, contact_data jsonb, PRIMARY KEY (ticket_no, book_month), FOREIGN KEY (book_ref, book_month) REFERENCES bookings_list (book_ref, book_month) ) PARTITION BY LIST (book_month);
Die Einschränkung FOREIGN KEY ist einen genaueren Blick wert. Dies ist einerseits der Fremdschlüssel
aus der partitionierten Tabelle (tickets_list) und andererseits der Schlüssel
zur partitionierten Tabelle (bookings_list). Daher werden Fremdschlüssel für partitionierte Tabellen in beide Richtungen unterstützt.
Abschnitte erstellen:
=> WITH dates AS ( SELECT date_trunc('month',min(book_date)) min_date, date_trunc('month',max(book_date)) max_date FROM bookings ), partition AS ( SELECT to_char(g.month, 'YYYYMM') AS book_month FROM dates, generate_series(dates.min_date, dates.max_date, '1 month'::interval) AS g(month) ) SELECT format('CREATE TABLE %I PARTITION OF tickets_list FOR VALUES IN (%L)', 'tickets_list_' || partition.book_month, partition.book_month) FROM partition\gexec
CREATE TABLE CREATE TABLE CREATE TABLE
=> \d+ tickets_list
Partitioned table "bookings.tickets_list" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ----------------+-----------------------+-----------+----------+---------+----------+--------------+------------- ticket_no | character(13) | | not null | | extended | | book_month | character(6) | | not null | | extended | | book_ref | character(6) | | not null | | extended | | passenger_id | character varying(20) | | not null | | extended | | passenger_name | text | | not null | | extended | | contact_data | jsonb | | | | extended | | Partition key: LIST (book_month) Indexes: "tickets_list_pkey" PRIMARY KEY, btree (ticket_no, book_month) Foreign-key constraints: "tickets_list_book_ref_book_month_fkey" FOREIGN KEY (book_ref, book_month) REFERENCES bookings_list(book_ref, book_month) Partitions: tickets_list_201706 FOR VALUES IN ('201706'), tickets_list_201707 FOR VALUES IN ('201707'), tickets_list_201708 FOR VALUES IN ('201708')
Wir füllen aus:
=> INSERT INTO tickets_list (ticket_no,book_month,book_ref,passenger_id,passenger_name,contact_data) SELECT t.ticket_no,b.book_month,t.book_ref, t.passenger_id,t.passenger_name,t.contact_data FROM bookings_list b JOIN tickets t ON (b.book_ref = t.book_ref);
INSERT 0 366733
=> VACUUM ANALYZE tickets_list;
Verteilung der Linien in Abschnitte:
=> SELECT tableoid::regclass, count(*) FROM tickets_list GROUP BY tableoid;
tableoid | count ---------------------+-------- tickets_list_201706 | 10160 tickets_list_201707 | 232755 tickets_list_201708 | 123818 (3 rows)
Verbindungs- und Aggregationsanforderungen
Verbinden Sie zwei Tabellen, die auf dieselbe Weise partitioniert sind:
=> EXPLAIN (COSTS OFF) SELECT b.* FROM bookings_list b JOIN tickets_list t ON (b.book_ref = t.book_ref and b.book_month = t.book_month);
QUERY PLAN ---------------------------------------------------------------------------- Hash Join Hash Cond: ((t.book_ref = b.book_ref) AND (t.book_month = b.book_month)) -> Append -> Seq Scan on tickets_list_201706 t -> Seq Scan on tickets_list_201707 t_1 -> Seq Scan on tickets_list_201708 t_2 -> Hash -> Append -> Seq Scan on bookings_list_201706 b -> Seq Scan on bookings_list_201707 b_1 -> Seq Scan on bookings_list_201708 b_2 (11 rows)
Vor dem Starten einer Verbindung kombiniert jede Tabelle zunächst die Abschnitte, die in die Abfragebedingung fallen.
Sie können jedoch zuerst die entsprechenden monatlichen Abschnitte beider Tabellen kombinieren und dann das Ergebnis kombinieren. Dies kann erreicht werden, indem der Parameter enable_partitionwise_join aktiviert wird:
=> SET enable_partitionwise_join = ON; => EXPLAIN (COSTS OFF) SELECT b.* FROM bookings_list b JOIN tickets_list t ON (b.book_ref = t.book_ref and b.book_month = t.book_month);
QUERY PLAN ------------------------------------------------------------------------------------------ Append -> Hash Join Hash Cond: ((t.book_ref = b.book_ref) AND (t.book_month = b.book_month)) -> Seq Scan on tickets_list_201706 t -> Hash -> Seq Scan on bookings_list_201706 b -> Hash Join Hash Cond: ((t_1.book_ref = b_1.book_ref) AND (t_1.book_month = b_1.book_month)) -> Seq Scan on tickets_list_201707 t_1 -> Hash -> Seq Scan on bookings_list_201707 b_1 -> Hash Join Hash Cond: ((t_2.book_ref = b_2.book_ref) AND (t_2.book_month = b_2.book_month)) -> Seq Scan on tickets_list_201708 t_2 -> Hash -> Seq Scan on bookings_list_201708 b_2 (16 rows)
Jetzt werden zuerst die entsprechenden Abschnitte der beiden Tabellen verknüpft und dann die Verknüpfungsergebnisse kombiniert.
Eine ähnliche Situation mit Aggregation:
=> EXPLAIN (COSTS OFF) SELECT count(*) FROM bookings_list;
QUERY PLAN ------------------------------------------------------------------- Finalize Aggregate -> Gather Workers Planned: 2 -> Partial Aggregate -> Parallel Append -> Parallel Seq Scan on bookings_list_201707 -> Parallel Seq Scan on bookings_list_201708 -> Parallel Seq Scan on bookings_list_201706 (8 rows)
Beachten Sie, dass Abschnittsscans parallel durchgeführt werden können. Aber zuerst kommen die Abschnitte zusammen, erst dann beginnt die Aggregation. Alternativ können Sie in jedem Abschnitt eine Aggregation durchführen und dann das Ergebnis kombinieren:
=> SET enable_partitionwise_aggregate = ON; => EXPLAIN (COSTS OFF) SELECT count(*) FROM bookings_list;
QUERY PLAN ------------------------------------------------------------------- Finalize Aggregate -> Gather Workers Planned: 2 -> Parallel Append -> Partial Aggregate -> Parallel Seq Scan on bookings_list_201707 -> Partial Aggregate -> Parallel Seq Scan on bookings_list_201708 -> Partial Aggregate -> Parallel Seq Scan on bookings_list_201706 (10 rows)
Diese Funktionen sind besonders wichtig, wenn ein Teil der Abschnitte externe Tabellen sind. Standardmäßig sind beide deaktiviert, weil Geeignete Parameter wirken sich auf die Zeit des Plans aus, werden jedoch möglicherweise nicht immer verwendet.
Hash-Partitionierung
Eine dritte Möglichkeit, eine Tabelle zu partitionieren, ist die Hash-Partitionierung.
Erstellen einer Tabelle:
=> CREATE TABLE bookings_hash ( book_ref character(6) PRIMARY KEY, book_date timestamptz NOT NULL, total_amount numeric(10,2) ) PARTITION BY HASH(book_ref);
In dieser Version von book_ref können Sie ihn als Partitionierungsschlüssel sofort als Primärschlüssel deklarieren.
Teilen Sie in drei Abschnitte:
=> CREATE TABLE bookings_hash_p0 PARTITION OF bookings_hash FOR VALUES WITH (MODULUS 3, REMAINDER 0); => CREATE TABLE bookings_hash_p1 PARTITION OF bookings_hash FOR VALUES WITH (MODULUS 3, REMAINDER 1); => CREATE TABLE bookings_hash_p2 PARTITION OF bookings_hash FOR VALUES WITH (MODULUS 3, REMAINDER 2);
Füllen mit automatischem Layout in Abschnitten:
=> INSERT INTO bookings_hash SELECT * FROM bookings;
INSERT 0 262788
Die Verteilung der Linien in Abschnitten erfolgt gleichmäßig:
=> SELECT tableoid::regclass AS partition, count(*) FROM bookings_hash GROUP BY tableoid;
partition | count ------------------+------- bookings_hash_p0 | 87649 bookings_hash_p1 | 87651 bookings_hash_p2 | 87488 (3 rows)
Neuer Befehl zum Anzeigen partitionierter Objekte:
=> \dP+
List of partitioned relations Schema | Name | Owner | Type | Table | Total size | Description ----------+--------------------+---------+-------------------+----------------+------------+------------- bookings | bookings_hash | student | partitioned table | | 13 MB | bookings | bookings_list | student | partitioned table | | 15 MB | bookings | bookings_range | student | partitioned table | | 13 MB | bookings | tickets_list | student | partitioned table | | 50 MB | bookings | book_date_idx | student | partitioned index | bookings_range | 5872 kB | bookings | bookings_hash_pkey | student | partitioned index | bookings_hash | 5800 kB | bookings | bookings_list_pkey | student | partitioned index | bookings_list | 8120 kB | bookings | tickets_list_pkey | student | partitioned index | tickets_list | 19 MB | (8 rows)
=> VACUUM ANALYZE bookings_hash;
Unterabfragen und verschachtelte Schleifenverknüpfungen
Der Ausschluss von Abschnitten zur Laufzeit ist mit verschachtelten Schleifenverbindungen möglich.
Verteilung der ersten 10 Buchungen in Abschnitte:
=> WITH top10 AS ( SELECT tableoid::regclass AS partition, * FROM bookings_hash ORDER BY book_ref LIMIT 10 ) SELECT partition, count(*) FROM top10 GROUP BY 1 ORDER BY 1;
partition | count ------------------+------- bookings_hash_p0 | 3 bookings_hash_p1 | 3 bookings_hash_p2 | 4 (3 rows)
Schauen wir uns den Abfrageausführungsplan mit dem Join der Tabelle bookings_hash und der vorherigen Unterabfrage an:
=> EXPLAIN (ANALYZE,COSTS OFF,TIMING OFF) WITH top10 AS ( SELECT tableoid::regclass AS partition, * FROM bookings ORDER BY book_ref LIMIT 10 ) SELECT bh.* FROM bookings_hash bh JOIN top10 ON bh.book_ref = top10.book_ref;
QUERY PLAN ----------------------------------------------------------------------------------------------------- Nested Loop (actual rows=10 loops=1) -> Limit (actual rows=10 loops=1) -> Index Only Scan using bookings_pkey on bookings (actual rows=10 loops=1) Heap Fetches: 0 -> Append (actual rows=1 loops=10) -> Index Scan using bookings_hash_p0_pkey on bookings_hash_p0 bh (actual rows=1 loops=3) Index Cond: (book_ref = bookings.book_ref) -> Index Scan using bookings_hash_p1_pkey on bookings_hash_p1 bh_1 (actual rows=1 loops=3) Index Cond: (book_ref = bookings.book_ref) -> Index Scan using bookings_hash_p2_pkey on bookings_hash_p2 bh_2 (actual rows=1 loops=4) Index Cond: (book_ref = bookings.book_ref) Planning Time: 0.632 ms Execution Time: 0.278 ms (13 rows)
Die Verbindung wird mit der Nested-Loop-Methode hergestellt. Die äußere Schleife gemäß dem allgemeinen Tabellenausdruck wird zehnmal ausgeführt. Achten Sie jedoch auf die Anzahl der Aufrufe der Tabellenabschnitte (Schleifen). Für jeden book_ref-Wert der äußeren Schleife wird nur der Abschnitt gescannt, in dem dieser Wert in der bookings_hash-Tabelle gespeichert ist.Vergleichen Sie mit dem Ausschluss deaktivierter Abschnitte: => SET enable_partition_pruning TO OFF; => EXPLAIN (ANALYZE,COSTS OFF,TIMING OFF) WITH top10 AS ( SELECT tableoid::regclass AS partition, * FROM bookings ORDER BY book_ref LIMIT 10 ) SELECT bh.* FROM bookings_hash bh JOIN top10 ON bh.book_ref = top10.book_ref;
QUERY PLAN ------------------------------------------------------------------------------------------------------ Nested Loop (actual rows=10 loops=1) -> Limit (actual rows=10 loops=1) -> Index Only Scan using bookings_pkey on bookings (actual rows=10 loops=1) Heap Fetches: 0 -> Append (actual rows=1 loops=10) -> Index Scan using bookings_hash_p0_pkey on bookings_hash_p0 bh (actual rows=0 loops=10) Index Cond: (book_ref = bookings.book_ref) -> Index Scan using bookings_hash_p1_pkey on bookings_hash_p1 bh_1 (actual rows=0 loops=10) Index Cond: (book_ref = bookings.book_ref) -> Index Scan using bookings_hash_p2_pkey on bookings_hash_p2 bh_2 (actual rows=0 loops=10) Index Cond: (book_ref = bookings.book_ref) Planning Time: 0.886 ms Execution Time: 0.771 ms (13 rows)
=> RESET enable_partition_pruning;
Wenn Sie die Auswahl auf eine Reservierung reduzieren, werden zwei Abschnitte überhaupt nicht angezeigt: => EXPLAIN (ANALYZE,COSTS OFF,TIMING OFF) WITH top AS ( SELECT tableoid::regclass AS partition, * FROM bookings ORDER BY book_ref LIMIT 1 ) SELECT bh.* FROM bookings_hash bh JOIN top ON bh.book_ref = top.book_ref;
QUERY PLAN --------------------------------------------------------------------------------------------------- Nested Loop (actual rows=1 loops=1) -> Limit (actual rows=1 loops=1) -> Index Only Scan using bookings_pkey on bookings (actual rows=1 loops=1) Heap Fetches: 0 -> Append (actual rows=1 loops=1) -> Index Scan using bookings_hash_p0_pkey on bookings_hash_p0 bh (actual rows=1 loops=1) Index Cond: (book_ref = bookings.book_ref) -> Index Scan using bookings_hash_p1_pkey on bookings_hash_p1 bh_1 (never executed) Index Cond: (book_ref = bookings.book_ref) -> Index Scan using bookings_hash_p2_pkey on bookings_hash_p2 bh_2 (never executed) Index Cond: (book_ref = bookings.book_ref) Planning Time: 0.250 ms Execution Time: 0.090 ms (13 rows)
Anstelle einer Unterabfrage können Sie die Funktion verwenden, die eine Menge mit der Variabilitätskategorie STABLE zurückgibt: => CREATE OR REPLACE FUNCTION get_book_ref(top int) RETURNS SETOF bookings AS $$ BEGIN RETURN QUERY EXECUTE 'SELECT * FROM bookings ORDER BY book_ref LIMIT $1' USING top; END;$$ LANGUAGE plpgsql STABLE;
=> EXPLAIN (ANALYZE,COSTS OFF,TIMING OFF) SELECT * FROM bookings_hash bh JOIN get_book_ref(10) f ON bh.book_ref = f.book_ref;
QUERY PLAN ----------------------------------------------------------------------------------------------------- Nested Loop (actual rows=10 loops=1) -> Function Scan on get_book_ref f (actual rows=10 loops=1) -> Append (actual rows=1 loops=10) -> Index Scan using bookings_hash_p0_pkey on bookings_hash_p0 bh (actual rows=1 loops=3) Index Cond: (book_ref = f.book_ref) -> Index Scan using bookings_hash_p1_pkey on bookings_hash_p1 bh_1 (actual rows=1 loops=3) Index Cond: (book_ref = f.book_ref) -> Index Scan using bookings_hash_p2_pkey on bookings_hash_p2 bh_2 (actual rows=1 loops=4) Index Cond: (book_ref = f.book_ref) Planning Time: 0.175 ms Execution Time: 0.843 ms (11 rows)
Zusammenfassung
Zusammenfassend können wir sagen, dass die in PostgreSQL 12 integrierte oder deklarative Partitionierung eine Vielzahl von Funktionen erhalten hat und es sicher empfohlen werden kann, die Partitionierung durch Vererbung zu ersetzen.