Monitoreo de Consultas Activas: Uso de pg_stat_activity para Ajuste de Rendimiento

Cuando tu base de datos se ralentiza repentinamente, necesitas saber qué está haciendo PostgreSQL en este momento. pg_stat_activity muestra consultas activas, sesiones inactivas, esperas de bloqueo y transacciones abiertas, para que puedas distinguir entre una consulta lenta y una bloqueada.

Úsalo durante incidentes, pero también mantén algunas consultas guardadas para revisiones rutinarias. Los ejemplos a continuación se centran en sistemas PostgreSQL donde tienes permiso para leer la actividad de las sesiones que necesitas inspeccionar.

Entendiendo la Vista pg_stat_activity

pg_stat_activity es una vista dinámica del sistema con una fila por cada proceso del servidor conectado al clúster de base de datos. Esto incluye backends de clientes, trabajadores en segundo plano y sesiones que están inactivas pero aún conectadas.

Monitorear esta vista te permite ver exactamente lo que la base de datos está haciendo ahora mismo, lo que la hace invaluable para depurar caídas repentinas de rendimiento o diagnosticar problemas de contención que son demasiado transitorios para que los archivos de registro típicos los capturen de manera efectiva.

Columnas Clave para el Análisis de Rendimiento

Si bien pg_stat_activity contiene docenas de columnas, las siguientes son esenciales al diagnosticar problemas de rendimiento:

Nombre de Columna	Descripción	Relevancia para el Ajuste
pid	ID del proceso del backend.	Requerido para cancelar o terminar sesiones.
datname	Nombre de la base de datos a la que está conectado este backend.	Ayuda a delimitar el monitoreo en entornos con múltiples bases de datos.
usename	Usuario que inició la conexión.	Identifica actividad específica de una aplicación o usuario.
application_name	Nombre de la aplicación que se conecta (si el cliente lo establece).	Excelente para identificar conexiones de microservicios específicos.
state	Estado actual de la actividad (ej., active, idle, idle in transaction).	Indicador principal de lo que está haciendo el backend.
query	La consulta actual, o la última consulta para sesiones inactivas. La visibilidad puede estar limitada por privilegios y configuraciones.	Identifica la declaración SQL involucrada.
query_start	Marca de tiempo cuando comenzó la ejecución de la consulta actual.	Se utiliza para calcular la duración de la consulta.
wait_event_type y wait_event	Detalles sobre qué está esperando el proceso (ej., adquisición de bloqueo, E/S).	Crítico para diagnosticar contención y bloqueo.

Casos de Uso Prácticos de Monitoreo

El verdadero poder de pg_stat_activity proviene de filtrar los datos para responder preguntas específicas de rendimiento.

Ver Todas las Consultas Activas

Para ver solo los procesos que están ejecutando actualmente una declaración (no inactivos), filtra la vista por la columna state.

-- Ver todas las consultas que se están ejecutando actualmente
SELECT
    pid,
    usename,
    client_addr,
    application_name,
    now() - query_start AS duration,
    query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;

Identificar Consultas de Larga Duración

Identificar consultas que se han estado ejecutando más tiempo del esperado suele ser el primer paso en el ajuste de rendimiento. Estas consultas pueden consumir recursos, causar picos de E/S o mantener bloqueos.

Para identificar consultas que se ejecutan durante más de un umbral específico (ej., 5 segundos), usa la resta de intervalos con now() y query_start.

-- Encontrar consultas que se ejecutan durante más de 5 segundos
SELECT
    pid,
    usename,
    datname,
    state,
    (now() - query_start) AS duration,
    query
FROM pg_stat_activity
WHERE state = 'active'
  AND (now() - query_start) > interval '5 seconds'
ORDER BY duration DESC;

Usa un umbral que se ajuste a tu carga de trabajo. Una consulta de pago de cinco segundos puede ser grave en una aplicación OLTP, mientras que una consulta de informes de cinco minutos puede ser normal si se ejecuta fuera del horario laboral.

Diagnosticar Sesiones Inactivas en Transacción

Una conexión que está idle in transaction ha iniciado una transacción pero no la ha confirmado ni revertido. Está esperando que el cliente envíe el siguiente comando. Estas sesiones pueden mantener bloqueos y mantener visibles versiones antiguas de filas, lo que puede retrasar la limpieza por parte de autovacuum y contribuir a la hinchazón de tablas.

