Stockage orienté colonne
On penche la tête !
Cette page n'a pas pour but de vous présenter le principe du stockage orienté colonne, il nest 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 dune 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 dextensions comme CSTORE_FDW. Cette implémentation est-elle efficace ? Préparation dun 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 quil est seulement possible dutiliser 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, cest à dire une quantité insuffisante pour mettre les données en cache. La vitesse du sous-système disque est faible puisquil se compose dun 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 nest 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 na 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, cest à 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 laugmentation des ressources puisque le temps dexé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 cest à 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 lindexation.
A présent pour samuser 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 despace 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 quil faut aussi charger les pages dindex en cache. La RAM nest 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 quun échantillon alors ce nest pas la bonne approche.
Le stockage orienté colonne est une piste intéressante doptimisation pour certains traitements. Il convient toutefois détudier attentivement sil peut être utile avant de lutiliser sur une table. De nombreux paramètres sont à prendre en compte. Certes le volume dentré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 dindexation 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