Advisory Locks in PostgreSQL

An advisory lock is a lock on a number. PostgreSQL lets an application invent its own lockable “objects” — identified by an arbitrary 64-bit integer, or by a pair of 32-bit integers — and take exclusive or shared locks on them through the ordinary lock manager, with ordinary blocking and ordinary deadlock detection. They are called advisory “because the system does not enforce their use — it is up to the application to use them correctly” (PostgreSQL 18 §13.3.5). Nothing in the engine consults them; the number 42 means whatever every participating session agrees it means. The payoff, in the documentation’s own words, is that compared with the obvious alternative of a flag column in a table, “advisory locks are faster, avoid table bloat, and are automatically cleaned up by the server at the end of the session.” The cost is that every guarantee is conventional, and the two most common production failures — leaking session-scoped locks behind a transaction pooler, and exhausting the fixed-size shared lock table — both come from taking that convention less seriously than the engine does.

Point-in-time note: every fact here is pinned to PostgreSQL 18.4 (the release the docs/current pages served on 2026-08-06), with source-level claims read from the REL_18_STABLE branch on the GitHub mirror (git.postgresql.org rate-limits under fleet load).

This note assumes no prior exposure. If you want the surrounding machinery, The PostgreSQL Lock Manager and Lock Modes covers the eight table-level lock modes and the shared hash table they live in; PostgreSQL Deadlock Detection covers the wait-for graph; PostgreSQL Row Locks and MultiXact covers the other thing people reach for when they want mutual exclusion.

Mental Model — a lock manager with a user-supplied key

PostgreSQL’s lock manager does not actually know what a “table” is. It knows about a 16-byte struct called a LOCKTAG and a hash table in shared memory keyed by that struct. Locking a table means building a LOCKTAG out of (database OID, relation OID); locking a tuple means building one out of (database OID, relation OID, block number, offset). Advisory locks are simply the case where the application supplies the bytes.

That is not a metaphor — it is literally the implementation. Every other lock type in the engine is acquired through a typed wrapper in src/backend/storage/lmgr/lmgr.c (LockRelation, LockTuple, LockDatabaseObject, …). Advisory locks skip that file entirely: src/backend/utils/adt/lockfuncs.c builds a LOCKTAG by hand and calls the raw LockAcquire() directly (lockfuncs.c, REL_18_STABLE). There is no advisory-lock subsystem. There is a hole punched in the side of the lock manager, exposed as SQL functions.

flowchart TB
    subgraph APP["Application"]
        SQL["SELECT pg_advisory_xact_lock(4242)"]
    end
    subgraph BE["Backend process"]
        LF["lockfuncs.c<br/>SET_LOCKTAG_INT64"]
        LA["LockAcquire(tag, ExclusiveLock,<br/>sessionLock, dontWait)"]
        LL["LOCALLOCK hash<br/>backend-private refcount"]
    end
    subgraph SHM["Shared memory"]
        PART["one of 16 lock partitions<br/>guarded by an LWLock"]
        LOCK["LOCK entry<br/>grantMask / waitMask"]
        PL["PROCLOCK<br/>this backend's holding"]
        WQ["wait queue<br/>ProcSleep"]
    end
    SQL --> LF --> LA --> LL --> PART --> LOCK
    LOCK --> PL
    LOCK -.conflict.-> WQ
    WQ -.deadlock_timeout.-> DD["deadlock detector<br/>wait-for graph"]
    LOCK --> PGL["pg_locks<br/>locktype = 'advisory'"]

How an advisory lock request reaches shared memory. What it shows: the SQL function is a thin shim that manufactures a lock tag and hands it to the same LockAcquire() path used for table locks, so the request lands in the same partitioned shared hash table, joins the same wait queue, and is visible in the same pg_locks view. The insight to take: advisory locks are not a lightweight side-channel — they are first-class entries in the finite shared lock table, which is exactly why exhausting that table is a real failure mode and why deadlock detection works on them for free.

The Key Space — two namespaces that provably cannot collide

