PostgreSQL en pratique : index et optimisation des performances
PostgreSQL en pratique (index, performances) est un cours de niveau avancé de 5 leçons (environ 3 h 36), avec 21 exercices et 15 questions de quiz. Prérequis : Modélisation de bases de données. Le cours demande un environnement de projet complet, prévu dans une phase ultérieure de la plateforme.
- SQL
- Avancé
- 5 leçons
- 3 h 36
- 21 exercices
- Environnement de projet complet (phase 2)
Le cours en bref
Ce cours avancé vous apprend à rendre une base PostgreSQL rapide et fiable. Vous utiliserez les types propres à PostgreSQL et le JSONB, lirez des plans d'exécution avec EXPLAIN ANALYZE, concevrez des index B-tree, partiels, GIN, trigrammes ou BRIN, puis optimiserez les requêtes applicatives et l'entretien de la base. Les exercices combinent requêtes exécutables et travaux pratiques sur une instance PostgreSQL.
Ce que tu sauras faire
- Choisir les types PostgreSQL adaptés et manipuler des données JSONB
- Écrire des upserts atomiques avec ON CONFLICT et RETURNING
- Lire un plan EXPLAIN ANALYZE et diagnostiquer une requête lente
- Concevoir des index B-tree composites et des conditions sargables
- Choisir entre index partiel, d'expression, GIN, pg_trgm, BRIN et GiST
- Mettre en place la pagination keyset, éliminer les N+1 et surveiller VACUUM et pg_stat_statements
Programme des 5 leçons
- Choisir les types PostgreSQL adaptés (identity, numeric, text, timestamptz, uuid, jsonb)
- Interroger une colonne JSONB avec ->, ->> et @>
- Écrire un upsert avec INSERT ... ON CONFLICT et récupérer les lignes avec RETURNING
- Se repérer dans psql
- 02
Lire un plan d'exécution avec EXPLAIN ANALYZE
- Comprendre le rôle du planificateur et des statistiques
- Lire un plan : nœuds, coûts estimés, lignes estimées et réelles, boucles
- Reconnaître les principaux nœuds de parcours et de jointure
- Utiliser EXPLAIN ANALYZE sans risque sur des requêtes d'écriture
EXPLAINEXPLAIN ANALYZE et BUFFERSNœuds de plan (Seq Scan, Index Scan, Hash Join…)Statistiques du planificateur
- 03
Les index B-tree : créer, composer, rendre utilisables
- Comprendre le fonctionnement et le coût d'un index B-tree
- Choisir l'ordre des colonnes d'un index composite
- Écrire des conditions utilisables par un index (sargables)
- Utiliser index couvrants et création sans blocage
Index B-treeIndex compositeConditions sargablesIndex couvrant (INCLUDE)
- 04
Index spécialisés : partiels, expressions, GIN, BRIN
- Créer des index partiels et des index d'expression
- Choisir un index GIN pour JSONB, tableaux et recherche plein texte
- Accélérer les recherches LIKE '%...%' avec pg_trgm
- Utiliser BRIN pour de très grandes tables ordonnées dans le temps
Index partielIndex d'expressionIndex GINIndex BRINIndex GiST
- 05
Optimiser les requêtes et entretenir la base
- Remplacer la pagination par OFFSET par une pagination par curseur (keyset)
- Éliminer le problème des requêtes N+1
- Comprendre MVCC, VACUUM, ANALYZE et l'autovacuum
- Trouver les requêtes les plus coûteuses avec pg_stat_statements
Pagination keysetProblème N+1MVCC, VACUUM et autovacuumpg_stat_statements
Projet final
Audit de performance d'une base applicative PostgreSQL
Vous recevez le schéma d'une application de commandes en ligne (utilisateurs, produits avec attributs JSONB, commandes, lignes de commande, journal d'événements) et un script qui le remplit de plusieurs millions de lignes. Une liste de dix requêtes issues de l'application est jugée trop lente. Réalisez l'audit complet : mesure, diagnostic, optimisation et recommandations d'entretien.
Livrables
- Un script de mise en place (schéma, données générées avec generate_series, ANALYZE)
- Pour chaque requête : le plan EXPLAIN (ANALYZE, BUFFERS) avant optimisation et le diagnostic
- Les index créés (B-tree composite, partiel, d'expression, GIN ou trigramme, BRIN selon les cas) avec la justification de chacun
- Les réécritures appliquées (conditions sargables, pagination keyset, suppression d'un N+1)
- Le plan et le temps après optimisation, résumés dans un tableau avant / après
- Une note d'exploitation : réglages d'autovacuum proposés, requête de suivi pg_stat_statements, liste des index à surveiller
Critères d'évaluation
- Chaque optimisation est justifiée par un plan et mesurée avant / après
- Le type et l'ordre des colonnes de chaque index sont adaptés aux requêtes
- Aucun index superflu : le coût en écriture est pris en compte
- Les réécritures conservent exactement le même résultat
- Les recommandations d'entretien sont réalistes pour une base en production (pas de VACUUM FULL ni de verrou long sans justification)
Questions fréquentes
Quelle version de PostgreSQL est utilisée dans ce cours ?
Le cours s'appuie sur PostgreSQL 18. Les notions présentées s'appliquent aussi aux versions récentes antérieures ; les nouveautés propres à une version sont signalées.
Comment savoir si une requête PostgreSQL utilise un index ?
Avec EXPLAIN ANALYZE : le plan affiche un nœud Index Scan, Index Only Scan ou Bitmap Index Scan qui nomme l'index utilisé. Un Seq Scan indique un parcours complet de la table.
Faut-il créer un index sur chaque colonne ?
Non. Chaque index ralentit les écritures et occupe de la place. On indexe les colonnes utilisées dans les filtres, jointures et tris fréquents, en commençant par les clés étrangères, puis on vérifie l'usage réel des index.
Quels prérequis pour suivre ce cours ?
Il faut maîtriser les requêtes SQL (jointures, sous-requêtes) et les bases de la modélisation : clés primaires et étrangères, contraintes et normalisation.
Ce cours, raconté pour ton métier
Dis ce que tu veux savoir faire : « PostgreSQL en pratique (index, performances) » prend sa place dans ton parcours, avec des exemples de ton secteur. Les notions restent celles du cours.