Stockage orienté lignes et partitions

Améliorer les performances 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 les performances 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 les performances 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 - zebra, 03 - zebra, 04 - koala.

Améliorer les performances des requêtes dans PostgreSQL

Stockage orienté lignes

 

  • Chaque ligne stockée au même endroit
  • Ajout/suppression de lignes rapides
  • Retour de toutes les colonnes rapide
    • Retour de toutes les lignes lent
Améliorer les performances des requêtes dans PostgreSQL

Réduire les lignes

Réduire le nombre de lignes

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

Méthodes pour bases orientées lignes

Partitions

  • Méthode pour scinder une table (parente) 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 partitions et index

  • Nécessitent configuration et maintenance
  • Leur existence est connue via l'administrateur de base de données ou la documentation
Améliorer les performances des requêtes dans PostgreSQL

Structure de partition

Une table parente d'animaux du zoo avec quatre enregistrements indiquant id, name, species, habitat et année de received. 01 - Bob - Panda - age - habitat - received. 02 - Sunny - Zebra - 3 - Africa - 2018. 03 - Beco - Zebra - 10 - Africa - 2017. 04 - Coco - Macaw - 5 - South America - 2016. Trois tables enfants correspondent à la colonne habitat. La table Asia a un enregistrement pour Bob. La table Africa en a deux pour Sunny et Beco. La table South America a un enregistrement pour Coco.

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

Structure de partition

Une table parente d'animaux du zoo avec quatre enregistrements indiquant id, name, species, habitat et année de received. 01 - Bob - Panda - age - habitat - received. 02 - Sunny - Zebra - 3 - Africa - 2018. 03 - Beco - Zebra - 10 - Africa - 2017. 04 - Coco - Macaw - 5 - South America - 2016. Trois tables enfants correspondent à la colonne habitat. La table Asia a un enregistrement pour Bob. La table Africa en a deux pour Sunny et Beco. La table South America a un enregistrement pour Coco. La table Africa est encadrée en rouge.

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

Aperçu des partitions

Quoi

  • Scinder une table en plusieurs plus petites

Pourquoi

  • Flexibilité de stockage
  • Requêtes plus rapides

Où

  • Colonnes souvent utilisées comme filtres
    • Date, lieu
Améliorer les performances des requêtes dans PostgreSQL

Évaluation des requêtes sur partitions

Planificateur de requêtes

Plusieurs chefs autour d'une grande marmite

EXPLAIN
SELECT species
FROM zoo_animals
WHERE habitat = 'Africa'

Plan de requête

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 les performances des requêtes dans PostgreSQL

Passons à la pratique !

Améliorer les performances des requêtes dans PostgreSQL

Preparing Video For Download...