Using PostgreSQL Advisory Locks for Lightweight Coordination: Leader Election, Rate Limiting & Migration Guards

Why Advisory Locks Are Useful

Advisory locks are PostgreSQL’s built‑in, application‑defined locking mechanism. They are stored in shared memory, require no catalog entries, and are released automatically at the end of a session or transaction. This makes them ideal for coordinating application logic that doesn’t naturally map to rows or tables.

Typical use cases:

• Leader election – pick a single worker to perform a periodic task.
• Rate limiting – share a counter or window across processes.
• Migration guard – prevent concurrent schema changes during a CI/CD pipeline.

Lock Basics: Modes & Scopes

Advisory locks are identified by either a single 64‑bit integer or two 32‑bit integers. PostgreSQL exposes four functions for each scope (session or transaction) and lock mode (exclusive or shared). The table below summarizes them.

Function	Scope	Mode	Behavior
pg_advisory_lock(key)	Session	Exclusive	Blocks until lock is free, then holds until session ends or pg_advisory_unlock is called.
pg_advisory_lock_shared(key)	Session	Shared	Blocks only if an exclusive lock exists; multiple shared locks can coexist.
pg_advisory_xact_lock(key)	Transaction	Exclusive	Automatically released on COMMIT or ROLLBACK.
pg_advisory_xact_lock_shared(key)	Transaction	Shared	Automatically released on transaction end.
pg_try_advisory_lock(key)	Session	Exclusive	Returns TRUE if lock acquired, FALSE otherwise; does not block.
pg_try_advisory_lock_shared(key)	Session	Shared	Non‑blocking shared lock.
pg_try_advisory_xact_lock(key)	Transaction	Exclusive	Non‑blocking transaction‑level lock.
pg_try_advisory_xact_lock_shared(key)	Transaction	Shared	Non‑blocking transaction‑level shared lock.

Two key distinctions:

• Session‑level locks persist across statements and transactions. They must be released explicitly or will be dropped when the connection closes.
• Transaction‑level locks are safer for most application code because they automatically release on commit or rollback, preventing leaked locks if a process crashes or a connection is returned to a pool.

Practical Pattern: Leader Election with pg_try_advisory_lock

Assume you have a pool of workers that should run a scheduled job, but you want only one to act as the leader at any time. Pick a fixed 64‑bit key that is unique to the job.

# Session A
BEGIN;
SELECT pg_try_advisory_lock(0x1234567890ABCDEF);  -- 64‑bit hex key
-- Returns t if lock acquired, f otherwise.
IF FOUND THEN
    -- We are the leader.
    PERFORM run_scheduled_job();
END IF;
COMMIT;

Session B runs the same snippet. If Session A holds the lock, Session B’s call returns FALSE and it does nothing. Because the lock is transaction‑level, it is automatically released when the transaction ends, allowing another worker to become leader on the next poll.

To avoid busy‑waiting, let the worker sleep for a few seconds before retrying. You can also use pg_advisory_lock to block until the lock becomes available, but that ties the worker to the lock’s lifetime.

Checking Who Holds the Lock

Use pg_locks to see active advisory locks:

SELECT pid, mode, granted, classid, objid, objsubid
FROM pg_locks
WHERE locktype = 'advisory' AND classid = 0x1234567890ABCDEF;

The pid column shows the backend that holds the lock. If granted is false, the session is waiting.

Rate Limiting with Shared Locks

Shared locks allow multiple workers to proceed while preventing a single exclusive lock from stepping on them. For example, to enforce that only one process can reload configuration at a time, use:

# Exclusive lock for reload
SELECT pg_advisory_lock(0xFEDCBA0987654321);
-- Perform reload
SELECT pg_advisory_unlock(0xFEDCBA0987654321);

Other workers that need to read the configuration can acquire a shared lock:

SELECT pg_advisory_lock_shared(0xFEDCBA0987654321);
-- Read config safely
SELECT pg_advisory_unlock_shared(0xFEDCBA0987654321);

Because shared locks do not block each other, many readers can coexist. Only the exclusive lock blocks both readers and writers.

Migration Guard: Preventing Concurrent Schema Changes

In a CI/CD pipeline that runs migrations on multiple environments, you can serialize the job by taking an exclusive advisory lock at the start of the pipeline:

# Acquire exclusive lock before running migrations
SELECT pg_advisory_lock(0x0F0F0F0F0F0F0F0F);
-- Run all ALTER TABLE statements
-- Release lock at the end
SELECT pg_advisory_unlock(0x0F0F0F0F0F0F0F0F);

If a previous run is still holding the lock, the new run will block until the lock is released, ensuring migrations run serially.

Key Space Design & Collision Avoidance

Using raw user IDs or small integers as keys can lead to accidental collisions with other subsystems that also use advisory locks. Adopt a namespacing scheme:

• High 32 bits: subsystem identifier (e.g., 1 for leader election, 2 for migration guard).
• Low 32 bits: entity identifier (e.g., job ID, tenant ID).

Example: key = (subsystem_id << 32) | entity_id. This keeps keys distinct across the application.

Operational Caveats

• Session‑level lock leaks – if a connection is returned to a pool without unlocking, the lock persists until the backend is recycled. Prefer transaction‑level locks unless you need cross‑transaction coordination.
• pg_bouncer transaction pooling – session‑level locks are not honored when the connection is reassigned to another client in transaction‑pooling mode. Use transaction‑level locks or session pooling mode.
• Standby replicas – advisory locks are not replicated. A lock taken on the primary will not block the same key on a replica, so don’t use them for read‑replica coordination.
• Non‑replicated across nodes – in a multi‑node cluster, advisory locks only coordinate within a single PostgreSQL instance. For cross‑node consensus, use an external system like etcd or Consul.
• Not visible to row locks – advisory locks do not conflict with SELECT ... FOR UPDATE or LOCK TABLE. They are orthogonal to data locking mechanisms.
• No durability across crash – if the server crashes, all advisory locks are lost. They are not persisted in the WAL.

Actionable Checklist

1. Choose the right scope: pg_advisory_xact_lock* for most application code, session‑level only if you need cross‑transaction holding.
2. Define a clear key space with namespaces to avoid collisions.
3. Use pg_try_advisory_lock* for non‑blocking attempts; implement backoff or retry loops in your application.
4. Monitor pg_locks and pg_stat_activity for long wait events to spot deadlocks or leaked locks.
5. Document the lock key meanings for future maintainers.

Advisory locks give you a lightweight, catalog‑free way to coordinate complex application logic. With careful key design and scope selection, they can replace ad‑hoc tables or external services for many common patterns.