Partitionnement et sous-partitionnement

Poupées russes

      Dans cet article, nous avons effleuré les possibilités du partionnement natif de PostgreSQL 10. Nous allons tenter de pousser plus loin l’expérience avec 200 millions de lignes et cent partitions.
      Comme d’habitude, nous travaillons avec le clan des Géants dont l’occupation principale est de lancer des cailloux. Le clan compte 10 géants. La table LANCERS_P va être partitionnée par plages de valeurs (1 an de lancers par partition), chaque partition étant ensuite sous-partitionnée par liste (1 géant par sous-partition). Les performances vont être comparées avec la table LANCERS qui n’est pas partitionnée. L’indexation BRIN ne consommant quasi pas d’espace est la seule utilisée pour les données de LANCERS_P comme de LANCERS.
      La machine virtuelle utilisée a 8 vieux coeurs Xeon. Ses 32 Go de RAM sont suffisants pour mettre les données de la table lancers en cache, ce qui annule une partie de l’avantage théorique du partionnement qui permet de diminuer les lectures sur certaines requêtes. Va-t-il se montrer malgré tout convaincant ?
      Démonstration avec PostgreSQL 10 beta :

select version(); version ----------------------------------------------------------------------------------------------------- PostgreSQL 10beta1 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18) 6.3.0 20170516, 64-bit create table lancers(dtl timestamp, idg integer, perf integer); CREATE TABLE create index lancers_br1 on lancers using brin(dtl); CREATE INDEX \d+ lancers Table " public.lancers " Colonne | Type | Collation | Nullable | Default | Stockage | Cible de statistiques | Description ---------+-----------------------------+-----------+----------+---------+----------+-----------------------+------------- dtl | timestamp without time zone | | | | plain | | idg | integer | | | | plain | | perf | integer | | | | plain | | Index : "lancers_br1" brin (dtl) create table lancers_p (dtl timestamp, idg integer, perf integer) partition by range(dtl); create table lancers_p_2010 partition of lancers_p for values FROM ('2010-01-01') TO ('2011-01-01') partition by list(idg); create table lancers_p_2010_001 partition of lancers_p_2010 for values in (1); ... create table lancers_p_2010_010 partition of lancers_p_2010 for values in (10); create index lancers_p_2010_001_br1 on lancers_p_2010_001 using brin(dtl); ... create index lancers_p_2010_010_br1 on lancers_p_2010_010 using brin(dtl); ... create table lancers_p_2019 partition of lancers_p for values FROM ('2019-01-01') TO ('2020-01-01') partition by list(idg); create table lancers_p_2019_001 partition of lancers_p_2019 for values in (1); ... create table lancers_p_2019_010 partition of lancers_p_2019 for values in (10); create index lancers_p_2019_001_br1 on lancers_p_2019_001 using brin(dtl); ... create index lancers_p_2019_010_br1 on lancers_p_2019_010 using brin(dtl); \d+ lancers_p Table " public.lancers_p " Colonne | Type | Collation | Nullable | Default | Stockage | Cible de statistiques | Description ---------+-----------------------------+-----------+----------+---------+----------+-----------------------+------------- dtl | timestamp without time zone | | not null | | plain | | idg | integer | | | | plain | | perf | integer | | | | plain | | Partition key: RANGE (dtl) Partitions: lancers_p_2010 FOR VALUES FROM ('2010-01-01 00:00:00') TO ('2011-01-01 00:00:00'), ... lancers_p_2019 FOR VALUES FROM ('2019-01-01 00:00:00') TO ('2020-01-01 00:00:00') WITH serie(i) AS (SELECT generate_series(200000000,1,-1)) insert into lancers(dtl, idg, perf) select current_timestamp - (i || ' seconds')::interval, trunc(random() * 10 + 1), case when random() <= 0.0001 then 50000 + trunc(random() * 50000 + 1) else trunc(random() * 50000 + 1) end from serie; INSERT 0 200000000 Time: 1385049,778 ms (23:05,050) WITH serie(i) AS (SELECT generate_series(200000000,1,-1)) insert into lancers_p(dtl, idg, perf) select current_timestamp - (i || ' seconds')::interval, trunc(random() * 10 + 1), case when random() <= 0.0001 then 50000 + trunc(random() * 50000 + 1) else trunc(random() * 50000 + 1) end from serie; INSERT 0 200000000 Time: 1614628,857 ms (26:54,629) vacuum analyze geants; VACUUM Temps : 18,443 ms vacuum analyze lancers; VACUUM Time: 157899,889 ms (02:37,900) vacuum analyze lancers_p; VACUUM Time: 139852,533 ms (02:19,853)

      La table LANCERS_P va donc compter 100 partitions pour réellement accueillir les données. Ces partitions peuvent initialement être créées manuellement, par un script quelconque ou via l’extension pg_partman. Certaines partitions basées sur la date de lancer ont ici été créées par anticipation. L’ajout d’un géant au clan est ici sans doute un événement rare qui nous conduira à ajouter une ligne dans la table GEANTS mais aussi une sous-partition pour l’année courante et chaque année suivante pour laquelle une partition a été créée.
      Au niveau des performances, l’insertion de 200 millions de lignes a pris un peu plus de 23 minutes dans la table LANCERS non partionnée contre un peu moins de 27 minutes dans la table LANCERS_P partitionnée. Le surcoût du partitionnement est donc ici modéré.
      A présent place aux SELECT, en commençant par une requête portant une infime partie des valeurs d’une seule partition :

