Lors des opérations de modification ou suppression de données dans une base PostgreSQL, les lignes supprimées ou rendues obsolètes par une mise à jour ne sont pas physiquement supprimées de leur table. C'est pourquoi, il est nécessaire de faire un nettoyage régulier, spécialement sur les tables fréquemment mises à jour.

Pour récupérer l'espace inutilisé dans une base PostgreSQL, on utilise la commande vaccum.

La syntaxe pour cette commande est

VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ nom_table [ (nom_colonne [, ...] ) ] ]

Si nom_table est omis, et donc nom_colonne aussi, l'exécution est effectuée sur l'ensemble de la base.

FULL

Choisit un vacuum « full », qui récupère plus d'espace, mais est beaucoup plus long et prend un verrou exclusif sur la table. Cette méthode requiert aussi un espace disque supplémentaire car il é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

Choisit un « gel » agressif des lignes. Indiquer FREEZE est équivalent à réaliser un VACUUM avec les paramètres vacuum_freeze_min_age et vacuum_freeze_table_age configurés à zéro. Un gel aggressif est toujours effectué quand la table est réécrite, cette option est donc redondante quand FULL est spécifié.

VERBOSE

Affiche un rapport détaillé de l'activité de vacuum sur chaque table.

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.

Lors de l'exploitation de base de données PostgreSQL, il est important d'effectuer un nettoyage régulier, ainsi qu'une analyse pour mettre à jour les statistiques utilisées par l'optimiseur

On peux par exemple utiliser la commande suivante

 $ cat /binaires/vacuum.sh 
 #!/bin/bash
 LOG_FILE=vaccum_$(date +"%Y%m%d%H%M").log
 psql -U postgres -d MY_DATABASE -c "vacuum full verbose analyze;" 1>${LOG_FILE} 2>&1

Avec MY_DATABASE a remplacer par le nom de votre base de données à traiter.

Cette commande effectue une connexion sur la base de données, lance en vacuum full pour nettoyer les lignes obsolètes, puis un analyze pour mettre à jour les statistiques.

Pour éviter tout blocage avec les tables traitées, il est important d'exécuter cette commande sur une période creuse.

Cette commande peut être exécuter de façon régulière avec l'appel via le planificateur de linux : crontab

Par exemple

 $ crontab -l
 # Nettoyage de la base (à 3h30)
 30 3 * * * /binaires/vacuum.sh 1>/dev/null 2>&1

Dans ces commandes on utilise 1> et 2>&1. L'option 1>monfichier redirige la sortie standard (canal 1 par défaut) vers monfichier. L'option 2>&1 redirige la sortie éventuelle d'erreur (canal 2 par défaut) vers le canal 1, soit monfichier aussi.

Attention, il est nécessaire de respecter l'ordre des options, sinon 2>&1 1>monfichier se traduit par : la sortie d'erreur est redirigée vers la sortie standard (qui est pour l'instant mon écran), puis la sortie standard vers monfichier, et donc les erreurs seront affichées l'écran et non pas dans monfichier avec la sortie standard.