Stockage orienté colonne

On penche la tête !

      Si vous voulez découvrir les principes du stockage orienté colonne, je vous conseille d’aller voir Wikipedia. Ce principe de stockage n’est PAS adapté à tous les traitements. Il peut cependant être efficace dans certaines conditions, par exemple avec des requêtes analytiques concernant beaucoup de lignes et peu de colonnes d’une table volumineuse.
      Le stockage orienté colonne natif est annoncé dans la roadmap de PostgreSQL mais il est déjà disponible en 9.x sous la forme d’extensions comme CSTORE_FDW. Cette implémentation est-elle efficace ? Préparation d’un environnement de travail avec PostgreSQL 9.6 :

CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; CREATE EXTENSION create extension if not exists cstore_fdw; CREATE EXTENSION CREATE SERVER cstore_server FOREIGN DATA WRAPPER cstore_fdw; CREATE SERVER select version(); version -------------------------------------------------------------------------------------------------- PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-6) 6.3.0 20170205, 64-bit (1 ligne) create table geants( idu uuid, dtn date, genre char(1), taille smallint, masse smallint, actif boolean, devise varchar(128), pw smallint, heureux boolean, couleur varchar(8), veteran boolean, clan smallint, gabarit varchar(8), revenu integer, pm smallint, berserk boolean, tutelaire smallint, ere varchar(10), cyclope boolean); CREATE TABLE WITH serie(i) AS (SELECT generate_series(55000000,1000001,-1)) insert into geants select uuid_generate_v4(), current_date - (ceil(i/3) || ' minutes')::interval + (trunc(random() * 100 + 1) || ' days')::interval, case when random() < 0.45 then 'M' else 'F' end, 200 + (trunc(random() * 200 + 1)), 300 + (trunc(random() * 200 + 1)), case when random() < 0.7 then false else true end, upper(md5(random()::text)), (trunc(random()*100 + 1)), case when random() < 0.1 then false else true end, case when random() < 0.7 then 'GRIS' when random() < 0.8 then 'NOIR' else 'BLEU' end, case when random() < 0.9 then false else true end, (trunc(random()*1000 + 1)), case when random() < 0.1 then 'PETIT' when random() < 0.9 then 'MOYEN' else 'GRAND' end, (trunc(random()*1000000 + 1)), (trunc(random()*10 + 1)), case when random() < 0.01 then true else false end, (trunc(random()*10 + 1)), case when i < 18000000 then 'TAUREAU' when i < 40000000 then 'LICORNE' else 'DRAGON' end, case when random() < 0.001 then true else false end from serie; INSERT 0 54000000 create foreign table geants_cslz( idu uuid, dtn date, genre char(1), taille smallint, masse smallint, actif boolean, devise varchar(128), pw smallint, heureux boolean, couleur varchar(8), veteran boolean, clan smallint, gabarit varchar(8), revenu integer, pm smallint, berserk boolean, tutelaire smallint, ere varchar(10), cyclope boolean) SERVER cstore_server OPTIONS(compression 'pglz'); CREATE FOREIGN TABLE insert into geants_cslz select * from geants; INSERT 0 54000000 create foreign table geants_cs( idu uuid, dtn date, genre char(1), taille smallint, masse smallint, actif boolean, devise varchar(128), pw smallint, heureux boolean, couleur varchar(8), veteran boolean, clan smallint, gabarit varchar(8), revenu integer, pm smallint, berserk boolean, tutelaire smallint, ere varchar(10), cyclope boolean) SERVER cstore_server; CREATE FOREIGN TABLE insert into geants_cs select * from geants; INSERT 0 54000000 postgres=# \d+ Liste des relations Schéma | Nom | Type | Propriétaire | Taille | Description --------+-------------+----------------+--------------+---------+------------- public | geants | table | postgres | 6918 MB | public | geants_cs | table distante | postgres | 0 bytes | public | geants_cslz | table distante | postgres | 0 bytes | (3 lignes) pwd /var/lib/postgresql/9.6/main/cstore_fdw/12407 3552 ./97745 1 ./97745.footer 5533 ./97748 1 ./97748.footer

      54 millions de lignes. La table GEANTS dont le stockage est classiquement orienté ligne fait 6918Mo. La table externe GEANTS_CS dont le stockage est orienté colonne ne pèse plus "que" 5533Mo. La table GEANTS_CSLZ qui bénéficie en plus de la compression ne pèse plus que 3552Mo. Il faut noter qu’il est seulement possible d’utiliser INSERT...SELECT ou COPY pour manipuler les données des tables externes basées sur CSTORE_FDW, impossible de faire un UPDATE ou un DELETE.
      Nous allons exécuter 10 requêtes en attaquant GEANTS, GEANTS_CS puis GEANTS_CSLZ. Dans un premier temps les ressources seront contraintes : 1 seul processeur disponible et 1Go de RAM, c’est à dire une quantité insuffisante pour mettre les données en cache. La vitesse du sous-système disque est faible puisqu’il se compose d’un simple disque dur.
      Démonstration :

