VACUUM y ANALYZE en SQLite: estadísticas y espacio

Dos tareas de mantenimiento muy distintas

ANALYZE y VACUUM suelen mencionarse juntas, pero resuelven problemas diferentes.

• ANALYZE recopila estadísticas sobre tus datos para que el planificador de consultas tome mejores decisiones. Escribe en una tabla llamada sqlite_stat1 y no toca tus filas reales.
• VACUUM reconstruye el archivo en sí para recuperar páginas no utilizadas y desfragmentar el almacenamiento. No modifica directamente los planes de consulta.

Si tus consultas están eligiendo el índice equivocado, lo que necesitas es ANALYZE. Si el archivo pesa más de lo que debería después de muchos DELETE, lo que necesitas es VACUUM. Confundir ambos comandos hace perder un montón de tiempo en mantenimiento innecesario.

Qué hace realmente ANALYZE en SQLite

El planificador de consultas tiene que adivinar. Cuando ve WHERE status = 'active', necesita estimar cuántas filas coinciden — ¿una?, ¿un millón? — para decidir si conviene usar un índice o recorrer la tabla entera. Sin estadísticas, recurre a heurísticas bastante toscas.

ANALYZE recorre cada índice y guarda información resumida sobre cómo se distribuyen los valores:

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  status TEXT,
  customer_id INTEGER
);

CREATE INDEX idx_orders_status ON orders(status);

INSERT INTO orders(status, customer_id) VALUES
  ('shipped', 1), ('shipped', 2), ('shipped', 3),
  ('shipped', 4), ('pending', 5);

ANALYZE;

SELECT * FROM sqlite_stat1;

La fila de sqlite_stat1 le indica al planificador, a grandes rasgos, cuántas filas tiene el índice y cuántos duplicados suele haber por clave. Así, la próxima vez que ejecutes una consulta tipo WHERE status = 'pending', sabrá que pending es poco frecuente y tirará del índice; en cambio, con WHERE status = 'shipped', puede que le salga más a cuenta hacer un escaneo completo.

También puedes analizar una sola tabla o un índice concreto, en lugar de toda la base de datos:

ANALYZE orders;
ANALYZE idx_orders_status;

Ejecuta ANALYZE después de cargas masivas, tras cambios grandes de esquema, o cuando notes que el planificador elige planes pobres en tablas cuya distribución de datos ha cambiado.

PRAGMA optimize: la opción recomendada hoy en día

Lanzar ANALYZE a ciegas cada vez que se cierra una conexión es un desperdicio: la mayoría de las veces nada ha cambiado lo suficiente como para que valga la pena. Por eso SQLite incluye un envoltorio más inteligente:

PRAGMA optimize;

PRAGMA optimize revisa qué cambió en la base de datos desde el último análisis y ejecuta ANALYZE únicamente en las tablas que lo necesiten. La recomendación oficial es llamarlo en cada conexión de larga duración justo antes de cerrarla, y de forma periódica en aquellas conexiones que permanecen abiertas durante horas.

Es barato cuando no hay cambios y efectivo cuando los hay. Tira primero de optimize; deja el ANALYZE directo para cuando de verdad necesites forzar una actualización.

Qué hace realmente VACUUM en SQLite

Cuando borras filas o eliminas una tabla, SQLite marca esas páginas como libres, pero no reduce el tamaño del archivo. Esas páginas libres se reutilizan en los siguientes INSERT, así que la mayor parte del tiempo no pasa nada. Sin embargo, con el desgaste del día a día se van acumulando dos problemas:

1. Espacio libre que el sistema operativo no ve. Tu archivo .db sigue pesando 2 GB aunque solo tengas 800 MB de datos reales.
2. Fragmentación. Las filas de una misma tabla acaban repartidas por páginas no contiguas, lo que penaliza el rendimiento de los escaneos.

VACUUM resuelve ambos problemas copiando toda la base de datos a un archivo nuevo, bien compactada, y reemplazando el original:

CREATE TABLE logs (id INTEGER PRIMARY KEY, body TEXT);
WITH RECURSIVE n(value) AS (
    SELECT 1 UNION ALL SELECT value + 1 FROM n WHERE value < 1000
)
INSERT INTO logs(body) SELECT 'entrada ' || value FROM n;
DELETE FROM logs WHERE id < 900;

VACUUM;

SELECT count(*) FROM logs;

Después de ejecutar VACUUM, el archivo queda del tamaño que tendría si hubieras insertado desde cero solo las 100 filas que sobreviven. Como efecto colateral, los rowid se conservan y la disposición en disco vuelve a ser contigua.

Antes de lanzarlo, conviene tener en cuenta varias cosas:

• Necesita un bloqueo exclusivo sobre la base de datos durante toda la operación. Ninguna otra conexión puede escribir mientras tanto.
• Requiere aproximadamente el doble del tamaño de la base de datos en espacio libre en disco, porque construye el archivo nuevo junto al antiguo.
• No se puede ejecutar dentro de una transacción y dará error si hay alguna transacción activa.
• En una base de datos de varios GB puede tardar bastante. Planifícalo.

Cuándo ejecutar VACUUM en SQLite

Para la mayoría de aplicaciones: no lo hagas, salvo que haya cambiado algo concreto.

Razones válidas para ejecutar VACUUM:

