Aprende a usar Autovacuum

Autovacuum elimina versiones de filas obsoletas para su reutilización y mantiene las estadísticas que necesita el planificador. Los valores globales, sin embargo, pueden disparar el mantenimiento demasiado tarde en tablas grandes. Esta guía permite detectar esas tablas, calcular un umbral específico, aplicar el cambio con seguridad y revertirlo si aumenta la latencia o el I/O.

El procedimiento está orientado a PostgreSQL 16–18 autogestionado. Los ejemplos utilizan la base appdb y la tabla ficticia public.orders.

Requisitos

Necesitas un rol propietario de la tabla o un superusuario, acceso a psql, métricas de aplicación e I/O y una ventana con carga representativa. n_live_tup, n_dead_tup y reltuples son estimaciones; n_dead_tup no mide directamente el bloat. Para observar todas las sesiones puede ser necesario pg_read_all_stats.

Cómo se calcula el umbral

En PostgreSQL 16 y 17:

umbral = autovacuum_vacuum_threshold
       + autovacuum_vacuum_scale_factor × reltuples

PostgreSQL 18 aplica además autovacuum_vacuum_max_threshold. El umbral efectivo es el menor entre el máximo y la fórmula anterior; si el máximo vale -1, no se aplica ese límite. Documentación de PostgreSQL 17 y diferencias en PostgreSQL 18.

Para una tabla estimada en 10 millones de filas, los valores 50 y 0.2 producen aproximadamente 2.000.050 tuplas obsoletas. Un override de ejemplo con 1000 y 0.02 reduce el disparador a 201.000.

Medir antes de modificar

Comprueba server_version, autovacuum, track_counts, stats_reset y los parámetros de autovacuum. Después localiza candidatos:

SELECT s.schemaname, s.relname,
       pg_size_pretty(pg_total_relation_size(s.relid)) AS total_size,
       c.reltuples::bigint AS estimated_rows,
       s.n_live_tup, s.n_dead_tup,
       round(100.0*s.n_dead_tup/NULLIF(s.n_live_tup+s.n_dead_tup,0),2) AS dead_pct,
       s.n_mod_since_analyze, s.n_ins_since_vacuum,
       s.last_autovacuum, s.autovacuum_count,
       s.last_autoanalyze, s.autoanalyze_count, c.reloptions
FROM pg_stat_user_tables s
JOIN pg_class c ON c.oid=s.relid
ORDER BY s.n_dead_tup DESC NULLS LAST
LIMIT 30;

Prioriza tablas que combinen deuda creciente, tamaño significativo, escrituras frecuentes y ciclos de autovacuum demasiado espaciados. Antes de ajustar, distingue entre disparo tardío, falta de capacidad de los workers y retención por transacciones o slots antiguos. Revisa pg_stat_activity, pg_prepared_xacts y pg_replication_slots; no finalices sesiones ni elimines slots automáticamente.

Calcular y aplicar el ajuste

El factor aproximado puede obtenerse mediante:

scale_factor = (objetivo_de_tuplas - threshold) / reltuples

Los valores siguientes son un punto de partida calculado, no una recomendación universal:

BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE public.orders SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold = 1000,
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_analyze_threshold = 500
);
COMMIT;

Si vence lock_timeout, la transacción se revierte. Reprograma el cambio en lugar de ampliar el tiempo de espera sin analizar el tráfico. No es necesario reiniciar PostgreSQL.

Guardar un rollback exacto

Antes del cambio, genera un archivo que elimine los cuatro overrides y restaure las reloptions anteriores:

ROLLBACK_SQL="$HOME/autovacuum-orders-rollback.sql"
sudo -u postgres psql -XAt -v ON_ERROR_STOP=1 -d appdb > "$ROLLBACK_SQL" <<'SQL'
SELECT '\set ON_ERROR_STOP on'; SELECT 'BEGIN;';
SELECT 'SET LOCAL lock_timeout = ''3s'';';
SELECT 'ALTER TABLE public.orders RESET (
 autovacuum_vacuum_scale_factor, autovacuum_vacuum_threshold,
 autovacuum_analyze_scale_factor, autovacuum_analyze_threshold);';
SELECT format('ALTER TABLE public.orders SET (%s);',array_to_string(reloptions,', '))
FROM pg_class WHERE oid='public.orders'::regclass AND reloptions IS NOT NULL;
SELECT 'COMMIT;';
SQL
chmod 600 "$ROLLBACK_SQL"

Para revertir, utiliza entrada estándar. De este modo, el shell abre el archivo privado y entrega su contenido a psql:

sudo -u postgres psql -X -v ON_ERROR_STOP=1 -d appdb \
  < "$HOME/autovacuum-orders-rollback.sql"

Verificar el resultado

Comprueba las opciones con SELECT reloptions FROM pg_class WHERE oid='public.orders'::regclass;. Durante un vacuum consulta pg_stat_progress_vacuum y pg_stat_progress_analyze. Después de una ventana comparable, revisa n_dead_tup, last_autovacuum, autovacuum_count, latencia, CPU e I/O.

No esperes necesariamente n_dead_tup = 0. Mantén el ajuste si la deuda queda controlada sin degradar la aplicación. Revierte si aparecen picos de I/O o latencia inaceptables.