21.1.1. Récupérer l'espace disque

Dans son fonctionnement normal, PostgreSQL ne supprime pas immédiatement les versions périmées des lignes après un UPDATE ou un DELETE. Cette approche est nécessaire pour la consistance des accès concurrents (voir le Chapitre 12) : la version de la ligne ne doit pas être supprimée tant qu'elle est susceptible d'être lue par une autre transaction. Mais finalement, une ligne qui est plus vieille que toutes les transactions en cours n'est plus utile du tout. La place qu'elle utilise doit être rendue pour être réutilisée par d'autres lignes afin d'éviter un accroissement constant du volume occupé sur le disque. Cela est réalisé en exécutant VACUUM.

Évidemment, une table qui subit beaucoup de mises à jour et suppressions nécessitera des nettoyages plus fréquents que les tables rarement modifiées. Il peut être pertinent de programmer périodiquement par cron des tâches spécifiques qui nettoient uniquement les tables concernées (avec VACUUM) et ignorent les tables que l'on sait peu modifiées. Ceci ne sera vraiment utile que s'il y a à la fois des tables volumineuses intensément modifiées et des tables volumineuses peu modifiées. En effet, le coût supplémentaire lié au nettoyage d'une petite table ne mérite pas que l'on s'en préoccupe.

Il existe deux variantes de la commande VACUUM. La première forme, connu en tant que << vacuum fainéant >> ou plus simplement VACUUM, marque les données expirées dans les tables et les index pour une utilisation future ; il ne tente pas de récupérer immédiatement l'espace utilisée par cette donnée expirée. Du coup, le fichier de la table n'est pas plus petit et tout l'espace inutilisé dans le fichier n'est pas redonné au système d'exploitation. Cette variante de VACUUM peut être lancé en concurrence avec les autres opérations normales de la base de données.

La seconde forme est la commande VACUUM FULL. Elle utilise un algorithme plus agressif pour récupérer l'espace consommé par les versions expirées des lignes. Tout espace qui est libéré par VACUUM FULL est immédiatement rendu au système d'exploitation. Malheureusement, cette variante de la commande VACUUM acquiert un verrou exclusif sur chaque table avant que VACUUM FULL ne la traite. Du coup, utiliser fréquemment VACUUM FULL peut avoir un effet extrêmement négatif sur les performances des requêtes concurrentes sur la base de données.

La forme standard de VACUUM est mieux utilisé dans le but de maintenir une utilisation simple de l'espace disque. Donc, vous avez besoin de redonner de l'espace disque au système d'exploitation, vous pouvez utiliser VACUUM FULL — mais quel est l'intérêt de redonner de l'espace disque qui devra ensuite être de nouveau alloué ? Des VACUUM standard et d'une fréquence modérée sont une meilleure approche que des VACUUM FULL, même non fréquents, pour maintenir des tables mises à jour fréquemment.

La meilleure stratégie pour la plupart des sites est de planifier un VACUUM général sur toute la base une fois par jour, en dehors des horaires normaux de production, accompagné si nécessaire de nettoyages plus fréquents pour les tables subissant d'intenses modifications. (Quelques installations avec un taux extrêmement important de modifications de données lancent un VACUUM sur les tables très occupées jusqu'à une fois toutes les quelques minutes.) S'il y a plusieurs bases de données dans un cluster (groupe de bases de données), ne pas oublier de nettoyer chacune d'entre elles ; l'exécutable vacuumdb peut s'avérer utile.

VACUUM FULL est recommandé dans les cas où vous savez que vous avez supprimé la majorité des lignes dans une table, de façon à ce que la taille de la table soit réduit de façon conséquente avec l'approche plus plus agressive de VACUUM FULL. Utilisez le VACUUM standard, et non pas VACUUM FULL, pour les nettoyages standards.

Si vous avez une table dont le contenu est supprimé sur une base périodique, considérez de le faire avec TRUNCATE plutôt qu'avec DELETE suivi par un VACUUM. TRUNCATE supprime le contenu entier de la table immédiatement sans nécessiter un VACUUM ou VACUUM FULL pour réclamer l'espace disque maintenant inutilisé.

21.1.2. Maintenir les statistiques du planificateur

