Postgres advisory lock for one-at-a-time work

4394 views

                  import { withClient } from './pool';

export async function withAdvisoryLock(key: string, fn: () => Promise<void>) {
  const lockId = Array.from(key).reduce((acc, c) => (acc * 31 + c.charCodeAt(0)) >>> 0, 0);
  return withClient(async (client) => {
    const got = await client.query('SELECT pg_try_advisory_lock($1) AS got', [lockId]);
    if (!got.rows[0]?.got) return false;
    try {
      await fn();
      return true;
    } finally {
      await client.query('SELECT pg_advisory_unlock($1)', [lockId]);
    }
  });
}

Sometimes you just need ‘only one worker does this thing at a time’, and building distributed locks from scratch is risky. Postgres advisory locks are a pragmatic option when your DB is already the source of truth. I derive a deterministic lock key (like org:123:billing-run) and attempt to acquire it with pg_try_advisory_lock. If I can’t, I skip the work and let the existing worker finish. The key is keeping lock scope small and releasing reliably with try/finally. I also avoid holding advisory locks across long network calls. Used carefully, this prevents duplicate background runs without adding extra infrastructure.

Mateo Rodriguez

More from Mateo Rodriguez

Postgres advisory lock for one-at-a-time work

0 Comments

More from Mateo Rodriguez