Cas pratiques PostgreSQL

Contexte : La table orders contient plusieurs milliers d’enregistrements. Le Problème : Les requêtes filtrant par customer_id sont lentes. Contraintes : Ajouter un index sur la colonne customer_id sans modifier les données existantes. Livrable attendu : Fournir la requête SQL permettant de créer un index adapté à cette colonne.

Contexte : Une requête doit récupérer uniquement les utilisateurs actifs. Le Problème : La requête actuelle retourne tous les enregistrements. Requête actuelle : SELECT * FROM users WHERE active = 'yes'; Contraintes : Adapter la requête en supposant que la colonne active est de type boolean. Livrable attendu : Fournir la requête SQL corrigée retournant uniquement les utilisateurs actifs.

Contexte : Une application interne utilise PostgreSQL pour stocker les données clients. Le Problème : Une requête simple provoque une erreur de syntaxe lors de son exécution en base. Contraintes : Corriger uniquement la requête SQL sans modifier la structure de la table. Requête actuelle : SELECT id, name email FROM customers WHERE active = true; Livrable attendu : Fournir la version corrigée de la requête permettant de récupérer correctement les colonnes id, name et email.

Contexte : Une application doit se connecter à PostgreSQL avec un compte dédié. Le Problème : Aucun rôle spécifique n’a été créé pour cette application. Contraintes : Créer un utilisateur appuser avec mot de passe sécurisé et accès à la base project_db. Livrable attendu : Fournir les commandes SQL permettant de créer le rôle et lui accorder les privilèges nécessaires sur la base.

Contexte : Un nouveau projet nécessite une base de données PostgreSQL dédiée. Le Problème : Aucune base n’est encore créée pour isoler les données. Contraintes : Créer une base nommée project_db avec encodage UTF8. Livrable attendu : Fournir la commande SQL permettant de créer la base de données avec les paramètres appropriés.

Contexte : Une nouvelle application nécessite une table pour stocker les utilisateurs. Le Problème : Aucune structure n’est encore définie dans la base PostgreSQL. Contraintes : Créer une table users avec id en clé primaire auto-incrémentée, email unique et champ created_at. Livrable attendu : Fournir la requête SQL complète permettant de créer la table avec les contraintes appropriées.

Contexte : Une table products contient les colonnes id, name, price et description. Le Problème : Une tentative d’insertion échoue à cause d’une mauvaise gestion des valeurs NULL. Contraintes : Corriger la requête INSERT sans modifier la structure existante. Requête actuelle : INSERT INTO products (id, name, price) VALUES (1, 'Laptop'); Livrable attendu : Proposer une requête INSERT valide respectant la définition de la table.

Contexte : Un client a changé d’adresse email dans le système. Le Problème : L’ancienne valeur doit être remplacée dans la table customers. Contraintes : Mettre à jour uniquement l’enregistrement correspondant à l’id 10. Livrable attendu : Fournir la requête UPDATE permettant de modifier l’email du client ciblé.

Contexte : L’équipe souhaite connaître le nombre total de commandes enregistrées. Le Problème : Aucun indicateur simple n’est disponible. Contraintes : Utiliser une requête SQL simple sans modifier les données. Livrable attendu : Fournir la requête permettant de compter le nombre total de lignes dans la table orders.

Contexte : Une table temporaire test_data a été créée durant des tests. Le Problème : Elle doit être supprimée sans générer d’erreur si elle n’existe pas. Contraintes : Utiliser une commande SQL sécurisée. Livrable attendu : Fournir la requête permettant de supprimer la table uniquement si elle existe.

Contexte : Une requête critique semble lente en production. Le Problème : Il est difficile d’identifier si PostgreSQL utilise un index ou effectue un sequential scan. Contraintes : Utiliser EXPLAIN ou EXPLAIN ANALYZE pour comprendre le plan d’exécution. Livrable attendu : Décrire la méthode d’analyse du plan d’exécution et expliquer comment identifier un sequential scan problématique.

Contexte : La table users contient des millions d’utilisateurs dont seulement une partie est active. Le Problème : Les requêtes sur les utilisateurs actifs restent lentes malgré un index classique. Contraintes : Optimiser uniquement les requêtes ciblant active = true. Livrable attendu : Proposer la création d’un index partiel adapté et expliquer en quoi il améliore la performance par rapport à un index standard.

