Utiliser le stockage orienté colonnes

Améliorer la performance des requêtes dans PostgreSQL

Amy McCarty

Instructor

Orienté colonnes

Stockage orienté colonnes

Relations entre les lignes conservées

id	nom	espèce	âge	habitat	reçu
01	Bob	panda	2	Asie	2018
02	Sunny	zèbre	3	Afrique	2018
03	Beco	zèbre	10	Afrique	2017
04	Coco	koala	5	Australie	2016

Stocké comme

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

Axé analytique : un bon choix

Caractéristiques du stockage orienté colonnes

Une colonne stockée au même endroit
Retour rapide de toutes les lignes
Calculs par colonne rapides

Axé analytique

Comptes, moyennes, calculs
Rapports
Agrégations par colonne

Stocké comme

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

Axé transactionnel : un mauvais choix

Relations entre lignes conservées

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

Axé 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 avec sa propre requête

id	nom	espèce	âge	habitat	reçu
01	Bob	panda	2	Asie	2018
02	Sunny	zèbre	3	Afrique	2018
03	Beco	zèbre	10	Afrique	2017
04	Coco	koala	5	Australie	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 la performance des requêtes dans PostgreSQL