Pas de très gros volumes de données

Cet article décrit les fonctionnalités fournies par le partitionnement intégré ou déclaratif dans la version 12 de PostgreSQL. La démonstration a été préparée pour la conférence éponyme lors de la conférence HighLoad ++ Siberia 2019 (mise à jour: une vidéo est apparue avec la conférence).

Tous les exemples sont exécutés sur la version bêta récemment apparue:

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

Les exemples utilisent les tables de réservations et de tickets de la base de données de démonstration. Le tableau de réservation contient des entrées pour trois mois de juin à août 2017 et a la structure suivante:

 => \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) 

Une réservation peut comprendre plusieurs billets. La structure de la table avec des 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) 

Ces informations devraient être suffisantes pour comprendre des exemples dans lesquels nous allons essayer de partitionner des tables.

→ En savoir plus sur la base de démonstration ici.

Partitionnement de la plage

Tout d'abord, essayez de partitionner le tableau des réservations par plage de dates. Dans ce cas, la table serait créée comme ceci:

 => CREATE TABLE bookings_range ( book_ref character(6), book_date timestamptz, total_amount numeric(10,2) ) PARTITION BY RANGE(book_date); 

Sections distinctes pour chaque mois:

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

Pour indiquer les limites d'une section, vous pouvez utiliser non seulement des constantes, mais également des expressions, par exemple, un appel de fonction. La valeur de l'expression est calculée au moment où la section est créée et stockée dans le répertoire système:

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

Description du tableau:

 => \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') 

C’est assez. Aucun déclencheur pour l'insertion d'enregistrements; aucune contrainte CHECK nécessaire. Le paramètre CONSTRAINT_EXCLUSION n'est pas non plus nécessaire, vous pouvez même le désactiver:

 => SET constraint_exclusion = OFF; 

Remplissage avec mise en page automatique en sections:

 => INSERT INTO bookings_range SELECT * FROM bookings; 

 INSERT 0 262788 

La syntaxe déclarative masque toujours les tables héritées, vous pouvez donc voir la distribution des lignes dans les sections par requête:

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

Mais il n'y a pas de données dans la table parent:

 => SELECT * FROM ONLY bookings_range; 

  book_ref | book_date | total_amount ----------+-----------+-------------- (0 rows) 

Vérifiez l'exclusion des sections dans le plan de requête:

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

Numérisation d'une seule section, comme prévu.

L'exemple suivant utilise la fonction to_timestamp avec la catégorie de variabilité STABLE au lieu d'une constante:

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

La valeur de la fonction est calculée lorsque le plan de requête est initialisé et une partie des sections est exclue de la visualisation (sous-plans supprimés).

Mais cela ne fonctionne que pour SELECT. Lors de la modification des données, l'exclusion de section basée sur les valeurs des fonctions STABLE n'est pas encore implémentée:

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

Par conséquent, vous devez utiliser des constantes:

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

Tri d'index

Pour effectuer la requête suivante, le tri des résultats obtenus à partir de différentes sections est requis. Par conséquent, dans le plan de requête, nous voyons le nœud SORT et le coût initial élevé du plan:

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

Créez un index le book_date. Au lieu d'un seul index global, des index sont créés dans chaque section:

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

La requête précédente avec tri peut désormais utiliser l'index sur la clé de partition et renvoyer le résultat de différentes sections immédiatement sous forme triée. Le nœud SORT n'est pas nécessaire et le coût minimum est requis pour produire la première ligne du résultat:

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

Les index partitionnés créés de cette manière sont pris en charge de manière centralisée. Lors de l'ajout d'une nouvelle section, un index sera automatiquement créé dessus. Et vous ne pouvez pas supprimer l'index d'une seule section:

 => 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. 

Seulement en totalité:

 => DROP INDEX book_date_idx; 

 DROP INDEX 

CRÉER UN INDEX ... CONCURRENTEMENT

Lors de la création d'un index sur une table partitionnée, vous ne pouvez pas spécifier CONCURRENTLY.

Mais vous pouvez faire ce qui suit. Tout d'abord, nous créons l'index uniquement sur la table principale, il recevra le statut invalide:

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

Créez ensuite des index sur toutes les sections avec l'option CONCURRENTLY:

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