Contexte : L’entreprise souhaite tracer automatiquement les modifications effectuées sur la table customers. Le Problème : Aucun mécanisme d’audit n’est actuellement en place. Contraintes : Créer une table d’audit et un trigger exécuté après chaque UPDATE. Livrable attendu : Fournir un exemple de fonction PL/pgSQL et de trigger permettant d’enregistrer les modifications dans une table d’audit.

Contexte : L’équipe finance souhaite accéder à une vue simplifiée des ventes par client. Le Problème : Les données sont réparties entre plusieurs tables (customers, orders, order_items), rendant les requêtes complexes. Contraintes : Créer une vue SQL agrégeant le total des ventes par client sans dupliquer les données. Livrable attendu : Proposer une requête CREATE VIEW permettant d’obtenir pour chaque client le total cumulé de ses commandes.

Contexte : Une application doit avoir accès en lecture/écriture sur certaines tables mais pas sur d’autres. Le Problème : Le rôle actuel dispose de privilèges trop larges. Contraintes : Configurer des rôles distincts et accorder uniquement les permissions nécessaires. Livrable attendu : Proposer une stratégie de gestion des rôles incluant GRANT et REVOKE afin de sécuriser l’accès aux tables sensibles.

Contexte : Une opération métier implique plusieurs insertions dans différentes tables. Le Problème : Si une étape échoue, les données deviennent incohérentes. Contraintes : Utiliser des transactions PostgreSQL pour garantir l’atomicité complète de l’opération. Livrable attendu : Fournir un exemple de transaction utilisant BEGIN, COMMIT et ROLLBACK assurant l’intégrité des données en cas d’erreur.

Contexte : La base contient deux tables : customers et orders. Le Problème : Des commandes existent avec des customer_id inexistants, ce qui compromet l’intégrité des données. Contraintes : Corriger la situation sans supprimer la table orders. Ajouter une contrainte de clé étrangère garantissant la cohérence future. Livrable attendu : Fournir la requête SQL permettant d’ajouter une contrainte FOREIGN KEY et expliquer les implications sur les insertions futures.

Contexte : Une base PostgreSQL doit être sauvegardée quotidiennement pour prévenir toute perte de données. Le Problème : Aucune stratégie de sauvegarde automatisée n’est en place. Contraintes : Utiliser pg_dump et planifier la tâche via cron sur le serveur Linux. Livrable attendu : Décrire la commande pg_dump adaptée et la planification cron permettant une sauvegarde quotidienne fiable.

Contexte : Une application e-commerce utilise PostgreSQL pour stocker les commandes clients. Le Problème : Une requête filtrant par customer_id et order_date devient très lente lorsque la table dépasse plusieurs millions de lignes. Contraintes : Vous ne pouvez pas modifier la structure fonctionnelle de la table ni supprimer de données. L’optimisation doit passer par l’analyse et l’ajout d’index adaptés. Requête concernée : SELECT * FROM orders WHERE customer_id = 42 AND order_date >= '2024-01-01'; Livrable attendu : Proposer une stratégie d’optimisation incluant la création d’un index pertinent et expliquer pourquoi cet index améliore les performances.

Contexte : La table logs contient plusieurs dizaines de millions de lignes et continue de croître. Le Problème : Les requêtes temporelles deviennent de plus en plus lentes. Contraintes : Mettre en place un partitionnement basé sur la date sans perte de données. Livrable attendu : Décrire une stratégie de partitionnement par range sur une colonne timestamp et fournir un exemple de création de table partitionnée.

Contexte : Un environnement multi-bases PostgreSQL nécessite une réplication logique pour synchroniser les données entre les bases de données primaires et secondaires. Le Problème : Aucune réplication logique n’est configurée et les données doivent être répliquées en temps réel. Contraintes : Configurer la réplication logique avec des abonnements et publications pour assurer une synchronisation fiable sans perturber les performances. Livrable attendu : Fournir la configuration de réplication logique entre deux bases de données PostgreSQL avec les étapes nécessaires à la configuration complète.

