Utiliser le stockage en colonnes

Améliorer les performances des requêtes dans PostgreSQL

Amy McCarty

Instructor

Orienté colonnes

Stockage orienté colonnes

Relation entre lignes conservée

id	name	species	age	habitat	received
01	Bob	panda	2	Asia	2018
02	Sunny	zebra	3	Africa	2018
03	Beco	zebra	10	Africa	2017
04	Coco	koala	5	Australia	2016

Stocké comme

Deux tableaux. Le premier est une liste d'id et d'espèces. 01 - panda. 02 - zebra. 03 - zebra. 04 - koala. Le second est une liste d'id et d'âges. 01 - 2. 02 - 3. 03 - 10. 04 - 5.

Focus analytique : bien adapté

Propriétés du stockage en colonnes

Une colonne stockée au même endroit
Rapide pour renvoyer toutes les lignes
Calculs par colonne rapides

Orienté analyse

Comptes, moyennes, calculs
Reporting
Agrégations par colonne

Stocké comme

Trois tableaux. Le premier est une liste d'id et de noms. 01 - Bob. 02 - Sunny. 03 - Beco. 04 - Coco. Le deuxième est une liste d'id et d'espèces. 01 - panda. 02 - zebra. 03 - zebra. 04 - koala. Le troisième est une liste d'id et d'âges. 01 - 2. 02 - 3. 03 - 10. 04 - 5.

Focus transactionnel : peu adapté

Relations entre lignes conservées

Lent pour renvoyer toutes les colonnes
Chargement des données lent

Orienté transactionnel

Insertion et suppression d'enregistrements rapides

Stocké comme

Exemples de bases de données


Postgres	Citus Data, Greenplum, Amazon Redshift
MySQL	MariaDB
Oracle	Oracle In-Memory Cloud Store
	Clickhouse, Apache Druid, CrateDB

Schéma d'information

Réduire le nombre de colonnes

Utilisez SELECT * avec parcimonie

SELECT column_name, data_type
FROM information_schema.columns
WHERE table_catalog = 'schema_name'
AND table_name = 'zoo_animals'

column_name	data_type
id	integer
name	text
species	text

Schéma d'information

Réduire le nombre de colonnes

Utilisez SELECT * avec parcimonie
Utilisez le schéma d'information

SELECT column_name, data_type
FROM information_schema.columns
WHERE table_catalog = 'schema_name'
AND table_name = 'zoo_animals'

column_name	data_type
id	integer
name	text
species	text

Rédiger vos requêtes

Examiner chaque colonne dans sa propre requête

id	name	species	age	habitat	received
01	Bob	panda	2	Asia	2018
02	Sunny	zebra	3	Africa	2018
03	Beco	zebra	10	Africa	2017
04	Coco	koala	5	Australia	2016

-- Structure pour l'orientation colonnes
SELECT MIN(age), MAX(age)
FROM zoo_animals
WHERE species = 'zebra'

-- Structure pour l'orientation lignes
SELECT *
FROM zoo_animals
WHERE species = 'zebra'
ORDER BY age

Passons à la pratique !

Améliorer les performances des requêtes dans PostgreSQL