-- RAM 1G VCPU 1 PAS DE // HDD SELECT mode() WITHIN GROUP (ORDER BY heureux) FROM geants; mode ------ t (1 ligne) Temps : 220166,858 ms SELECT idu, devise FROM geants WHERE not actif AND not heureux AND veteran AND berserk AND cyclope; idu | devise --------------------------------------+---------------------------------- a7997a3f-b318-4c0f-b11a-ee7451a58342 | C188AA9C92F9E835FEF625F6DB85762A 8d6a89b0-6917-410f-ae64-9fada4193720 | C188AA9C92F9E835FEF625F6DB85762A (2 lignes) Temps : 88830,273 ms SELECT count(*) FROM geants WHERE ere = 'LICORNE'; count ---------- 22000000 (1 ligne) Temps : 96332,042 ms SELECT avg(taille) FROM geants WHERE revenu > 800000; avg ---------------------- 300.5730298801670093 (1 ligne) Temps : 91138,567 ms SELECT mode() WITHIN GROUP(ORDER BY actif) FROM geants WHERE dtn > CURRENT_TIMESTAMP - interval '10 years'; mode ------ f (1 ligne) Temps : 110100,983 ms SELECT mode() WITHIN GROUP(ORDER BY cyclope) FROM geants WHERE dtn > CURRENT_TIMESTAMP - interval '10 years' and dtn < CURRENT_TIMESTAMP - interval '9 years'; mode ------ f (1 ligne) Temps : 80641,584 ms SELECT avg(masse) FROM geants WHERE not actif; avg ---------------------- 400.4814439218300955 (1 ligne) Temps : 76870,851 ms SELECT count(*) FROM geants WHERE couleur = 'NOIR'; count ---------- 12963665 (1 ligne) Temps : 85611,636 ms SELECT count(*) FROM geants WHERE genre = 'M' OR berserk; count ---------- 24596217 (1 ligne) Temps : 83437,818 ms SELECT count(*) FROM geants WHERE gabarit = 'MOYEN'; count ---------- 43742061 (1 ligne) Temps : 88686,148 ms -- TOTAL : 1021816,76ms 1022s 17min02s SELECT mode() WITHIN GROUP (ORDER BY heureux) FROM geants_cs; mode ------ t (1 ligne) Temps : 75278,395 ms SELECT idu, devise FROM geants_cs WHERE not actif AND not heureux AND veteran AND berserk AND cyclope; idu | devise --------------------------------------+---------------------------------- a7997a3f-b318-4c0f-b11a-ee7451a58342 | C188AA9C92F9E835FEF625F6DB85762A 8d6a89b0-6917-410f-ae64-9fada4193720 | C188AA9C92F9E835FEF625F6DB85762A (2 lignes) Temps : 48726,149 ms SELECT count(*) FROM geants_cs WHERE ere = 'LICORNE'; count ---------- 22000000 (1 ligne) Temps : 15092,059 ms SELECT avg(taille) FROM geants_cs WHERE revenu > 800000; avg ---------------------- 300.5730298801670093 (1 ligne) Temps : 15689,142 ms SELECT mode() WITHIN GROUP(ORDER BY actif) FROM geants_cs WHERE dtn > CURRENT_TIMESTAMP - interval '10 years'; mode ------ f (1 ligne) Temps : 40800,874 ms SELECT mode() WITHIN GROUP(ORDER BY cyclope) FROM geants_cs WHERE dtn > CURRENT_TIMESTAMP - interval '10 years' and dtn < CURRENT_TIMESTAMP - interval '9 years'; mode ------ f (1 ligne) Temps : 36672,621 ms SELECT avg(masse) FROM geants_cs WHERE not actif; avg ---------------------- 400.4814439218300955 (1 ligne) Temps : 7543,420 ms SELECT count(*) FROM geants_cs WHERE couleur = 'NOIR'; count ---------- 12963665 (1 ligne) Temps : 15689,548 ms SELECT count(*) FROM geants_cs WHERE genre = 'M' OR berserk; count ---------- 24596217 (1 ligne) Temps : 17885,673 ms SELECT count(*) FROM geants_cs WHERE gabarit = 'MOYEN'; count ---------- 43742061 (1 ligne) Temps : 16903,274 ms -- TOTAL : 290281,155ms 290s 4min50s SELECT mode() WITHIN GROUP (ORDER BY heureux) FROM geants_cslz; mode ------ t (1 ligne) Temps : 104519,080 ms SELECT idu, devise FROM geants_cslz WHERE not actif AND not heureux AND veteran AND berserk AND cyclope; idu | devise --------------------------------------+---------------------------------- a7997a3f-b318-4c0f-b11a-ee7451a58342 | C188AA9C92F9E835FEF625F6DB85762A 8d6a89b0-6917-410f-ae64-9fada4193720 | C188AA9C92F9E835FEF625F6DB85762A (2 lignes) Temps : 42178,958 ms SELECT count(*) FROM geants_cslz WHERE ere = 'LICORNE'; count ---------- 22000000 (1 ligne) Temps : 11171,904 ms SELECT avg(taille) FROM geants_cslz WHERE revenu > 800000; avg ---------------------- 300.5730298801670093 (1 ligne) Temps : 16213,786 ms SELECT mode() WITHIN GROUP(ORDER BY actif) FROM geants_cslz WHERE dtn > CURRENT_TIMESTAMP - interval '10 years'; mode ------ f (1 ligne) Temps : 39576,755 ms SELECT mode() WITHIN GROUP(ORDER BY cyclope) FROM geants_cslz WHERE dtn > CURRENT_TIMESTAMP - interval '10 years' and dtn < CURRENT_TIMESTAMP - interval '9 years'; mode ------ f (1 ligne) Temps : 30809,234 ms SELECT avg(masse) FROM geants_cslz WHERE not actif; avg ---------------------- 400.4814439218300955 (1 ligne) Temps : 8828,817 ms SELECT count(*) FROM geants_cslz WHERE couleur = 'NOIR'; count ---------- 12963665 (1 ligne) Temps : 11130,598 ms SELECT count(*) FROM geants_cslz WHERE genre = 'M' OR berserk; count ---------- 24596217 (1 ligne) Temps : 10058,288 ms SELECT count(*) FROM geants_cslz WHERE gabarit = 'MOYEN'; count ---------- 43742061 (1 ligne) Temps : 12423,892 ms -- TOTAL : 286911,312 287s 4min47s

      Le stockage orienté colonne écrase ici le stockage classique. Nous passons de plus de 17 minutes à moins de 5 minutes avec le stockage orienté colonne, que les données soient ou non compressées.
      Ce n’est pas le moteur PostgreSQL qui effectue le travail avec CSTORE_FDW et nous ne pouvons pas indexer les tables externes basées sur cette extension. Impossible également de tirer parti du parallélisme. Cela laisse présager que le stockage orienté ligne n’a pas dit son dernier mot si les ressources sont moins contraintes et si des index sont ajoutés.
      Démonstration avec le même sous-système de stockage mais 4 processeurs virtuels et 8Go de RAM, c’est à dire une quantité suffisante pour mettre les données à analyser en cache :

