Ver conexiones activas en PostgreSQL con pg_stat_activity

¿Cómo ver las sesiones activas y las consultas en ejecución en PostgreSQL? La vista pg_stat_activity muestra las conexiones abiertas, el usuario, la base de datos, el estado y la consulta que ejecuta cada sesión.

Es una consulta útil cuando una aplicación parece lenta, una base de datos tiene demasiadas conexiones o quieres saber qué está haciendo PostgreSQL en ese momento.

Para ver todos los detalles de sesiones de otros usuarios necesitas ser superusuario o tener el rol pg_read_all_stats. Sin ese permiso, varias columnas aparecerán vacías.

Ver conexiones activas

La consulta base puede ser:

SELECT
    pid,
    usename,
    datname,
    client_addr,
    state,
    query_start,
    wait_event_type,
    wait_event,
    query
FROM pg_stat_activity
ORDER BY query_start NULLS LAST;

Campos interesantes:

• pid: identificador del proceso backend.
• usename: usuario conectado.
• datname: base de datos.
• client_addr: IP del cliente.
• state: estado de la sesión.
• query_start: cuándo empezó la consulta.
• wait_event_type y wait_event: si está esperando por algo.
• query: consulta actual o última consulta conocida.

Ver solo consultas en ejecución

Para quedarte con sesiones activas:

SELECT
    pid,
    usename,
    datname,
    client_addr,
    now() - query_start AS duracion,
    wait_event_type,
    wait_event,
    query
FROM pg_stat_activity
WHERE state = 'active'
  AND pid <> pg_backend_pid()
ORDER BY query_start;

La condición pid <> pg_backend_pid() evita que salga la propia consulta que estás ejecutando para revisar la actividad.

Ver sesiones idle in transaction

Las sesiones en idle in transaction pueden mantener bloqueos abiertos más tiempo del necesario:

SELECT
    pid,
    usename,
    datname,
    client_addr,
    now() - xact_start AS duracion_transaccion,
    state,
    query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start;

No siempre son un problema, pero conviene revisarlas si aparecen durante mucho tiempo.

Contar conexiones por base de datos y usuario

Para una vista rápida de consumo de conexiones:

SELECT
    datname,
    usename,
    state,
    count(*) AS conexiones
FROM pg_stat_activity
GROUP BY datname, usename, state
ORDER BY conexiones DESC;

Esto ayuda a detectar aplicaciones que abren demasiadas conexiones o usuarios que concentran actividad.

Resumen rápido

-- Sesiones activas
SELECT pid, usename, datname, state, query
FROM pg_stat_activity
WHERE state = 'active';

-- Sesiones idle in transaction
SELECT pid, usename, datname, now() - xact_start AS duracion, query
FROM pg_stat_activity
WHERE state = 'idle in transaction';

-- Conexiones agrupadas
SELECT datname, usename, state, count(*)
FROM pg_stat_activity
GROUP BY datname, usename, state;

pg_stat_activity es una de las primeras vistas que conviene mirar cuando PostgreSQL parece lento, tiene demasiadas conexiones o necesitas saber qué consultas están vivas.