Menu

VACUUM y autovacuum en PostgreSQL: filas muertas y bloat

VACUUM elimina las versiones muertas de filas que dejan UPDATE y DELETE para que su espacio se pueda reutilizar. Autovacuum lo ejecuta por ti; esta página muestra cuándo, cómo ajustarlo y cuándo VACUUM FULL compensa su bloqueo.

Esta página incluye editores ejecutables: edita, ejecuta y ve el resultado al instante.

VACUUM elimina las versiones muertas de las filas, las copias antiguas que UPDATE y DELETE dejan en una tabla, y marca su espacio como libre para filas nuevas. VACUUM ANALYZE además actualiza las estadísticas que usa el planificador. VACUUM FULL reescribe la tabla entera para devolver espacio al sistema operativo. Autovacuum, activado por defecto, ejecuta VACUUM y ANALYZE por ti en segundo plano.

Después del DELETE, la tabla tiene 700 filas vivas y 300 muertas; después de VACUUM, 0 filas muertas. Las vistas de estadísticas se actualizan con retraso; pg_stat_force_next_flush() (PostgreSQL 15+) hace que se actualicen al momento para esta demostración.

Por qué PostgreSQL deja filas muertas

PostgreSQL conserva las versiones antiguas de las filas para que cada transacción vea una instantánea coherente (MVCC). Un UPDATE escribe una versión nueva de la fila y marca la antigua como caducada; un DELETE solo la marca. La versión antigua se queda hasta que ninguna transacción en curso pueda necesitarla, y entonces VACUUM puede eliminarla. ctid, la ubicación física de una fila, muestra el movimiento:

La fila 1 empieza en (0,1), página 0 posición 1, y después del update vive en (0,3). La posición 1 sigue teniendo la versión antigua hasta que se limpie. Una tabla que se actualiza a menudo produce filas muertas sin parar, así que autovacuum vuelve a ella una y otra vez.

VACUUM frente a VACUUM FULL

Un VACUUM normal deja el archivo con el mismo tamaño: el espacio libre se queda en la tabla para futuros inserts. VACUUM FULL escribe una copia nueva y compacta y elimina el archivo antiguo.

VACUUMVACUUM FULL
Bloqueopermite lecturas y escriturasbloquea todo acceso
Espaciolo reutiliza la misma tablase devuelve al sistema operativo
Disco extra necesarionoespacio para una copia completa de la tabla
Cuándode forma rutinaria (lo hace autovacuum)rara vez, tras borrar la mayor parte de una tabla grande

Las opciones van entre paréntesis:

  • VACUUM (VERBOSE, ANALYZE) items; muestra lo que hizo y actualiza las estadísticas.
  • VACUUM (PARALLEL 4) (PostgreSQL 13+) limpia los índices con workers en paralelo.
  • VACUUM (SKIP_LOCKED) se salta las tablas que no puede bloquear en el momento.

Cómo decide autovacuum cuándo ejecutarse

Autovacuum revisa cada tabla cada autovacuum_naptime. Hace vacuum de la tabla cuando sus filas muertas superan autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * table rows: con los valores por defecto, 50 filas más el 20 por ciento de la tabla. La analiza cuando las filas cambiadas desde el último análisis superan 50 más el 10 por ciento. Desde PostgreSQL 13, los inserts por sí solos también disparan un vacuum. Los valores por defecto en postgresql.conf:

autovacuum = on
autovacuum_naptime = 1min
autovacuum_max_workers = 3
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.2
autovacuum_vacuum_insert_threshold = 1000
autovacuum_vacuum_insert_scale_factor = 0.2
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.1

En una tabla de 100 millones de filas, el 20 por ciento son 20 millones de filas muertas antes de que empiece un vacuum. Baja el factor de escala solo en las tablas grandes y con mucha actividad:

ALTER TABLE events RESET (autovacuum_vacuum_scale_factor); devuelve la tabla a la configuración general del servidor. El capítulo de vacuum rutinario explica el resto de parámetros.

Comprobar qué ha hecho autovacuum

pg_stat_user_tables muestra las filas muertas y el último vacuum manual y automático de cada tabla:

SELECT relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

Si las filas muertas siguen creciendo aunque autovacuum se ejecuta, busca transacciones abiertas antiguas, que le impiden eliminar nada más reciente que su instantánea:

SELECT pid, state, xact_start, query
FROM pg_stat_activity
WHERE xact_start < now() - interval '10 minutes'
ORDER BY xact_start;

Congelación y wraparound de los IDs de transacción

Los IDs de transacción son contadores de 32 bits que dan la vuelta. Para que las filas antiguas sigan visibles, vacuum las congela, marcándolas como más antiguas que cualquier transacción. Cuando el ID sin congelar más antiguo de una tabla supera autovacuum_freeze_max_age (200 millones de transacciones por defecto), autovacuum se ejecuta sobre ella aunque esté desactivado.

Si aun así la congelación se queda muy atrás, PostgreSQL deja de asignar IDs de transacción nuevos, y las escrituras fallan hasta que un vacuum se pone al día. Esta consulta muestra lo cerca que está cada base de datos:

SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2 DESC;

Preguntas frecuentes

¿VACUUM bloquea la tabla?

Un VACUUM normal toma un bloqueo que sigue permitiendo SELECT, INSERT, UPDATE y DELETE; solo choca con cambios de esquema, creación de índices, ANALYZE y otros vacuums. VACUUM FULL toma un bloqueo ACCESS EXCLUSIVE, que bloquea todas las consultas sobre la tabla hasta que termina.

¿Debería desactivar autovacuum?

No. Sin él, las filas muertas se acumulan, las estadísticas se quedan viejas y al final la base de datos tiene que dejar de aceptar escrituras para evitar el wraparound de los IDs de transacción. Ajusta sus umbrales por tabla en su lugar.

¿Por qué mi tabla no se hace más pequeña después de VACUUM?

Un VACUUM normal marca el espacio de las filas muertas como libre para filas nuevas de la misma tabla; solo devuelve espacio al sistema operativo cuando las páginas vacías están al final del archivo. VACUUM FULL reescribe la tabla y devuelve el espacio.

¿Por qué VACUUM no elimina las filas muertas?

Una fila solo se puede eliminar cuando ninguna transacción en curso puede necesitarla todavía. Una transacción abierta desde hace mucho (a menudo una sesión inactiva que ejecutó BEGIN), un slot de replicación antiguo o una transacción preparada abandonada las retienen.

Ilustración de los lenguajes de programación de Coddy

Aprende a programar con Coddy

Comenzar