Formation PostgreSQL : optimisation des bases de données relationnelles pour développeurs

Programme de la formation
PostgreSQL : optimisation des bases de données relationnelles pour développeurs

• Introduction à l'optimisation PostgreSQL et outils

  - Rappels fondamentaux sur PostgreSQL et son architecture
  - Pourquoi optimiser ? Enjeux et bénéfices pour le développement
  - Présentation des outils d'analyse de performance (pg_stat_statements, pg_explain)
  - Configuration de l'environnement de travail pour l'optimisation
  - Méthodologie d'optimisation : Identifier, Analyser, Corriger, Valider

• Analyse approfondie des plans d'exécution EXPLAIN/EXPLAIN ANALYZE

  - Comprendre les opérateurs de plan (Seq Scan, Index Scan, Join)
  - Interpréter les coûts et durées d'exécution des requêtes
  - Identification des goulots d'étranglement avec EXPLAIN ANALYZE
  - Utilisation des formats de sortie (TEXT, XML, JSON, YAML)
  - Bonnes pratiques pour la lecture et l'analyse de plans complexes

• Optimisation des requêtes SQL - Bases

  - Réécriture de requêtes inefficaces : filtres, tris, GROUP BY
  - Utilisation optimale des clauses WHERE, ORDER BY, LIMIT
  - Impact des fonctions dans les requêtes et alternatives
  - Pré-calculs et vues matérialisées pour les requêtes complexes
  - Utilisation des CTE (Common Table Expressions) pour la lisibilité et la performance

• Stratégies d'indexation avancées

  - Types d'index PostgreSQL (B-Tree, Hash, GIN, GIST, BRIN, SP-GiST)
  - Création d'index multi-colonnes et index partiels
  - Conditions d'utilisation des index et leur impact sur les inserts/updates
  - Monitoring de l'utilisation des index et détection des index inutiles
  - Recommandations pratiques pour le choix et la gestion des index

• Optimisation des jointures et sous-requêtes

  - Approches des jointures complexes : NESTED LOOP, HASH JOIN, MERGE JOIN
  - Cohérence des types de données pour les jointures rapides
  - Réécriture des sous-requêtes corrélées en jointures si possible
  - Optimisation des requêtes avec EXISTS, NOT EXISTS, IN, NOT IN
  - Bonnes pratiques pour l'ordre des tables dans les jointures

• Gestion des transactions et verrous

  - Niveaux d'isolation des transactions (Read Committed, Repeatable Read...)
  - Impact des verrous et deadlocks sur la performance
  - Identification et résolution des blocages (pg_locks, pg_stat_activity)
  - Optimisation des transactions pour minimiser les conflits
  - Utilisation des advisory locks pour la gestion manuelle des ressources

• Configuration fine du serveur PostgreSQL

  - Paramètres de mémoire : work_mem, maintenance_work_mem, effective_cache_size
  - Paramètres de disque : wal_buffers, checkpoint_timeout, random_page_cost
  - Paramètres du planificateur : cpu_tuple_cost, cpu_index_tuple_cost
  - Optimisation du VACUUM et AUTOVACUUM
  - Surveillance et ajustement dynamique des paramètres de configuration

• Optimisation du schéma de base de données

  - Normalisation et dénormalisation : avantages et inconvénients
  - Choix des types de données appropriés (integer vs bigserial, text vs varchar)
  - Utilisation des contraintes (CHECK, UNIQUE, NOT NULL, FOREIGN KEY)
  - Partitionnement des tables pour les bases volumineuses
  - Audit et refactoring de schémas existants

• Performance avec les fonctions et procédures stockées

  - Impact de PL/pgSQL sur la performance des requêtes SQL
  - Bonnes pratiques de conception pour des fonctions efficaces
  - Utilisation des langages procéduraux alternatifs (PL/Python, PL/Perl)
  - Debugging et profilage des fonctions et procédures
  - Sécurité et privilèges des fonctions : impact sur la performance

• Optimisation des opérations d'écriture (INSERT, UPDATE, DELETE)

  - Influence des index et contraintes sur les performances d'écriture
  - Utilisation de COPY pour les importations massives de données
  - Optimisation des transactions pour les écritures concurrentes
  - Traitement de larges blocs de données par lots (batch processing)
  - Stratégies de suppression et mise à jour efficaces

• Surveillance et maintenance de la performance

  - Outils de monitoring (pg_top, PGMON, DataDog, Prometheus)
  - Analyse des journaux d'erreurs et de requêtes lentes
  - Suivi de l'activité du serveur (pg_stat_activity, pg_stat_statements)
  - Maintenance régulière : VACUUM, REINDEX, CLUSTER
  - Planification des tâches d'optimisation et audit de performance

• Cas pratiques d'optimisation et architecture avancée

  - Exercices pratiques d'optimisation de requêtes réelles
  - Stratégies de montée en charge : réplication, sharding, pooling de connexions
  - Utilisation des caches applicatifs et côté base de données
  - Déploiement et tests des optimisations en production
  - Récapitulatif et meilleures pratiques à adopter en développement PostgreSQL