TRUNCATE — la eliminación rápida de todas las filas y la marca de agua alta

El código de este artículo está pensado para RDBMS de producción

La sintaxis TRUNCATE TABLE de este artículo es la forma usada por RDBMS de producción como MySQL / PostgreSQL / Oracle / SQL Server. La consola del navegador de este curso (que usa SQLite) no acepta directamente la palabra clave TRUNCATE. En SQLite, DELETE FROM table; tiene el mismo efecto, y una optimización interna de truncate hace que se ejecute a la misma velocidad que TRUNCATE.

TRUNCATE — borrar todas las filas, rápido

Hay dos formas de eliminar todas las filas de una tabla a la vez. Una es DELETE FROM table; (sin WHERE), que cubrimos en el artículo anterior. La otra es `TRUNCATE TABLE table;`, el tema de este artículo. Ambas dejan la tabla vacía, pero cómo llegan ahí y a qué velocidad lo hacen son completamente diferentes.

DELETE es un comando DML (Data Manipulation Language) que elimina filas una a una, dejando una entrada de log por cada eliminación. TRUNCATE, en cambio, se inclina hacia DDL (Data Definition Language) — resetea la tabla entera sin contabilidad por fila, casi como si la propia definición de la tabla se recreara desde cero.

-- 1) DELETE de todo (log de borrado por fila, se puede hacer rollback)
DELETE FROM customer;

-- 2) TRUNCATE de todo (reset de un solo golpe, sin log por fila, normalmente hace commit inmediatamente)
TRUNCATE TABLE customer;

-- Nota: la consola de este curso (SQLite) no acepta TRUNCATE.
--   DELETE FROM customer; recibe internamente la misma optimización de truncate y se ejecuta rápido.

Diferencias entre MySQL / PostgreSQL / Oracle

La sintaxis TRUNCATE TABLE table; es compartida por MySQL / PostgreSQL / Oracle / SQL Server, pero el comportamiento fino difiere según el DB.

- MySQL: Tratado como DDL, no se puede hacer rollback, AUTO_INCREMENT se resetea

- PostgreSQL: Se puede hacer rollback, RESTART IDENTITY también resetea la secuencia

- Oracle: Tratado como DDL, no se puede hacer rollback, REUSE STORAGE / DROP STORAGE te permite controlar cómo se devuelve el almacenamiento

- SQL Server: Se puede hacer rollback

La marca de agua alta — qué hay detrás de la diferencia de velocidad

La clave para entender la diferencia de velocidad entre DELETE y TRUNCATE es el concepto de la marca de agua alta (High Water Mark, HWM). La HWM es un marcador que apunta al bloque de almacenamiento más alto que la tabla ha usado alguna vez. El término viene de Oracle, pero PostgreSQL / SQL Server / MySQL (InnoDB) tienen todos optimizaciones equivalentes funcionando internamente.

Cuando el DB hace un full scan (un barrido fila por fila como SELECT * FROM table), lee todos los bloques desde el inicio de la tabla hasta la HWM en orden. Cuanto más alta la HWM, más bloques tiene que leer — y como no salta los bloques vacíos, un scan puede ser lento incluso en una tabla vacía si la HWM está alta.

El comportamiento crucial aquí es que DELETE no baja la HWM. Cuando ejecutas DELETE FROM table; para eliminar todas las filas, el contenido de la tabla queda vacío, pero los bloques en sí permanecen en el segmento. La HWM sigue en block 5. Ejecuta SELECT COUNT(*) FROM table; después y, aunque el conteo de filas sea 0, el DB lee todos los bloques iguales o por debajo de la HWM hasta el final — tardando aproximadamente lo mismo que antes del borrado.

TRUNCATE TABLE table; es el comando que resetea la HWM al inicio de la tabla, reduciendo instantáneamente el objetivo del full scan a 0 bloques. Esa es la razón real por la que se describe TRUNCATE como "instantáneo incluso en tablas enormes".

¿DELETE no encogió los datos?

Me he encontrado en producción con una situación en la que ejecuté DELETE para reducir el tamaño de los datos, pero el uso de almacenamiento apenas bajó.

La causa fue exactamente esta: la HWM no baja. DELETE solo elimina filas lógicamente, mientras que los bloques (páginas) que la tabla había reservado se quedan ahí. El archivo en disco sigue ocupando esa zona, así que desde la perspectiva del SO el tamaño de los datos no ha cambiado.

Cuando realmente quieres reducir el almacenamiento, ejecuta un comando de reorganización específico del DB (VACUUM FULL en PostgreSQL, OPTIMIZE TABLE en MySQL InnoDB, ALTER TABLE ... SHRINK SPACE en Oracle), o usa TRUNCATE para un borrado total.

Elegir entre DELETE y TRUNCATE

En la práctica, la elección no es simplemente "TRUNCATE para todo, DELETE cuando necesites una condición". Los factores reales son si necesitas hacer rollback, si los triggers tienen que dispararse y cuánto importa la velocidad de procesamiento. La tabla comparativa de abajo es la lista de comprobación estándar.

Trampas de TRUNCATE

TRUNCATE es rápido y cómodo, pero suele ser una operación irreversible. Cuidado con:

- Algunos DB hacen commit al ejecutar (Oracle / MySQL). En producción, ejecuta primero SELECT COUNT(*) para confirmar el conteo de filas antes de dispararlo

- `AUTO_INCREMENT` se resetea (depende del DB). Si los IDs están expuestos a sistemas externos o URLs, cuidado con las colisiones tras el reset

- Las restricciones de clave foránea pueden bloquearlo (la mayoría de DB). Si subscription referencia a customer, tendrás que vaciar primero la tabla hija para hacer TRUNCATE de customer

- Los triggers generalmente no se disparan. Un trigger es el mecanismo del DB que ejecuta SQL automáticamente en respuesta a inserciones / actualizaciones / eliminaciones de filas — por ejemplo, "escribe una entrada de log de auditoría en otra tabla cada vez que se elimina una fila". DELETE dispara este trigger fila por fila, pero TRUNCATE no opera a nivel de fila, así que los logs de auditoría y registros similares no se crearán

Recurre a TRUNCATE cuando necesites máxima velocidad en un borrado masivo de datos enormes; en cualquier otro caso DELETE WHERE o un DELETE con rollback es la opción más segura.