La configuración por defecto de autovacuum es adecuada sobre todo para bases de datos de tamaño reducido y orientadas principalmente a la lectura. En bases de datos de mayor tamaño, la limpieza pudiera retrasarse durante largos periodos de tiempo (afectando al rendimiento a causa del sobredimensionamiento) y luego realizarse de una sola vez. Además, puesto que los límites de regulación estándar son demasiado bajos para el hardware actual, es posible que la limpieza no alcance el ritmo de las bases de datos con mucha carga de trabajo.

Esta Base de Conocimientos interna contiene un artículo complementario para el cliente que simplifica el tema evitando entrar en detalles. Le recomendamos que lo lean primero.

Hay mucho que aprender sobre vacuum y MVCC. Si les resulta abrumador, lean primero el resumen que aparece al final de la página. Lo más importante que deben tomar en cuenta, es que no se debería deshabilitar autovacuum; de hecho, normalmente debería incrementarse, no reducirse, si se considera que está utilizando demasiados recursos.

Tuplas muertas

Cuando PostgreSQL realiza un DELETE, la fila no se elimina de forma inmediata del archivo de datos. Sólo se marca como eliminada estableciendo un campo en una cabecera. Lo mismo ocurre con UPDATE, que puede ser considerado como el DELETE de la fila antigua + el INSERT de la fila nueva. Esta es una de las ideas detrás del "Multi-Version Concurrency Control" (MVCC), ya que permite una mayor concurrencia y un bloqueo mínimo entre transacciones. La desventaja de la implementación peculiar de MVCC en PostgreSQL, es que mantiene las filas eliminadas, incluso tras la finalización de todas las transacciones que puedan necesitarlas.

Si no se eliminan, esas "tuplas muertas" (efectivamente invisibles para cualquier transacción) permanecerán en los archivos de datos para siempre, consumiendo espacio en el disco, y para las tablas con muchos comandos DELETE y UPDATE, las filas muertas podrían ocupar fácilmente una gran parte del espacio en el disco. Esas tuplas muertas también serían referenciadas desde los índices, lo cual aumentaría aún más la cantidad de espacio desperdiciado en el disco. Esto es lo que llamamos "sobredimensionamiento" (bloat), que no sólo desperdicia espacio en el disco, sino que también afecta al rendimiento: cuantos más datos tengan que procesar las consultas (aunque el 99% de ellos se desechen inmediatamente como "muertos"), más lentas serán las consultas.

ID de transacción y wrap-around (reciclaje del ID de transacción)

PostgreSQL utiliza un contador de transacciones de 32 bits. El ID de la transacción que se crea y (posteriormente) se borra, se registra junto con las tuplas en el disco para permitir a PostgreSQL omitir las tuplas que una determinada transacción no debería "ver". Muchas instalaciones realizan suficientes transacciones como para que el contador alcance su límite de 2 mil millones de ID de transacciones cada pocas semanas o meses; otras tardan años. Cuando el contador alcanza su límite, se reinicia desde el principio, reutilizando los IDs de transacción antiguos.

Si las tuplas con IDs de transacción antiguos siguen existiendo en el disco en el momento de reutilizar sus IDs, PostgreSQL ya no puede filtrarlas correctamente para que sean visibles. Así que debe vaciarlas si se borran, o "congelarlas" como tuplas permanentes. Si no lo hace, se pueden producir resultados de consulta incorrectos. PostgreSQL detecta este problema y advierte al usuario a través de los registros, forzando la ejecución de vacuum, y eventualmente dejando de aceptar comandos para evitar la pérdida de datos.

VACUUM

La forma más directa de recuperar el espacio ocupado por las tuplas muertas (y hacerlo disponible para nuevas filas) es ejecutando el comando VACUUM de forma manual. Este comando de mantenimiento escaneará la tabla y eliminará las tuplas muertas tanto de la tabla (como de los índices). Por lo general, no liberará el espacio de disco para el sistema operativo, pero lo hará utilizable para nuevas filas en la misma tabla.

Nota: VACUUM FULL recupera el espacio y lo devuelve al sistema operativo, aunque presenta una serie de graves desventajas. En primer lugar, adquiere un bloqueo exclusivo sobre la tabla, lo cual bloqueará todas las operaciones (incluidas las consultas SELECT). En segundo lugar, crea en esencia una copia de la tabla que contiene todas las filas no muertas, duplicando de forma casi temporal el espacio necesario en el disco: una solución poco práctica cuando ya se está agotando ese espacio.

