Seguimiento de transacciones activas en tiempo real con pg_stat_activity

pg_stat_activity es básicamente una ventana en tiempo real que te ayuda a entender qué está pasando en tu base de datos justo ahora. En la lección anterior vimos lo básico, ahora vamos a profundizar en cómo sacarle el jugo a esta herramienta tan potente.

Ejemplo de una consulta básica a pg_stat_activity:

SELECT * 
FROM pg_stat_activity;

Esta consulta te muestra todas las conexiones activas y las consultas actuales. ¡Genial! Pero vas a tener un montón de datos y podrías pasarte la vida mirándolos. Por eso es útil filtrar la info más importante.

Campos principales en pg_stat_activity

Echemos un vistazo a los campos clave que te van a servir además de los que ya conoces. query_start muestra la hora en que empezó la consulta, lo cual es clave para detectar operaciones largas. pid tiene el identificador del proceso de la conexión — esto lo necesitas para gestionar (por ejemplo, terminar) la conexión. state_change muestra cuándo se estableció el estado actual de la conexión, lo que es súper útil para analizar estados problemáticos que duran mucho.

Ejemplo de selección de procesos activos:

SELECT pid, usename, state, query, query_start 
FROM pg_stat_activity
WHERE state = 'active';

¿Cómo detectar consultas largas?

Imagina que eres admin de la base de datos y de repente la carga del servidor se dispara. ¿Qué hacer? Primero hay que ver qué consulta está chupando todos los recursos. Usamos pg_stat_activity para encontrar esas consultas “tragonas”.

SELECT pid, usename, query, state, now() - query_start AS duration
FROM pg_stat_activity
WHERE state = 'active'
  AND (now() - query_start) > interval '10 seconds';

Esta consulta te muestra todas las consultas que llevan más de 10 segundos ejecutándose. Ajusta el intervalo según lo que necesites.

Terminar consultas problemáticas

Vamos a ver cómo cargarnos esas consultas que llevan demasiado tiempo y están molestando a la base de datos. Usa la función pg_terminate_backend() para forzar el cierre del proceso.

Ejemplo para terminar un proceso con un PID concreto:

SELECT pg_terminate_backend(12345);

Donde 12345 es el identificador del proceso (campo pid) de pg_stat_activity.

Importante: Terminar un proceso puede provocar un rollback si la transacción no se cierra bien, así que ojo con esto.

Ahora, si quieres terminar automáticamente todos los procesos “colgados”, por ejemplo transacciones idle, puedes ejecutar este bloque PL/pgSQL. Como ya has visto programación, el concepto de ciclo (loop) te suena — es una estructura que repite instrucciones mientras se cumple una condición o hasta que se acaban los datos:

DO $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN 
        SELECT pid 
        FROM pg_stat_activity 
        WHERE state = 'idle in transaction' 
          AND (now() - state_change) > interval '5 minutes'
    LOOP
        PERFORM pg_terminate_backend(r.pid);
    END LOOP;
END $$;

Esta solución dinámica te permite limpiar el sistema de transacciones problemáticas. El ciclo FOR recorre cada registro del resultado y ejecuta la operación de terminar el proceso para cada PID encontrado.

Pronto vamos a empezar con PL/pgSQL, ¡ya queda poco! :P

Filtrar por estado de transacciones

A veces no solo quieres encontrar una consulta activa, sino también ver qué conexiones están en un estado especial, como idle o idle in transaction. Esto te puede ayudar a detectar problemas antes de que se vuelvan críticos.

Ejemplo de consulta para encontrar transacciones en idle in transaction:

SELECT pid, usename, query, state, state_change
FROM pg_stat_activity
WHERE state = 'idle in transaction';

El campo state_change te dice cuándo se puso ese estado. Así puedes encontrar transacciones que llevan mucho tiempo sin hacer nada útil, pero que pueden estar bloqueando recursos de la base de datos.

Aplicación práctica

Monitorización de consultas largas en producción: puedes montar una monitorización regular de consultas que superen cierto tiempo y avisar por Slack, Telegram o cualquier otra herramienta de notificaciones. Así puedes reaccionar rápido ante problemas de rendimiento.

Análisis de consultas durante incidentes: si el servidor empieza a ir lento, lo primero que tienes que mirar es pg_stat_activity para buscar la causa. Esto debería ser tu protocolo estándar para problemas de rendimiento.

Mantenimiento de la base de datos: analizar pg_stat_activity de forma regular te ayuda a detectar consultas ineficientes y optimizarlas (por ejemplo, añadiendo índices o reescribiendo consultas).

Cuando se trata de monitorización, pueden surgir errores por filtrar o interpretar mal los datos. Por ejemplo, si filtras solo por estado active, puedes pasar por alto consultas en estado idle in transaction, que también pueden bloquear recursos. Otro error es terminar procesos demasiado agresivamente, lo que puede causar rollbacks no deseados y pérdida de datos. Analiza siempre el contexto antes de tomar medidas drásticas.

Técnicas extra de monitorización

Para monitorización más avanzada puedes crear consultas complejas que muestren estadísticas por usuario, base de datos o tipo de consulta. Por ejemplo, puedes ver cuánto tiempo de media tarda cada usuario en ejecutar consultas, o encontrar las bases de datos con más conexiones activas.

También es útil configurar el log automático de consultas largas en los archivos de log de PostgreSQL, usando los parámetros de configuración log_min_duration_statement y log_statement. Esto te ayuda a analizar problemas de rendimiento a posteriori y ver patrones en el comportamiento de las apps.