The functions come in two arities: one taking a single bigint, one taking two integers. The documentation states flatly that these “can be identified either by a single 64-bit key value or two 32-bit key values (note that these two key spaces do not overlap)” (§9.28.10 Advisory Lock Functions). Why they cannot overlap is only visible in the source:

/*
 *	field1: MyDatabaseId ... ensures locks are local to each database
 *	field2: first of 2 int4 keys, or high-order half of an int8 key
 *	field3: second of 2 int4 keys, or low-order half of an int8 key
 *	field4: 1 if using an int8 key, 2 if using 2 int4 keys
 */
#define SET_LOCKTAG_INT64(tag, key64) \
	SET_LOCKTAG_ADVISORY(tag, MyDatabaseId, \
	                     (uint32) ((key64) >> 32), (uint32) (key64), 1)
#define SET_LOCKTAG_INT32(tag, key1, key2) \
	SET_LOCKTAG_ADVISORY(tag, MyDatabaseId, key1, key2, 2)

Read it line by line. field4 is a discriminator: 1 for the bigint form, 2 for the two-integer form. The bigint is split into its high and low 32-bit halves and stored in field2/field3. So pg_advisory_lock(1) produces the tag (dbid, 0, 1, 1) and pg_advisory_lock(0, 1) produces (dbid, 0, 1, 2). Identical in every field but the last — and since the whole 16-byte struct is the hash key, they are two distinct lockable objects that never conflict. The non-overlap is not a policy; it is a tag field.

The other half of that comment is the fact most people miss: field1 is MyDatabaseId. Advisory locks are scoped to a database, not to the cluster. Two sessions connected to different databases in the same PostgreSQL instance can both hold pg_advisory_lock(1) simultaneously and will never see each other. The pg_locks documentation confirms this from the user side: “Advisory locks are local to each database, so the database column is meaningful for an advisory lock” (pg_locks).

The 16-byte tag itself is worth drawing, because it explains both the 64-bit ceiling and the discriminator trick. The header comment in lock.h says the struct “is defined with malice aforethought to fit into 16 bytes with no padding” (lock.h).

packet-beta
0-31: "locktag_field1 = MyDatabaseId (32 bits)"
32-63: "locktag_field2 = key1, or high half of bigint key"
64-95: "locktag_field3 = key2, or low half of bigint key"
96-111: "field4 (16b) — 1 = bigint, 2 = two int4"
112-119: "type (8b) = LOCKTAG_ADVISORY (10)"
120-127: "method (8b) = USER_LOCKMETHOD (2)"

The 16-byte LOCKTAG as filled in for an advisory lock. What it shows: the exact byte budget — 32 bits of database OID, 64 bits of user key, a 16-bit key-space discriminator, and two 8-bit fields naming the tag type (LOCKTAG_ADVISORY, the 11th member of LockTagType, hence value 10) and the lock method (USER_LOCKMETHOD = 2, as opposed to DEFAULT_LOCKMETHOD = 1). The insight to take: there is no room for a longer key, which is why hashing a string into an advisory key is standard practice and why collisions between unrelated subsystems sharing one integer space are a real design hazard — the tag has no room for a namespace, so you must build one into the key yourself.

Because the key is a fixed 64 bits, applications routinely derive it from a string: hashtext('nightly-billing-job')::bigint or ('x' || md5(name))::bit(64)::bigint. That works, but understand what you have bought — a 64-bit hash of an unbounded string space, with birthday-paradox collisions that will manifest as two unrelated jobs mysteriously serializing against each other. The two-integer form gives you a cheap escape: use key1 as a hand-assigned subsystem ID and key2 as the object ID within it.

One more structural fact, confirmed at source and load-bearing for the capacity discussion below: advisory locks do not get their own lock table. lock.h says so in the comment on the LOCKTAG struct — “We include lockmethodid in the locktag so that a single hash table in shared memory can store locks of different lockmethods” — and LockManagerShmemInit() in lock.c confirms it by allocating exactly one "LOCK hash" sized at NLOCKENTS() (plus a "PROCLOCK hash" at twice that, on the comment “Assume an average of 2 holders per lock”). USER_LOCKMETHOD exists to give advisory locks their own namespace and their own trace flag, not their own storage. Advisory locks compete for capacity with every table lock in the cluster.