El problema con VACUUM es que es una acción completamente manual - sólo ocurre cuando deciden ejecutarlo, no cuando es necesario. Es posible ponerlo en cron y ejecutarlo cada 5 minutos en todas las tablas, pero lo más probable es que la mayoría de las ejecuciones no limpien absolutamente nada, y el único resultado consistirá en un mayor uso de la CPU y de la E/S en el sistema y en una mayor dificultad para conseguir bloqueos para los cambios de esquema. También es posible ejecutarlo solamente una vez al día, por la noche, en cuyo caso probablemente se acumularán más tuplas muertas de las que se esperaría.

autovacuum

La base de datos sabe, aproximadamente, cuántas tuplas muertas se han producido a lo largo del tiempo, ya que cada transacción reporta el número de tuplas que ha borrado y actualizado. Esto significa que la base de datos puede activar la limpieza cuando la tabla acumula un determinado número de tuplas muertas (por defecto es el 20% de la tabla). Por lo tanto, la limpieza se ejecutará más a menudo durante los periodos de actividad, y con menos frecuencia cuando la base de datos esté más inactiva (o cuando realice principalmente consultas de sólo lectura).

Este es el propósito principal del daemon autovacuum; realizar la limpieza según sea necesario, para mantener la cantidad de espacio desperdiciado bajo control.

Se puede configurar para que se ejecute con distintos niveles e intensidades, tanto a nivel de sistema como por tabla, en función de la carga de trabajo.

Monitoreo

Antes de realizar cualquier tipo de ajuste, es necesario recopilar datos relevantes que permitan evaluar el impacto de los cambios en la configuración. Es preciso contar con algún tipo de monitoreo básico, que recopile las métricas básicas de la base de datos. Para la limpieza, se deberían analizar por lo menos estos valores:

• pg_stat_all_tables.n_dead_tup – número de filas muertas en cada tabla (tanto en las tablas de usuarios como en los catálogos del sistema)
• n_dead_tup / (n_dead_tup + n_live_tup) – fracción de filas muertas en cada tabla
• (pg_class.relpages * 8192 / pg_class.reltuples) – espacio "por fila"

Si ya diaponen de un sistema de monitoreo (y así debería ser), lo más probable es que ya estén recolectando estas métricas. De no ser así, este es un buen momento para empezar, ya que se necesita algo que nos alerte en caso de emergencias de espacio en el disco y otros problemas graves que pueden provocar la caída de la base de datos. También es necesario supervisar el reciclaje del ID de transacción.

Nota: Existe también una práctica extensión pgstattuple, que permite realizar análisis de tablas e índices, incluyendo el cálculo de la cantidad de espacio libre, tuplas muertas, etc.

Objetivos de los ajustes

Al ajustar los parámetros de autovacuum, queremos lograr dos objetivos principales:

• Limpiar las tuplas muertas en el momento oportuno - Mantener el número de filas muertas razonablemente bajo, para no desperdiciar una cantidad excesiva de espacio en el disco, evitar el sobredimensionamiento del índice y mantener la rapidez en las consultas.
• Minimizar el impacto en la base de datos - No realizar la limpieza con demasiada frecuencia, y no consumir demasiados recursos (CPU, E/S y RAM) lo cual podría afectar negativamente al rendimiento de las consultas de los usuarios.

Estos dos objetivos son algo contradictorios: mientras que realizar la limpieza "de forma inmediata" haría que las consultas fueran más rápidas (al no tener que procesar ninguna fila extra), también supondría la ejecución del proceso de forma continuada (lo cual resultaría muy costoso). Por lo tanto, el ajuste ideal de autovacuum consiste en encontrar el equilibrio adecuado en función del sistema y de la carga de trabajo. Para ello, es posible que se necesiten varias pruebas hasta alcanzar los valores adecuados.

La mayoría de los valores por defecto en postgresql.conf son bastante conservadores, por dos razones. En primer lugar, los valores por defecto se decidieron hace unos años, basándose en los recursos (CPU, RAM, ...) comunes en aquel entonces. En segundo lugar, la configuración por defecto tiene que ser compatible con cualquier tipo de máquinas, incluyendo las más pequeñas como Raspberry Pi o pequeños servidores VPS. Aun así, para muchas implementaciones (especialmente las más pequeñas y/o que manejan principalmente cargas de trabajo de lectura) los parámetros de configuración por defecto funcionarán bien.

Pero a medida que el tamaño de la base de datos y/o la cantidad de escrituras aumentan, empiezan a aparecer problemas. Un problema típico es que la limpieza no se realiza con suficiente frecuencia, y cuando se realiza, afecta significativamente al rendimiento, ya que tiene que eliminar una gran cantidad de basura. En estos casos se recomienda seguir esta sencilla regla:

