Guía para auditar y optimizar cron jobs que causan bloqueos y deadlocks en PostgreSQL

Por Kevin Marcondes · Publicado el 29/09/2026 · Actualizado el 11/10/2026 · 10 min de lectura

Guía para auditar y optimizar cron jobs que generan bloqueos y deadlocks en PostgreSQL: paso a paso para diagnosticar contención, ajustar logs, usar pg_stat_activity/pg_locks, aplicar timeouts, procesar en lotes y SKIP LOCKED. Incluye queries listas, correcciones rápidas y patrones de rediseño para reducir el impacto sin reescribir todo.

Cuando el cron se vuelve cuello de botella: síntomas e impacto

Los cron jobs que tocan tablas concurridas pueden bloquear consultas y generar colas de espera. Notarás lentitud repentina, timeouts en la aplicación y “idle in transaction” prolongado. La base de datos parece “contener la respiración” hasta que termina el cron.

En los logs aparecen mensajes de lock wait y, a veces, deadlocks. En picos, procesos de negocio quedan bloqueados esperando un LOCK exclusivo. Esto amplifica el impacto del cron más allá del propio job.

Otro indicio es el aumento de transacciones largas. Una actualización masiva con commit tardío mantiene locks durante mucho tiempo. A continuación, cualquier lectura o escritura relacionada queda bloqueada.

Fíjate también en autovacuum atrasado. Si el cron genera muchas escrituras y transacciones largas, el autovacuum se retrasa. Eso empeora el rendimiento y aumenta la contención, creando un círculo vicioso.

También pueden aparecer confusiones con advisory locks. Un job que adquiere un advisory lock demasiado amplio puede bloquear tareas no relacionadas. Revisa el alcance y las claves usadas para evitar bloqueos cruzados.

Activa y revisa los logs adecuados antes de cambiar código

Sin visibilidad, los ajustes se vuelven conjeturas. Comienza validando parámetros que revelan esperas por lock y consultas lentas. Hazlo en una ventana controlada o en un entorno de prueba cuando sea posible.

Verifica los valores actuales con comandos simples. Ejemplo: SHOW deadlock_timeout; y SHOW log_lock_waits;. En jobs psql, define timeouts por sesión con SET statement_timeout = '30s'; y SET lock_timeout = '2s'; antes de la query principal.

Usa application_name para rastrear cada job en el log. En cron, pasa PGAPPNAME=tu-job en el entorno o establece application_name en la cadena de conexión. Así enlazas eventos de log con procesos específicos.

• SHOW log_lock_waits; e SHOW deadlock_timeout; para confirmar diagnósticos.
• En psql del job, ejecuta primero: SET lock_timeout = '2s'; e SET statement_timeout = '30s';
• Incluye PGAPPNAME=job-diario al invocar psql o configura application_name en la conexión.

Inspección en tiempo real con SQL: descubre quién bloquea a quién

Usa las vistas internas para ver sesiones, locks y progreso. Comienza por pg_stat_activity y pg_locks. Muestran quién está esperando y quién sostiene el recurso.

Encuentra sesiones esperando por lock. Enfócate en queries del cron y en los bloqueadores primarios. Mapea la relación usando funciones nativas.

Detecta transacciones largas y sesiones en “idle in transaction”. Mantienen locks durante mucho tiempo e impiden que autovacuum avance.

Revisa el uso de índices y el progreso del autovacuum. Si hay muchas lecturas secuenciales, el cron puede estar sin índices de apoyo. Ajustes simples reducen el tiempo de lock.

• Esperando por lock: SELECT pid, wait_event_type, wait_event, query FROM pg_stat_activity WHERE wait_event_type = 'Lock';
• Quién bloquea a quién: SELECT pid, pg_blocking_pids(pid) AS blockers, query FROM pg_stat_activity WHERE wait_event_type = 'Lock';
• Transacciones largas: SELECT pid, now()-xact_start AS age, state, query FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY age DESC;
• Idle en transacción: SELECT pid, now()-xact_start AS age, query FROM pg_stat_activity WHERE state = 'idle in transaction';
• Detalles de locks: SELECT pid, locktype, relation::regclass, mode, granted FROM pg_locks WHERE NOT granted OR mode LIKE '%Exclusive%';
• Índices vs lecturas: SELECT relname, idx_scan, seq_scan FROM pg_stat_user_tables ORDER BY seq_scan DESC LIMIT 20;
• Autovacuum en progreso: SELECT relid::regclass, phase FROM pg_stat_progress_vacuum;
• Si sospechas de una query ineficiente, ejecuta EXPLAIN ANALYZE en un entorno seguro o de baja carga. Vea también optimización de consultas SQL en PostgreSQL.

