Stockage orienté lignes et partitions

Améliorer la performance des requêtes dans PostgreSQL

Amy McCarty

Instructor

Types de stockage de base de données

Stockage orienté lignes

  • Relation entre colonnes conservée

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
Améliorer la performance des requêtes dans PostgreSQL

Orienté lignes

Stockage orienté lignes

  • Relation entre colonnes conservée

 

id name species age habitat received
01 Bob panda 2 Asia 2018
Améliorer la performance des requêtes dans PostgreSQL

Orienté colonnes

Stockage orienté colonnes

  • Relation entre lignes conservée  

Deux tableaux. Le premier liste des id et des noms : 01 - Bob. 02 - Sunny. 03 - Beco. 04 - Coco. Le second reprend les mêmes id avec les espèces : 01 - panda. 02 - zèbre. 03 - zèbre. 04 - koala.

Améliorer la performance des requêtes dans PostgreSQL

Stockage orienté lignes

 

  • Une ligne stockée au même endroit
  • Ajout/suppression de dossiers complets rapide
  • Retour de toutes les colonnes rapide
    • Retour de toutes les lignes lent
Améliorer la performance des requêtes dans PostgreSQL

Réduire les lignes

Réduire le nombre de lignes

  • Filtre WHERE
  • INNER JOIN
  • DISTINCT
  • LIMIT
Améliorer la performance des requêtes dans PostgreSQL

Méthodes pour bases orientées lignes

Partitions

  • Méthode pour scinder une table (parent) en plusieurs petites tables (enfants)

Index

  • Méthode pour créer des clés de colonnes triées afin d'accélérer la recherche

 

Utiliser des partitions et des index

  • Exigent configuration et entretien
  • Leur existence est connue via l'administrateur de bases de données ou la documentation
Améliorer la performance des requêtes dans PostgreSQL

Structure de partition

Une table parente d'animaux du zoo avec quatre enregistrements indiquant id, nom, espèce, habitat et année de réception. 01 - Bob - Panda - âge - habitat - reçu. 02 - Sunny - Zèbre - 3 - Afrique - 2018. 03 - Beco - Zèbre - 10 - Afrique - 2017. 04 - Coco - Ara - 5 - Amérique du Sud - 2016. Il y a 3 tables enfants correspondant à la colonne habitat. La table Asie a un enregistrement pour Bob. La table Afrique a deux enregistrements pour Sunny et Beco. La table Amérique du Sud a un enregistrement pour Coco.

  • Table parente
    • Visible dans l'interface de la base
    • Écrire des requêtes
  • Tables enfants
    • Non visibles dans l'interface de la base
    • Les requêtes les parcourent
Améliorer la performance des requêtes dans PostgreSQL

Structure de partition

Une table parente d'animaux du zoo avec quatre enregistrements indiquant id, nom, espèce, habitat et année de réception. 01 - Bob - Panda - âge - habitat - reçu. 02 - Sunny - Zèbre - 3 - Afrique - 2018. 03 - Beco - Zèbre - 10 - Afrique - 2017. 04 - Coco - Ara - 5 - Amérique du Sud - 2016. Il y a 3 tables enfants correspondant à la colonne habitat. La table Asie a un enregistrement pour Bob. La table Afrique a deux enregistrements pour Sunny et Beco. La table Amérique du Sud a un enregistrement pour Coco. La table Afrique est encadrée en rouge.

SELECT species
FROM zoo_animals
WHERE habitat = 'Africa'
Améliorer la performance des requêtes dans PostgreSQL

Aperçu des partitions

Quoi

  • Découper une table en plusieurs plus petites

Pourquoi

  • Souplesse de stockage
  • Requêtes plus rapides

Où

  • Colonnes de filtrage courantes
    • Date, lieu
Améliorer la performance des requêtes dans PostgreSQL

Évaluer une requête avec partitions

Planificateur de requêtes

Plusieurs chefs autour d'un grand chaudron

EXPLAIN
SELECT species
FROM zoo_animals
WHERE habitat = 'Africa'

Plan d'exécution

Seq Scan on zoo_animals  (cost=0.00..
17.70 rows=2 width=182)
  Filter: (state_code = 15)
  • Estimations de coût (temps)
Améliorer la performance des requêtes dans PostgreSQL

Passons à la pratique !

Améliorer la performance des requêtes dans PostgreSQL

Preparing Video For Download...