PostgreSQL Advisory Locks for Distributed Job Scheduling

Horizontally scaling web services is straightforward until background jobs enter the system. When multiple instances of an application run simultaneously, executing scheduled tasks—such as generating nightly billing reports, clearing expired sessions, or processing event queues—presents a concurrency challenge. Without explicit synchronization, competing application nodes will execute the same job concurrently, causing race conditions, data corruption, and duplicate side effects.

Engineers often reach for dedicated orchestration services like Redis using Redlock, or heavyweight coordination tools like Apache Zookeeper. While effective, these tools introduce operational overhead, additional network hops, and failure domains. If your primary data store is already PostgreSQL, you can handle distributed mutual exclusion natively using Advisory Locks.

Understanding Advisory Locks vs Row Locks

Standard database locking mechanisms operate on relational entities. Commands like SELECT ... FOR UPDATE lock specific table rows, while schema mutations lock entire tables. This model works well for data updates, but using row locks for job coordination causes unnecessary database bloat, deadlocks, and transaction overhead.

Advisory locks operate on abstract integer keys rather than table rows. The database does not enforce any semantics on these keys; application code defines what a lock ID represents. Because advisory locks do not modify table data, acquiring and releasing them creates zero write amplification in write-ahead logs (WAL) or table tuples.

PostgreSQL supports two primary dimensions of advisory locks:

1. Exclusive vs. Shared: Exclusive locks allow only one session to hold the lock ID. Shared locks allow multiple readers but block exclusive acquirers.
2. Session-Level vs. Transaction-Level: Session-level locks persist across multiple transactions until explicitly released or until the database connection closes. Transaction-level locks automatically release when the current SQL transaction commits or rolls back.

For distributed task execution, exclusive transaction-level locks (pg_try_advisory_xact_lock) are generally the safest pattern because they release automatically if the holding worker crashes or loses network connectivity.

Designing the Lock Identifier Strategy

PostgreSQL advisory lock functions accept either a single 64-bit integer (bigint) or two 32-bit integers (integer, integer). To avoid lock collisions across different application features, establish a deterministic hashing strategy.

A reliable approach maps a namespace string (e.g., "cron_jobs") and a specific job key (e.g., "generate_invoices") into two distinct 32-bit integers using a consistent hashing algorithm like MurmurHash3 or CRC32:

import crypto from 'crypto';
 
export function generateLockKeys(namespace: string, jobKey: string): [number, number] {
  const nsHash = crypto.createHash('sha256').update(namespace).digest();
  const keyHash = crypto.createHash('sha256').update(jobKey).digest();
 
  // Extract signed 32-bit integers from digest buffers
  const lockId1 = nsHash.readInt32BE(0);
  const lockId2 = keyHash.readInt32BE(0);
 
  return [lockId1, lockId2];
}

Implementation of Non-Blocking Task Execution

When a scheduled worker fires on multiple nodes simultaneously, only one node should execute the task. The remaining nodes should skip execution immediately without blocking or queuing up behind the winner. PostgreSQL provides pg_try_advisory_xact_lock, which returns true if the lock was successfully acquired and false if another session holds it.

Below is an implementation using node-postgres (pg) that executes a job within a managed database transaction.

import { Pool, PoolClient } from 'pg';
import { generateLockKeys } from './lock-utils';
 
export class DistributedScheduler {
  constructor(private pool: Pool) {}
 
  async runExclusiveJob<T>(
    namespace: string,
    jobKey: string,
    task: (client: PoolClient) => Promise<T>
  ): Promise<{ executed: boolean; result?: T }> {
    const client = await this.pool.connect();
    const [id1, id2] = generateLockKeys(namespace, jobKey);
 
    try {
      await client.query('BEGIN');
 
      // Attempt non-blocking lock acquisition
      const lockRes = await client.query<
        { acquired: boolean }
      >('SELECT pg_try_advisory_xact_lock($1, $2) as acquired', [id1, id2]);
 
      const acquired = lockRes.rows[0]?.acquired ?? false;
 
      if (!acquired) {
        // Another node is already processing this job
        await client.query('ROLLBACK');
        return { executed: false };
      }
 
      // Execute business logic with exclusive lock active
      const result = await task(client);
 
      await client.query('COMMIT');
      return { executed: true, result };
    } catch (error) {
      await client.query('ROLLBACK');
      throw error;
    } finally {
      client.release();
    }
  }
}

Critical Edge Cases and Mitigation Strategies

While advisory locks simplify infrastructure, distributed systems present edge cases that require defensive design.

1. Connection Pool Leakage and Stale Locks

Using session-level locks (pg_advisory_lock) alongside connection pools like PgBouncer in transaction-pooling mode is dangerous. If a worker acquires a session lock and the connection is returned to the pool without releasing it, a completely unrelated worker thread might inherit that connection—and its active locks.

Mitigation: Use transaction-level locks (pg_try_advisory_xact_lock). Because transaction locks release immediately upon COMMIT or ROLLBACK, they are safe under transaction-pooled infrastructure.

2. Long-Running Workloads and Timeout Cascades

If a job executes business logic outside the database for longer than the database's statement_timeout or network TCP keepalive limits, the database may terminate the backend connection. If the connection drops, PostgreSQL instantly releases the transaction-level lock, allowing another node to pick up the task while the original worker is still processing.

Mitigation: Keep transactional locks scoped strictly to coordination steps. If a background job takes several minutes, do not hold an open database transaction for the duration of the work. Instead, use advisory locks to write a state record in the database marking the job as IN_PROGRESS with an expiration timestamp, then commit and close the transaction.

3. Monitoring Lock Contention

Unbounded lock requests can strain database system catalogs. You can inspect active advisory locks by querying the pg_locks system view:

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

Set up alerting on your observability platform to trigger if lock check queries exceed baseline connection times or if pg_locks shows excessive lock accumulation.

Tradeoffs and Architectural Recommendation

PostgreSQL advisory locks offer low-latency, zero-dependency locking for workloads that already use Postgres as their primary store. They eliminate the cost of operating separate Redis or Consul clusters solely for distributed locking.

However, they are not a complete replacement for robust event streams or dedicated task queues. If your architecture requires sub-millisecond throughput across thousands of parallel workers, complex priority queues, or cross-datacenter quorum consensus, dedicated orchestration platforms remain necessary. For standard microservice architectures executing cron jobs or ensuring single-execution invariants, PostgreSQL advisory locks provide a resilient, operational-simple alternative.