Introduction
Dans PostgreSQL, il est essentiel de comprendre comment l’espace disque est alloué aux tables. Cette connaissance est cruciale pour une gestion efficace des ressources, la planification de la capacité et la prévention de la dégradation des performances.
En effet, contrairement à certains systèmes de bases de données qui stockent les tables volumineuses dans un seul fichier, PostgreSQL utilise une stratégie de segmentation.
Comprendre les espaces de tables (Tablespaces)
Avant d’explorer les fonctions permettant de déterminer la taille des tables, il est important de comprendre la notion d’espace de tables (tablespace en anglais).
Un tablespace est un emplacement physique sur le disque où sont stockées les données de la base de données, y compris les tables, les index et autres objets.
PostgreSQL utilise les tablespaces pour mapper les noms logiques des objets de la base de données sur des emplacements physiques sur le disque. Par défaut, PostgreSQL fournit deux tablespaces : pg_default et pg_global. pg_default est utilisé pour stocker la plupart des données, tandis que pg_global est utilisé pour les objets globaux du système .
Se connecter à la base de données et sélectionner une table
Avant d’exécuter les commandes SQL pour obtenir la taille et le taux de remplissage des tables, vous devez vous connecter à la base de données PostgreSQL et sélectionner la table cible. Voici les étapes à suivre :
- Connectez-vous à votre base de données en utilisant un client SQL comme psql.
- Sélectionnez la base de données qui contient la table que vous souhaitez analyser.
- Utilisez la commande \dt pour afficher la liste des tables dans la base de données sélectionnée.
Une fois ces étapes effectuées, vous pouvez exécuter les commandes SQL décrites dans les sections suivantes.
Obtenir la taille allouée à une table
PostgreSQL propose plusieurs fonctions pour obtenir la taille d’une table. Voici les plus courantes :
- pg_table_size(‘nom_table’): Retourne la taille de la table en octets, excluant les index. Cette fonction inclut l’espace libre, les visibility maps et les données stockées dans les tables TOAST . Il est important de noter que la technique TOAST (The Oversized-Attribute Storage Technique) permet à PostgreSQL de gérer les champs de données volumineux qui ne tiennent pas dans une page standard. TOAST stocke ces données dans une zone de stockage séparée et conserve un pointeur vers ces données dans la table principale .
- pg_indexes_size(‘nom_table’): Retourne la taille totale de tous les index attachés à la table en octets [5, 6].
- pg_total_relation_size(‘nom_table’): Retourne la taille totale de la table en octets, y compris les index et tous les objets associés [5, 6].
Pour afficher la taille dans un format lisible par l’homme (ko, Mo, Go…), vous pouvez utiliser la fonction pg_size_pretty() en combinaison avec les fonctions précédentes. Par exemple :
| SELECT pg_size_pretty(pg_total_relation_size(‘ma_table’)); |
Obtenir le taux de remplissage d’une table
Il n’existe pas de fonction unique pour obtenir directement le taux de remplissage d’une table dans PostgreSQL. Cependant, vous pouvez le calculer en utilisant les informations fournies par la fonction pgstattuple(‘nom_table’) et en considérant l’espace occupé par les tuples « morts » et l’espace libre .
Voici les colonnes retournées par pgstattuple() et leur signification :
| Colonne | Description |
|---|---|
| table_len | Longueur physique de la relation en octets |
| tuple_count | Nombre de tuples vivants |
| tuple_len | Longueur totale des tuples vivants en octets |
| tuple_percent | Pourcentage de tuples vivants |
| dead_tuple_count | Nombre de tuples morts |
| dead_tuple_len | Longueur totale des tuples morts en octets |
| dead_tuple_percent | Pourcentage de tuples morts |
| free_space | Espace libre total en octets |
| free_percent | Pourcentage d’espace libre |
Calcul du taux de remplissage :
Pour calculer le taux de remplissage, suivez ces étapes :
| Étape | Formule |
|---|---|
| Espace vivant | espace_vivant = tuple_len |
| Espace total utilisé | espace_total_utilise = table_len – free_space |
| Taux de remplissage | taux_remplissage = (espace_vivant / espace_total_utilise) * 100 |
Exemple:
Prenons l’exemple concret de la sortie de pgstattuple() présentée plus loin dans cet article .
| table_len | 458752 tuple_count | 1470 tuple_len | 438896 tuple_percent | 95.67 dead_tuple_count | 11 dead_tuple_len | 3157 dead_tuple_percent | 0.69 free_space | 8932 free_percent | 1.95 |
En appliquant les formules ci-dessus, nous obtenons :
- espace_vivant = 438896
- espace_total_utilise = 458752 – 8932 = 449820
- taux_remplissage = (438896 / 449820) * 100 = 97.57%
Remarques :
- L’espace total utilisé (table_len – free_space) inclut l’espace occupé par les tuples vivants, les tuples morts et l’overhead de la table (en-têtes de page, pointeurs vers les tuples, etc.) .
- Le taux de remplissage ne prend pas en compte l’espace occupé par les index.
- Les tuples « morts » sont des enregistrements qui ont été supprimés ou mis à jour mais qui ne sont pas encore physiquement supprimés de la table. Ils peuvent être récupérés en utilisant la commande VACUUM .
- Il existe deux variantes de la commande VACUUM : VACUUM et VACUUM FULL. VACUUM récupère l’espace et le rend disponible pour la réutilisation au niveau de la base de données, tandis que VACUUM FULL est plus agressif, verrouille la table, supprime les tuples morts et les pages vides, puis retourne l’espace récupéré au système d’exploitation. VACUUM FULL peut être gourmand en ressources et nécessite un verrou exclusif sur la table pendant le processus .
Exemple concret
Voici un exemple d’utilisation de pg_total_relation_size() pour obtenir la taille de la table « actor » :
| SELECT pg_size_pretty(pg_total_relation_size(‘actor’)); |
Résultat :
| 72 kB |
Et voici un exemple de sortie de la fonction pgstattuple() :
| SELECT * FROM pgstattuple(‘pg_catalog.pg_proc’); |
Résultat :
| table_len | 458752 tuple_count | 1470 tuple_len | 438896 tuple_percent | 95.67 dead_tuple_count | 11 dead_tuple_len | 3157 dead_tuple_percent | 0.69 free_space | 8932 free_percent | 1.95 |
Bonnes pratiques pour la gestion de la taille et du taux de remplissage des tables
Pour maintenir des performances optimales de votre base de données PostgreSQL, il est important de gérer efficacement la taille et le taux de remplissage de vos tables. Voici quelques bonnes pratiques à suivre :
- Surveillez régulièrement la taille de vos tables en utilisant les fonctions décrites dans cet article.
- Analysez le gonflement des tables (table bloat) en utilisant des outils comme pgstattuple() pour identifier les tables contenant un grand nombre de tuples morts.
- Exécutez la commande VACUUM régulièrement pour supprimer les tuples morts et libérer de l’espace.
- Envisagez d’utiliser VACUUM FULL pour les tables fortement fragmentées, mais soyez conscient de son impact sur les performances.
- Optimisez la conception de vos tables en choisissant les types de données appropriés et en évitant les colonnes inutiles.
- Utilisez des index judicieusement pour améliorer les performances des requêtes, mais n’oubliez pas que les index consomment également de l’espace disque.
Conclusion
PostgreSQL offre une variété de fonctions pour déterminer la taille des tables, y compris pg_table_size(), pg_indexes_size() et pg_total_relation_size().
La fonction pgstattuple() permet d’analyser l’espace occupé par les tuples vivants, les tuples morts et l’espace libre dans une table.
En combinant ces informations, vous pouvez calculer le taux de remplissage d’une table.
Il est crucial de surveiller la taille et le taux de remplissage des tables pour garantir les performances de la base de données et une allocation efficace des ressources. La commande VACUUM, avec ses variantes VACUUM et VACUUM FULL, joue un rôle important dans la récupération de l’espace disque occupé par les tuples morts.
En suivant les bonnes pratiques de gestion de la taille des tables et en utilisant les outils fournis par PostgreSQL, vous pouvez optimiser l’utilisation de l’espace disque et maintenir les performances de votre base de données.