-- RAM 8G VCPU 4 // 4 HDD set max_parallel_workers_per_gather=3; SET create index geants_br1 on geants using brin(dtn); CREATE INDEX Temps : 13543,410 ms create index geants_br2 on geants using brin(ere); CREATE INDEX Temps : 22800,459 ms \di+ Liste des relations Schéma | Nom | Type | Propriétaire | Table | Taille | Description --------+------------+-------+--------------+--------+--------+------------- public | geants_br1 | index | postgres | geants | 216 kB | public | geants_br2 | index | postgres | geants | 272 kB | (2 lignes) SELECT mode() WITHIN GROUP (ORDER BY heureux) FROM geants; mode ------ t (1 ligne) Temps : 126655,932 ms SELECT idu, devise FROM geants WHERE not actif AND not heureux AND veteran AND berserk AND cyclope; idu | devise --------------------------------------+---------------------------------- a7997a3f-b318-4c0f-b11a-ee7451a58342 | C188AA9C92F9E835FEF625F6DB85762A 8d6a89b0-6917-410f-ae64-9fada4193720 | C188AA9C92F9E835FEF625F6DB85762A (2 lignes) Temps : 4617,662 ms SELECT count(*) FROM geants WHERE ere = 'LICORNE'; count ---------- 22000000 (1 ligne) Temps : 5697,316 ms SELECT avg(taille) FROM geants WHERE revenu > 800000; avg ---------------------- 300.5730298801670093 (1 ligne) Temps : 4689,190 ms SELECT mode() WITHIN GROUP(ORDER BY actif) FROM geants WHERE dtn > CURRENT_TIMESTAMP - interval '10 years'; mode ------ f (1 ligne) Temps : 15540,635 ms SELECT mode() WITHIN GROUP(ORDER BY cyclope) FROM geants WHERE dtn > CURRENT_TIMESTAMP - interval '10 years' and dtn < CURRENT_TIMESTAMP - interval '9 years'; mode ------ f (1 ligne) Temps : 2310,363 ms SELECT avg(masse) FROM geants WHERE not actif; avg ---------------------- 400.4814439218300955 (1 ligne) Temps : 4062,508 ms SELECT count(*) FROM geants WHERE couleur = 'NOIR'; count ---------- 12963665 (1 ligne) Temps : 4454,668 ms SELECT count(*) FROM geants WHERE genre = 'M' OR berserk; count ---------- 24596217 (1 ligne) Temps : 6233,714 ms SELECT count(*) FROM geants WHERE gabarit = 'MOYEN'; count ---------- 43742061 (1 ligne) Temps : 5500,715 ms -- TOTAL : 179762,703 180s 3min00s SELECT mode() WITHIN GROUP (ORDER BY heureux) FROM geants_cs; mode ------ t (1 ligne) Temps : 53047,641 ms SELECT idu, devise FROM geants_cs WHERE not actif AND not heureux AND veteran AND berserk AND cyclope; idu | devise --------------------------------------+---------------------------------- a7997a3f-b318-4c0f-b11a-ee7451a58342 | C188AA9C92F9E835FEF625F6DB85762A 8d6a89b0-6917-410f-ae64-9fada4193720 | C188AA9C92F9E835FEF625F6DB85762A (2 lignes) Temps : 46364,896 ms SELECT count(*) FROM geants_cs WHERE ere = 'LICORNE'; count ---------- 22000000 (1 ligne) Temps : 15476,630 ms SELECT avg(taille) FROM geants_cs WHERE revenu > 800000; avg ---------------------- 300.5730298801670093 (1 ligne) Temps : 16526,642 ms SELECT mode() WITHIN GROUP(ORDER BY actif) FROM geants_cs WHERE dtn > CURRENT_TIMESTAMP - interval '10 years'; mode ------ f (1 ligne) Temps : 35803,980 ms SELECT mode() WITHIN GROUP(ORDER BY cyclope) FROM geants_cs WHERE dtn > CURRENT_TIMESTAMP - interval '10 years' and dtn < CURRENT_TIMESTAMP - interval '9 years'; mode ------ f (1 ligne) Temps : 28758,268 ms SELECT avg(masse) FROM geants_cs WHERE not actif; avg ---------------------- 400.4814439218300955 (1 ligne) Temps : 9884,136 ms SELECT count(*) FROM geants_cs WHERE couleur = 'NOIR'; count ---------- 12963665 (1 ligne) Temps : 13684,574 ms SELECT count(*) FROM geants_cs WHERE genre = 'M' OR berserk; count ---------- 24596217 (1 ligne) Temps : 14752,051 ms SELECT count(*) FROM geants_cs WHERE gabarit = 'MOYEN'; count ---------- 43742061 (1 ligne) Temps : 15921,996 ms -- TOTAL : 250220,814 250s 4min10s SELECT mode() WITHIN GROUP (ORDER BY heureux) FROM geants_cslz; mode ------ t (1 ligne) Temps : 46108,121 ms SELECT idu, devise FROM geants_cslz WHERE not actif AND not heureux AND veteran AND berserk AND cyclope; idu | devise --------------------------------------+---------------------------------- a7997a3f-b318-4c0f-b11a-ee7451a58342 | C188AA9C92F9E835FEF625F6DB85762A 8d6a89b0-6917-410f-ae64-9fada4193720 | C188AA9C92F9E835FEF625F6DB85762A (2 lignes) Temps : 42477,413 ms SELECT count(*) FROM geants_cslz WHERE ere = 'LICORNE'; count ---------- 22000000 (1 ligne) Temps : 11253,293 ms SELECT avg(taille) FROM geants_cslz WHERE revenu > 800000; avg ---------------------- 300.5730298801670093 (1 ligne) Temps : 16479,564 ms SELECT mode() WITHIN GROUP(ORDER BY actif) FROM geants_cslz WHERE dtn > CURRENT_TIMESTAMP - interval '10 years'; mode ------ f (1 ligne) Temps : 34052,909 ms SELECT mode() WITHIN GROUP(ORDER BY cyclope) FROM geants_cslz WHERE dtn > CURRENT_TIMESTAMP - interval '10 years' and dtn < CURRENT_TIMESTAMP - interval '9 years'; mode ------ f (1 ligne) Temps : 28668,861 ms SELECT avg(masse) FROM geants_cslz WHERE not actif; avg ---------------------- 400.4814439218300955 (1 ligne) Temps : 10835,851 ms SELECT count(*) FROM geants_cslz WHERE couleur = 'NOIR'; count ---------- 12963665 (1 ligne) Temps : 10679,429 ms SELECT count(*) FROM geants_cslz WHERE genre = 'M' OR berserk; count ---------- 24596217 (1 ligne) Temps : 9499,016 ms SELECT count(*) FROM geants_cslz WHERE gabarit = 'MOYEN'; count ---------- 43742061 (1 ligne) Temps : 12822,377 ms -- TOTAL : 222876,8342 223s 3min43s

      Les temps de réponse des requêtes effectuées sur les tables externes bénéficient de l’augmentation des ressources puisque le temps d’exécution cumulé passe à 4 minutes 10 secondes voire 3 minutes 43 secondes lorsque les données sont compressées. Ce résultat est intéressant mais c’est à présent insuffisant pour faire mieux que le stockage orienté ligne. Le temps cumulé des requêtes passe à en effet à 3 minutes dans ce mode sous les effets combinés du cache, du parallélisme ou de l’indexation.
      A présent pour s’amuser que se passe-t-il si nous conservons les mêmes conditions techniques mais que nous indexons TOUTES les colonnes impliquées dans les différentes requêtes ?
      Démonstration :