Scope — session-level versus transaction-level

This is the single most consequential choice in the API, and the function names encode it: anything with xact in the name is transaction-scoped; everything else is session-scoped.

The distinction reduces, in the source, to one boolean. LockAcquire() takes a sessionLock argument, and lockfuncs.c passes true for pg_advisory_lock and false for pg_advisory_xact_lock — (void) LockAcquire(&tag, ExclusiveLock, true, false) versus (void) LockAcquire(&tag, ExclusiveLock, false, false). The fourth argument is dontWait, which is what separates pg_advisory_lock from pg_try_advisory_lock.

The behavioural consequences are documented precisely and are worth quoting because they surprise people: “session-level advisory lock requests do not honor transaction semantics: a lock acquired during a transaction that is later rolled back will still be held following the rollback, and likewise an unlock is effective even if the calling transaction fails later” (§13.3.5). A session-level advisory lock is outside the transaction system. ROLLBACK does not release it. ROLLBACK TO SAVEPOINT does not release it (see Subtransactions and Savepoints in PostgreSQL). Only an explicit unlock, or the end of the session, releases it.

Session-level locks also stack. “A lock can be acquired multiple times by its owning process; for each completed lock request there must be a corresponding unlock request before the lock is actually released.” Internally this is the nLocks counter on the backend-private LOCALLOCK entry; LockRelease() decrements it and only touches shared memory when it hits zero. Call pg_advisory_lock(1) three times, and you owe three pg_advisory_unlock(1) calls.

stateDiagram-v2
    [*] --> Unheld
    Unheld --> XactHeld: pg_advisory_xact_lock(k)
    XactHeld --> Unheld: COMMIT or ROLLBACK
    XactHeld --> XactHeld: ROLLBACK TO SAVEPOINT<br/>(still held)
    Unheld --> SessHeld1: pg_advisory_lock(k)
    SessHeld1 --> SessHeldN: pg_advisory_lock(k) again<br/>nLocks++
    SessHeldN --> SessHeld1: pg_advisory_unlock(k)<br/>nLocks--
    SessHeld1 --> Unheld: pg_advisory_unlock(k)
    SessHeld1 --> Unheld: pg_advisory_unlock_all()
    SessHeld1 --> Unheld: session ends<br/>(even on ungraceful disconnect)
    SessHeld1 --> SessHeld1: COMMIT / ROLLBACK<br/>(NOT released)

Lifecycle of an advisory lock under each scope. What it shows: transaction-level locks have exactly one exit (end of transaction, either way) and no manual release; session-level locks have a reference count and survive every transaction boundary, including a ROLLBACK. The insight to take: the “still held” self-loop on SessHeld1 at COMMIT/ROLLBACK is the entire bug class — an application that assumes its locks vanish when its transaction ends is correct for xact locks and catastrophically wrong for session locks.

pg_advisory_unlock_all() releases every session-level advisory lock held by the current session and, per the docs, “is implicitly invoked at session end, even if the client disconnects ungracefully” (§9.28.10). It maps to a single call: LockReleaseSession(USER_LOCKMETHOD). Crucially, it does not release transaction-level advisory locks — the regression suite tests exactly this, with the comment -- pg_advisory_unlock_all() shouldn't release xact locks followed by a count(*) that still returns 4 (advisory_lock.sql).

The Full Function Matrix

Sixteen functions fall out of three orthogonal choices: scope (session / transaction), mode (exclusive / shared), and blocking (wait / try). Each exists in both key-space arities.

	Exclusive, waits	Shared, waits	Exclusive, no wait	Shared, no wait
Session	pg_advisory_lock	pg_advisory_lock_shared	pg_try_advisory_lock	pg_try_advisory_lock_shared
Transaction	pg_advisory_xact_lock	pg_advisory_xact_lock_shared	pg_try_advisory_xact_lock	pg_try_advisory_xact_lock_shared
Release	pg_advisory_unlock	pg_advisory_unlock_shared	— (no xact release)	pg_advisory_unlock_all

