Postgres Advisory Locks: The 20-Character Primitive That Replaces Redis for Coordination

You have five Node.js workers running invoice generation every fifteen minutes. Every few weeks, two of them pick the same window, generate duplicate PDFs, and email the customer twice. Your first instinct is to install Redis and wire up Redlock. That is the instinct most teams have, and it is almost always overkill. Postgres (the database you are already running, already replicating, already backing up) has a coordination primitive that fits this exact problem in fewer than twenty characters of SQL.

That primitive is pg_advisory_lock. It is application-level, not row-level. It is explicit, not tied to a table. And unlike row locks, it survives for the exact lifetime you define (a session or a transaction) and blocks concurrent callers cleanly. This post shows what it is, when it beats a row lock, three patterns we have shipped to production, and the pool-level footgun that will make you think locks do not work.

What advisory locks actually are

Postgres has two locking layers. The first is the heavy one: row locks, table locks, deadlock detection, the lock manager. The second is advisory: a lightweight namespace of 64-bit integers (or two 32-bit integers) that exists only for your application to coordinate work. The database does not know what the number means. It just guarantees that only one session can hold the exclusive lock for that number at a time.

Because the lock is not tied to a row, you do not need a row to coordinate work that does not naturally map to a single record. You can use it to ensure only one worker runs a cron job. You can use it to rate-limit an operation per user. You can use it to prevent two parallel webhooks from processing the same logical event. All without a single row in a table, and all with the same consistency guarantees Postgres already gives you.

There are two lifetimes that matter.

Session-level locks (pg_advisory_lock) last until you explicitly call pg_advisory_unlock, or until the database connection closes. They are perfect for background workers that hold long-lived connections, because the lock survives multiple transactions.

Transaction-level locks (pg_advisory_xact_lock) last only for the current transaction. They automatically release at COMMIT or ROLLBACK. They are perfect for web requests, because you do not have to remember to unlock before returning a response to the client.

Both have non-blocking variants (pg_try_advisory_lock and pg_try_advisory_xact_lock) that return immediately with a boolean instead of waiting. Both use the same namespace, so a session-level lock on the number 42 will block a transaction-level attempt on 42, and vice versa.

Pattern 1: Exactly-once scheduled job execution

This is the classic duplicate-invoice scenario. You have a worker that wakes up every five minutes and looks for unprocessed orders. Without coordination, every worker that wakes up at the same time processes the same batch.

The naive fix is a row lock: SELECT id FROM orders WHERE status = 'pending' FOR UPDATE SKIP LOCKED. That works if the job is processing individual rows, but it does not help when the job is a single global operation (generate a nightly summary report, recompute a leaderboard, export a zip file for the entire account). Those jobs have no natural row to lock.

Advisory locks give you a cheap global mutex. The worker wakes up, tries to grab the lock, and either does the work or skips to the next task.

import pg from 'pg';
import crypto from 'node:crypto';

function lockIdFor(name) {
  // Stable 64-bit integer derived from the job name.
  // Any hash-to-int function works; this one uses the first 8 bytes of a SHA-256.
  const hash = crypto.createHash('sha256').update(name).digest();
  return hash.readBigInt64BE();
}

async function runJobWithLock(pool, jobName, workFn) {
  const client = await pool.connect();
  try {
    const lockId = lockIdFor(jobName);
    // Non-blocking: if another worker has it, we skip immediately.
    const { rows } = await client.query(
      'SELECT pg_try_advisory_lock($1) AS acquired',
      [lockId]
    );
    if (!rows[0].acquired) {
      console.log(JSON.stringify({ event: 'job_skipped', job: jobName }));
      return;
    }

    try {
      await workFn();
    } finally {
      // Release so the next scheduled window can pick it up.
      await client.query('SELECT pg_advisory_unlock($1)', [lockId]);
    }
  } finally {
    client.release();
  }
}

A few things that are easy to get wrong.

First, the lock must be released on the same connection that acquired it. Postgres advisory locks are bound to a session, not to a transaction. If you acquire with one pool.query() call and try to release with another, the second call might run on a different pooled connection, and the lock will stay held until that first connection is returned to the pool and eventually closed. The example above uses a dedicated pool.connect() / client.release() pair so the session is stable.

Second, the non-blocking variant is usually the right default for scheduled jobs. If you use the blocking variant (pg_advisory_lock) and every worker tries to run the same job every minute, you will end up with a queue of workers sitting idle, waiting on the lock, instead of doing useful work. Skip early, move on.

Third, job restarts matter. If your worker crashes after acquiring the lock and before releasing it (network hiccup, OOM kill, deploy mid-job), the lock stays held until Postgres notices the connection is dead, which can take up to tcp_keepalives_idle plus some retries. Plan for that delay. Make the scheduled interval longer than the worst-case connection timeout, or pair advisory locks with a timestamp heartbeat in a jobs table for faster lease expiry.

