"PostgreSQL Advisory Locks: Distributed Cron, No Lock Service"
"postgresql advisory locks tutorial — pg_advisory_lock for distributed cron and leader election, session vs transaction lock scope, lock-key design with hashtext, and the PgBouncer gotcha."
You run three app instances, each with a cron that sends the daily digest email. Without coordination, the email goes out three times. The usual answers — Redis Redlock, ZooKeeper, a dedicated lock service — are entire infrastructure for a one-line problem. This postgresql advisory locks tutorial shows the Postgres-native answer: advisory locks, application-defined mutexes that live in the database you already run, with pg_try_advisory_lock() giving you single-flight cron jobs in about ten lines of SQL. Last updated: October 4, 2026 What you'll learn What advisory locks are and why they're "advisory" (Postgres doesn't enforce them) pg_advisory_lock() vs pg_try_advisory_lock() for single-flight distributed cron Session locks vs transaction locks ( pg_advisory_xact_lock ) — the scope gotcha Designing lock keys: the 64-bit space and hashtext() for string names Why PgBouncer transaction pooling silently breaks session-level advisory locks Monitoring lock contention with pg_locks What Advisory Locks Are An advisory lock is a mutex identified by a 64-bit integer (or a pair of 32-bit integers) that PostgreSQL tracks but never enforces against your tables. "Advisory" means the database doesn't stop anyone from touching any row — the lock only works because every participant *agrees* to check it first. It's a coordination primitive, not a data lock. That sounds weak, but it's exactly what distributed cron needs: the thing being protected isn't a row, it's an *action* ("send the digest", "rebuild the report"). There's no table to put a row lock on, so you invent a lock ID — say, hashtext('daily_digest') — and every instance tries to acquire it before running the job. Whoever holds it runs; everyone else skips. The core…