The _shared variants use ShareLock where the plain ones use ExclusiveLock, drawn from the same conflict table as ordinary table locks — user_lockmethod in lock.c reuses LockConflicts and lock_mode_names verbatim. So ShareLock conflicts with ExclusiveLock but not with itself, giving you a straightforward readers-writer lock over an integer. That reuse is also why the mode column in pg_locks shows ExclusiveLock/ShareLock for advisory locks even though only two of the eight modes are reachable through the SQL API.

The try_ variants return boolean rather than void and are implemented by passing dontWait = true, then testing res != LOCKACQUIRE_NOT_AVAIL. They return immediately — there is no timeout variant. If you want “wait up to five seconds,” you set lock_timeout around a blocking call and catch the error; there is no pg_advisory_lock_timeout.

The waiting versions are genuinely blocking, and blocking with all the usual machinery: the backend enters ProcSleep, pg_stat_activity.wait_event_type becomes Lock and wait_event becomes advisory — documented in Table 27.11 as “Waiting to acquire an advisory user lock” (monitoring). See Wait Events in PostgreSQL for how to sample that. After deadlock_timeout the deadlock detector runs, and because advisory locks live in the same shared table with the same wait queues, a cycle of advisory-lock waits is detected and one transaction is aborted with SQLSTATE 40P01.

Mechanical Walk-through — no fast path, one of sixteen partitions

Ordinary weak relation locks (AccessShareLock through RowExclusiveLock) usually never touch shared memory at all: they are recorded in a per-backend “fast path” array. Advisory locks are explicitly excluded. From lock.c:

#define EligibleForRelationFastPath(locktag, mode) \
	((locktag)->locktag_lockmethodid == DEFAULT_LOCKMETHOD && \
	(locktag)->locktag_type == LOCKTAG_RELATION && \
	(locktag)->locktag_field1 == MyDatabaseId && \
	MyDatabaseId != InvalidOid && \
	(mode) < ShareUpdateExclusiveLock)

An advisory lock fails the very first clause — its lockmethodid is USER_LOCKMETHOD, not DEFAULT_LOCKMETHOD — and the second as well. So every advisory lock acquisition takes an LWLock on a shared hash partition and inserts a LOCK and a PROCLOCK entry. There are exactly 16 such partitions (LOG2_NUM_LOCK_PARTITIONS 4 in lwlock.h), chosen by hashing the tag. Correspondingly, pg_locks.fastpath is always false for advisory locks.

This matters twice. First, it is why a very hot advisory-lock workload can show LWLock:lock_manager contention: thousands of acquisitions per second all serialize on 16 partition locks. Second, it is why the shared lock table’s capacity is a hard ceiling — there is no overflow path.

sequenceDiagram
    participant A as Session A
    participant B as Session B
    participant LM as Lock manager<br/>(partition LWLock)
    participant DD as Deadlock detector
    A->>LM: pg_advisory_xact_lock(7) — ExclusiveLock
    LM-->>A: granted (LOCK + PROCLOCK created)
    B->>LM: pg_advisory_xact_lock(7)
    LM-->>B: conflict → enqueue, ProcSleep
    Note over B: pg_stat_activity:<br/>wait_event_type = Lock<br/>wait_event = advisory
    B->>DD: deadlock_timeout (default 1s) fires
    DD-->>B: no cycle → keep waiting
    A->>LM: COMMIT → release all xact locks
    LM-->>B: granted
    Note over B: pg_locks.granted flips f → t

Two sessions contending for the same transaction-level advisory key. What it shows: the waiter is a normal lock-manager waiter — it enqueues, it is visible in pg_locks with granted = false and a waitstart timestamp, it triggers the deadlock check after deadlock_timeout, and it is woken by the holder’s COMMIT. The insight to take: everything you already know about diagnosing table-lock waits applies verbatim to advisory locks; the only novelty is that the “object” is an integer you invented.

Advisory locks and two-phase commit do not mix in one specific way: AtPrepare_Locks() calls CheckForSessionAndXactLocks(), and if a session holds both a session-level and a transaction-level lock on the same object, PREPARE TRANSACTION fails with cannot PREPARE while holding both session-level and transaction-level locks on the same object (lock.c). Since advisory locks are the only lock type where an application routinely takes both scopes on the same key, this error is in practice an advisory-lock error.