Pattern 2: One-at-a-time rate limiter per entity

Your API allows users to trigger an expensive report export. You want to prevent a user from queueing ten exports in a burst. A row lock on the users table works, but it also blocks legitimate reads and writes to that user record while the report runs. An advisory lock on a stable integer derived from the user ID gives you the same exclusion without touching the users table at all.

async function withRateLimit(pool, userId, fn) {
  const client = await pool.connect();
  try {
    // Use the user ID's numeric value directly, or hash if it is a UUID string.
    const lockId = BigInt.asIntN(64, BigInt(userId));
    const { rows } = await client.query(
      'SELECT pg_try_advisory_xact_lock($1) AS acquired',
      [lockId]
    );
    if (!rows[0].acquired) {
      const err = new Error('Export already in progress for this user');
      err.code = 'RATE_LIMITED';
      throw err;
    }

    // The lock releases automatically at COMMIT / ROLLBACK.
    await fn();
  } finally {
    client.release();
  }
}

Notice the transaction-level variant (pg_try_advisory_xact_lock). Because this runs inside a web request, the lock must release before the route handler returns. If you used a session-level lock and forgot to unlock, the next request from that user (even an hour later, on a different server) could still see the lock held. Transaction-level scopes the lock to the exact lifetime of the request’s database transaction, so forgetting to unlock is harmless.

The trade-off is that if your fn() spans multiple transactions, the lock drops after the first one. Use session-level locks only when the work and the lock are confined to a single, obvious lifetime.

Pattern 3: Deduplicating webhook delivery

You receive a webhook from a payment provider with an event ID. The provider retries aggressively, and your API is horizontally scaled. You need exactly-once processing by event ID, but you do not want to insert a row into a deduplication table if the event is already being processed right now.

Advisory locks give you an in-flight mutex. Two requests with the same event ID arrive; the first acquires the lock and starts working, the second blocks for a short timeout and then either waits for the result or returns a 202 Accepted to the caller.

async function processWebhookEvent(pool, eventId, processor) {
  const client = await pool.connect();
  try {
    const lockId = lockIdFor(`webhook:${eventId}`);
    await client.query('SET LOCAL lock_timeout = \'5s\'');
    const { rows } = await client.query(
      'SELECT pg_try_advisory_xact_lock($1) AS acquired',
      [lockId]
    );
    if (!rows[0].acquired) {
      // Another request is processing this event. Return early.
      return { status: 'already_in_progress' };
    }

    const result = await processor();
    return { status: 'processed', result };
  } finally {
    client.release();
  }
}

The SET LOCAL lock_timeout is important. Postgres does not time out lock waits by default. If you use the blocking pg_advisory_xact_lock (without the try_ prefix), a duplicate request could wait forever. Set a per-transaction lock timeout low enough that the duplicate returns an error before the HTTP gateway times out.

After the lock is acquired and the work is done, the event should still be recorded in a processed_events table with a unique constraint on event_id. The advisory lock prevents duplicate concurrent work; the unique constraint prevents duplicate retried work after the transaction commits and the lock is gone. You need both layers.

The pool-level footgun

The biggest source of “advisory locks do not work” bug reports is connection pooling, specifically PgBouncer in transaction mode (statement pooling), or any Node.js pg-pool configuration that aggressively resets connections.

Advisory locks are attached to a server-side session. If your pooler multiplexes multiple client connections onto one server connection, a lock acquired by one client can be released, or leak, when the pooler assigns that server connection to a different client. PgBouncer in transaction mode is the worst case: every transaction may run on a different physical connection, so session-level locks are completely broken. Transaction-level locks can work if the entire lock lifetime fits inside a single transaction and PgBouncer keeps the same backend for that transaction, but many poolers do not even guarantee that.

The fix is simple, but easy to miss: use a dedicated, non-pooled connection for session-level advisory locks, or configure your pooler in session mode. If you use node-pg with pg-pool, pool.connect() gives you a stable connection for the lifetime of the client object, so session-level locks work fine. The problem usually appears when someone switches to an external pooler later without re-testing the locking logic.

Node.js pg has another subtle behavior. When a client is released back to the pool with client.release(true), the pool discards the connection and opens a new one. That would drop a session-level lock correctly. But client.release() without the error parameter returns the connection to the pool for reuse. The lock stays held. That is usually what you want if you are going to hold the lock across multiple pooled queries, but it means you must manually unlock before releasing. If you forget, the lock leaks until the pooled connection ages out.

Here is the safe lifecycle for session-level locks in node-pg:

