Guide to auditing and optimizing cron jobs that cause locks and deadlocks in PostgreSQL

By Kevin Marcondes · Published on Sep 29, 2026 · Updated on Oct 10, 2026 · 10 min read

A step-by-step guide to audit and optimize cron jobs that create contention, locks and deadlocks in PostgreSQL. Learn how to adjust logging, use pg_stat_activity/pg_locks, apply timeouts and batching, and leverage SKIP LOCKED. Includes ready queries, quick fixes and redesign patterns to reduce impact without full rewrites.

When cron becomes a bottleneck: symptoms and impact

Cron jobs that hit hot tables can stall queries and create wait queues. You may see sudden slowness, application timeouts and long “idle in transaction” states. The database can seem to “hold its breath” until the cron job finishes.

Logs often show lock wait messages and occasionally deadlock notices. During peaks, business processes can be stuck waiting for an exclusive LOCK. This extends the cron job's impact beyond the job itself.

Another sign is an increase in long-running transactions. A large update that commits late will hold locks for a long time, blocking related reads and writes.

Also watch for delayed autovacuum. If the cron job produces heavy writes and long transactions, autovacuum can fall behind, worsening performance and contention in a vicious cycle.

Misuse of advisory locks can also cause trouble. A job that acquires an overly broad advisory lock may block unrelated tasks. Review the scope and keys used to avoid cross-blocking.

Enable and read the right logs before changing code

Without visibility, adjustments become guesses. Start by validating parameters that reveal lock waits and slow queries. Do this in a controlled window or a test environment when possible.

Check current values with simple commands, for example: SHOW deadlock_timeout; and SHOW log_lock_waits;. In psql jobs, set per-session timeouts with SET statement_timeout = '30s'; and SET lock_timeout = '2s'; before the main query.

Use application_name to trace each job in the logs. In cron, set PGAPPNAME=your-job in the environment or provide application_name in the connection string. That lets you tie log events to specific processes.

• SHOW log_lock_waits; and SHOW deadlock_timeout; to confirm diagnostics.
• In the job's psql session, run first: SET lock_timeout = '2s'; and SET statement_timeout = '30s';
• Include PGAPPNAME=daily-job when invoking psql or set application_name in the connection.

Real-time inspection with SQL: find who blocks whom

Use internal views to see sessions, locks and progress. Start with pg_stat_activity and pg_locks. They show who is waiting and who holds the resource.

Find sessions waiting for locks. Focus on cron queries and primary blockers. Map relationships using native functions.

Locate long transactions and sessions in “idle in transaction”. They hold locks for too long and prevent autovacuum from progressing.

Check index usage and autovacuum progress. If you see many sequential scans, the cron job might lack supporting indexes. Simple adjustments can reduce lock time.

• Waiting for a lock: SELECT pid, wait_event_type, wait_event, query FROM pg_stat_activity WHERE wait_event_type = 'Lock';
• Who blocks whom: SELECT pid, pg_blocking_pids(pid) AS blockers, query FROM pg_stat_activity WHERE wait_event_type = 'Lock';
• Long transactions: SELECT pid, now()-xact_start AS age, state, query FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY age DESC;
• Idle in transaction: SELECT pid, now()-xact_start AS age, query FROM pg_stat_activity WHERE state = 'idle in transaction';
• Lock details: SELECT pid, locktype, relation::regclass, mode, granted FROM pg_locks WHERE NOT granted OR mode LIKE '%Exclusive%';
• Indexes vs scans: SELECT relname, idx_scan, seq_scan FROM pg_stat_user_tables ORDER BY seq_scan DESC LIMIT 20;
• Autovacuum in progress: SELECT relid::regclass, phase FROM pg_stat_progress_vacuum;
• If you suspect an inefficient query, run EXPLAIN ANALYZE in a safe or low-traffic environment. See also SQL query optimization in PostgreSQL.

Map database sessions back to your cron jobs

If you can't identify the origin, you'll treat symptoms, not causes. Name your jobs. In cron, set PGAPPNAME=job-name when invoking the DB client. This shows up in application_name and in logs.

Example with psql and a 'fail fast' lock approach: set timeouts before the job's main call. Let logs show what was blocked and by whom.

Create a job_runs table to record start, end, pid and status. At job start insert a record and store pg_backend_pid() and txid_current(). On finish update with success or error. This makes auditing much easier.

• Example cron: PGAPPNAME=recalc-daily psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -c "SET lock_timeout='2s'; SELECT run_recalc();"
• Also set application_name via the connection string: ...?application_name=recalc-daily
• A control table helps measure duration, failures and accidental concurrency between instances.

Quick fixes that reduce locks today

Before a full rewrite, try local contention and performance tactics. Ease the contention window: combine timeouts, scheduling and indexes. When the query runs faster, the lock duration shrinks.

Optimize the query and the plan. Apply Pareto: index filter and join columns. Use CREATE INDEX CONCURRENTLY to avoid blocking writes. See guidance on reducing latency and query optimization.

• Stagger schedules. Avoid running heavy jobs together on hot tables.
• Ensure mutual exclusion with flock: flock -n /var/lock/meujob.lock -c "/path/meujob.sh".
• Use advisory locks: SELECT pg_try_advisory_lock(key); and give up if not acquired.
• Set SET lock_timeout = '1-3s' and a statement_timeout that matches your SLA.
• Break updates into batches. For example, process 500 to 2,000 rows per transaction.
• Prefer SELECT ... FOR UPDATE SKIP LOCKED for concurrent work queues.
• Use NOWAIT when you want immediate failure instead of waiting for a lock.
• Avoid long transactions; commit frequently and limit the write scope.