-- Encontrar sesiones que están inactivas pero mantienen una transacción abierta
SELECT
    pid,
    usename,
    client_addr,
    application_name,
    now() - xact_start AS txn_duration,
    query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY txn_duration DESC;

Si encuentras sesiones que mantienen transacciones abiertas durante minutos u horas, verifica la ruta de la aplicación que abrió la transacción. Las causas comunes incluyen la falta de manejo de reversión después de una excepción, una conexión devuelta a un pool antes de la limpieza, o una sesión de administración interactiva dejada abierta.

Analizar Contención de Bloqueos y Bloqueo

Cuando una consulta se cuelga, a menudo está esperando un bloqueo retenido por otro proceso. La vista pg_stat_activity, combinada con pg_locks, es crucial para diagnosticar la contención.

Para encontrar sesiones que están actualmente esperando un recurso (un bloqueo, E/S, etc.), mira la columna wait_event. Si una sesión está bloqueada, su wait_event_type a menudo será Lock.

-- Identificar procesos actualmente bloqueados por un bloqueo
SELECT
    a.pid,
    a.usename,
    a.query_start,
    a.query,
    a.wait_event,
    a.wait_event_type
FROM pg_stat_activity a
WHERE a.state = 'active'
  AND a.wait_event IS NOT NULL
ORDER BY a.query_start;

Para una vista rápida de "quién bloquea a quién", PostgreSQL también proporciona pg_blocking_pids().

-- Mostrar sesiones bloqueadas y las sesiones que las bloquean
SELECT
    blocked.pid AS blocked_pid,
    blocked.usename AS blocked_user,
    blocked.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.usename AS blocking_user,
    blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE blocked.wait_event_type = 'Lock';

Si esto devuelve una solicitud web bloqueada esperando una actualización administrativa larga, cancelar la consulta administrativa puede ser más seguro que terminar la sesión de la aplicación.

Gestionando Sesiones Problemáticas

Una vez que se identifica una consulta o sesión problemática usando su ID de Proceso (pid), PostgreSQL proporciona dos funciones para gestionarla:

Cancelar una Consulta con pg_cancel_backend

Esta función intenta detener de manera elegante la ejecución de una consulta específica. La sesión en sí misma permanece conectada y disponible para consultas futuras.

-- Ejemplo: Cancelar la consulta que se ejecuta en el PID 12345
SELECT pg_cancel_backend(12345);

Terminar una Sesión con pg_terminate_backend

Esta función desconecta forzosamente el proceso backend del servidor. Si la sesión estaba en medio de una transacción, PostgreSQL revertirá automáticamente la transacción.

-- Ejemplo: Terminar forzosamente la sesión con PID 54321
SELECT pg_terminate_backend(54321);

Prueba pg_cancel_backend primero cuando la sesión simplemente está ejecutando una consulta incorrecta. Usa pg_terminate_backend para sesiones que están atascadas, abandonadas o que mantienen una transacción abierta que no se puede limpiar normalmente. Revertir una transacción grande puede llevar tiempo y agregar carga de E/S, así que hazlo deliberadamente.

Mejores Prácticas para el Monitoreo

Filtra Agresivamente

Evita SELECT * FROM pg_stat_activity como tu hábito predeterminado en producción. La salida es ruidosa y el texto de la query puede exponer valores sensibles si tus aplicaciones envían literales en lugar de parámetros de enlace. Selecciona las columnas que necesitas y filtra por state, datname, application_name o duración.

Usa Herramientas para Monitoreo Automatizado

Las verificaciones manuales son útiles durante un incidente, pero las tendencias pertenecen al monitoreo. Rastrea sesiones activas, sesiones en espera, transacciones largas y conteos de idle in transaction en tu panel de control de PostgreSQL.

Configura el Registro de Declaraciones

Combina el monitoreo en tiempo real con datos históricos. Configura parámetros como log_min_duration_statement para registrar consultas que superen un cierto umbral, proporcionando datos para análisis incluso después de que la consulta termine de ejecutarse.

Conclusión

Mantén tres verificaciones guardadas: consultas activas ordenadas por duración, transacciones inactivas ordenadas por antigüedad de la transacción, y sesiones bloqueadas con sus bloqueadores. Cuando PostgreSQL se sienta lento, esas vistas te dirán si debes ajustar SQL, corregir el manejo de transacciones o limpiar una sesión bloqueadora.