const client = await pool.connect();
try {
  const { rows } = await client.query(
    'SELECT pg_try_advisory_lock($1) AS acquired',
    [lockId]
  );
  if (!rows[0].acquired) return;

  await doWork();

  // Unlock explicitly before releasing the client.
  await client.query('SELECT pg_advisory_unlock($1)', [lockId]);
} catch (err) {
  await client.query('SELECT pg_advisory_unlock_all()');
  throw err;
} finally {
  client.release();
}

pg_advisory_unlock_all() is the panic button. It releases every advisory lock held by the current session. Use it in the error path so you do not leak a lock when doWork() throws before the explicit unlock.

Lock IDs you should avoid

Postgres advisory locks are a flat 64-bit namespace. Every application on the server shares it. If two different code paths accidentally use the same integer, they will block each other in ways that are nearly impossible to debug.

There are two safe ways to allocate lock IDs.

Hash-based: SHA-256 the fully qualified lock name, take the first 8 bytes, and treat it as a signed 64-bit integer. The birthday problem makes collisions vanishingly unlikely for a codebase of any reasonable size.

Offset-based: Pick a random 64-bit offset per service (store it in config), then add a small, manually assigned enum for each lock type within that service. This is what some large monorepos do. It is collision-proof across services as long as the offsets are unique.

Never use a small auto-incrementing integer like 1, 2, 3 without a namespace prefix. Someone else will use the same numbers in a different microservice, and you will spend a day reading TCP captures.

When not to use advisory locks

Advisory locks solve coordination problems that do not map to rows. If your coordination target is a row in a table, a normal SELECT ... FOR UPDATE or FOR UPDATE SKIP LOCKED is often better. Row locks participate in deadlock detection. Advisory locks do not. If you acquire advisory lock A then B, and another process acquires B then A, you can deadlock, and Postgres will not detect it. Keep advisory lock acquisition order consistent, or use only a single lock per operation.

Also, advisory locks do not survive failover. If your primary Postgres fails over to a hot standby, the new primary has no memory of which connections held which advisory locks. In-flight work that relied on that lock may duplicate. For critical exactly-once operations, pair advisory locks with an idempotency table or a state machine transition that has a unique constraint.

Testing that it actually works

You can verify advisory lock behavior with two parallel psql sessions.

-- Session 1
SELECT pg_advisory_lock(12345);
-- returns successfully

-- Session 2
SELECT pg_try_advisory_lock(12345);
-- returns f

-- Session 1
SELECT pg_advisory_unlock(12345);
-- returns t

-- Session 2
SELECT pg_try_advisory_lock(12345);
-- now returns t

In automated tests, spawn two connections from the same pool, have each try to acquire the same lock, and assert that only one succeeds. Then release the lock and assert the second succeeds. Run it a thousand times under Promise.all to confirm there is no race.

import { test, describe } from 'node:test';
import assert from 'node:assert';

describe('advisory lock exclusion', async () => {
  test('only one caller holds the lock', async () => {
    const c1 = await pool.connect();
    const c2 = await pool.connect();
    try {
      const [{ rows: r1 }, { rows: r2 }] = await Promise.all([
        c1.query('SELECT pg_try_advisory_lock(99999) AS acquired'),
        c2.query('SELECT pg_try_advisory_lock(99999) AS acquired'),
      ]);
      const acquired = [r1[0].acquired, r2[0].acquired];
      assert.deepStrictEqual(acquired.sort(), [false, true]);
    } finally {
      await c1.query('SELECT pg_advisory_unlock_all()');
      c1.release();
      c2.release();
    }
  });
});

Summary

Advisory locks are not a novel idea. They have been in Postgres since version 8.2. But most engineering teams do not know they exist, because the problems they solve are usually handed off to Redis, BullMQ, or some other external store. That works, but it adds infrastructure, adds latency, adds another failure mode, and adds another thing to monitor.

If you already have Postgres, and the coordination problem is a single shared resource, a scheduled window, or a duplicate request, start with pg_try_advisory_lock. It is twenty characters, zero new infrastructure, and the exact same atomicity guarantees as every other transaction in your system. Just remember: same connection for acquire and release, session mode only, no PgBouncer transaction pooling, and a hash-based lock ID so you never collide with the team next door.

---

A note from Yojji

The kind of backend work this post describes (picking the right primitive for coordination, testing it under concurrent load, catching the pooler footgun before it hits production) is exactly the unglamorous craft that separates a system that runs quietly from one that wakes you up at 3 a.m. It is also the kind of work Yojji’s senior engineers build into the full-stack products they ship.

Yojji is an international custom software development company founded in 2016, with offices in Europe, the US, and the UK. Their teams specialize in the JavaScript ecosystem (React, Node.js, TypeScript), cloud platforms (AWS, Azure, Google Cloud), and microservices architectures, and they run both dedicated senior outstaffed teams and full-cycle product engagements covering discovery, design, development, QA, and DevOps.