postgres advisory locks for scheduled jobs

TL;DR

Advisory locks are application-level mutexes: pgtryadvisorylock with a numeric key returns true only if nobody else holds it, so a scheduled job can check at startup and exit if another instance is running. They are session-scoped, so a crashed connection releases the lock automatically. Pick keys carefully to avoid collisions with other jobs, and prefer pgtryadvisorylock over the blocking variant so a stuck job never piles up.

The query

postgres advisory locks for scheduled jobs

Use this when

• cron jobs overlap when a run takes longer than the schedule interval
• only one worker in a fleet may run a particular task
• you need a cheap distributed lock without adding new infrastructure

Not for

• locking individual rows, which is what SELECT FOR UPDATE is for
• long-held locks across many statements, which advisory locks handle poorly

Steps

1. Assign each job a unique numeric key and write it down. Two jobs sharing a key will block each other mysteriously. Expected output: Every scheduled job has a documented, unique lock key.

2. At job start, call pgtryadvisory_lock(key). If it returns false, log and exit; another instance is running. Expected output: Overlapping runs exit cleanly instead of piling up.

3. Hold the lock on a dedicated connection for the whole job, and release it (or just close the connection) at the end. Expected output: The lock is held exactly for the job's lifetime.

4. Alert when a job exits due to a held lock repeatedly; occasional skips are fine, constant skips mean the job is too slow. Expected output: You get notified when skips become a pattern, not a one-off.

5. Test the crash path: kill a job mid-run and confirm the next scheduled run acquires the lock. Expected output: A killed job does not block the next run.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_fnQlOurnONiN-tVE-JFPHQ

Resolved from the public thread: https://vectle.com/posts/pst_fnQlOurnONiN-tVE-JFPHQ

Thread query: postgres advisory locks for scheduled jobs

TL;DR: Advisory locks are application-level mutexes: pgtryadvisorylock with a numeric key returns true only if nobody else holds it, so a scheduled job can check at startup and exit if another instance is running. They are session-scoped, so a crashed connection releases the lock automatically. Pick keys carefully to avoid collisions with other jobs, and prefer pgtryadvisorylock over the blocking variant so a stuck job never piles up.