Si te duele, no lo estás haciendo con suficiente frecuencia.

Es decir, ajustar los parámetros para que la limpieza se produzca con mayor frecuencia y procese un menor número de tuplas muertas en cada operación.

No desactiven autovacuum: En ocasiones, los usuarios siguen una regla diferente: si te duele, no lo hagas. - y desactivan por completo autovacuum. Por favor, no hagan eso a menos que realmente (realmente) sepan lo que están haciendo, y cuenten con un script apropiado para la limpieza. De lo contrario, se estarían metiendo en un callejón sin salida, y en lugar de tener que lidiar con un menor rendimiento, tendrán que enfrentarse a un rendimiento considerablemente deteriorado o incluso a una interrupción total del servicio.

Autovacuum es importante incluso para las tablas de sólo lectura o de sólo anexión: se encarga de reescribir las tuplas cuando el contador de IDs de transacciones de PostgreSQL se acerca a su punto de reciclaje de 32 bits y necesita reutilizar los IDs de transacciones antiguos para las nuevas transacciones. Si se desactiva por completo autovacuum, PostgreSQL lo volverá a activar y forzará el vaciado de las tablas que se acerquen a este umbral de reciclaje. Esto resultaría muy perjudicial si las tablas son de gran tamaño, y si el proceso se prolonga demasiado o se cancela repetidas veces; podría ocurrir que la base de datos deje de aceptar transacciones por completo hasta que se complete(n) la(s) operación(es) de VACUUM.

Umbrales y factores de escala

Lo primero que hay que configurar es el momento en que se activa la limpieza de una tabla, lo cual depende de dos parámetros: Lo primero que hay que configurar es el momento en que se activa la limpieza de una tabla, lo cual depende de dos parámetros:

autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.2

La limpieza de una tabla se activa cuando el número de tuplas muertas (que puede verse como pg_stat_all_tables.n_dead_tup) excede el siguiente valor

threshold + pg_class.reltuples * scale_factor

Este parámetro indica, básicamente, que hasta el 20% de una tabla puede contener tuplas muertas antes de que se realice la limpieza (el umbral de 50 filas existe para evitar limpiezas muy frecuentes de tablas de tamaño reducido).

El factor de escala por defecto funciona bien para tablas de tamaño pequeño y mediano, pero no es muy eficaz para tablas de gran tamaño. Para una tabla de 10GB, permite aproximadamente 2GB de tuplas muertas, lo que parece una cantidad razonable, ya que se puede reutilizar para nuevos datos tras la limpieza. En una tabla de 1TB el límite es aproximadamente de 200GB, lo cual resulta excesivo. Y una vez que se activa el proceso de limpieza, es posible que tenga que realizar una gran cantidad de trabajo (CPU, E/S) a la vez.

Según la regla "hazlo más a menudo" mencionada anteriormente, la solución correcta es realizar la limpieza con más frecuencia disminuyendo el factor de escala, quizás incluso de la siguiente manera:

autovacuum_vacuum_scale_factor = 0.01

que reduce el límite a sólo el 1% de la tabla. Una solución alternativa es descartar por completo el factor de escala y utilizar en su lugar el umbral:

autovacuum_vacuum_scale_factor = 0
autovacuum_vacuum_threshold = 10000

que debería activar la limpieza después de generar 10000 tuplas muertas. Sin embargo, en la mayoría de los casos es recomendable reducir el factor de escala, ya que funciona tanto para las tablas de gran tamaño como para las pequeñas.

Un posible inconveniente, es que estos cambios en postgresql.conf afectan a todas las tablas (de hecho a todo el cluster), y pueden afectar negativamente a las limpiezas de tablas más pequeñas, incluyendo por ejemplo los catálogos de sistema. La solución más fácil consiste en ignorar el problema por completo. La limpieza de las tablas pequeñas resultará muy económica, y la mejora en las tablas de mayor tamaño suele ser tan significativa que, incluso ignorando esa pequeña ineficacia en las tablas de tamaño reducido, el resultado global seguirá siendo muy positivo. Sólo supondría un problema si se tuviera un gran número de tablas pequeñas, como en un sistema de esquemas compartidos.

Si se decide cambiar la configuración de forma que retrase significativamente la limpieza en las tablas pequeñas (como por ejemplo con la configuración de factor_escala=0 y umbral=10000), podría resultar conveniente aplicar esos cambios sólo a tablas específicas utilizando ALTER TABLE:

ALTER TABLE t SET (autovacuum_vacuum_scale_factor = 0);
ALTER TABLE t SET (autovacuum_vacuum_threshold = 10000);

