Introducción a pg_stat_statements: instalación y configuración de la extensión

La extensión pg_stat_statements en PostgreSQL es una herramienta para recolectar estadísticas de consultas. Te deja ver qué consultas se ejecutan más a menudo, cuáles tardan más y cómo se usan los recursos de la base de datos. En vez de analizar cada consulta a mano con EXPLAIN, puedes tener una visión general del rendimiento de tu base de datos.

Ventajas de usar pg_stat_statements:

Monitorización en tiempo real: puedes ver qué consultas están cargando la base de datos ahora mismo.

Análisis del rendimiento de todo el sistema: la info está disponible para todas las consultas de la base de datos, no solo para las que decides analizar manualmente.

Búsqueda de consultas lentas: es fácil ver qué consultas tardan más.

Detección de consultas repetitivas: te ayuda a optimizar el caché y añadir índices para las consultas más populares.

Instalación y configuración de pg_stat_statements

Ahora que ya sabes para qué sirve pg_stat_statements, vamos a ver cómo instalarlo y configurarlo paso a paso.

1. Comprobar si PostgreSQL está listo. Asegúrate de que tu PostgreSQL soporta la extensión pg_stat_statements. Esta extensión viene incluida de serie desde PostgreSQL 9.2. Para comprobar si está disponible, ejecuta:

SELECT extname FROM pg_extension;

Si pg_stat_statements no aparece en la lista, puede que tu admin no la haya instalado.

Así debería verse la extensión instalada y activada:

2. Añadir la extensión.

Para activar pg_stat_statements hay que añadirla a la lista de librerías precargadas de PostgreSQL. Esto se hace en el archivo de configuración postgresql.conf.

Pasos:

1. Busca el archivo postgresql.conf. Normalmente está en el directorio de datos de PostgreSQL.
2. Ábrelo para editarlo.
3. Añade o cambia la línea:

   shared_preload_libraries = 'pg_stat_statements'

¿Por qué hace falta esto? Porque pg_stat_statements necesita precargarse, ya que monitoriza las consultas a nivel de sistema.

4. Guarda los cambios y reinicia el servidor de PostgreSQL para activar los cambios. Aquí tienes el comando para Linux:

   sudo systemctl restart postgresql
   

Si estás desarrollando o probando en local, con reiniciar el servidor basta.

3. Crear la extensión en la base de datos. Después de reiniciar el servidor PostgreSQL, puedes crear la extensión pg_stat_statements en la base de datos que quieras. Conéctate a la base con psql u otra herramienta y ejecuta:

CREATE EXTENSION pg_stat_statements;

Si todo va bien, el comando termina sin errores. Ahora pg_stat_statements está activo para tu base de datos.

4. Configuración de los parámetros de pg_stat_statements.

Después de instalar la extensión, es útil ajustar sus parámetros para que recoja bien las estadísticas. Los parámetros principales se ponen en el archivo postgresql.conf.

Parámetros principales

• pg_stat_statements.track
• Define qué consultas se van a monitorizar.
• Valores:
  • all — monitoriza todas las consultas (recomendado para debug y análisis).
  • top — solo monitoriza las consultas de nivel superior.
  • none — desactiva la monitorización.
• Ejemplo de configuración:

pg_stat_statements.track = 'all'

• pg_stat_statements.max

  • Indica el número máximo de consultas que se guardarán en las estadísticas.
  • Por defecto: 5000.
  • Si en tu sistema hay muchas consultas, sube este valor, por ejemplo:

    pg_stat_statements.max = 10000
    

• pg_stat_statements.save

  • Define si se guardan las estadísticas entre reinicios del servidor.
  • Valores: on o off.
  • Recomendamos dejarlo en on:

    pg_stat_statements.save = on
    

Después de cambiar los parámetros, reinicia otra vez el servidor PostgreSQL.

Comprobar que pg_stat_statements funciona

Ahora que la extensión está instalada y configurada, vamos a ver si funciona. Para ver las estadísticas de las consultas, ejecuta esta consulta:

SELECT
    queryid,        -- Identificador único de la consulta
    query,          -- Texto de la consulta
    calls,          -- Número de veces que se ha ejecutado la consulta
    total_time,     -- Tiempo total de ejecución (en milisegundos)
    rows            -- Número de filas devueltas por la consulta
FROM pg_stat_statements
ORDER BY total_time DESC;

¿Qué significan las columnas?

• queryid: identificador único de la consulta, útil para encontrar consultas iguales con diferentes parámetros.
• query: el texto de la consulta SQL ejecutada.
• calls: cuántas veces se ha ejecutado la consulta.
• total_time: tiempo total (la suma de todos los tiempos de ejecución de la consulta).
• rows: número total de filas devueltas por la consulta.

Por ejemplo, si ves que una consulta tiene calls = 100 y total_time = 50000 (50 segundos) y ocupa la mayor parte del tiempo de ejecución, es una señal clara de que hay que optimizarla.

Escenarios típicos de uso de pg_stat_statements

1. Buscar las consultas más lentas. Para encontrar las consultas que más tiempo consumen, ordena los resultados por total_time:

SELECT query, total_time, calls
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 5;

2. Detectar las consultas más activas. Para ver las consultas que más se ejecutan, ordena por calls:

SELECT query, calls, total_time
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 5;

3. Análisis del uso de índices. Si ves muchas consultas lentas, revisa el uso de índices. Por ejemplo, en consultas con filtros (WHERE), la falta de un índice suele ser la causa de bajo rendimiento.

Limpiar los datos de pg_stat_statements

A veces puede que necesites resetear las estadísticas acumuladas para empezar el análisis desde cero. Lo puedes hacer con este comando:

SELECT pg_stat_statements_reset();

Después de resetear, todas las estadísticas se borran y la recogida de datos empieza de nuevo.

Consejos prácticos

Limita la cantidad de estadísticas recogidas: si trabajas en un sistema con mucha carga y millones de consultas, deja pg_stat_statements.max en un valor razonable para no sobrecargar el sistema.

Limpia las estadísticas regularmente: es útil hacerlo antes de empezar un análisis de rendimiento, así no mezclas datos viejos y nuevos.

Presta atención a las consultas lentas: aunque se ejecuten pocas veces, una consulta lenta puede cargar mucho tu base de datos.

Ahora ya sabes cómo instalar, configurar y usar la extensión pg_stat_statements para analizar el rendimiento de las consultas. En la próxima lección vamos a profundizar en cómo encontrar consultas lentas con esta herramienta y optimizarlas.