Redesign jobs for safety and sustainable scale

Some locks stem from job design, not just queries. Idempotency, reentrancy and predictable batching make execution safe. A common pattern is a work queue with 'claim' using SKIP LOCKED.

Limit the transaction scope. Open it right before the batch and close it when done. Create checkpoints per batch. This reduces lock duration and the risk of deadlocks across different operations.

Avoid DDL during peak hours. If you must create indexes, use CREATE INDEX CONCURRENTLY. For large transformations, plan windows and safe migration strategies such as zero-downtime database migrations.

• Use a tasks table with status, taken_at and worker_id columns. Claim rows with FOR UPDATE SKIP LOCKED and LIMIT.
• Implement retries with exponential backoff and jitter to avoid thundering retries.
• Ensure keys and indexes that support predictable UPSERTs.
• Avoid SERIALIZABLE unless necessary. READ COMMITTED is usually sufficient for batch jobs.
• Consider partitioning and per-partition processing to reduce contention.

Understand MVCC and the types of locks that affect your jobs

PostgreSQL uses MVCC to allow consistent reads without blocking most writes. Still, certain operations require stronger locks and can conflict with concurrent jobs.

Row-level locks appear with UPDATE/DELETE and SELECT FOR UPDATE/SHARE. They coexist with READ COMMITTED reads but conflict between writes on the same row.

Relation-level (table) locks include modes like ACCESS EXCLUSIVE, which block reads and writes. DDL tends to acquire aggressive locks. Avoid running DDL alongside batch jobs.

Deadlocks arise when two transactions acquire locks in different orders. Standardize the access order to tables and indexes within jobs to reduce dependency cycles.

• Prefer READ COMMITTED for batch jobs; use SERIALIZABLE only when correctness requires it.
• Standardize update order: Table A then Table B, always in the same sequence.
• Create indexes that support filters; touching fewer rows means holding fewer locks.
• Review triggers that may perform extra DML and lengthen the transaction.

Audit and optimization checklist for cron on PostgreSQL

Use this checklist as an operational guide. Adapt it to your environment and maintenance windows. Review periodically, since data and load change over time.

• Inventory all cron jobs. Document target table(s), windows and SLOs.
• Assign a unique application_name to each job and job stage.
• Enable log_lock_waits and set an appropriate deadlock_timeout.
• Collect pg_stat_activity and pg_locks samples at peak and quiet times.
• Identify primary blockers with pg_blocking_pids and address them first.
• Set lock_timeout and statement_timeout per job.
• Reduce concurrency via scheduling, flock or advisory locks.
• Optimize critical queries. Index filter and join columns.
• Use CREATE INDEX CONCURRENTLY to avoid write blocks.
• Split writes into small batches and short transactions.
• Avoid DDL in hot windows; plan safe maintenance windows.
• Monitor autovacuum and prevent idle in transaction.
• Implement a work queue with SKIP LOCKED for safe parallel execution.
• Document decisions and outcomes. Tune limits periodically.

Frequently asked questions

What is the difference between a lock and a deadlock in PostgreSQL?

A lock is when one transaction waits for a resource held by another. A deadlock is a dependency cycle where two or more transactions wait on each other with no way forward. PostgreSQL detects deadlocks and aborts one transaction; normal locks can persist a long time if there are no timeouts.

Should I kill sessions to resolve locks?

Only as a last resort and when you understand the context. Prefer reducing concurrency, applying timeouts and optimizing queries. If necessary, use pg_terminate_backend(pid) on the blocking session that cannot finish, but consider rollback cost and dependencies. Always log the action and monitor the impact.

SKIP LOCKED or NOWAIT: when to use each?

NOWAIT fails immediately if a lock can't be acquired—useful when you want to give up fast. SKIP LOCKED skips locked rows and continues with others—ideal for work queues and parallel processing when logic is idempotent.

How to choose the ideal batch size for batch jobs?

It depends on target duration, indexes and I/O. Start small, e.g. 500 to 2,000 rows, and measure lock duration and impact. Adjust until it consistently meets your SLO. Use LIMIT with SKIP LOCKED, commits per batch, and monitor average and p95 execution times.

Does tuning autovacuum help reduce locks?

It helps when there's heavy writing and long transactions. Long transactions impede autovacuum. Monitor pg_stat_progress_vacuum and avoid idle in transaction. Tuning varies by table and workload, but reducing transaction duration improves autovacuum efficiency.

How to avoid cron DDL blocking production?

Avoid DDL during peak hours. For indexes, use CREATE INDEX CONCURRENTLY. Plan changes in stages with controlled windows and test in staging. For large transformations, evaluate stepwise migration strategies to avoid prolonged exclusive locks.

See also

• Pre-deploy checklist and quick rollback plan for PHP applications in production
• Security checklist for CI/CD pipelines with self‑hosted runners and secrets management
• Guide: Centralize logs with Fluentd and Elasticsearch for PHP apps on a VPS
• Guide: Organizing PostgreSQL Migrations and Rollbacks for Small Teams