set max_parallel_workers_per_gather = 7; SET explain select avg(perf) from lancers where idg=5 and dtl >= to_timestamp('29/02/2016', 'DD/MM/YYYY') and dtl < to_timestamp('01/03/2016', 'DD/MM/YYYY'); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------ Aggregate (cost=286396.34..286396.35 rows=1 width=32) -> Bitmap Heap Scan on lancers (cost=136.52..286372.43 rows=9560 width=4) Recheck Cond: ((dtl >= to_timestamp('29/02/2016'::text, 'DD/MM/YYYY'::text)) AND (dtl < to_timestamp('01/03/2016'::text, 'DD/MM/YYYY'::text))) Filter: (idg = 5) -> Bitmap Index Scan on lancers_br1 (cost=0.00..134.13 rows=94719 width=0) Index Cond: ((dtl >= to_timestamp('29/02/2016'::text, 'DD/MM/YYYY'::text)) AND (dtl < to_timestamp('01/03/2016'::text, 'DD/MM/YYYY'::text))) select avg(perf) from lancers where idg=5 and dtl >= to_timestamp('29/02/2016', 'DD/MM/YYYY') and dtl < to_timestamp('01/03/2016', 'DD/MM/YYYY'); Temps : 236,620 ms ... Temps : 263,635 ms ... Temps : 250,413 ms explain select avg(perf) from lancers_p where idg=5 and dtl >= to_timestamp('29/02/2016', 'DD/MM/YYYY') and dtl < to_timestamp('01/03/2016', 'DD/MM/YYYY'); QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=115825.57..115825.58 rows=1 width=32) -> Gather (cost=115825.25..115825.56 rows=3 width=32) Workers Planned: 3 -> Partial Aggregate (cost=114825.25..114825.26 rows=1 width=32) -> Append (cost=0.00..114818.33 rows=2769 width=4) -> Parallel Seq Scan on lancers_p_2010_005 (cost=0.00..34.49 rows=1 width=4) Filter: ((idg = 5) AND (dtl >= to_timestamp('29/02/2016'::text, 'DD/MM/YYYY'::text)) AND (dtl < to_timestamp('01/03/2016'::text, 'DD/MM/YYYY'::text))) -> Parallel Bitmap Heap Scan on lancers_p_2011_005 (cost=12.04..17059.24 rows=1 width=4) Recheck Cond: ((dtl >= to_timestamp('29/02/2016'::text, 'DD/MM/YYYY'::text)) AND (dtl < to_timestamp('01/03/2016'::text, 'DD/MM/YYYY'::text))) Filter: (idg = 5) -> Bitmap Index Scan on lancers_p_2011_005_br1 (cost=0.00..12.04 rows=23608 width=0) Index Cond: ((dtl >= to_timestamp('29/02/2016'::text, 'DD/MM/YYYY'::text)) AND (dtl < to_timestamp('01/03/2016'::text, 'DD/MM/YYYY'::text))) ... ... -> Parallel Seq Scan on lancers_p_2018_005 (cost=0.00..34.49 rows=1 width=4) Filter: ((idg = 5) AND (dtl >= to_timestamp('29/02/2016'::text, 'DD/MM/YYYY'::text)) AND (dtl < to_timestamp('01/03/2016'::text, 'DD/MM/YYYY'::text))) -> Parallel Seq Scan on lancers_p_2019_005 (cost=0.00..34.49 rows=1 width=4) Filter: ((idg = 5) AND (dtl >= to_timestamp('29/02/2016'::text, 'DD/MM/YYYY'::text)) AND (dtl < to_timestamp('01/03/2016'::text, 'DD/MM/YYYY'::text))) (46 lignes) select avg(perf) from lancers_p where idg=5 and dtl >= to_timestamp('29/02/2016', 'DD/MM/YYYY') and dtl < to_timestamp('01/03/2016', 'DD/MM/YYYY'); Temps : 50,569 ms ... Temps : 51,031 ms ... Temps : 50,153 ms