Procuren mantener la configuración lo más simple posible, y modifiquen los parámetros para el menor número de tablas posible. También es una buena idea incluir todo esto en su documentación interna, incluyendo las razones para utilizar determinados valores.

Throttling (Limitación)

Una excelente característica incorporada en autovacuum es el throttling. La limpieza está pensada para ser una tarea de mantenimiento que se ejecuta en segundo plano, con un impacto mínimo en las consultas de los usuarios, etc. En otras palabras, no debería consumir demasiados recursos (CPU y E/S de disco), y este es exactamente el propósito del throttling.

El proceso de limpieza es bastante sencillo: lee las páginas ( fragmentos de datos de 8kB) de los archivos de datos, y comprueba si es necesaria una limpieza. Si no hay tuplas muertas, la página simplemente se descarta sin ningún cambio. En caso contrario, se limpia (eliminando las tuplas muertas), se marca como "sucia" y se vuelve a escribir en el disco. El throttling se basa en la definición del costo de tres operaciones básicas:

vacuum_cost_page_hit = 1
vacuum_cost_page_miss = 10
vacuum_cost_page_dirty = 20

Es decir, si la página se encuentra en shared_buffers (por lo que no es necesaria una lectura desde el archivo de datos), cuenta como 1. Si no se encuentra en shared_buffers, necesita ser leída desde el sistema operativo y cuenta como 10 (podría seguir siendo servida desde la RAM, aunque eso no lo sabemos). Y finalmente, si la página se ensucia al realizar el proceso de limpieza, cuenta como 20. Esto nos permite calcular el "costo del trabajo" realizado por autovacuum.

Cada vez que el proceso autovacuum realiza cierta cantidad de trabajo, se suspende por un tiempo. Por defecto, la cantidad de trabajo en una iteración se limita a 200 y el tiempo de suspensión es de 20ms:

autovacuum_vacuum_cost_delay = 20ms
autovacuum_vacuum_cost_limit = 200

Este "costo" y "retardo" es un concepto un poco abstracto, así que ¿cuánto trabajo supone en realidad? Con 20ms de retraso, la limpieza puede efectuar 50 rondas por segundo, y considerando 200 tokens por ronda se obtienen aproximadamente 10000 tokens por segundo. Eso significa que autovacuum puede realizar:

• 4 MB/s de escritura (páginas ensuciadas por el proceso de autovacuum)
• 8 MB/s de lectura desde el sistema operativo (posiblemente desde el disco)
• 80 MB/s de lecturas de shared_buffers (asumiendo que no esté sucio)

Teniendo en cuenta las capacidades del hardware actual, y el hecho de que las lecturas/escrituras son principalmente secuenciales, esos límites son bastante bajos. De hecho, las bases de datos con mucha actividad pueden generar tuplas muertas a un ritmo más elevado, lo cual significa que autovacuum puede ser incapaz de mantener el ritmo.

Recomendamos aumentar el parámetro cost_limit para que refleje mejor las capacidades del hardware empleado. Por ejemplo, un incremento a 1000 tokens aumentará el rendimiento en 5 veces. Por supuesto, se pueden ajustar los demás parámetros (costo por operación de página, retardo en la suspensión), aunque sólo lo haríamos en raras ocasiones - modificar el límite de costo resulta suficiente.

Otra medida de throttling es que, a diferencia de un VACUUM explícito, un proceso de autovacuum se anula por sí mismo al detectar intentos de bloqueo conflictivos. Esto facilita las operaciones de mantenimiento del sistema, aunque también puede suponer un problema si la aplicación realiza habitualmente tareas que anulan la operación de vacuum. Habiliten el registro de autovacuum y revisen sus registros y las estadísticas de autovacuum para determinar si esto se ha convertido en un problema. Normalmente se hace evidente por el incremento del sobredimensionamiento.

Número de Workers (Trabajadores)

Una opción de configuración que aún no ha sido mencionada es autovacuum_max_workers. ¿De qué se trata? Bueno, la limpieza no se realiza en un único proceso de autovacuum, sino que se permite a la base de datos iniciar varios procesos de autovacuum_max_workers que se encargarán de la limpieza de diferentes bases de datos/tablas. Esto resulta muy útil, ya que, por ejemplo, no queremos detener la limpieza de las tablas pequeñas hasta terminar la limpieza de una tabla de gran tamaño (lo cual puede tardar bastante tiempo, debido al throttling).

El problema es que los usuarios suelen suponer que el número de workers sea proporcional a la cantidad de limpieza que se puede realizar. Si se incrementa el número de workers a 6, seguramente se hará el doble de trabajo en comparación con los 3 workers por defecto, ¿no es cierto?

