Tareas de mantenimiento en PostgreSQL

En algunas ocasiones omitimos las tareas de mantenimiento en PostgreSQL por causas de tiempo, ya que tenemos que tener el sistema preparado lo antes posible: crear las estructuras para los datos, implementar alta disponibilidad, etc. Es por ello que en esta entrada os voy a hablar sobre las operaciones de mantenimiento que se deben de realizar para mantener un sistema limpio y funcionando a pleno rendimiento. También puedes ver el curso de optimización de consultas en PostgreSQL, dónde se ejecutan los comandos que veremos a continuación.

Antes de empezar

Antes de empezar tienes que comprender el concepto «filas muertas» y «filas vivas». Hay que tener en cuenta que el MVCC cuando se ejecuta un UPDATE o un DELETE, marca la fila como borrada. Esto produce internamente «filas muertas«, que son aquellas filas cuyos valores han sido eliminados o los valores anteriores a una actualización y «filas vivas«, que son los nuevos valores insertados o los nuevos valores de una fila actualizada.

Comandos requeridos

En primer lugar necesitamos conocer los comandos necesarios para ejecutar las tareas de mantenimiento.

Comando ANALYZE

Con este comando se analizan cada una de las tablas o la base de datos para informar al planificador de consultas del estado de las mismas, de esta forma obtenemos mejor rendimiento cuando se ejecutan las consultas en la PostgreSQL.

Comando VACUUM

Este comando se utiliza para realizar limpieza en cada tabla o en la base de datos, así evitamos que el sistema se sobrecargue de filas muertas o que las tablas ocupen demasiado espacio físico en el disco duro. Esto podría hacer que el sistema se vea mermado en su rendimiento con el paso del tiempo.

Comando REINDEX

Este comando es similar al anterior, con la diferencia que actúa sobre los índices. Reconstruye los índices eliminando aquellas páginas que no contienen filas. De esta forma se disminuye el tamaño.

Tareas de mantenimiento

Ya conocemos los comando a utilizar, es hora de ver el procedimiento de las tareas de mantenimiento que se van a ejecutar.

Es posible ejecutar el comando ANALYZE junto al comando VACUUM, de hecho es una buena práctica. De esta forma limpiamos cada una de las tablas de manera que aquellas filas muertas producidas por los UPDATES y DELETES, sean reutilizadas para nuevos INSERT y además, se actualiza la información obtenida de las tablas.

Para las tablas que no se realizan nuevas escrituras, es conveniente ejecutar el comando VACUUM FULL. De esta forma se recupera el espacio en el disco duro a nivel físico ocupado por aquellas filas muertas.

Lo siguiente a realizar es el comando REINDEX para reconstruir aquellos índices que están hinchados (bloated), es decir, contiene muchas páginas vacías con filas muertas. También hay que ejecutar este comando si actualizas a la versión 13 de PostgreSQL, pues se mejora los índices B-tree.

Consideraciones a tener en cuenta al realizar las tareas de mantenimiento en PostgreSQL

Antes que nada, es muy recomendable aumentar el valor del parámetro maintenace_work_men para reducir el tiempo de ejecución de tales tareas. Podemos hacer uso del comando SET para modificar el parámetro en la sesión. Recordad que una vez se terminen de ejecutar todos los comandos hay que volver a poner el valor predeterminado del parámetro.

El comando VACUUM ANALYZE es relativamente rápido y no bloquea las tablas que está limpiando. Por lo contrario el comando VACUUM FULL, es mucho más lento y además, bloquea la tabla que está reconstruyendo para recuperar el espacio ocupado.

Para recuperar el espacio ocupado por las tablas se requiere de espacio extra en el disco duro, pues realmente clona en un nuevo fichero aquellas páginas que están llenas y elimina el fichero antiguo que contiene las páginas vacías. En la versión 13 de PostgreSQL se puede mejorar el comando VACUUM ejecutándose en varios procesos de forma paralela.

Conclusión

Para realizar las tareas de mantenimiento, podemos crear un script dónde en la primera línea se aumente el valor del parámetro maintenace_work_men y en la última línea se le asigne el valor por defecto. En las líneas intermedias, ejecutar el comando VACUUM ANALYZE por cada una de las tablas existentes en la base de datos y ejecutar el comando VACUUM FULL sólo en aquellas tablas dónde no se realizan INSERT. Por último ejecutar el comando REINDEX por cada tabla que contenga índices.