Comandos básicos de monitorización en PostgreSQL — pg_stat_activity y pg_stat_user_tables

Monitorizar una base de datos sin pg_stat_activity y pg_stat_user_tables es como vigilar la salud solo mirando la fiebre. No puedes pillar dónde está el problema si solo ves el panorama general. Estos dos comandos clave de PostgreSQL te ayudan no solo a observar, sino a analizar activamente lo que pasa en tu base.

¿Qué es pg_stat_activity?

pg_stat_activity es una vista del sistema en PostgreSQL que muestra info sobre todas las conexiones a tu base de datos. Responde preguntas como: ¿quién está conectado?, ¿qué consultas se están ejecutando ahora mismo? y ¿qué conexiones están "colgadas" en estado inactivo? Es tu herramienta para analizar la actividad actual en el servidor.

Vamos a ver los campos principales que tienes en pg_stat_activity. El campo datname tiene el nombre de la base a la que se conecta el cliente, y usename muestra el usuario que hizo la conexión. application_name indica el nombre de la app que usa la conexión, client_addr contiene la IP del cliente conectado al server. backend_start muestra cuándo se conectó el cliente, state refleja el estado actual de la conexión (active, idle, idle in transaction), y query contiene la consulta que se está ejecutando o la última que se ejecutó.

Ejemplo 1: ver todas las conexiones activas

Para ver las conexiones activas, ejecuta esta consulta:

SELECT datname, usename, client_addr, state, query
FROM pg_stat_activity
WHERE state = 'active';

Fíjate en el campo query. Muestra las consultas que se están ejecutando ahora mismo. Si una consulta tarda demasiado, igual hay algo raro ahí.

Ejemplo 2: Analizar el estado de las transacciones

A veces las conexiones se quedan "atascadas" en estado idle in transaction. Eso significa que se empezó una transacción pero no se terminó, y eso puede causar bloqueos.

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

¿Cómo lo arreglas? Si pillas una transacción "colgada", puedes terminarla con este comando:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction';

Algunos devs se flipan con esto. Mejor pregunta primero a tu equipo si puedes "matar" el proceso. Ups, perdón, quería decir — terminar la conexión.

Monitorizar el uso de tablas: la vista pg_stat_user_tables

Si pg_stat_activity te deja vigilar las conexiones, pg_stat_user_tables te cuenta sobre el rendimiento de las tablas. Con ella puedes saber: con qué frecuencia se leen o escriben datos en las tablas, cuáles se usan más, y dónde puede haber problemas de rendimiento.

Aquí tienes los campos principales de pg_stat_user_tables para analizar tablas. relname es el nombre de la tabla, seq_scan muestra cuántos escaneos secuenciales se han hecho, idx_scan — cuántos escaneos usando índice. n_tup_ins es el número de filas insertadas, n_tup_upd — filas actualizadas, y n_tup_del — filas borradas.

Ejemplo 1: comparar uso de índices y escaneos secuenciales

Si el índice se usa muy poco (idx_scan cerca de cero), seguramente puedes optimizar las consultas a esa tabla.

SELECT relname, seq_scan, idx_scan
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;

Ejemplo de resultado:

Si ves que la tabla orders tiene un montón de escaneos secuenciales (seq_scan), piensa en añadir un índice. Imagina la tabla orders con 3500 escaneos secuenciales y solo 100 por índice, mientras que la tabla employees tiene 50 secuenciales y 1000 por índice — eso es una señal clara de que hay que optimizar.

Ejemplo 2: analizar el número de operaciones en tablas

Para ver cuán "vivos" están los datos en las tablas, pide info sobre filas insertadas, actualizadas y borradas:

SELECT relname, n_tup_ins, n_tup_upd, n_tup_del
FROM pg_stat_user_tables
ORDER BY n_tup_ins DESC;

¿Qué puedes aprender? Las tablas con muchas inserciones (n_tup_ins) y borrados (n_tup_del) pueden ser puntos calientes en tu base. Eso significa que su rendimiento merece atención especial.

Uso práctico de los comandos para analizar el rendimiento: combinando datos de pg_stat_activity y pg_stat_user_tables

Cuando analizas el rendimiento de la base, puedes juntar datos de ambas vistas. Primero localiza las consultas lentas con pg_stat_activity, luego mira qué tablas usan esas consultas con pg_stat_user_tables. Si las consultas lentas van sobre tablas con seq_scan alto, prueba a optimizar la consulta o añade un índice.

Ejemplo de consulta:

WITH active_queries AS (
    SELECT pid, query
    FROM pg_stat_activity
    WHERE state = 'active' AND query <> '<IDLE>'
)
SELECT a.pid, a.query, t.relname, t.seq_scan, t.idx_scan
FROM active_queries a
JOIN pg_stat_user_tables t ON a.query LIKE '%' || t.relname || '%';