• Acabas de borrar una tabla grande o eliminar un lote enorme de filas y quieres recuperar espacio en disco.
• La base de datos lleva años acumulando movimiento y las consultas que escanean tablas se notan más lentas que antes.
• Vas a distribuir un archivo de base de datos como parte de una release y quieres que ocupe lo mínimo posible.

Malas razones:

• "Por si acaso". Reescribe el archivo entero cada vez. No tiene nada de seguro hacer eso en un sistema en producción.
• Después de cada lote de borrados. Las páginas liberadas se iban a reutilizar de todos modos.

auto_vacuum e incremental vacuum

Si quieres que SQLite gestione las páginas libres de forma automática, debes configurar auto_vacuum al crear la base de datos: no se puede cambiar después sin hacer un vacuum completo:

PRAGMA auto_vacuum = INCREMENTAL;

Tres modos disponibles:

• NONE (por defecto): las páginas libres permanecen en el archivo y se reutilizan en inserciones posteriores.
• FULL: cada commit que libere páginas también trunca el archivo. Es cómodo, pero cada transacción asume ese coste.
• INCREMENTAL: SQLite lleva el control de las páginas libres, pero solo las libera cuando tú lo indicas:

PRAGMA auto_vacuum = INCREMENTAL;
CREATE TABLE t(x INTEGER);
WITH RECURSIVE n(value) AS (
    SELECT 1 UNION ALL SELECT value + 1 FROM n WHERE value < 1000
)
INSERT INTO t SELECT value FROM n;
DELETE FROM t;

PRAGMA incremental_vacuum(100);
PRAGMA freelist_count;

PRAGMA incremental_vacuum(N) libera hasta N páginas libres y se las devuelve al sistema operativo: es rápido, no mantiene un lock exclusivo demasiado tiempo y lo puedes programar para que corra periódicamente. Es el punto justo para bases de datos con muchas escrituras que necesitan mantenerse compactas sin pagar el coste de un VACUUM completo.

VACUUM INTO: exportar una copia compacta

VACUUM INTO escribe una copia nueva y compacta en otro archivo, sin tocar la base de datos original:

VACUUM INTO 'backup.db';

Esto resulta genuinamente útil:

• Copias de seguridad. El archivo resultante es una instantánea consistente y totalmente compactada: sin páginas a medio escribir y sin .wal del que preocuparse. Mucho mejor que copiar el archivo con cp.
• Reducir el tamaño sin bloquear a los escritores demasiado tiempo. Haces el vacuum sobre un archivo aparte y luego intercambias de forma atómica. Los escritores no quedan bloqueados durante todo el proceso.
• Distribución. Puedes entregar una copia pequeña y desfragmentada de una base de datos de desarrollo.

El archivo de destino no debe existir previamente. Si existe, obtendrás un error.

Receta práctica de mantenimiento

Para una base de datos típica de una aplicación:

-- En cada conexión de larga duración, antes de cerrar:
PRAGMA optimize;

-- Después de una carga masiva o un cambio de esquema:
ANALYZE;

-- Después de borrar muchos datos y querer recuperar espacio en disco:
VACUUM;

-- Para copias de seguridad:
VACUUM INTO '/backups/app-2026-04-23.db';

Si la base de datos recibe muchas escrituras/borrados y está en producción 24/7, configura auto_vacuum = INCREMENTAL al momento de crearla y ejecuta PRAGMA incremental_vacuum(N) de forma periódica — por ejemplo, una vez al día en horarios de poco tráfico.

Cómo diagnosticar "¿por qué pesa tanto mi archivo .sqlite?"

Hay dos pragmas que te cuentan qué está pasando por dentro:

CREATE TABLE t(x INTEGER);
WITH RECURSIVE n(value) AS (
    SELECT 1 UNION ALL SELECT value + 1 FROM n WHERE value < 10000
)
INSERT INTO t SELECT value FROM n;
DELETE FROM t WHERE x > 100;

PRAGMA page_count;
PRAGMA freelist_count;
PRAGMA page_size;

• page_count × page_size = tamaño actual del archivo.
• freelist_count × page_size = bytes desperdiciados en páginas no usadas.

Si freelist_count representa una fracción grande de page_count, un VACUUM (o un incremental_vacuum) va a reducir el tamaño del archivo de forma notable. Si es pequeña, la base de datos ya está empaquetada de forma eficiente y VACUUM no aporta nada.

Errores comunes

• Ejecutar VACUUM dentro de una transacción. No se puede. Haz COMMIT primero.
• Olvidar que VACUUM necesita espacio libre en disco. Una base de datos de 10 GB necesita otros ~10 GB libres para vacuumar.
• Configurar auto_vacuum cuando ya hay datos. No hace nada hasta el próximo VACUUM completo. Si lo quieres, actívalo al crear la base de datos.
• Ejecutar ANALYZE esperando archivos más pequeños. De eso se encarga VACUUM.
• Ejecutar VACUUM esperando mejores planes de consulta. De eso se encarga ANALYZE.

Los dos comandos se complementan; ninguno sustituye al otro.

Siguiente: transacciones

Los comandos de mantenimiento como VACUUM ponen sobre la mesa algo que hemos dado por hecho hasta ahora: el modelo transaccional de SQLite y qué bloquea, y cuándo. El próximo capítulo arranca justo ahí: cómo funcionan las transacciones, qué garantizan realmente BEGIN / COMMIT / ROLLBACK, y cómo usarlas para que un grupo de sentencias se ejecute de forma atómica.