Contexte : Une application critique nécessite une réplication entre deux serveurs PostgreSQL pour la haute disponibilité. Le Problème : Aucun mécanisme de bascule automatique n’est configuré et l’application risque de devenir indisponible en cas de panne. Contraintes : Mettre en place une réplication logique ou physique avec bascule automatique en cas de défaillance du serveur principal. Livrable attendu : Proposer une architecture complète incluant la configuration de la réplication maître-esclave, ainsi que la mise en place du failover automatique avec un outil comme repmgr ou pgpool.

Contexte : Une entreprise souhaite effectuer des sauvegardes fréquentes sans impacter les performances de la base. Le Problème : Les sauvegardes full prennent trop de temps et l’espace disque est limité. Contraintes : Configurer l’archivage des WAL (Write-Ahead Logs) pour permettre des sauvegardes incrémentielles et une restauration fine. Livrable attendu : Proposer une configuration de sauvegarde incluant l’archivage des WAL et une procédure de restauration incrémentielle.

Contexte : Une base de données PostgreSQL commence à manquer d’espace sur le disque principal. Le Problème : Le serveur manque d’espace disque pour les fichiers de la base de données et des logs. Contraintes : Configurer des tablespaces afin de répartir les fichiers de données et les WAL sur plusieurs disques physiques. Livrable attendu : Proposer une configuration complète de tablespaces incluant la création, la gestion des fichiers de données et la répartition des fichiers journaux sur différents disques.

Contexte : Une entreprise a plusieurs applications accédant à une même base de données PostgreSQL avec différents niveaux d’accès. Le Problème : Les utilisateurs peuvent accéder à des données sensibles sans restriction claire. Contraintes : Configurer des rôles et des permissions spécifiques via GRANT et REVOKE afin de s’assurer que les utilisateurs n’accèdent qu’aux données nécessaires. Livrable attendu : Proposer une gestion fine des rôles avec des droits distincts pour les utilisateurs des différentes applications et expliquer les implications des configurations proposées.

Contexte : Une table contenant des transactions mensuelles est soumise à de nombreuses requêtes de sélection. Le Problème : Les requêtes sur des mois passés prennent trop de temps en raison de l’indexation sur toute la table. Contraintes : Implémenter un partitionnement des index sur la colonne transaction_date sans modifier les requêtes existantes. Livrable attendu : Décrire la stratégie de partitionnement des index et expliquer en quoi cela améliore les performances des requêtes filtrées par date.

Contexte : Une table contenant des transactions de paiement dépasse les 50 Go et devient difficile à manipuler. Le Problème : Les requêtes sur des périodes anciennes sont lentes en raison du volume de données. Contraintes : Mettre en place un partitionnement basé sur la date tout en minimisant l’impact sur la structure de la base et les applications existantes. Livrable attendu : Fournir la stratégie de partitionnement (par range, hash ou list) et les requêtes SQL nécessaires pour partitionner la table sans perdre d’historique.

Contexte : Une table de factures contient des millions d’enregistrements et plusieurs requêtes complexes sont exécutées régulièrement. Le Problème : Les requêtes qui filtrent par customer_id et status sont lentes. Contraintes : Ajouter un index multi-colonnes combinant customer_id et status sans altérer les performances d’autres requêtes. Livrable attendu : Proposer un index composite adapté et expliquer pourquoi il est plus performant que l’index actuel.

Contexte : Une base de données PostgreSQL contient des tables volumineuses qui connaissent un taux élevé d’insertion et de mise à jour. Le Problème : Les requêtes deviennent lentes au fil du temps en raison de l’accumulation de tuples morts. Contraintes : Ajuster la configuration de l’autovacuum et optimiser les statistiques sans interrompre le service. Livrable attendu : Fournir des paramètres d’ajustement de l’autovacuum adaptés aux besoins de performance et expliquer leur impact sur les requêtes.

Contexte : Une requête complexe impliquant des jointures sur plusieurs tables prend beaucoup de temps à s’exécuter. Le Problème : Le plan d’exécution suggère qu’il existe un scan séquentiel inefficace sur une des grandes tables. Contraintes : Analyser et optimiser cette requête en utilisant EXPLAIN ANALYZE pour identifier les goulots d’étranglement. Livrable attendu : Fournir un plan d’action détaillé d’optimisation, incluant les index nécessaires, les modifications de la requête et les raisons des choix effectués.