Relaciona las sesiones de la base de datos con tus cron jobs

Sin identificar el originador, solucionas síntomas y no la causa. Nombra los jobs. En cron, define PGAPPNAME=job-rutina al invocar el cliente de la base de datos. Esto aparece en application_name y en el log.

Ejemplo con psql y “falla rápida” por lock. Define timeouts antes de la llamada principal del job. Deja que el log muestre qué fue bloqueado y por quién.

Crea una tabla job_runs para registrar inicio, fin, pid y estado. Al inicio del job inserta un registro y guarda pg_backend_pid() y txid_current(). Al finalizar, actualiza con éxito o error. Esto facilita la auditoría.

• Ejemplo cron: PGAPPNAME=recalc-daily psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -c "SET lock_timeout='2s'; SELECT run_recalc();"
• Define application_name también vía la cadena de conexión: ...?application_name=recalc-daily
• Una tabla de control ayuda a medir duración, fallos y concurrencia accidental entre instancias.

Correcciones rápidas que reducen bloqueos hoy

Antes de reescribir, prueba tácticas de contención y de rendimiento locales. Alivia la disputa y disminuye la ventana de lock. Combina timeouts, escalonado e índices. Cuando la query mejora, el lock dura menos.

Optimiza la consulta y el plan. La regla de Pareto ayuda. Indexa columnas de filtro y de join. Usa CREATE INDEX CONCURRENTLY para evitar detener escrituras. También considera reducción de latencia y optimización de queries.

• Escalona horarios. Evita que jobs pesados se ejecuten juntos sobre tablas calientes.
• Garantiza exclusión mutua con flock: flock -n /var/lock/meujob.lock -c "/path/meujob.sh".
• Usa locks consultivos: SELECT pg_try_advisory_lock(chave); y desiste si no se obtiene.
• Define SET lock_timeout = '1-3s' y SET statement_timeout compatible con tu SLA.
• Divide las actualizaciones en lotes. Ejemplo: procesa 500 a 2.000 filas por transacción.
• Prefiere SELECT ... FOR UPDATE SKIP LOCKED para colas de trabajo concurrentes.
• Usa NOWAIT cuando quieras fallar rápido sin esperar por lock.
• Evita transacciones largas; haz commits frecuentes y limita el alcance de los writes.

Rediseña los jobs para seguridad y escala sostenibles

Algunos bloqueos provienen del diseño del job, no solo de la query. Idempotencia, reentrancia y lotes predecibles hacen la ejecución segura. Un patrón eficaz es una cola con “claim” usando SKIP LOCKED.

Delimita la transacción. Ábrela justo antes del lote y ciérrala al concluir. Haz checkpoints por lote. Así reduces la duración del lock y el riesgo de deadlock entre operaciones distintas.

Evita DDL en horas pico. Si necesitas crear índices, usa CREATE INDEX CONCURRENTLY. Para grandes transformaciones, planifica ventanas y estrategias de migración segura, por ejemplo migraciones sin downtime.

• Usa una tabla de tareas con columnas status, taken_at y worker_id. Haz claim con FOR UPDATE SKIP LOCKED y LIMIT.
• Define reintentos con backoff exponencial y jitter para evitar picos simultáneos.
• Asegura claves e índices que soporten UPSERTs predecibles.
• Evita SERIALIZABLE sin necesidad. READ COMMITTED suele ser suficiente para jobs batch.
• Considera particionamiento y procesamiento por partición para reducir contención.

Entiende MVCC y los tipos de lock que afectan a tus jobs

PostgreSQL usa MVCC para permitir lecturas consistentes sin bloquear escrituras en la mayoría de los casos. Aun así, ciertas operaciones requieren locks más fuertes y pueden entrar en conflicto con jobs concurrentes.

Los locks de fila (row-level) aparecen con UPDATE/DELETE y SELECT FOR UPDATE/SHARE. Convivien con lecturas en READ COMMITTED, pero confligen entre writes sobre la misma fila.

Los locks de relación (tabla) incluyen modos como ACCESS EXCLUSIVE, que bloquean lecturas y escrituras. DDL tiende a adquirir locks más agresivos. Evítalos junto con jobs batch.

