DELETE, VACUUM, VACUUM FULL : où va l'espace disque dans PostgreSQL ?

Vous avez supprimé la moitié de votre table, et le disque n'a pas bougé

Supprimer des lignes ne rend pas de place au système de fichiers. C'est un comportement connu, mais les chiffres exacts aident à comprendre ce que chaque commande fait vraiment. Hollis Varden a mesuré tout cela dans What deleting rows does to a PostgreSQL table, sur PostgreSQL 18.6 avec les réglages par défaut et l'autovacuum activé. Voici ce qu'il faut en retenir pour la production.

L'expérience : une table de 2 millions de lignes

Le test est simple. Une table de 2 000 000 de lignes occupe 137 Mo (mesurés avec pg_table_size). On supprime une ligne sur deux avec un DELETE ... WHERE id % 2 = 0, puis on observe la taille à chaque étape :

• après le DELETE : 137 Mo
• après un VACUUM : 137 Mo
• après un VACUUM FULL : 68 Mo

Autrement dit, seul VACUUM FULL a réellement réduit le fichier. Les deux premières commandes n'ont rien changé à la taille sur disque.

Ce que fait chaque commande

DELETE : il marque, il ne libère pas

Un DELETE ne retire pas physiquement les lignes. Il les marque comme mortes, dans le cadre du fonctionnement MVCC de PostgreSQL. Les pages du fichier gardent donc exactement la même taille.

VACUUM : il rend l'espace réutilisable

Un VACUUM standard ne restitue pas l'espace au système d'exploitation. Il le rend en revanche réutilisable : les prochaines insertions viendront combler les trous au lieu d'agrandir le fichier. Il existe une exception : les pages entièrement vides situées à la toute fin du fichier peuvent être tronquées.

Dans l'expérience, une ligne sur deux a été supprimée, donc chaque page conserve la moitié de ses lignes et aucune page vide n'apparaît en fin de fichier. Il n'y avait rien à couper, d'où les 137 Mo inchangés.

VACUUM FULL : il réécrit tout

VACUUM FULL construit une nouvelle copie compacte de la table, puis supprime l'ancienne. C'est pourquoi il récupère l'espace, mais c'est aussi pourquoi il pose un verrou qui bloque lectures et écritures pendant toute l'opération. Sur une table critique en production, c'est rarement acceptable sans fenêtre de maintenance.

Vider une table : DELETE ou TRUNCATE ?

La seconde mesure compare deux façons de vider une table d'un million de lignes :

La raison est mécanique. DELETE parcourt et journalise chaque ligne, alors que TRUNCATE attribue simplement à la table un nouveau fichier vide. L'écart s'amplifie avec la taille de la table, et la colonne WAL compte si vous avez des réplicas ou de l'archivage : 125 Mo de journal, ce sont 125 Mo à transmettre.

TRUNCATE n'est cependant pas un remplaçant universel. Il prend un verrou ACCESS EXCLUSIVE et ne déclenche pas les triggers ON DELETE. L'article source signale aussi qu'il refuse de s'exécuter dans certains cas, notamment avec des contraintes de clé étrangère (l'extrait fourni s'arrête avant le détail).

Que faire concrètement ?

Quelques pistes pour vos tables à forte rotation de données :

• Surveillez la taille réelle des tables et le bloat, pas seulement le nombre de lignes.
• Comptez sur l'autovacuum pour recycler l'espace : si la table continue de recevoir des insertions, les trous seront réutilisés et la taille se stabilisera.
• Réservez VACUUM FULL aux cas où il faut vraiment rendre de la place à l'OS, et planifiez-le en tenant compte de son verrou.
• Pour vider entièrement une table, privilégiez TRUNCATE si ses limites (verrou, triggers, clés étrangères) sont compatibles avec votre usage.
• Pour retirer de gros volumes de lignes anciennes, le partitionnement permet de supprimer une partition entière plutôt que des millions de lignes une par une.

Conclusion

Retenez trois idées : un DELETE ne libère rien, un VACUUM prépare la réutilisation sans rendre d'octets, et seule une réécriture complète compacte le fichier. Savoir distinguer ces trois comportements évite de saturer un disque par surprise, ou de lancer un VACUUM FULL bloquant au pire moment. Pour le détail des mesures et du protocole, lisez l'article original de Hollis Varden.