Postgres Advisory Locks for Distributed Cron: Killing Duplicate Job Runs Without a Redis Dependency

The problem: your cron job runs twice (or five times)

You have a scheduled job -- cleaning up expired sessions, sending a digest email, reconciling a billing ledger. It runs on a schedule inside your application process, not as an external cron entry, because that's simpler to deploy and version alongside your code. Then you scale out to three replicas for availability, and now the job fires three times at 2:00 AM, and your "send one digest per user" job sends three.

This is one of the most common bugs in systems that grow past a single instance, and it's usually discovered in production, from a support ticket, not a test. The fix people reach for first is almost always the wrong one for the problem's actual size.

The usual fixes, and why they're often overkill

"Just designate one instance as the leader." Works until that instance restarts during a deploy and two replicas briefly both think they're the leader. You now need leader election, which is its own distributed systems problem.

Redis SET NX locks. This is the standard advice, and it's fine -- if you already run Redis. If you don't, you've just added a stateful dependency, a client library, retry/backoff logic for Redis being unavailable, and a new thing that pages someone at 3 AM, purely to solve "don't run this twice." That's a lot of new surface area for a problem your existing database can solve in about ten lines of code.

A dedicated "jobs" table with a status column. Also fine, but you're now hand-rolling a lock primitive with UPDATE ... WHERE status = 'pending' and hoping you got the isolation level right. You usually haven't, and the failure mode (two workers both see pending before either commits) is subtle enough that it survives code review.

If your jobs are triggered from application code that already talks to Postgres, Postgres already has a locking primitive built for exactly this: advisory locks. No new dependency, no schema changes, released automatically on disconnect.

Advisory locks in one paragraph

Postgres advisory locks are application-defined locks keyed by an integer (or two integers) that the database tracks per-session or per-transaction, independent of any table or row. They don't lock data -- they're a mutex that happens to live in Postgres. Two functions matter here: pg_try_advisory_lock(key), which acquires the lock and returns immediately with true/false rather than blocking, and pg_advisory_unlock(key), which releases it. There's also a transaction-scoped variant, pg_try_advisory_xact_lock(key), which releases automatically at COMMIT or ROLLBACK -- no explicit unlock needed, which matters once you introduce connection pooling (more on that below).

Wiring it into a job runner

Turn your job's name into a stable lock key with hashtext(), so you don't have to maintain a registry of magic integers:

SELECT pg_try_advisory_lock(hashtext('send_daily_digest'));

In Python, wrapped around the actual job body:

import psycopg2

def run_with_lock(conn, job_name: str, job_fn) -> bool:
    """Run job_fn only if no other worker currently holds this job's lock.
    Returns True if the job ran, False if another worker already had it."""
    with conn.cursor() as cur:
        cur.execute("SELECT pg_try_advisory_lock(hashtext(%s))", (job_name,))
        acquired = cur.fetchone()[0]
        if not acquired:
            return False
    try:
        job_fn()
        return True
    finally:
        with conn.cursor() as cur:
            cur.execute("SELECT pg_advisory_unlock(hashtext(%s))", (job_name,))

Every replica's scheduler calls run_with_lock(conn, "send_daily_digest", send_digest) at 2:00 AM. Whichever one gets there first acquires the lock; the others get False back immediately (no blocking, no timeout tuning) and skip the run. If the winning process crashes mid-job, Postgres releases the lock the moment that session's connection closes -- you don't need a heartbeat or a TTL, which is the part Redis-based locks usually get wrong on the first attempt (a hardcoded expiry that's either too short, causing double-runs, or too long, causing a stuck lock).

The PgBouncer trap

If you're running behind PgBouncer in transaction pooling mode -- common at any scale -- session-level advisory locks silently stop working correctly. PgBouncer can hand your logical "session" a different physical connection between statements, so the pg_advisory_unlock call in your finally block might run on a different backend than the one that acquired the lock, and it just does nothing. You won't get an error. You'll get a lock that never releases until that particular backend connection happens to close.

The fix is to use the transaction-scoped variant instead, and hold the entire job inside one transaction:

def run_with_xact_lock(conn, job_name: str, job_fn) -> bool:
    with conn:
        with conn.cursor() as cur:
            cur.execute("SELECT pg_try_advisory_xact_lock(hashtext(%s))", (job_name,))
            if not cur.fetchone()[0]:
                return False
            job_fn()
    return True

Here with conn: opens a transaction and commits (or rolls back on exception) at the end of the block, which is also when the lock releases -- no separate unlock call to get wrong, and no cross-connection mismatch under PgBouncer.

Proving it actually works: a concurrency test

The bug this solves only shows up under concurrency, so a test that runs the job once isn't proof of anything. Spin up several real connections and threads, and assert only one gets through:

import threading

def test_only_one_worker_runs_job(pg_conn_factory):
    ran = []
    def worker():
        conn = pg_conn_factory()
        run_with_xact_lock(conn, "test_job", lambda: ran.append(1))

    threads = [threading.Thread(target=worker) for _ in range(5)]
    for t in threads:
        t.start()
    for t in threads:
        t.join()

    assert len(ran) == 1

Run this with five threads hitting real connections (not mocks -- mocking the lock call defeats the entire point of the test) and you have direct evidence of mutual exclusion, not just an assumption about how the SQL behaves.

Watching locks in production

pg_locks exposes every advisory lock currently held, which is worth a quick alerting query if a job ever seems to silently stop running:

SELECT pid, mode, granted, objid
FROM pg_locks
WHERE locktype = 'advisory';

A lock held with granted = true for far longer than the job should ever take is your signal that something crashed without going through the finally/transaction-close path -- worth a health check if these jobs are business-critical.

When this isn't the right tool

Advisory locks solve "don't run this job twice across replicas that share one Postgres instance." They don't help if your jobs need to coordinate across multiple databases, if you need locks that outlive a single deploy's Postgres connection pool, or if you need visibility into which jobs are queued versus running -- at that point you actually do want a real job queue (Sidekiq, Celery, Postgres-backed queues like pgqueuer or river) with its own state table. But for the extremely common case of "we have three replicas and this cron job must only fire once," reaching for a primitive your database already has beats installing a new one.