Nota Bene

      Pas mal à première vue puisque le partitionnement permet de passer de 250ms à 50ms.
      Oui mais pourquoi n’attaquons-nous pas qu’une seule partition ?!? A priori un problème de types. La fonction TO_TIMESTAMP utilisée dans la clause WHERE renvoie un TIMESTAMP WITH TIMEZONE alors que la table LANCERS_P est partitionnée sur dtl qui est un TIMESTAMP.
      Nous allons modifier la requête pour utiliser un TIMESTAMP WITHOUT TIME ZONE via une syntaxe conforme au standard ISO 8601 :

explain select avg(perf) from lancers where idg=5 and dtl >= timestamp '2016-02-29' and dtl < timestamp '2016-03-01'; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=285922.74..285922.75 rows=1 width=32) -> Bitmap Heap Scan on lancers (cost=136.52..285898.83 rows=9560 width=4) Recheck Cond: ((dtl >= '2016-02-29 00:00:00'::timestamp without time zone) AND (dtl < '2016-03-01 00:00:00'::timestamp without time zone)) Filter: (idg = 5) -> Bitmap Index Scan on lancers_br1 (cost=0.00..134.13 rows=94719 width=0) Index Cond: ((dtl >= '2016-02-29 00:00:00'::timestamp without time zone) AND (dtl < '2016-03-01 00:00:00'::timestamp without time zone)) select avg(perf) from lancers where idg=5 and dtl >= timestamp '2016-02-29' and dtl < timestamp '2016-03-01'; Temps : 44,754 ms ... Temps : 44,946 ms ... Temps : 45,256 ms explain select avg(perf) from lancers_p where idg=5 and dtl >= timestamp '2016-02-29' and dtl < timestamp '2016-03-01'; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=18440.68..18440.69 rows=1 width=32) -> Append (cost=14.17..18419.29 rows=8557 width=4) -> Bitmap Heap Scan on lancers_p_2016_005 (cost=14.17..18419.29 rows=8557 width=4) Recheck Cond: ((dtl >= '2016-02-29 00:00:00'::timestamp without time zone) AND (dtl < '2016-03-01 00:00:00'::timestamp without time zone)) Filter: (idg = 5) -> Bitmap Index Scan on lancers_p_2016_005_br1 (cost=0.00..12.03 rows=23597 width=0) Index Cond: ((dtl >= '2016-02-29 00:00:00'::timestamp without time zone) AND (dtl < '2016-03-01 00:00:00'::timestamp without time zone)) select avg(perf) from lancers_p where idg=5 and dtl >= timestamp '2016-02-29' and dtl < timestamp '2016-03-01'; Temps : 18,195 ms ... Temps : 19,449 ms ... Temps : 17,943 ms

      La modification a bénéficié à la requête portant sur la table LANCERS_P partitionnée puisque nous passons sous les 20ms. Le plan d’exécution montre bien qu’une seule partition est cette fois sollicitée.
      Mais il faut également noter que nous sommes passés sous les 50ms pour la requête portant sur la table LANCERS non partitionnée, ce qui constitue un gain encore plus important par rapport au temps d’exécution initial.
      A présent nous allons passer sur une requête attaquant 1 partition complète :

