Perfectionnez votre maîtrise de SQL

Après avoir renforcé l'intégrité et la validation des données dans la base DVD Rental, Rodolphe est très satisfait de votre travail. Il souhaite maintenant mettre en place un système automatisé pour suivre les tables, les colonnes, et les statistiques courantes de la base de données. Votre mission consiste à collecter les informations et requêtes nécessaires afin que Rodolphe puisse mettre en place un processus de suivi efficace, permettant une analyse approfondie des opérations et l'optimisation des performances de la base de données.

Récupérez les informations associées à l’environnement PostgreSQL

Votre DBA a besoin d'informations détaillées sur l'environnement PostgreSQL pour garantir que tout fonctionne comme prévu. Vous allez donc récupérer les informations de configuration du serveur.

Récupérez les informations associées à PostgreSQL via psql

Pour obtenir un aperçu complet de la configuration actuelle de PostgreSQL, vous utiliserez psql, l'outil en ligne de commande. Cela vous permettra de vérifier les paramètres essentiels du serveur.

-- Afficher les paramètres de configuration de PostgreSQL
SHOW ALL;

Cette commande vous fournira un rapport détaillé sur la configuration du serveur, y compris les paramètres de performance, de sécurité, et de connexion.

Récupérez les informations associées à PostgreSQL via la table pg_settings

Pour une vue plus structurée des paramètres de configuration, vous pouvez interroger la table pg_settings directement via pgAdmin :

-- Récupérer les paramètres de configuration du serveur
SELECT
name,
setting,
unit,
category,
short_desc,
extra_desc,
context,
vartype,
source,
min_val,
max_val,
enumvals,
boot_val,
reset_val,
sourcefile,
sourceline,
pending_restart
FROM pg_settings;

Ces informations sont essentielles pour optimiser et sécuriser le fonctionnement de la base de données. Par exemple, elles permettent d’ajuster les paramètres de performance, comme la mémoire allouée ou le nombre de connexions simultanées, et de vérifier la configuration des sauvegardes, de la sécurité, ou de la gestion des logs. En connaissant ces réglages, vous pouvez diagnostiquer des problèmes potentiels ou améliorer l’efficacité du serveur.

En interrogeant PostgreSQL directement, vous pouvez automatiser la récupération de ces données, les inclure dans des scripts, et ainsi surveiller l'état du serveur de manière régulière ou programmée.

Utilisez psql pour avoir des informations associées aux métadonnées de la base de données

Votre DBA, enthousiaste de vos progrès, vous demande maintenant de fournir des informations détaillées sur la structure des tables et des colonnes de la base de données. Plutôt que de vous lancer dans une tâche fastidieuse de référencement manuel, il vous révèle un secret précieux : PostgreSQL (comme de nombreux systèmes SGBD) dispose de tables de métadonnées qui vous permettent de récupérer ces informations de manière automatisée.

Vous allez donc pouvoir interroger directement les métadonnées :

• avec PSQL, dans un premier temps ;

• avec l’INFORMATION_SCHEMA dans un second temps.

Ces deux approches vous fourniront toutes les informations nécessaires, sans avoir à les rechercher manuellement.

Comprenez les métadonnées

Dans le contexte d'une base de données, les métadonnées décrivent la structure et les objets de la base, permettant de comprendre comment les données sont organisées et interconnectées. Voici quelques éléments clés des métadonnées dans PostgreSQL :

1. Tables et colonnes : Les métadonnées incluent des informations sur toutes les tables présentes dans la base de données, ainsi que sur les colonnes de chaque table. Cela comprend les noms des tables, les types de données des colonnes, et les contraintes appliquées (comme les clés primaires ou étrangères).

2. Index : Les métadonnées fournissent des détails sur les index définis sur les tables. Les index sont utilisés pour améliorer les performances des requêtes en permettant des recherches plus rapides.

