Ce cours s’adresse aux DBA PostgreSQL qui veulent avoir une maîtrise de son fonctionnement et veulent optimiser ses performances. Le cours pga5 (ou des connaissances équivalentes) constitue un prérequis pour ce cours.
Plan détaillé
Introduction et rappels
- Historique et versions.
- Introduction à SQL.
- Serveur PostgreSQL.
- Utilisateurs et schémas.
- Initialisation du cluster.
- Paramétrage de PostgreSQL.
- Processus PostgreSQL et configuration de la mémoire.
- Processus bgwriter et configuration des tampons partagés.
- Processus walwriter, tampon WAL, et journal de transactions.
- Processus checkpointer et configuration des points de synchronisation.
- Processus autovacuum.
- MVCC façon PostgreSQ, vacuum.
- Collecte des statiques de données, analyze.
Travaux pratiques : Initialisation d’un cluster PostgreSQL, consultation et modification des paramètres.
Traitement des instructions SQL et outils de mesures
- Traitement des instructions SQL, planification des requêtes, instructions préparées.
- Processus logger et journal applicatif.
- Configuration et paramétrage.
- Utilisation de pgBadger.
- Collecte des statistiques d’activité et processus « stats collector ».
- Activation et paramétrage.
- Consultation et réinitialisation.
- Les différentes vues disponibles.
- Commande EXPLAIN et ses différentes fonctionnalités.
- Utilisation du module auto_explain.
- Extension pg_stat_statements, paramétrage, intérêt de l’utilisation.
- Utilisation de la mémoire et l’extension pg_buffercache.
- Utilisation de pgbench, options importantes, interprétation du rapport.
Travaux pratiques : Les travaux pratiques concernent tous les points mentionnés.
Gestion de l’espace
- Gestion des tablespaces, déplacement, privilèges.
- Gestion des bases de données, privilèges spécifiques à une base de données.
- Gestion de l’espace dans les tables
- Segments et fichiers de stockage, taille de stockage.
- La carte de visibilité et l’extension pg_visibility.
- La carte de l’espace libre et l’extension pg_freespacemap.
- L’extension pageinspect et la structure d’une page.
- HOT (Heap Only Tuples)
- Stockage des lignes et l’extension pgstattuple.
Travaux pratiques : utilisation de l’ensemble des points abordés.
Objets d’une base de données PostgreSQL
- Création/modification des tables, paramètres de stockage des tables, stockage des colonnes, taille stockage d’une table.
- Les différents types de tables : tables temporaires, tables « UNLOGGED », tables partitionnées.
- Utilisation des vues matérialisées.
- Les différents types d’index. Maintenance des index
- Types d’index dans PostgreSQL.
- Organisation d’une table en fonction d’un index (opération CLUSTER).
- VACUUM et VACUUM FULL.
- Partitionnement de tables.
- Foreign Data Wrapper, utilisation des tables FDW.
Travaux pratiques : Organisation d’une table selon un index, partitionnement de tables, utilisation des tables FDW.
Tuning et optimisation du serveur PostgreSQL
- Tuning au niveau de l’OS, système de fichiers, mémoire, disques.
- Tuning de walwriter, bgwriter.
- Tuning d’autovacuum.
- Tuning de checkpointer.
- Tuning de la mémoire, shared_buffers, work_mem.
- Optimisation des connexions et utilisation de pgBouncer.
Travaux pratiques : Les travaux pratiques conncernent l’ensemble des points abordés.
Optimisation et plans d’exécution des instructions SQL
- Méthodes d’accès aux données : seq scan, index scan, …
- Opérateurs et différents types d’index, Organisation d’une table selon un index (CLUSTER).
- Méthodes d’exécution des jointures.
- Traitement des requêtes imbriquées.
- Traitement des vues.
- Traitement des CTE (Clause WITH).
- Configuration du parallélisme.
- Optimiseur génétique (GEQO).
- Paramétrage JIT.
Travaux pratiques : Méthodes d’accès et plan d’exécution, méthodes d’exécution de jointures, Utilisation des index.