explain select avg(perf) from lancers where idg=5 and dtl >= timestamp '2016-01-01' and dtl < timestamp '2017-01-01'; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=1163598.81..1163598.82 rows=1 width=32) -> Gather (cost=1163598.08..1163598.79 rows=7 width=32) Workers Planned: 7 -> Partial Aggregate (cost=1162598.08..1162598.09 rows=1 width=32) -> Parallel Bitmap Heap Scan on lancers (cost=980.87..1161449.57 rows=459406 width=4) Recheck Cond: ((dtl >= '2016-01-01 00:00:00'::timestamp without time zone) AND (dtl < '2017-01-01 00:00:00'::timestamp without time zone)) Filter: (idg = 5) -> Bitmap Index Scan on lancers_br1 (cost=0.00..176.91 rows=31754677 width=0) Index Cond: ((dtl >= '2016-01-01 00:00:00'::timestamp without time zone) AND (dtl < '2017-01-01 00:00:00'::timestamp without time zone)) select avg(perf) from lancers where idg=5 and dtl >= timestamp '2016-01-01' and dtl < timestamp '2017-01-01'; Time: 1804,751 ms (00:01,805) ... Time: 1835,381 ms (00:01,835) ... Time: 1812,111 ms (00:01,812) explain select avg(perf) from lancers_p where idg=5 and dtl >= timestamp '2016-01-01' and dtl < timestamp '2017-01-01'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Finalize Aggregate (cost=38493.02..38493.03 rows=1 width=32) -> Gather (cost=38492.70..38493.01 rows=3 width=32) Workers Planned: 3 -> Partial Aggregate (cost=37492.70..37492.71 rows=1 width=32) -> Append (cost=0.00..34943.18 rows=1019806 width=4) -> Parallel Seq Scan on lancers_p_2016_005 (cost=0.00..34943.18 rows=1019806 width=4) Filter: ((dtl >= '2016-01-01 00:00:00'::timestamp without time zone) AND (dtl < '2017-01-01 00:00:00'::timestamp without time zone) AND (idg = 5)) (7 lignes) select avg(perf) from lancers_p where idg=5 and dtl >= timestamp '2016-01-01' and dtl < timestamp '2017-01-01'; Temps : 394,411 ms ... Temps : 400,804 ms ... Temps : 393,034 ms set min_parallel_table_scan_size="256kB"; explain select avg(perf) from lancers_p where idg=5 and dtl >= timestamp '2016-01-01' and dtl < timestamp '2017-01-01'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Finalize Aggregate (cost=27127.88..27127.89 rows=1 width=32) -> Gather (cost=27127.15..27127.86 rows=7 width=32) Workers Planned: 7 -> Partial Aggregate (cost=26127.15..26127.16 rows=1 width=32) -> Append (cost=0.00..24998.08 rows=451629 width=4) -> Parallel Seq Scan on lancers_p_2016_005 (cost=0.00..24998.08 rows=451629 width=4) Filter: ((dtl >= '2016-01-01 00:00:00'::timestamp without time zone) AND (dtl < '2017-01-01 00:00:00'::timestamp without time zone) AND (idg = 5)) select avg(perf) from lancers_p where idg=5 and dtl >= timestamp '2016-01-01' and dtl < timestamp '2017-01-01'; Temps : 263,094 ms ... Temps : 264,124 ms ... Temps : 258,650 ms
      1s8 avec la table LANCERS, 8 process ont travaillé. 400ms avec la table LANCERS_P, 4 process ont travaillé.
      En diminuant MIN_PARALLEL_TABLE_SCAN_SIZE de 8Mb à 256Kb, nous incitons le planner à utiliser davantage de process, ce qui abaisse le temps d’exécution sous les 300ms.
      A présent nous allons passer sur une requête attaquant 10 partitions :