L'optimiseur de requêtes de PostgreSQL s'appuie sur des informations statistiques sur le contenu des tables dans l'optique de générer des plans d'exécutions efficaces pour les requêtes. Ces statistiques sont collectées par la commande ANALYZE, qui peut être invoquée seule ou comme une option de VACUUM. Il est important d'avoir des statistiques relativement à jour sans quoi des mauvais choix dans les plans d'exécution pourraient pénaliser la performance de la base.

À l'instar du nettoyage pour récupérer l'espace, les statistiques doivent être plus souvent collectées pour les tables intensément modifiées que pour celles qui le sont moins. Mais même si la table est très modifiée, il se peut que ces collectes soient inutiles si la distribution probabiliste des données évolue peu. Une règle simple pour décider est de voir comment évoluent les valeurs minimum et maximum des données. Par exemple, une colonne de type timestamp qui contient la date de mise à jour de la ligne aura une valeur maximum en continuelle croissance au fur et à mesure des modifications ; une telle colonne nécessitera plus de collectes statistiques qu'une colonne qui contient par exemple les URL des pages accédées sur un site web. La colonne qui contient les URL peut très bien être aussi souvent modifiée mais la distribution probabiliste des données changera certainement moins rapidement.

Il est possible d'exécuter ANALYZE sur des tables spécifiques, voire des colonnes spécifiques ; il a donc toute flexibilité pour mettre à jour certaines statistiques plus souvent que les autres en fonction des besoins de l'application. Quoi qu'il en soit, dans la pratique, l'utilité de cette fonctionnalité est douteuse. En effet, depuis PostgreSQL 7.2, ANALYZE est une opération plutôt rapide, même pour les grosses tables, parce que la collecte se base sur un échantillon aléatoire de la table et non sur toutes les données. Il est donc probablement plus simple de l'utiliser systématiquement sur toute la base.

Pour la plupart des site, la meilleure stratégie est de programmer une collecte générale des statistiques sur toute la base, une fois par jour. Ceci peut être profitablement couplé avec un VACUUM (la nuit par exemple). Quoi qu'il en soit, les administrateurs des bases dont les statistiques changent peu pourront juger que cela est exagéré et que des exécutions moins fréquentes de ANALYZE sont bien suffisantes.

21.1.3. Éviter les cycles des identifiants de transactions

Le mécanisme de contrôle de concurrence multiversion (MVCC) de PostgreSQL s'appuie sur la possibilité de comparer des identifiants de transactions (XID) ; c'est un nombre croissant : la version d'une ligne dont le XID d'insertion est supérieur au XID de la transaction en cours est << dans le futur >> et ne doit pas être visible de la transaction courante. Comme les identifiants ont une taille limitée (32 bits à ce jour), un groupe qui est en activité depuis longtemps (plus de 4 milliards de transactions) connaîtra un cycle des identifiants de transaction : le XID reviendra à 0 et soudainement les transactions du passé sembleront appartenir au futur - ce qui signifie qu'elles deviennent invisibles. En bref, perte de données totale. (En réalité, les données sont toujours là mais c'est un piètre réconfort puisqu'elles resteront inaccessibles.)

Avant PostgreSQL 7.2, la seule parade contre ces cycles de XID était de ré-exécuter initdb au minimum tous les 4 milliards de transaction. Bien sûr, cela n'était pas satisfaisant pour les sites connaissant un trafic important, donc une nouvelle solution a été élaborée. La nouvelle approche permet à un cluster de fonctionner indéfiniment, sans initdb ni aucune sorte de réinitialisation. Le prix en est le suivant : toute table dans la base doit être nettoyée au moins une fois tous les milliards de transactions.

Dans la pratique, cette exigence n'est pas onéreuse mais comme son manquement aurait pour conséquence une perte totale des données (pas seulement de l'espace disque perdu ou des performances moindres), des dispositions ont été prises pour aider les administrateurs à surveiller le temps écoulé depuis le dernier VACUUM. La suite de cette section en explique les détails.