Introspection — reading pg_locks

The documentation gives the decoding rule exactly: “A bigint key is displayed with its high-order half in the classid column, its low-order half in the objid column, and objsubid equal to 1. The original bigint value can be reassembled with the expression (classid::bigint << 32) | objid::bigint. Integer keys are displayed with the first key in the classid column, the second key in the objid column, and objsubid equal to 2” (pg_locks).

The regression suite prints the result for four locks taken at once. This output is verbatim from src/test/regress/expected/advisory_lock.out on REL_18_STABLE, and it is the clearest single demonstration of the non-overlapping key spaces:

BEGIN;
SELECT pg_advisory_xact_lock(1),        pg_advisory_xact_lock_shared(2),
       pg_advisory_xact_lock(1, 1),     pg_advisory_xact_lock_shared(2, 2);
 
SELECT locktype, classid, objid, objsubid, mode, granted
  FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid
  ORDER BY classid, objid, objsubid;

 locktype | classid | objid | objsubid |     mode      | granted
----------+---------+-------+----------+---------------+---------
 advisory |       0 |     1 |        1 | ExclusiveLock | t
 advisory |       0 |     2 |        1 | ShareLock     | t
 advisory |       1 |     1 |        2 | ExclusiveLock | t
 advisory |       2 |     2 |        2 | ShareLock     | t

Rows 1 and 3 are the punchline. pg_advisory_xact_lock(1) — the bigint 1 — decomposes to classid = 0, objid = 1, objsubid = 1. pg_advisory_xact_lock(1, 1) gives classid = 1, objid = 1, objsubid = 2. Different tags, no conflict. The objsubid column is locktag_field4 — the discriminator from the macro above, surfaced in SQL.

For operational use, join pg_locks to pg_stat_activity on pid — the pattern the PostgreSQL wiki’s Lock Monitoring page builds all of its blocked/blocking queries around:

SELECT l.pid,
       (l.classid::bigint << 32) | l.objid::bigint AS bigint_key,  -- valid only when objsubid = 1
       l.mode, l.granted, l.waitstart,
       a.state, a.state_change, a.application_name,
       left(a.query, 60) AS query
FROM pg_locks l
JOIN pg_stat_activity a USING (pid)
WHERE l.locktype = 'advisory'
ORDER BY l.granted, l.waitstart;

Sort by granted first: ungranted rows are your waiters. The diagnostic that matters most is a row with granted = true, objsubid decoding to a key you recognize, and a.state = 'idle' — a session holding a lock while doing nothing. That is a leak, and it is the next section.

Failure Mode 1 — leaked session locks, and why transaction pooling breaks them outright

The canonical incident is: a job takes pg_advisory_lock(k), throws an exception before its pg_advisory_unlock(k), and the connection goes back to the application’s client-side pool. The lock is still held — ROLLBACK did not release it, because session-level advisory locks “do not honor transaction semantics.” The next run of the job blocks forever against a lock held by an idle connection that will never release it, because that connection has moved on to other work. Nothing recovers this except killing the session or restarting the app.

There is a second, sharper version of the problem that is not a bug in your code at all: a transaction-level connection pooler makes session-scoped advisory locks meaningless by construction. PgBouncer’s own feature matrix lists “Session-level advisory locks” as supported under session pooling and Never under transaction pooling (PgBouncer features). The mechanism is stated on the same page: transaction pooling “breaks client expectations of the server by design and can be used only if the application cooperates by not using non-working features,” and pool_mode = transaction means “server is released back to pool after transaction finishes” (PgBouncer config).

Put those two facts together and the failure is total. In transaction pooling the client’s notion of “my session” does not exist — consecutive statements may land on different backend connections. So pg_advisory_lock(k) in one statement and pg_advisory_unlock(k) in the next may run on different server sessions: the unlock returns false and emits WARNING: you don't own a lock of type ExclusiveLock (the exact elog text in lock.c when locallock->nLocks <= 0), while the original lock stays held on a backend the client can no longer address. You have simultaneously failed to release a lock and failed to hold one. See PostgreSQL Connection Model and Pooling for why transaction pooling is nonetheless the default recommendation for high-connection-count deployments.