Los deadlocks nacen cuando dos transacciones adquieren locks en órdenes distintas. Estandariza el orden de acceso a tablas e índices dentro de los jobs para reducir ciclos de dependencia.

• Prefiere READ COMMITTED para batch; SERIALIZABLE solo cuando la corrección lo exija.
• Estandariza el orden de updates: tabla A después de tabla B, siempre en la misma secuencia.
• Crea índices que soporten filtros; menos filas tocadas, menos locks mantenidos.
• Revisa triggers que puedan ejecutar DML adicional y alargar la transacción.

Checklist de auditoría y optimización de cron en PostgreSQL

Usa esta lista como guía operativa. Adáptala a tu entorno y a las ventanas permitidas. Revisa periódicamente, porque los datos y la carga cambian con el tiempo.

• Inventaría todos los cron jobs. Documenta tabla(s), ventanas y SLOs.
• Asigna application_name único a cada job y etapa.
• Activa log_lock_waits y define deadlock_timeout adecuado.
• Recoge muestras de pg_stat_activity y pg_locks en picos y en días tranquilos.
• Identifica blockers primarios con pg_blocking_pids y trátalos primero.
• Define lock_timeout y statement_timeout por job.
• Reduce concurrencia vía escalonado, flock o advisory locks.
• Optimiza consultas críticas. Índices en columnas de filtro y join.
• Usa CREATE INDEX CONCURRENTLY para evitar bloqueos de escritura.
• Divide los writes en lotes pequeños y transacciones cortas.
• Evita DDL en horarios calientes; planifica ventanas seguras.
• Monitorea autovacuum y evita “idle in transaction”.
• Implementa colas con SKIP LOCKED para ejecución paralela segura.
• Documenta decisiones y resultados. Ajusta periódicamente los límites.

Preguntas frecuentes

¿Cuál es la diferencia entre bloqueo y deadlock en PostgreSQL?

Un bloqueo es cuando una transacción espera por un recurso bloqueado por otra. Un deadlock es un ciclo de dependencias en el que dos o más transacciones se esperan entre sí sin salida. PostgreSQL detecta deadlocks y aborta una de ellas. Los bloqueos habituales pueden perdurar si no hay timeout.

¿Debo matar sesiones para resolver bloqueos?

Solo como último recurso y con contexto. Prefiere reducir concurrencia, aplicar timeouts y optimizar queries. Si es necesario, usa pg_terminate_backend(pid) sobre quien bloquea y no puede concluir. Revisa dependencias y el riesgo de un rollback costoso. Registra siempre la acción y monitorea el impacto.

SKIP LOCKED o NOWAIT: ¿cuándo usar cada uno?

NOWAIT falla inmediatamente si no se puede obtener el lock. Es útil cuando quieres desistir rápido. SKIP LOCKED ignora filas bloqueadas y continúa con otras. Es ideal para colas de trabajo y procesamiento en paralelo sin contención, siempre que la lógica sea idempotente.

¿Cómo definir el tamaño ideal del lote en jobs batch?

Depende del objetivo de tiempo, índices y I/O. Comienza pequeño, por ejemplo 500 a 2.000 filas, y mide duración e impacto del lock. Ajusta hasta estabilizar dentro de tu SLO. Usa LIMIT con SKIP LOCKED, commits por lote y monitorea tiempo medio y p95 por ejecución.

¿Ajustar autovacuum ayuda a reducir bloqueos?

Ayuda cuando hay muchas escrituras y transacciones largas. Las transacciones largas entorpecen el autovacuum. Monitorea pg_stat_progress_vacuum y evita 'idle in transaction'. Los ajustes varían por tabla y carga, pero reducir la duración de transacciones ya mejora la eficiencia del autovacuum.

¿Cómo evitar que DDL del cron bloquee producción?

Evita DDL en horas pico. Para índices, usa CREATE INDEX CONCURRENTLY. Planifica cambios en etapas, con ventanas controladas. Prueba el plan en staging. Para transformaciones extensas, evalúa estrategias de migración seguras y por partes, evitando locks exclusivos prolongados.

Ver también

• Guía: checklist de pre‑deploy y plan de rollback rápido para aplicaciones PHP en producción
• Checklist de seguridad para pipelines CI/CD con runners self‑hosted y gestión de secretos
• Guía: centralización de logs con Fluentd y Elasticsearch para aplicaciones PHP en VPS
• Guía: organizar migraciones y rollbacks en PostgreSQL para equipos pequeños