La nouvelle approche pour la comparaison des XID distingue deux XID spéciaux, numéros 1 et 2 (BootstrapXID et FrozenXID). Ces deux XID sont toujours considérés comme plus vieux que n'importe quel autre. Les XID normaux (ceux qui sont supérieurs à deux) sont comparés sur une base modulo-2³¹. Cela signifie que pour chaque XID normal, il y en a deux milliards qui sont plus vieux et deux milliards qui sont plus récents. Une autre manière de le dire est que l'ensemble de définition des XID est circulaire et sans limite. De plus, une ligne créée avec un XID normal donné, la version de la ligne apparaîtra comme appartenant au passé pour les deux milliards de transactions qui suivront quelque soit le XID. Si la ligne existe encore après deux milliards de transactions, elle apparaîtra soudainement comme appartenant au futur. Pour éviter la disparition des données, les versions trop anciennes doivent se voir affecter le XID FrozenXID avant d'atteindre le seuil fatidique des deux milliards de transactions. Une fois qu'elles ont ce XID spécifique, elles appartiendront au passé pour toutes les transactions même en cas de cycle. Cette affectation est réalisée par VACUUM.

La politique normale de VACUUM est d'affecter FrozenXID à toute les lignes dont le XID se situe à plus de un milliard de transactions dans le passé. Elle préserve le XID original tant qu'il est utile. (En réalité, la plupart des lignes existeront et disparaîtront avant d'être << gelée >>. Avec cette méthode, l'intervalle de sécurité maximum entre les exécutions de VACUUM pour une table est d'exactement un milliard de transactions : en attendant plus longtemps, on s'expose à conserver des versions qui n'étaient pas assez vielles pour se voir affecter FrozenXID lors de la précédente exécution et qui apparaissent maintenant dans le futur du fait d'un cycle - c'est-à-dire que les données semblent perdues. (Bien sûr, elles réapparaîtront après deux nouveaux milliards de transactions mais cela n'a pas d'intérêt).

Puisque des exécutions périodiques de VACUUM sont nécessaires de toutes manières, pour les raisons évoquées ci-dessus, il est très peu probable qu'une table ne soit pas nettoyée du tout durant un milliard de transactions. Pour aider les administrateurs à assurer que cette exigence est remplie, VACUUM conserve des statistiques sur les XID dans la table système pg_database. Notamment, la table pg_database contient, pour chaque base, une colonne datfrozenxid qui est mise à jour après les VACUUM de la base (c'est-à-dire VACUUM qui ne spécifie aucune table particulière). La valeur qui est stockée est la limite en deçà de laquelle cette exécution de VACUUM a marqué la ligne comme << gelée >>. Tous les XID plus vieux que ce XID limite ont reçu le XID FrozenXID pour cette base. Pour obtenir cette information, il suffit d'exécuter la requête :

SELECT datname, age(datfrozenxid) FROM pg_database;

La colonne age calcule le nombre de transactions effectuées entre le XID limite et le XID courant.

Avec la méthode standard de gel du XID, La colonne age démarre à un milliard pour une base fraîchement nettoyée. Si l'age approche des deux milliards, la base doit de nouveau être nettoyée pour éviter les erreurs liées au cycle du XID. Il est recommandé d'exécuter un VACUUM une fois tous les demi milliard de transactions pour garder une marge de sécurité maximale. Pour aider à remplir cette exigence, chaque VACUUM émet un message si n'importe lequel des enregistrements de pg_database indique un age de plus de 1,5 milliard de transactions, par exemple :

play=# VACUUM;
WARNING:  some databases have not been vacuumed in 1613770184 transactions
HINT:  Better vacuum them within 533713463 transactions, or you may have a wraparound failure.
VACUUM

Avec l'option FREEZE, la commande VACUUM a un comportement plus poussé : 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 pour éviter les problèmes de cycle du XID. Cette technique est notamment utilisée par initdb pour préparer la base template0. Cela pourrait également être utilisé pour préparer n'importe quelle base créée par l'administrateur avec datallowconn = false dans pg_database, puisqu'il n'y a pas moyen d'exécuter VACUUM sur une base à laquelle on ne peut pas se connecter. On notera que VACUUM n'émet aucun message d'avertissement pour les enregistrements de pg_database où datallowconn = false afin de ne pas induire d'erreur ; c'est donc à l'administrateur de s'assurer que ces bases sont correctement gelées.