Backend · PostgreSQL · Reliability

PostgreSQL Advisory Locks for Scheduled Jobs

A cron expression tells a job when to run. It does not guarantee that only one application instance will run it. PostgreSQL advisory locks are a small, useful coordination primitive when scheduled work already depends on PostgreSQL and duplicate execution would be expensive or unsafe.

October 16, 2026

The problem: cron is not a distributed lock

A scheduled job may look safe in local development because only one process exists. In production, the same application can run on multiple instances, replicas or workers. If every instance receives the same schedule, they can all start the same job at nearly the same time.

For a weather-data pipeline, that can mean duplicate upstream API calls, duplicate database writes, repeated alert evaluation and unnecessary pressure on rate-limited providers. The schedule is correct, but the execution is not coordinated.

Use PostgreSQL as the coordination point

PostgreSQL advisory locks let an application associate an integer key with a logical piece of work. Unlike a row lock, the key does not need to correspond to a table row. That makes advisory locks useful for jobs such as refresh-forecasts, generate-daily-summaries or reconcile-subscriptions.

For scheduled work I prefer non-blocking acquisition. One instance obtains the lock and runs the job; the others see that the lock is already held and exit instead of waiting. A delayed duplicate execution is usually not useful for cron-style work.

SELECT pg_try_advisory_lock($1) AS acquired;

The lock must stay on the same database connection

Session-level advisory locks belong to the PostgreSQL connection that acquired them. This is easy to get wrong when the application uses a connection pool: acquiring a lock with one pooled query and releasing it with another does not guarantee that both queries use the same backend connection.

Keep acquisition, the protected work and release tied to the same database client or transactional connection. Release in a finally block so an application-level failure does not leave the lock held for the rest of that session.

const acquired = await db.$queryRaw<boolean[]>`
  SELECT pg_try_advisory_lock(${jobLockId}) AS acquired
`;

if (!acquired[0]?.acquired) return;

try {
  await refreshForecasts();
} finally {
  await db.$queryRaw`
    SELECT pg_advisory_unlock(${jobLockId})
  `;
}

Choose lock keys around the work you actually want to serialize

A single global key is simple, but it can serialize unrelated work. The right key depends on the job. A global forecast-refresh job may need one key for the entire process, while tenant-scoped or field-scoped work can derive a stable key from the job type plus the tenant or resource identifier.

The important property is determinism: every competing instance must calculate the same key for the same logical job, and different jobs should not collide accidentally.

Advisory locks are not the whole reliability strategy

A lock prevents concurrent execution while it is held. It does not make the job idempotent, decide whether the data is already fresh, recover an upstream provider or prevent repeated writes after a retry.

In Vienor I combine coordination with freshness policies, deduplication, retries and circuit breakers. Fresh forecast data can be reused instead of fetched again, unchanged snapshots do not need to be rewritten, and rate-limit responses can temporarily open a circuit instead of causing every scheduled run to keep calling an already constrained provider.

Transaction-level locks are another option

PostgreSQL also provides transaction-level advisory locks such as pg_try_advisory_xact_lock. They are automatically released when the transaction ends, which removes the need for an explicit unlock.

That can be useful when the protected operation naturally fits inside a short transaction. It is less attractive when a job performs long-running network calls, because holding a database transaction open around external work can create a different set of operational problems.

SELECT pg_try_advisory_xact_lock($1) AS acquired;

When this pattern is a good fit

Advisory locks work well when PostgreSQL is already a required dependency, only a small amount of distributed coordination is needed and skipping a duplicate invocation is acceptable. They avoid introducing a separate queue or lock service just to solve one narrow scheduling problem.

They are not a replacement for a job queue when work needs durable delivery, retries per item, priorities, backpressure or horizontal worker orchestration. The goal is not to turn PostgreSQL into a queue; it is to use an existing system primitive for a problem it solves cleanly.

The production rule

Treat a scheduled job as a distributed-systems boundary as soon as more than one process can execute it. Make ownership explicit, acquire coordination non-blockingly, keep connection semantics correct, and design the work so retries and duplicates are safe even when the lock is not the only failure boundary.

That combination is more important than the cron syntax itself. The schedule starts the work; the reliability controls determine whether the system behaves correctly in production.