set min_parallel_table_scan_size="8MB"; explain select avg(perf) from lancers where dtl >= timestamp '2016-01-01' and dtl < timestamp '2017-01-01'; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=1169579.57..1169579.58 rows=1 width=32) -> Gather (cost=1169578.84..1169579.55 rows=7 width=32) Workers Planned: 7 -> Partial Aggregate (cost=1168578.84..1168578.85 rows=1 width=32) -> Parallel Bitmap Heap Scan on lancers (cost=8113.35..1157241.08 rows=4535105 width=4) Recheck Cond: ((dtl >= '2016-01-01 00:00:00'::timestamp without time zone) AND (dtl < '2017-01-01 00:00:00'::timestamp without time zone)) -> Bitmap Index Scan on lancers_br1 (cost=0.00..176.91 rows=31754677 width=0) Index Cond: ((dtl >= '2016-01-01 00:00:00'::timestamp without time zone) AND (dtl < '2017-01-01 00:00:00'::timestamp without time zone)) select avg(perf) from lancers where dtl >= timestamp '2016-01-01' and dtl < timestamp '2017-01-01'; Time: 1949,837 ms (00:01,950) ... Time: 1951,155 ms (00:01,951) ... Time: 1957,299 ms (00:01,957) explain select avg(perf) from lancers_p where dtl >= timestamp '2016-01-01' and dtl < timestamp '2017-01-01'; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=350447.77..350447.78 rows=1 width=32) -> Gather (cost=350447.45..350447.76 rows=3 width=32) Workers Planned: 3 -> Partial Aggregate (cost=349447.45..349447.46 rows=1 width=32) -> Append (cost=0.00..323950.61 rows=10198734 width=4) -> Parallel Seq Scan on lancers_p_2016_001 (cost=0.00..32412.31 rows=1020417 width=4) Filter: ((dtl >= '2016-01-01 00:00:00'::timestamp without time zone) AND (dtl < '2017-01-01 00:00:00'::timestamp without time zone)) ... ... -> Parallel Seq Scan on lancers_p_2016_010 (cost=0.00..32416.37 rows=1020554 width=4) Filter: ((dtl >= '2016-01-01 00:00:00'::timestamp without time zone) AND (dtl < '2017-01-01 00:00:00'::timestamp without time zone)) select avg(perf) from lancers_p where dtl >= timestamp '2016-01-01' and dtl < timestamp '2017-01-01'; Time: 2696,316 ms (00:02,696) ... Time: 2705,367 ms (00:02,705) ... Time: 2702,969 ms (00:02,703) set min_parallel_table_scan_size="256kB"; SET explain select avg(perf) from lancers_p where dtl >= timestamp '2016-01-01' and dtl < timestamp '2017-01-01'; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=250993.48..250993.49 rows=1 width=32) -> Gather (cost=250992.75..250993.46 rows=7 width=32) Workers Planned: 7 -> Partial Aggregate (cost=249992.75..249992.76 rows=1 width=32) -> Append (cost=0.00..238701.29 rows=4516584 width=4) -> Parallel Seq Scan on lancers_p_2016_001 (cost=0.00..23882.84 rows=451899 width=4) Filter: ((dtl >= '2016-01-01 00:00:00'::timestamp without time zone) AND (dtl < '2017-01-01 00:00:00'::timestamp without time zone)) ... ... -> Parallel Seq Scan on lancers_p_2016_010 (cost=0.00..23885.75 rows=451960 width=4) Filter: ((dtl >= '2016-01-01 00:00:00'::timestamp without time zone) AND (dtl < '2017-01-01 00:00:00'::timestamp without time zone)) select avg(perf) from lancers_p where dtl >= timestamp '2016-01-01' and dtl < timestamp '2017-01-01'; Time: 1430,222 ms (00:01,430) ... Time: 1415,288 ms (00:01,415) ... Time: 1420,465 ms (00:01,420)

      Cette fois, le partitionnement n’est pas tout de suite vainqueur. La requête sur la table LANCERS non partitionnée dure 1s9. La requête sur la table LANCERS_P partitionnée dure 2s7.
      Mais la requête sur la table LANCERS est exécutée par 8 process alors que la requête sur la table LANCERS_P est exécutée par 4 process. Cela permet de noter que le parallélisme opère ici sur chaque partition mais pas entre les partitions.
      En affectant le même paramètre que pour la requête précédente, nous pouvons toutefois à nouveau utiliser les 8 coeurs de la machine virtuelle. Cela permet au partitionnement de sortir à nouveau vainqueur avec 1s4. Attention, il faut noter que modifier ce paramètre MIN_PARALLEL_TABLE_SCAN_SIZE n’est tout de même pas très pertinent en règle générale.
      A présent nous allons passer sur une requête attaquant toute la table et donc toutes les partitions :

