You have two worker processes that must not run at the same time. The naive fix is a LOCK TABLE on some sentinel table, but that blocks reads. The elegant fix is a row in a jobs table with a locked_at column — but now you have a state machine you have to remember to clean up, and a process crash leaves the lock row dangling.
Advisory locks are Postgres's answer to this. They're integer-keyed locks, tracked in memory by Postgres, that have no relation to any row or table. You acquire them, you release them, the database owns the lock lifecycle. If your session terminates — crash, network drop, anything — Postgres cleans them up automatically.
The API
There are two variants: session-level and transaction-level.
Session-level locks persist until you explicitly release them or your connection closes:
-- Acquire (blocks until available)
SELECT pg_advisory_lock(12345);
-- Try to acquire (returns immediately)
SELECT pg_try_advisory_lock(12345); -- true or false
-- Release
SELECT pg_advisory_unlock(12345);
Transaction-level locks are released automatically when the transaction ends:
BEGIN;
SELECT pg_advisory_xact_lock(12345);
-- lock held until COMMIT or ROLLBACK
COMMIT;
The key argument is a 64-bit integer. In practice you derive it from a string via hashing:
SELECT pg_advisory_lock(hashtext('my-cron-job-name'));
Or pass two 32-bit integers if you want a natural namespace:
SELECT pg_advisory_lock(42, 7); -- namespace 42, resource 7
The canonical use case: distributed cron
You have 3 instances of your application. All three have a background thread that fires a cron job at the same time. Without coordination, all three run. With an advisory lock:
acquired = db.execute("SELECT pg_try_advisory_lock(hashtext('hourly-digest'))")
if not acquired.scalar():
return # another instance has it, skip
try:
run_hourly_digest()
finally:
db.execute("SELECT pg_advisory_unlock(hashtext('hourly-digest'))")
Exactly one instance runs, the others skip immediately. If the winner crashes mid-job, the lock is released when its connection closes and the next cron tick will succeed.
This is what queue tables and distributed lock services solve, but an advisory lock does it in three lines using infrastructure you already have.
The row-level variant
Postgres 9.4+ added pg_advisory_lock(oid, int) and a corresponding FOR UPDATE SKIP LOCKED clause. For queue workers the row lock is usually better — it ties the lock to the actual work item. But advisory locks remain the right tool when the "resource" you're coordinating has no corresponding row: startup sequences, scheduled jobs, migration scripts, external API rate limit windows.
What to watch for
Session-level locks don't nest cleanly. Calling pg_advisory_lock(x) twice requires two pg_advisory_unlock(x) calls to release. Use pg_advisory_xact_lock if you can — the automatic release is safer.
Lock ID collisions are real. hashtext('job-a') and hashtext('job-b') will never collide in practice (32-bit hash space over a small key set), but documenting what each integer means somewhere is worth doing. A centralized constants file beats scattering magic numbers across services.
They don't survive connection pool recycling. If your connection pool closes and reopens connections, session-level advisory locks on the old connection are gone. This is usually fine — you wanted the lock tied to the process, not the physical connection — but if you're using connection poolers like PgBouncer in transaction mode, session-level locks won't behave as expected. Use transaction-level in that context.
You can see current holders via pg_locks:
SELECT pid, locktype, classid, objid, granted
FROM pg_locks
WHERE locktype = 'advisory';
The classid and objid columns hold the two 32-bit halves of your lock integer. If a worker appears stuck, this is where you look first.
What advisory locks are not
They're not a job queue — they have no persistence, no retry semantics, no priority. They don't replace a dedicated scheduler for anything that needs reliability guarantees across restarts. They're a coordination primitive, not a workflow engine.
For the class of problem they do solve — "exactly one process should be doing this right now" — they're the simplest correct answer in a Postgres-backed application.
---
Find more writing at anethoth.com. If you're building an indie SaaS, list it on builds.anethoth.com.