Si tenes una base de datos PostgreSQL y te anda lenta, podes usar unos comandos para mejorar y optimizar el rendimiento: VACUUM, ANALYZE y REINDEX
Para evitar problemas es preferible ejecutar estos comandos durante el periodo de mantenimiento cuando nadie usa la base.
En la configuración por defecto de PostgreSQL, el demonio AUTOVACUUM debe estar habilitado y todos los parámetros de configuración necesarios se configuran según sea necesario. El demonio ejecutará VACUUM y ANALYZE a intervalos regulares. Para confirmar si el daemon de autovacuum se está ejecutando en UNIX o Linux, podes hacer :
$ ps aux | grep autovacuum | grep -v grep
postgres 334 0.0 0.0 2654128 1232 ?? Ss 16Mar17 0:05.63 postgres: autovacuum launcher process
En la base, se puede verificar el estado de autovacuum en pg_settings con la siguiente consulta:
select name, setting from pg_settings where name = 'autovacuum' ;
El comando VACUUM recuperará el espacio que todavía utilizan los datos que se han actualizado.
En PostgreSQL, las tuplas de clave-valor actualizadas no se eliminan de las tablas cuando se cambian las filas, por lo que el comando VACUUM debe ejecutarse ocasionalmente para hacer esto.
VACUUM se puede ejecutar solo o con ANALYZE.
Veamos algunos ejemplos, ojo el nombre de la tabla es opcional, por eso se escribe así [nombre de tabla]. Sin una tabla especificada, VACUUM se ejecutará en las tablas disponibles en el esquema actual al que el usuario tiene acceso.
VACUUM simple: libera espacio para reutilizar
VACUUM [tablename]
VACUUM FULL: bloquea la tabla de la base de datos y reclama más espacio que un VACUUM simple
/* Before Postgres 9.0: */
VACUUM FULL
/* Postgres 9.0+: */
VACUUM(FULL) [tablename]
VACUUM completo y ANALYZE: realiza un VACUUM completo y recopila nuevas estadísticas sobre las rutas de ejecución de consultas utilizando ANALIZAR
/* Before Postgres 9.0: */
VACUUM FULL ANALYZE [tablename]
/* Postgres 9.0+: */
VACUUM(FULL, ANALYZE) [tablename]
Verbose Full VACUUM and ANALYZE: Igual que el anterior, pero con salida de progreso detallado
/* Before Postgres 9.0: */
VACUUM FULL VERBOSE ANALYZE [tablename]
/* Postgres 9.0+: */
VACUUM(FULL, ANALYZE, VERBOSE) [tablename]
ANALYZE recopila estadísticas para el planificador de consultas para crear las rutas de ejecución de consultas más eficientes. Según la documentación de PostgreSQL, las estadísticas precisas ayudarán al planificador a elegir el plan de ejecución más apropiado y, por lo tanto, a mejorar la velocidad del procesamiento de la consulta.
En el siguiente ejemplo, [nombre de tabla] es opcional. Sin una tabla especificada, ANALYZE se ejecutará en las tablas disponibles en el esquema actual al que el usuario tiene acceso.
ANALYZE VERBOSE [tablename]
El comando REINDEX reconstruye uno o más índices, reemplazando la versión anterior del índice. REINDEX se puede usar en muchos escenarios, incluidos los siguientes:
- Un índice se ha dañado y ya no contiene datos válidos. Aunque en teoría esto nunca debería suceder, en la práctica los índices pueden corromperse debido a errores de software o fallas de hardware. REINDEX proporciona un método de recuperación.
- Un índice se ha "hinchado", es decir, contiene muchas páginas vacías o casi vacías. Esto puede ocurrir con índices de árbol B en PostgreSQL bajo ciertos patrones de acceso poco comunes. REINDEX proporciona una forma de reducir el consumo de espacio del índice escribiendo una nueva versión del índice sin las páginas muertas.
- Ha modificado un parámetro de almacenamiento (como el factor de relleno) para un índice y desea asegurarse de que el cambio haya tenido pleno efecto.
- Una construcción de índice con la opción CONCURRENTEMENTE falló, dejando un índice "inválido". Dichos índices son inútiles, pero puede ser conveniente usar REINDEX para reconstruirlos. Tenga en cuenta que REINDEX no realizará una compilación concurrente. Para construir el índice sin interferir con la producción, debe borrar el índice y volver a crearlo con el comando CREATE INDEX CONCURRENTLY.
Veamos unos ejemplos, cualquiera de estos puede forzarse agregando la palabra clave FORCE después del comando:
Recree un solo índice, myindex:
REINDEX INDEX myindex
Recrea todos los índices en una tabla, mytable:
REINDEX TABLE mytable
Recree todos los índices en el esquema público:
REINDEX SCHEMA public
Recree todos los índices en la base de datos postgres:
REINDEX DATABASE postgres
Recree todos los índices en los catálogos del sistema en bases de datos de postgres:
REINDEX SYSTEM postgres