Maintenant, nous connectons les index locaux au global:

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

Ceci est similaire à la connexion des tables de partition, que nous verrons un peu plus tard. Dès que toutes les sections d'index sont connectées, l'index principal change son statut:

 => SELECT indisvalid FROM pg_index WHERE indexrelid::regclass::text = 'book_date_idx'; 

  indisvalid ------------ t (1 row) 

Connecter et déconnecter les sections

La création automatique de sections n'est pas fournie. Par conséquent, ils doivent être créés à l'avance, avant que des enregistrements avec de nouvelles valeurs de la clé de partitionnement soient ajoutés à la table.

Nous allons créer une nouvelle section pendant que d'autres transactions fonctionnent avec la table, en même temps nous allons regarder les verrous:

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

Le verrou AccessShareLock est imposé sur la table principale, toutes les sections et tous les index au début de l'instruction. Le calcul de la fonction to_timestamp et l'exclusion des sections se produit plus tard. Si une constante était utilisée à la place d'une fonction, seules la table principale et la section bookings_range_201707 seraient verrouillées. Par conséquent, si possible, spécifiez des constantes dans la demande - cela doit être fait, sinon le nombre de lignes dans pg_locks augmentera proportionnellement au nombre de sections, ce qui peut entraîner la nécessité d'augmenter max_locks_per_transaction.

Sans terminer la transaction précédente, créez la section suivante pour septembre dans une nouvelle session:

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

Lors de la création d'une nouvelle section, le verrou ShareUpdateExclusiveLock, compatible avec AccessShareLock, est imposé sur la table principale. Par conséquent, les opérations d'ajout de partitions n'entrent pas en conflit avec les requêtes sur une table partitionnée.

 => COMMIT; 

  || => COMMIT; 

Le partitionnement est effectué avec la commande ALTER TABLE ... DETACH PARTITION. La section elle-même n'est pas supprimée, mais devient une table indépendante. Les données peuvent être téléchargées à partir de celui-ci, elles peuvent être supprimées et si nécessaire reconnectées (ATTACH PARTITION).

Une autre option à désactiver consiste à supprimer la section avec la commande DROP TABLE.

Malheureusement, les deux options, DROP TABLE et DETACH PARTITION, utilisent le verrou AccessExclusiveLock sur la table principale.

Section par défaut

Si vous essayez d'ajouter un enregistrement pour lequel une section n'a pas encore été créée, une erreur se produit. Si ce comportement n'est pas souhaité, vous pouvez créer une section par défaut:

 => CREATE TABLE bookings_range_default PARTITION OF bookings_range DEFAULT; 

Supposons que lors de l'ajout d'enregistrements, ils aient mélangé la date sans spécifier de millénaire:

 => 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 

Nous notons que l'expression RETOURNE renvoie une nouvelle ligne, qui tombe dans la section par défaut.

Après avoir défini la date actuelle (modification de la clé de partitionnement), l'enregistrement se déplace automatiquement vers la section souhaitée, les déclencheurs ne sont pas nécessaires:

 => 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 

Sectionnement de la liste de valeurs

Dans la base de données de démonstration, la colonne book_ref doit être la clé primaire de la table des réservations. Cependant, le schéma de partitionnement sélectionné ne permet pas de créer une telle clé:

 => 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. 

La clé de partitionnement doit être incluse dans la clé primaire.

Pour décomposer par mois et toujours inclure book_ref dans la clé primaire, essayons un autre schéma de partitionnement de la table des réservations - selon la liste de valeurs. Pour ce faire, ajoutez la colonne redondante book_month comme clé de partition:

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

Nous formerons des sections de manière dynamique sur la base des données du tableau des réservations:

 => 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 

Voici ce qui s'est passé:

 => \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') 

Remplir la disposition en sections:

 => 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 

Comme une retraite. Pour remplir automatiquement book_month, il est tentant d'utiliser la nouvelle fonctionnalité de la version 12 - Colonnes GÉNÉRÉES TOUJOURS. Mais, malheureusement, ils ne peuvent pas être utilisés comme clé de partition. Par conséquent, la tâche de remplir le mois doit être résolue d'autres manières.

