¿PostgreSQL lento? Cómo ver locks y queries activas

¿Qué pasa cuando PostgreSQL se congela de repente?

Cuando una aplicación sufre caídas repentinas de rendimiento, la base de datos suele ser el primer lugar a investigar. A menudo, la lentitud no es causada por falta de hardware, sino por transacciones que se niegan a terminar. En la práctica, esto significa que una consulta SQL lenta o mal estructurada bloqueó los datos, impidiendo que otras solicitudes legítimas lean o escriban.

Para entender este comportamiento, imagine una fila de banco donde un cliente olvida los documentos en el mostrador y se pone a conversar. Nadie más puede ser atendido. En PostgreSQL, esto ocurre debido a los mecanismos de concurrencia, que garantizan que dos operaciones no modifiquen el mismo registro al mismo tiempo. Cuando estos bloqueos se cruzan sin salida, obtenemos el famoso interbloqueo o deadlock, donde dos procesos se esperan indefinidamente.

Antes de investigar problemas complejos de infraestructura, vale la pena cruzar esta información con métricas detalladas de consultas lentas usando pg_stat_statements, ya que muchos cuellos de botella nacen de consultas que corren sin los índices adecuados en las tablas principales.

Cómo ver lo que se está ejecutando ahora mismo en la base

PostgreSQL mantiene una tabla de sistema llamada pg_stat_activity. Funciona como un panel de control en tiempo real, mostrando exactamente lo que cada conexión está haciendo en ese milisegundo. Cada fila representa un proceso conectado, mostrando el usuario, la base de datos accedida, el comando SQL actual y el estado de la transacción.

Para revisar las consultas activas que tardan más de lo habitual, podemos ejecutar un comando simple filtrando por procesos que no estén ociosos. Esto nos da una visión inmediata de quién consume los recursos del servidor y retiene conexiones valiosas que podrían atender a otros usuarios.

SELECT pid, usename, client_addr, state, query, backend_start ⏎FROM pg_stat_activity ⏎WHERE state <> 'idle' AND pid <> pg_backend_pid();

En este bloque de código, la columna pid indica el identificador único del proceso en el sistema operativo, mientras que la columna query muestra el texto exacto del comando SQL en ejecución. El filtro evita que nuestra propia consulta aparezca en los resultados, manteniendo la lista limpia y enfocada solo en los acaparadores reales de recursos.

Cómo rastrear transacciones bloqueadas por locks

Descubrir qué consulta corre es útil, pero el verdadero desafío surge cuando una consulta está atrapada esperando un bloqueo. PostgreSQL gestiona el acceso simultáneo a través de un mecanismo llamado lock. Cuando un proceso intenta alterar una fila que otro ya modificó, entra en estado de espera, congelando la aplicación en ese punto específico.

Para mapear quién bloquea a quién, necesitamos cruzar información de pg_stat_activity con la tabla pg_locks. Esta vista combinada revela qué proceso tiene el bloqueo y qué procesos esperan pacientemente (o impacientemente) a que el recurso sea liberado.

SELECT blocked_locks.pid     AS blocked_pid, ⏎       blocked_activity.usename  AS blocked_user, ⏎       blocking_locks.pid    AS blocking_pid, ⏎       blocking_activity.usename AS blocking_user, ⏎       blocked_activity.query    AS blocked_statement ⏎FROM pg_catalog.pg_locks          blocked_locks ⏎JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid ⏎JOIN pg_catalog.pg_locks          blocking_locks ⏎  ON blocking_locks.locktype = blocked_locks.locktype ⏎  AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database ⏎  AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation ⏎  AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page ⏎  AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple ⏎  AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid ⏎  AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid ⏎  AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid ⏎  AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid ⏎  AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid ⏎  AND blocking_locks.pid <> blocked_locks.pid ⏎JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid ⏎WHERE NOT blocked_locks.granted;

Esta consulta avanzada cruza múltiples campos de identificación de locks para asegurar que miramos con precisión la dependencia real entre procesos. El resultado lista el proceso bloqueado y el culpable, permitiendo al administrador identificar el origen del problema en segundos sin adivinar a ciegas.

Cómo terminar conexiones problemáticas con seguridad

Tan pronto como se descubre al culpable mediante las consultas anteriores, se requiere acción. En producción, a menudo necesitamos detener una transacción que congeló el sistema. Sin embargo, apagar toda la base de datos por una mala consulta está descartado. La solución correcta es cancelar solo el proceso específico que causa el daño.

PostgreSQL ofrece dos funciones nativas para esto: pg_cancel_backend y pg_terminate_backend. La primera envía una solicitud educada para que el proceso cancele la consulta actual manteniendo la conexión abierta. La segunda es más enérgica, cortando la conexión por completo de inmediato y forzando el retroceso de cualquier transacción pendiente.

-- Para pedir educadamente la cancelación de la consulta actual: ⏎SELECT pg_cancel_backend(12345); ⏎⏎-- Para tumbar la conexión por completo si falla la cancelación: ⏎SELECT pg_terminate_backend(12345);

Reemplace el número 12345 por el PID real obtenido en las consultas anteriores. Usar estas funciones exige privilegios administrativos en la base de datos. Prefiera siempre la cancelación suave, ya que el cierre brusco puede dejar a la aplicación cliente confundida hasta que se reconecte y restaure el estado lógico correcto. Para complementar esta implementación en entornos de producción, consulte también nuestros análisis técnicos sobre ¿Contenedor bloqueado en Kubernetes? Cómo inspeccionar logs con stern y ¿Certificado SSL expirando? Cómo automatizar renovación en Nginx.

Consideraciones finales sobre salud y monitoreo continuo

Identificar locks y consultas activas no debe ser una tarea exclusiva de momentos de crisis. Cuando el sistema presenta lentitud recurrente, el uso frecuente de las tablas de sistema de PostgreSQL convierte el trabajo de ingeniería en una rutina predecible y controlada. Tener listas de comandos de inspección acelera el diagnóstico en entornos críticos.

Además, vale la pena configurar alarmas basadas en el tiempo de ejecución de transacciones y revisar periódicamente el diseño de tablas e índices. Si un problema de concurrencia se repite con frecuencia, el cuello de botella suele apuntar a un diseño de arquitectura que necesita ser refactorizado. Con monitoreo preventivo, la base de datos deja de ser una caja negra misteriosa y se vuelve un componente transparente y estable de la infraestructura.