explain select idg, avg(perf) from lancers group by idg order by 2 desc; QUERY PLAN ----------------------------------------------------------------------------------------------------------- Sort (cost=1510663.49..1510663.52 rows=10 width=36) Sort Key: (avg(perf)) DESC -> Finalize GroupAggregate (cost=1510662.68..1510663.33 rows=10 width=36) Group Key: idg -> Sort (cost=1510662.68..1510662.85 rows=70 width=36) Sort Key: idg -> Gather (cost=1510653.43..1510660.53 rows=70 width=36) Workers Planned: 7 -> Partial HashAggregate (cost=1509653.43..1509653.53 rows=10 width=36) Group Key: idg -> Parallel Seq Scan on lancers (cost=0.00..1366796.29 rows=28571429 width=8) select idg, avg(perf) from lancers group by idg order by 2 desc; Time: 11111,965 ms (00:11,112) ... Time: 11105,885 ms (00:11,106) ... Time: 11116,931 ms (00:11,117) explain select idg, avg(perf) from lancers_p group by idg order by 2 desc; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------ Sort (cost=1515915.69..1515915.72 rows=10 width=36) Sort Key: (avg(lancers_p_2010_001.perf)) DESC -> Finalize GroupAggregate (cost=1515914.88..1515915.53 rows=10 width=36) Group Key: lancers_p_2010_001.idg -> Sort (cost=1515914.88..1515915.05 rows=70 width=36) Sort Key: lancers_p_2010_001.idg -> Gather (cost=1515905.63..1515912.73 rows=70 width=36) Workers Planned: 7 -> Partial HashAggregate (cost=1514905.63..1514905.73 rows=10 width=36) Group Key: lancers_p_2010_001.idg -> Append (cost=0.00..1370409.77 rows=28899172 width=8) -> Parallel Seq Scan on lancers_p_2010_001 (cost=0.00..20.88 rows=1088 width=8) ... -> Parallel Seq Scan on lancers_p_2011_001 (cost=0.00..20320.72 rows=424772 width=8) ... -> Parallel Seq Scan on lancers_p_2019_010 (cost=0.00..20.88 rows=1088 width=8) select idg, avg(perf) from lancers_p group by idg order by 2 desc; Time: 11189,967 ms (00:11,190) ... Time: 11231,577 ms (00:11,232) ... Time: 11165,659 ms (00:11,166)

      Il y a bien un petit accroissement de la durée de la requête exécutée sur LANCERS_P mais nous restons dans les mêmes eaux qu’avec LANCERS, un peu plus de 11 secondes. La perte de performance liée au partitionnement dans un cas défavorable n’est pas du tout du même ordre de grandeur que le gain de performance dans un cas favorable.

Conclusion

      L’utilisation du partitionnement natif dans PostgreSQL 10 est simple. Les gains de performance sont larges dans les cas favorables de SELECT. Les pertes de performance sont faibles au niveau des INSERT et des cas défavorables de SELECT.
      La communauté PostgreSQL peut maintenant réellement affirmer que son SGBD dispose de la fonctionnalité de partitionnement.

Mise à jour : 24/05/2017