Lamentablemente, no es así. El límite de costo es global, compartido por todos los workers de autovacuum. Cada proceso worker sólo recibe aproximadamente 1/autovacuum_max_workers del límite de costo total, por lo que aumentar el número de workers simplemente hará que sean más lentos.

Por lo tanto, si la limpieza de una base de datos no logra mantener el ritmo de la actividad de los usuarios, aumentar el número de workers probablemente no sea la solución adecuada, a menos que también se modifiquen los demás parámetros.

Throttling por tabla

Al igual que con el factor de escala y el umbral, es posible establecer el límite de costo y el retardo por tabla:

ALTER TABLE t SET (autovacuum_vacuum_cost_limit = 1000);
ALTER TABLE t SET (autovacuum_vacuum_cost_delay = 10);

Los workers que procesan estas tablas no se incluyen en el cálculo de costo global, y pueden ser limitados de forma independiente. Esto le proporciona mayor flexibilidad y potencia, aunque también complica significativamente la configuración global. Además, no garantiza la disponibilidad de un worker, ya que podría estar ocupado procesando otras tablas.

En la práctica, casi nunca utilizamos esta función, por dos razones básicas. En primer lugar, normalmente se quiere utilizar un único límite global para la limpieza en segundo plano. En segundo lugar, disponer de varios workers que a veces son limitados de forma conjunta y otras veces de forma independiente complica considerablemente el monitoreo y el análisis del comportamiento del sistema.

autoanalyze

Limpiar las tuplas muertas y congelar las tuplas no son las únicas tareas de autovacuum. Se encarga también de actualizar las estadísticas de distribución de datos, utilizadas por el optimizador al planificar las consultas. Aunque es posible recopilarlas manualmente ejecutando ANALYZE, este método presenta problemas similares a los de VACUUM: puede ejecutarse con demasiada o insuficiente frecuencia. Sin embargo, puesto que la base de datos sabe cuántas filas de la tabla han sido modificadas, ejecuta ANALYZE de forma automática. Esto es precisamente lo que hace autoanalyze.

**Nota: El costo que supone la ejecución manual de ANALYZE es superior al de VACUUM, ya que mientras el costo de VACUUM es, en su mayoría, proporcional a la cantidad de tuplas muertas (y por ende, relativamente bajo cuando son pocas o ninguna), ANALYZE necesita realizar un muestreo de la tabla y reconstruir las estadísticas desde cero en cada ejecución. Así que el costo que supone ejecutarlo con demasiada frecuencia es considerable. Por otro lado, si no se ejecuta con suficiente frecuencia, puede resultar en una mala elección de planes, lo cual también es negativo. En la actualidad, PostgreSQL no cuenta con ninguna función que permita actualizar las estadísticas de forma incremental.

La configuración de autoanalyze es muy similar a la de limpieza - sigue más o menos el mismo razonamiento y utiliza prácticamente la misma fórmula (umbral + factor de escala). El factor de escala se establece por defecto en un 10%, lo que significa que ANALYZE ocurrirá después de aproximadamente un 10% de cambios en la tabla. En la mayoría de los casos esto es suficiente para una buena planificación, y puesto que la recopilación de estadísticas es un proceso bastante costoso, el costo de realizarlo innecesariamente y con demasiada frecuencia es elevado.

En resumen

• No desactiven autovacuum, a menos que realmente sepan lo que están haciendo. Aunque hacerlo pueda resultar temporalmente útil, lo más probable es que, posteriormente, provoque graves problemas o interrupciones del servicio.
• En bases de datos con mucha actividad (que realizan un gran número de UPDATE y DELETE), especialmente las de gran tamaño, probablemente deberá reducirse el factor de escala, para que la limpieza se realice con mayor frecuencia.
• Con un hardware adecuado (buen almacenamiento, múltiples núcleos), probablemente se deberían incrementar los parámetros de throttling, para que la limpieza mantenga el ritmo.
• Incrementar únicamente autovacuum_max_workers, en la mayoría de los casos, no servirá. Se obtendrán un mayor número de procesos que irán más lentos.
• Aunque es posible establecer los parámetros por tabla utilizando ALTER TABLE, consideren si realmente lo necesitan, puesto que el sistema se vuelve más complejo y difícil de inspeccionar.
• No modifiquen la configuración de autoanalyze, los valores por defecto suelen ser adecuados.
• Es necesario utilizar autovacuum incluso en las tablas de sólo anexión o de sólo lectura

Más información

The PostgreSQL manual on vacuuming.