-- RAM 8G VCPU 4 // 4 HDD set max_parallel_workers_per_gather=3; SET create index geants_i1 on geants(heureux); create index geants_i2 on geants(veteran); create index geants_i3 on geants(berserk); create index geants_i4 on geants(cyclope); create index geants_i5 on geants(ere); create index geants_i6 on geants(revenu); create index geants_i7 on geants(dtn); create index geants_i8 on geants(actif); create index geants_i9 on geants(couleur); create index geants_i10 on geants(genre); create index geants_i11 on geants(gabarit); \di+ Liste des relations Schéma | Nom | Type | Propriétaire | Table | Taille | Description --------+------------+-------+--------------+--------+---------+------------- public | geants_br1 | index | postgres | geants | 216 kB | public | geants_br2 | index | postgres | geants | 272 kB | public | geants_i1 | index | postgres | geants | 1157 MB | public | geants_i10 | index | postgres | geants | 1157 MB | public | geants_i11 | index | postgres | geants | 1157 MB | public | geants_i2 | index | postgres | geants | 1157 MB | public | geants_i3 | index | postgres | geants | 1157 MB | public | geants_i4 | index | postgres | geants | 1157 MB | public | geants_i5 | index | postgres | geants | 1157 MB | public | geants_i6 | index | postgres | geants | 1157 MB | public | geants_i7 | index | postgres | geants | 1157 MB | public | geants_i8 | index | postgres | geants | 1157 MB | public | geants_i9 | index | postgres | geants | 1157 MB | SELECT mode() WITHIN GROUP (ORDER BY heureux) FROM geants; mode ------ t (1 ligne) Temps : 114406,350 ms SELECT idu, devise FROM geants WHERE not actif AND not heureux AND veteran AND berserk AND cyclope; idu | devise --------------------------------------+---------------------------------- a7997a3f-b318-4c0f-b11a-ee7451a58342 | C188AA9C92F9E835FEF625F6DB85762A 8d6a89b0-6917-410f-ae64-9fada4193720 | C188AA9C92F9E835FEF625F6DB85762A (2 lignes) Temps : 429,935 ms SELECT count(*) FROM geants WHERE ere = 'LICORNE'; count ---------- 22000000 (1 ligne) Temps : 7392,056 ms SELECT avg(taille) FROM geants WHERE revenu > 800000; avg ---------------------- 300.5730298801670093 (1 ligne) Temps : 49580,836 ms SELECT mode() WITHIN GROUP(ORDER BY actif) FROM geants WHERE dtn > CURRENT_TIMESTAMP - interval '10 years'; mode ------ f (1 ligne) Temps : 24045,533 ms SELECT mode() WITHIN GROUP(ORDER BY cyclope) FROM geants WHERE dtn > CURRENT_TIMESTAMP - interval '10 years' and dtn < CURRENT_TIMESTAMP - interval '9 years'; mode ------ f (1 ligne) Temps : 2051,025 ms SELECT avg(masse) FROM geants WHERE not actif; avg ---------------------- 400.4814439218300955 (1 ligne) Temps : 32677,835 ms SELECT count(*) FROM geants WHERE couleur = 'NOIR'; count ---------- 12963665 (1 ligne) Temps : 10350,781 ms SELECT count(*) FROM geants WHERE genre = 'M' OR berserk; count ---------- 24596217 (1 ligne) Temps : 4999,281 ms SELECT count(*) FROM geants WHERE gabarit = 'MOYEN'; count ---------- 43742061 (1 ligne) Temps : 5510,050 ms -- TOTAL : 251443,682 251s 4min11

      OUILLE ! En consommant 12727Mo d’espace supplémentaire soit près de 20Go au total nous sommes passés à 4min11s. Le stockage orienté colonne consommant 3552Mo fait mieux. Ne perdez pas de vue qu’il faut aussi charger les pages d’index en cache. La RAM n’est pas toujours suffisamment importante pour accueillir ces données supplémentaires. Les performances dépendent alors du moment où vous exécutez votre requête, ce qui peut occasionner un ressenti utilisateur mitigé. Bien sûr, ici, nous pourrions créer des index B-Tree composés spécialement adaptés à ces requêtes pour obtenir artificiellement un excellent résultat global. Mais si ces requêtes ne sont qu’un échantillon alors ce n’est pas la bonne approche.

      Le stockage orienté colonne est une piste intéressante d’optimisation pour certains traitements. Il convient toutefois d’étudier attentivement s’il peut être utile avant de l’utiliser sur une table. De nombreux paramètres sont à prendre en compte. Certes le volume d’entrées/sorties est potentiellement réduit massivement sur certaines requêtes mais les restrictions sont actuellement importantes : DML limité, pas de parallélisme et pas d’indexation en dehors de celle intégrée par défaut. Les futures versions de PostgreSQL devraient lever nombre de ces limitations, à suivre...

Mise à jour : 25/03/2017