TRUNCATE — la suppression rapide de toutes les lignes et le High Water Mark

Le code de cet article cible les SGBDR de production

La syntaxe TRUNCATE TABLE de cet article est la forme utilisée par les SGBDR de production comme MySQL / PostgreSQL / Oracle / SQL Server. La console navigateur de ce cours (qui utilise SQLite) n'accepte pas directement le mot-clé TRUNCATE. Sous SQLite, DELETE FROM table; produit le même effet, et une optimisation truncate interne lui donne la même vitesse que TRUNCATE.

TRUNCATE — vider toutes les lignes, vite

Il y a deux manières de supprimer toutes les lignes d'une table d'un coup. La première est DELETE FROM table; (sans WHERE), couverte dans l'article précédent. La seconde est `TRUNCATE TABLE table;`, le sujet de cet article. Les deux laissent la table vide, mais la façon dont elles y arrivent et leur vitesse sont complètement différentes.

DELETE est une commande DML (Data Manipulation Language) qui retire les lignes une par une, en laissant une entrée de log pour chaque suppression. TRUNCATE, à l'inverse, penche vers le DDL (Data Definition Language) — il réinitialise toute la table sans tenue de comptes ligne par ligne, presque comme si la définition même de la table était recréée à partir de zéro.

-- 1) DELETE de tout (log de suppression par ligne, peut être rollbacké)
DELETE FROM customer;

-- 2) TRUNCATE de tout (reset en une fois, pas de log par ligne, commit immédiat en général)
TRUNCATE TABLE customer;

-- Note : la console de ce cours (SQLite) n'accepte pas TRUNCATE.
--   DELETE FROM customer; bénéficie en interne de la même optimisation truncate et reste rapide.

Différences entre MySQL / PostgreSQL / Oracle

La syntaxe TRUNCATE TABLE table; elle-même est partagée par MySQL / PostgreSQL / Oracle / SQL Server, mais le comportement fin diffère selon la base.

- MySQL : traité comme DDL, ne peut pas être rollbacké, AUTO_INCREMENT est réinitialisé

- PostgreSQL : peut être rollbacké, RESTART IDENTITY réinitialise aussi la séquence

- Oracle : traité comme DDL, ne peut pas être rollbacké, REUSE STORAGE / DROP STORAGE permet de contrôler comment le stockage est rendu

- SQL Server : peut être rollbacké

Le High Water Mark — ce qui se cache derrière l'écart de vitesse

La clé pour comprendre l'écart de vitesse entre DELETE et TRUNCATE est le concept de High Water Mark (HWM). Le HWM est un marqueur qui pointe vers le bloc de stockage le plus haut que la table ait jamais utilisé. Le terme vient d'Oracle, mais PostgreSQL / SQL Server / MySQL (InnoDB) ont tous des optimisations équivalentes qui fonctionnent en interne.

Quand la base fait un full scan (un balayage ligne par ligne comme SELECT * FROM table), elle lit chaque bloc depuis le début de la table jusqu'au HWM dans l'ordre. Plus le HWM est haut, plus elle a de blocs à lire — et comme elle ne saute pas les blocs vides, un scan peut être lent même sur une table vide si le HWM est haut.

Le comportement crucial ici, c'est que DELETE n'abaisse pas le HWM. Quand tu lances DELETE FROM table; pour retirer toutes les lignes, le contenu de la table devient vide, mais les blocs eux-mêmes restent dans le segment. Le HWM reste sur bloc 5. Lance ensuite SELECT COUNT(*) FROM table; et même si le compte de lignes vaut 0, la base lit chaque bloc au niveau du HWM ou en dessous jusqu'au bout — en prenant à peu près autant de temps qu'avant la suppression.

TRUNCATE TABLE table; est la commande qui réinitialise le HWM au début de la table, ramenant instantanément la cible du full scan à 0 bloc. C'est la vraie raison pour laquelle TRUNCATE est décrit comme « instantané même sur les très grandes tables ».

DELETE n'a pas réduit la taille des données ?

Je suis tombé sur une situation en production où j'avais lancé DELETE pour réduire la taille des données, mais l'usage du stockage avait à peine baissé.

La cause était exactement celle-ci : le HWM ne descend pas. DELETE ne retire les lignes que logiquement, alors que les blocs (pages) que la table avait réservés restent en place. Le fichier sur disque conserve toujours cette zone, donc du point de vue de l'OS, la taille des données n'a pas changé.

Quand tu veux vraiment réduire le stockage, lance la commande de réorganisation propre à chaque base (VACUUM FULL sur PostgreSQL, OPTIMIZE TABLE sur MySQL InnoDB, ALTER TABLE ... SHRINK SPACE sur Oracle), ou utilise TRUNCATE pour un nettoyage complet.

Choisir entre DELETE et TRUNCATE

En pratique, le choix n'est pas simplement « TRUNCATE pour tout, DELETE quand il faut une condition ». Les vrais facteurs sont si tu as besoin de pouvoir rollbacker, si les triggers doivent se déclencher, et à quel point la vitesse compte. Le tableau de comparaison ci-dessous est la checklist standard.

Pièges de TRUNCATE

TRUNCATE est rapide et pratique, mais c'est typiquement une opération qu'on ne peut pas reprendre. Fais attention à :

- Certaines bases commitent à l'exécution (Oracle / MySQL). En production, lance d'abord SELECT COUNT(*) pour confirmer le compte de lignes avant de tirer

- `AUTO_INCREMENT` est réinitialisé (selon la base). Si les ids sont exposés à des systèmes externes ou à des URL, attention aux collisions après reset

- Les contraintes de clé étrangère peuvent le bloquer (la plupart des bases). Si subscription référence customer, tu devras d'abord vider la table fille pour faire un TRUNCATE de customer

- Les triggers ne se déclenchent généralement pas. Un trigger est le mécanisme de la base qui exécute automatiquement du SQL en réponse à des insertions / mises à jour / suppressions de lignes — par exemple « écrire une entrée d'audit dans une autre table chaque fois qu'une ligne est supprimée ». DELETE déclenche ce trigger ligne par ligne, mais TRUNCATE n'opère pas au niveau ligne, donc les logs d'audit et autres enregistrements similaires ne seront pas créés

Sors TRUNCATE quand tu as besoin de la vitesse maximale pour un nettoyage massif de très grosses données ; sinon DELETE WHERE ou un DELETE rollbackable est le choix plus sûr.