3. Fonctions et procédures : Les métadonnées incluent également des informations sur les fonctions et procédures stockées. Ces objets de base de données contiennent du code SQL qui peut être réutilisé pour effectuer des opérations complexes.

4. Relations : Les métadonnées montrent comment les différentes tables sont reliées entre elles à travers des clés étrangères, permettant ainsi de comprendre les relations entre les données.

En utilisant les outils de psql pour interroger ces métadonnées, vous pouvez obtenir une vue complète de la structure de la base de données sans avoir à examiner chaque élément manuellement. Cela simplifie considérablement le processus de gestion et d'analyse des données.

Utilisez psql pour récupérer les informations associées aux tables et colonnes

Avant toute chose, connectez-vous à votre base de données dvdrental pour la suite des requêtes, vous devriez voir alors un changement apparaître sur votre terminal ; indiquant que vous êtes désormais connecté à la base de données dvdrental :

\c dvdrental

Pour obtenir des détails sur les tables et les colonnes, vous pouvez utiliser la commande\d:

-- Afficher les informations sur toutes les tables
\d
-- Afficher les détails d'une table spécifique
\d table_name

Utilisez psql pour récupérer les informations associées aux autres éléments (index, fonctions, triggers, procédures…)

Pour examiner d'autres éléments importants comme les index, les fonctions, et les triggers, utilisez les commandes suivantes :

-- Afficher les index et informations détaillées d'une table
\d+ table_name
-- Lister toutes les fonctions & procédures stockées
\df
-- Lister les triggers associés à une table spécifique
\dS table_name

Cela vous donne une vue complète des différents objets de la base de données, facilitant ainsi la gestion et l'optimisation.

Utilisez Information_schema pour avoir des informations associées aux métadonnées de la base de données

Pour une approche plus standardisée et détaillée, Rodolphe vous demande d'utiliser cette fois-ci le schéma INFORMATION_SCHEMA (IS) pour accéder aux métadonnées.

Utilisez l’IS pour récupérer les informations associées aux tables et colonnes

Le schéma INFORMATION_SCHEMA fournit une manière uniforme d'accéder aux métadonnées.

Pour pouvoir lister les tables associées à l’INFORMATION_SCHEMA il vous suffit de dérouler les tables associées au Catalogs “ANSI (information_schema)”.

Vous pouvez directement requêter ces tables dans votre éditeur de texte :

-- Récupérer des informations sur toutes les tables
SELECT
*
FROM
information_schema.tables
WHERE
table_catalog = 'dvdrental'
AND table_schema = 'public';
-- Récupérer des informations associées aux colonnes de la table `rental`
SELECT
*
FROM
information_schema.columns
WHERE
table_catalog = 'dvdrental'
AND table_schema = 'public'
AND table_name = 'rental'

Le screenshot ci-dessus est le retour des informations associées aux tables de votre base de données.

Utilisez l’IS pour récupérer les informations associées aux autres éléments

On peut récupérer des informations sur de nombreux objets via l'INFORMATION_SCHEMA (fonctions, triggers…), il suffit de trouver la bonne table associée. Ci-dessous quelques exemples de récupérations d’informations intéressantes :

- Récupérer les informations associées aux contraintes check
SELECT
*
FROM
information_schema.check_constraints
WHERE
constraint_catalog = 'dvdrental'
AND constraint_schema = 'public'
-- Récupérer les informations associées aux vues
SELECT
*
FROM
information_schema.views
WHERE
table_catalog = 'dvdrental'
AND table_schema = 'public'
-- Récupérer les informations associées aux triggers
SELECT
*
FROM
information_schema.triggers
WHERE
trigger_catalog = 'dvdrental'
AND trigger_schema = 'public'

Maintenant que vous maîtrisez la récupération des métadonnées, passons à la configuration des statistiques pour surveiller et optimiser les performances de la base de données.

Configurez la collecte de statistiques