Des contraintes d'intégrité telles que CHECK et NOT NULL peuvent être créées sur une table partitionnée. Comme pour l'héritage, la spécification INHERIT / NOINHERIT indique si la restriction doit être héritée sur toutes les tables de partition. HÉRITAGE par défaut:

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

Une contrainte EXCLUDE ne peut être créée que localement sur des partitions.

Une recherche sur book_ref cherchera dans toutes les sections, mais par index, grâce au fait que book_ref est répertorié en premier:

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

Une recherche sur book_ref et une plage de sections ne doit chercher que dans la plage spécifiée:

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

La commande INSERT ... ON CONFLICT trouve correctement la section souhaitée et effectue la mise à jour:

 => 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 

Clés étrangères

Dans la base de données de démonstration, le tableau des tickets fait référence aux réservations.

Pour rendre la clé étrangère possible, ajoutez la colonne book_month et, en même temps, divisez-la en sections par mois, comme bookings_list.

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

La restriction FOREIGN KEY mérite un examen plus approfondi. D'une part, c'est la clé étrangère de la table partitionnée (tickets_list), et d'autre part, c'est la clé de la table partitionnée (bookings_list). Ainsi, les clés étrangères pour les tables partitionnées sont prises en charge dans les deux sens.

Créez des sections:

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

Nous remplissons:

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

Répartition des lignes en sections:

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

Demandes de connexion et d'agrégation

Joignez deux tables partitionnées de la même manière:

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

Avant de démarrer une connexion, chaque table combine d'abord les sections correspondant à la condition de requête.

Mais vous pouvez d'abord combiner les sections mensuelles correspondantes des deux tableaux, puis combiner le résultat. Ceci peut être réalisé en activant le paramètre enable_partitionwise_join:

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

Maintenant, tout d'abord, les sections correspondantes des deux tables sont jointes, puis les résultats des jointures sont combinés.

Une situation similaire avec agrégation:

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

Notez que les analyses de section peuvent être effectuées en parallèle. Mais d'abord les sections se rejoignent, ce n'est qu'ensuite que l'agrégation commence. Alternativement, vous pouvez effectuer une agrégation dans chaque section, puis combiner le résultat:

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

Ces fonctionnalités sont particulièrement importantes si une partie des sections sont des tables externes. Par défaut, les deux sont désactivés car les paramètres appropriés affectent l'heure du plan, mais ne sont pas toujours utilisés.

Partitionnement de hachage

Une troisième façon de partitionner une table est le partitionnement de hachage.

Création d'une table:

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

Dans cette version de book_ref, en tant que clé de partitionnement, vous pouvez immédiatement le déclarer comme clé primaire.

Divisez-vous en trois sections:

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

Remplissage avec mise en page automatique en sections:

 => INSERT INTO bookings_hash SELECT * FROM bookings; 

 INSERT 0 262788 

La répartition des lignes dans les sections se produit de manière uniforme:

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

Nouvelle commande pour afficher les objets partitionnés:

 => \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; 

Sous-requêtes et jointures de boucles imbriquées

L'exclusion de section lors de l'exécution est possible avec des connexions de boucle imbriquées.

Répartition des 10 premières réservations en sections:

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

Regardons le plan d'exécution de la requête avec la jointure de la table bookings_hash et la sous-requête précédente:

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

La connexion est établie à l'aide de la méthode de boucle imbriquée. La boucle externe selon l'expression générale de la table est exécutée 10 fois. Mais faites attention au nombre d'appels aux sections de table (boucles). Pour chaque valeur book_ref de la boucle externe, seule la section est analysée où cette valeur est stockée dans la table bookings_hash.

Comparer avec l'exclusion de la section désactivée:

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

Si vous réduisez la sélection à une seule réservation, alors deux sections ne seront pas visibles du tout:

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

Au lieu d'une sous-requête, vous pouvez utiliser la fonction renvoyant un ensemble avec la catégorie de variabilité STABLE:

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

Résumé

En résumé, nous pouvons dire que le partitionnement intégré ou déclaratif dans PostgreSQL 12 a reçu un ensemble riche de fonctionnalités et il peut être recommandé en toute sécurité de remplacer le partitionnement par héritage.