flowchart TB
    subgraph OK["Session pooling — works"]
        C1["client"] --> P1["pooler: 1 client<br/>= 1 server conn"]
        P1 --> S1["backend 7<br/>holds advisory lock k<br/>for the whole session"]
    end
    subgraph BAD["Transaction pooling — broken"]
        C2["client"] --> P2["pooler: server released<br/>after each transaction"]
        P2 -- "stmt 1: pg_advisory_lock(k)" --> S2["backend 11<br/>now holds k forever"]
        P2 -- "stmt 2: pg_advisory_unlock(k)" --> S3["backend 19<br/>WARNING: you don't<br/>own a lock ... returns false"]
    end
    S2 -.->|"leaked: idle backend<br/>holding k"| LEAK["every later attempt<br/>blocks or try_ returns false"]

Why session-level advisory locks cannot survive a transaction pooler. What it shows: under pool_mode = transaction the two halves of a lock/unlock pair can be dispatched to different backend processes, so the lock is orphaned on one and the unlock fails on another. The insight to take: this is not a tuning problem or a race — it is a categorical incompatibility, which is why PgBouncer’s matrix says “Never” rather than “sometimes”. Under transaction pooling, the only safe advisory locks are the _xact_ ones, whose lifetime is exactly the pooler’s unit of allocation.

The mitigations, in order of preference:

1. Prefer pg_advisory_xact_lock* unconditionally unless you have a specific reason to outlive the transaction. Transaction scope has no leak path: COMMIT and ROLLBACK both release, and the lifetime matches what a transaction pooler guarantees.
2. If you must hold session scope, hold it in code that cannot skip its release — a try/finally, a context manager, a RAII guard — and call pg_advisory_unlock_all() on every connection checkout as a belt-and-braces reset.
3. Set idle_in_transaction_session_timeout and idle_session_timeout so a stuck holder eventually dies and the server reclaims its locks at session end.
4. Alert on it. A query for pg_locks rows with locktype = 'advisory', granted = true, joined to pg_stat_activity with state = 'idle' and state_change < now() - interval '5 minutes' is a cheap and specific leak detector.

Failure Mode 2 — exhausting the shared lock table

The docs are unusually direct about this: “Both advisory locks and regular locks are stored in a shared memory pool whose size is defined by the configuration variables max_locks_per_transaction and max_connections. Care must be taken not to exhaust this memory or the server will be unable to grant any locks at all. This imposes an upper limit on the number of advisory locks grantable by the server, typically in the tens to hundreds of thousands depending on how the server is configured” (§13.3.5).

The sizing formula is one macro in lock.c:

#define NLOCKENTS() \
	mul_size(max_locks_per_xact, add_size(MaxBackends, max_prepared_xacts))

Walk it symbol by symbol. max_locks_per_xact is the GUC max_locks_per_transaction, whose default is 64 and which “can only be set at server start” (lock management config). MaxBackends is the total backend budget — max_connections plus autovacuum workers, background workers, and WAL senders. max_prepared_xacts is max_prepared_transactions. Multiply and you get the number of LOCK entries in the shared hash table. LockShmemSize() adds a 10 % safety margin on top (“Since NLOCKENTS is only an estimate, add 10% safety margin”), but the hash table cannot grow past its shared-memory allocation.

The critical property is that this is a global pool, not a per-transaction quota. The docs say so explicitly: “This parameter limits the average number of object locks used by each transaction; individual transactions can lock more objects as long as the locks of all transactions fit in the lock table.” One session that takes 200,000 advisory locks eats everyone’s budget. When the table fills, LockAcquire() raises:

ERROR:  out of shared memory
HINT:  You might need to increase "max_locks_per_transaction".

And note what “unable to grant any locks at all” means in practice: not just advisory locks. Ordinary DDL, VACUUM, and any query needing a strong relation lock start failing too, because they share the table. An advisory-lock leak is therefore capable of taking down unrelated workloads.

