Administration de PostgreSQL / PostGIS

VACUUM récupère l'espace inutilisé et, optionnellement, analyse une base.

VACUUM récupère l'espace de stockage occupé par des lignes mortes. Lors des opérations normales de PostgreSQL, les lignes supprimées ou rendues obsolètes par une mise à jour ne sont pas physiquement supprimées de leur table. Elles restent présentes jusqu'à ce qu'un VACUUM soit lancé. C'est pourquoi, il est nécessaire de faire un VACUUM régulièrement, spécialement sur les tables fréquemment mises à jour.

Sans paramètre, VACUUM traite toutes les tables de la base de données courante pour lequel l'utilisateur connecté dispose du droit d'exécution du VACUUM. Avec un paramètre, VACUUM ne traite que cette table.

Les options :

• FULL récupère plus d'espace (compactage des tables), mais est beaucoup plus long et prend un verrou exclusif sur la table (inaccessible à d'autres requêtes pendant le VACCUM). Cette méthode requiert aussi un espace disque supplémentaire, car elle écrit une nouvelle copie de la table et ne supprime l'ancienne copie qu'à la fin de l'opération. Habituellement, cela doit seulement être utilisé quand une quantité importante d'espace doit être récupérée de la table. .

• FREEZE : les versions des lignes sont gelées si elles sont suffisamment vieilles pour être visibles de toutes les transactions en cours. En particulier, sur une base en lecture seulement, VACUUM FREEZE aura pour résultat de geler toutes les lignes de la base. Donc, tant que la base n'est pas modifiée, aucun nettoyage supplémentaire n'est nécessaire.

• ANALYZE : Met à jour les statistiques utilisées par l'optimiseur pour déterminer la méthode la plus efficace pour exécuter une requête.

Pour exécuter un VACUUM sur une table, vous devez habituellement être le propriétaire de la table ou un superutilisateur. Néanmoins, les propriétaires de la base de données sont autorisés à exécuter VACUUM sur toutes les tables de leurs bases de données, sauf sur les catalogues partagés. Cette restriction signifie qu'un vrai VACUUM sur une base complète ne peut se faire que par un superutilisateur.)

Il est recommandé que les bases de données actives de production soient traitées par VACUUM fréquemment (au moins toutes les nuits), pour supprimer les lignes mortes. Après avoir ajouté ou supprimé un grand nombre de lignes, il peut être utile de faire un VACUUM ANALYZE sur la table affectée. Cela met les catalogues système à jour de tous les changements récents et permet à l'optimiseur de requêtes de PostgreSQL™ de faire de meilleurs choix lors de l'optimisation des requêtes

PostgreSQL inclut un « autovacuum » qui peut automatiser la maintenance par VACUUM. Certains administrateurs de bases de données voudront suppléer ou remplacer les activités du démon avec une gestion manuelle des commandes VACUUM, qui seront typiquement exécutées suivant un planning par des scripts cron ou par le planificateur de tâches.

A noter que si on est positionné dans le navigateur au niveau d'un objet (table,...) la maintenance ne s’exécutera qu'au niveau de l'objet sélectionné.

À noter que le VACCCUM est également disponible pour l'administrateur système sous forme de la commande système vacuumdb.

ANALYZE collecte des statistiques sur le contenu des tables de la base de données et stocke les résultats dans le catalogue système pg_statistic.

L'optimiseur de requêtes les utilise pour déterminer les plans d'exécution les plus efficaces.

ANALYZE peut être exécuté comme une option de VACUUM.

REINDEX reconstruit un index en utilisant les données stockées dans la table, remplaçant l'ancienne copie de l'index. Il y a plusieurs raisons pour utiliser REINDEX :

• Un index a été corrompu et ne contient plus de données valides

• L'index en question a « explosé », c'est-à-dire qu'il contient beaucoup de pages d'index mortes ou presque mortes

• Vous avez modifié un paramètre de stockage (par exemple, fillfactor) pour un index et vous souhaitez vous assurer que la modification a été prise en compte.

• La construction d'un index avec l'option CONCURRENTLY a échoué, laissant un index « invalide »

Dans les vieilles versions de PostgreSQL, le gain avec REINDEX peut-être énorme.

Nota bene : disponible pour l'administrateur système avec la commande reindexdb.

CLUSTER commande permet de réécrire les données d'une table dans un ordre donné (suivant un index). Cela peut être utile pour optimiser des requêtes SQL, selon la façon dont les données sont accédées.

Pour des tables spatiales, elle permet d'ordonner physiquement la table selon l'index géométrique et permet donc un accès aux enregistrements "proches" plus rapide.

Attention toutefois : cette opération reste lourde puisqu'elle pause un verrou exclusif sur la table traitée (aucune autre action possible sur la table durant ce temps).