Après avoir exploré les métadonnées et récupéré les informations nécessaires sur la structure de la base de données, votre DBA souhaite maintenant mettre en place un système de collecte de statistiques. Ce système est crucial pour analyser les performances et les actions effectuées sur la base de données. En configurant correctement cette collecte, vous pourrez surveiller l'état de la base de données et identifier les éventuels goulets d'étranglement ou problèmes de performance.

Configurez et comprenez les statistiques possibles

Les statistiques de base de données fournissent des informations essentielles sur le fonctionnement et les performances de la base. PostgreSQL collecte divers types de statistiques, notamment :

• Statistiques de tables : Incluent des informations sur la taille des tables, le nombre de lignes, et la distribution des données.

• Statistiques d'index : Donnent des détails sur l'efficacité des index, comme le nombre de fois qu'ils sont utilisés et leur taille.

• Statistiques des requêtes : Mesurent la performance des requêtes, y compris le temps d'exécution et le nombre de requêtes traitées.

• Statistiques d'activité : Fournissent des données sur l'activité générale de la base de données, comme le nombre de connexions et d'opérations effectuées.

Pour vérifier la configuration de la collecte de ces statistiques, vous pouvez utiliser les commandes suivantes et vérifier que le retour est bien àon: 

-- Afficher les paramètres de collecte de statistiques
SHOW track_activities;
SHOW track_counts;

Ces paramètres vous permettent de configurer le niveau de détail des statistiques collectées et d'ajuster les paramètres de performance en fonction de vos besoins spécifiques.

Manipulez les informations associées aux performances et activités de la base de données

Une fois que vous avez validé que la collecte de statistiques était bien activée, vous pouvez utiliser diverses vues système pour accéder et analyser ces informations. Voici quelques-unes des vues les plus utiles :

Rien de plus simple ! Il suffit de les utiliser comme source dans les requêtes SQL. Je vous laisse effectuer les requêtes suivantes pour comprendre les données retournées :

-- Afficher les statistiques de la base de données
SELECT * FROM pg_stat_database;
-- Afficher les statistiques des tables utilisateur
SELECT * FROM pg_stat_user_tables;
-- Afficher les statistiques des index
SELECT * FROM pg_stat_user_indexes;
-- Afficher les activités en cours
SELECT * FROM pg_stat_activity;

Ces vues fournissent des informations sur les performances des requêtes, l'utilisation des ressources, et les activités en cours.

À vous de jouer

Contexte

Après avoir renforcé l'intégrité des données, Rodolphe est prêt à passer à l'étape suivante : automatiser le suivi des tables, des colonnes et des statistiques courantes de la base de données. Pour l'aider à démarrer, il vous a demandé de collecter des informations précises à partir des métadonnées et de produire des statistiques qui lui permettront de prioriser ses travaux d'analyse et d'optimisation. À travers ces exercices, vous allez fournir à Rodolphe les bases nécessaires pour un monitoring efficace.

Consignes

• Utilisez les vuesinformation_schema.tables&information_schema.columnspour récupérer la liste des tables et le nombre de colonnes pour chaque table de la base de données DVD Rental.

• Utilisez la vuepg_stat_user_tablespour obtenir des statistiques sur l'utilisation des tables, comme le nombre de scans et d'insertions. Identifiez les tables les plus fréquemment interrogées.

• Combinez ces informations dans une seule table en faisant une jointure sur le nom des tables.

En résumé

• Vous avez appris à récupérer les informations de configuration du serveur PostgreSQL via psql et la table pg_settings.

• Vous avez exploré comment utiliser psql pour interroger les métadonnées des tables, colonnes, index, et autres objets.

• Vous avez découvert l'utilisation de la vue information_schema pour accéder aux informations structurées des métadonnées des tables, colonnes, index, et autres objets.

• Vous avez configuré des statistiques pour suivre l'utilisation et les performances des tables et optimiser la base de données.     

Maintenant que vous aidez à la mise en place d'un système pour surveiller et analyser les performances de la base de données, il est temps d'aller plus loin en assurant la fiabilité de vos données grâce à l'application de contraintes efficaces.