The realistic trigger is the pattern the documentation itself warns about — bulk acquisition by accident:

SELECT pg_advisory_lock(id) FROM foo WHERE id = 12345;              -- ok
SELECT pg_advisory_lock(id) FROM foo WHERE id > 12345 LIMIT 100;    -- danger!
SELECT pg_advisory_lock(q.id) FROM
  (SELECT id FROM foo WHERE id > 12345 LIMIT 100) q;                -- ok

The middle form is dangerous because “the LIMIT is not guaranteed to be applied before the locking function is executed. This might cause some locks to be acquired that the application was not expecting, and hence would fail to release… From the point of view of the application, such locks would be dangling, although still viewable in pg_locks.” This is a general truth about volatile functions in a target list — the executor evaluates the projection for rows the Limit node later discards — but advisory locks make it durable: the discarded rows leave permanent side effects. Always push the row-limiting into a subquery, or better, out of SQL entirely.

Capacity planning: with max_connections = 200 and defaults elsewhere, MaxBackends is on the order of 220 and the table holds roughly 64 × 220 ≈ 14,000 entries plus margin. That is small. If your design calls for a per-row advisory lock over a million-row table, the design is wrong — not the setting.

Use Cases Where Advisory Locks Are Genuinely the Right Tool

A singleton background job / distributed cron. You run three copies of a worker for availability but want exactly one to execute the nightly rollup. Each attempts SELECT pg_try_advisory_lock(hashtext('nightly-rollup')::bigint); whoever gets true runs, the others exit immediately. No wait, no queue, no coordination service. If session scope makes you nervous (it should), wrap the whole job in one transaction and use pg_try_advisory_xact_lock — but only if the job is short enough that a long-running transaction is acceptable, since a hours-long transaction pins the XID horizon and blocks VACUUM (Transaction ID Wraparound and Freezing).

Serializing schema migrations. Every migration runner takes pg_advisory_lock(<constant>) before inspecting the version table, so two simultaneous deploys cannot both decide migration 47 needs applying. This is the one case where session scope is genuinely right: the lock must span multiple transactions, including the DDL transactions themselves.

An application-level mutex over a resource that has no row. “Only one process may be rebuilding the search index.” “Only one process may be talking to this external payment provider for account X.” There is no row to lock because the thing being protected is not data. A row lock would require inventing a placeholder row; an advisory lock does not.

Reducing lock-queue pileups on hot rows. Instead of a hundred workers all queueing on SELECT … FOR UPDATE against one row, they can pg_try_advisory_xact_lock(account_id) and bail out cheaply, converting a blocking queue into a fast-fail the application can back off from.

Alternatives and When to Choose Them

A row lock (SELECT … FOR UPDATE). If the thing you are protecting is a row, lock the row. You get automatic release on transaction end, no key-collision hazard, no fixed-size table to exhaust — the docs note row-level locks are stored on disk in tuple headers, “not in memory, and therefore row-level locks normally do not appear in [pg_locks]” (pg_locks). The cost is bloat: every FOR UPDATE may set xmax and, when several sessions share a row lock, allocate a MultiXact (PostgreSQL Row Locks and MultiXact). For a work-queue, FOR UPDATE SKIP LOCKED is almost always better than advisory locks — it is purpose-built (“can be used to avoid lock contention with multiple consumers accessing a queue-like table”, SELECT) and it hands you the row you locked in the same statement.

A dedicated lock table with a unique constraint. INSERT INTO locks(name) VALUES ('job') ON CONFLICT DO NOTHING RETURNING 1. This is the option that survives connection pooling of any flavour, survives client crashes only if you add a lease/heartbeat column, and is inspectable with a plain SELECT by anyone. It is also durable and replicated — advisory locks are neither; they live in shared memory and are silently gone after a crash or a failover. The costs are exactly the ones §13.3.5 names: it is slower, it produces dead tuples and therefore bloat, and it needs explicit cleanup. Choose it when you need the lock to outlive the database process, or when operators need to see and clear locks without superuser-grade tooling.

LOCK TABLE. Too coarse for almost any application mutex — it blocks unrelated readers or writers of the whole relation depending on mode (LOCK). Use it for DDL coordination, not for business logic.

An external coordination service (etcd, ZooKeeper, Redis, Consul). Necessary when the lock must span more than one database, must survive database failover, or needs a fencing token. Advisory locks give you none of that: they are cluster-local, non-durable, and provide no fencing token, so a session that is presumed dead but is actually alive and slow can still act while a second holder is granted the lock after the first session is terminated. See Distributed Locks and Coordination Services.

	Advisory lock	Row lock (FOR UPDATE)	Dedicated lock table
Storage	shared memory (NLOCKENTS() entries)	tuple header xmax / MultiXact	heap rows
Bloat	none	yes (dead tuples, MultiXacts)	yes
Survives crash / failover	no	n/a (transactional)	yes
Survives transaction pooling	only _xact_ variants	yes	yes
Auto-release	yes (xact end or session end)	yes (xact end)	no — needs leases
Capacity ceiling	hard (max_locks_per_transaction × MaxBackends)	unlimited rows	unlimited rows
Visible to non-DBA tooling	pg_locks only	effectively invisible	plain SELECT
Deadlock detection	yes	yes	no (it is not a lock)
Needs an existing row	no	yes	no

Production Notes

Three habits separate teams that use advisory locks successfully from teams that get paged by them.

Default to transaction scope, and treat session scope as a documented exception. Every leak story starts with pg_advisory_lock. pg_advisory_xact_lock has no leak path at all. If you cannot use transaction scope because your unit of work spans transactions, write down why, next to the call.

Namespace your keys deliberately. Prefer the two-integer form with key1 as a registered subsystem ID from a table in your repository, and key2 as the object. If you use hashtext() on strings, be aware you are accepting silent collisions — and that hashtext() is not a documented, cross-version-stable hash function, so a value computed by an application in one language and by the database in another can disagree.

Uncertain

Verify: the stability of hashtext() output across PostgreSQL major versions. Reason: hashtext() is an internal function backing hash indexes and is not documented in the user-facing function reference I fetched (functions-admin.html); the widely repeated claim that its output changed in a past major release was not confirmed against a primary source during this research. To resolve: check src/backend/access/hash/hashfunc.c history and the release notes for any change to hash_any/hashtext, and confirm whether the value is guaranteed stable for on-disk or cross-version use. Until then, do not persist hashtext() values. #uncertain

Monitor two numbers. First, the count of granted advisory locks (SELECT count(*) FROM pg_locks WHERE locktype = 'advisory') against your computed NLOCKENTS() ceiling — alert well before the table fills, because filling it breaks the whole cluster, not just your feature. Second, the age of the oldest granted advisory lock whose holder is idle; anything past a few minutes is a leak until proven otherwise.

Finally, be honest about what an advisory lock is not. It is not a distributed lock — it is a lock inside one database of one PostgreSQL instance, gone the moment that instance restarts or fails over. It is not durable. It has no fencing token. It is an in-memory mutex with a SQL interface, and it is excellent at exactly that job.

See Also

• PostgreSQL Internals MOC — the parent map; advisory locks sit in §3, MVCC, Transactions, and Locking
• The PostgreSQL Lock Manager and Lock Modes — the LOCKTAG, the partitioned shared hash table, and the eight-mode conflict matrix that advisory locks reuse
• PostgreSQL Row Locks and MultiXact — the alternative when the thing you are protecting really is a row
• PostgreSQL Deadlock Detection — the wait-for graph that also covers advisory-lock cycles
• PostgreSQL Connection Model and Pooling — why transaction pooling exists, and therefore why session-level advisory locks are usually the wrong choice
• Wait Events in PostgreSQL — reading Lock:advisory and LWLock:lock_manager from pg_stat_activity
• Subtransactions and Savepoints in PostgreSQL — the other transaction-scope subtlety, and why ROLLBACK TO SAVEPOINT does not release advisory locks
• Distributed Locks and Coordination Services — the engine-agnostic treatment of locks that must span more than one database
• Database Internals MOC — the engine-agnostic parent