How Instatic Achieves Multi-Instance High Availability with PostgreSQL Advisory Locks

Instatic uses PostgreSQL advisory locks to elect a single "leader" instance for each background scheduler, ensuring that only one container runs recurring tasks like publishing scheduled content or executing plugin timers, even when multiple instances are deployed behind a load balancer.

Instatic is a single-process Bun server designed for content management, but production deployments often scale horizontally across many containers. To prevent duplicate work in multi-instance setups, the codebase implements a lightweight leader-election mechanism using Postgres advisory locks. This approach relies on the database itself as the single source of truth for coordination, eliminating the need for external message queues or distributed lock managers.

The Advisory Lock Primitive in server/db/advisoryLock.ts

The core utility resides in server/db/advisoryLock.ts, which abstracts the pg_try_advisory_lock and pg_advisory_unlock functions into a reusable TypeScript API. The module exports three primary functions: tryAcquireLeader, releaseLeader, and withSchedulerLeaderLock.

PostgreSQL vs. SQLite Handling

The implementation accounts for both database backends using sentinel tokens:

• PG_TOKEN – Returned when pg_try_advisory_lock successfully acquires a session-level lock in PostgreSQL.
• SQLITE_TOKEN – Returned immediately for SQLite deployments, since SQLite lacks advisory locks but is inherently single-instance.

// server/db/advisoryLock.ts
const PG_TOKEN = 'pg-advisory'
const SQLITE_TOKEN = 'sqlite-no-lock'

export async function tryAcquireLeader(
  db: DbClient,
  lockKey: bigint,
  logPrefix = ''
): Promise<string | undefined> {
  // SQLite short-circuit: no actual lock needed
  if (db.isSqlite) return SQLITE_TOKEN

  const { rows } = await db`select pg_try_advisory_lock(${lockKey}) as got`
  const got = rows[0]?.got as boolean
  if (got) return PG_TOKEN
  console.error(`${logPrefix} failed to acquire advisory lock for ${lockKey}`)
  return undefined
}

Lock Release Mechanics

The releaseLeader function checks the token type before invoking pg_advisory_unlock. For SQLite, it performs a no-op; for PostgreSQL, it executes the unlock SQL. Errors during release are logged but do not throw, ensuring cleanup attempts remain safe.

// server/db/advisoryLock.ts
export async function releaseLeader(
  db: DbClient,
  token: string,
  lockKey: bigint,
  logPrefix = ''
): Promise<void> {
  if (token === SQLITE_TOKEN) return
  if (token !== PG_TOKEN) {
    console.error(`${logPrefix} unexpected advisory token ${token}`)
    return
  }
  try {
    await db`select pg_advisory_unlock(${lockKey})`
  } catch (err) {
    console.error(`${logPrefix} failed to release advisory lock:`, err)
  }
}

Leader Election Pattern with withSchedulerLeaderLock

The withSchedulerLeaderLock function provides a higher-order abstraction that combines acquisition, execution, and guaranteed release. It accepts a database client, a unique numeric lock key, and an async function to execute. If the lock cannot be acquired, the function returns undefined immediately, allowing non-leader instances to skip work efficiently.

// server/db/advisoryLock.ts
export async function withSchedulerLeaderLock<T>(
  db: DbClient,
  lockKey: bigint,
  fn: () => Promise<T>,
  logPrefix = ''
): Promise<T | undefined> {
  const token = await tryAcquireLeader(db, lockKey, logPrefix)
  if (!token) return undefined
  try {
    return await fn()
  } finally {
    await releaseLeader(db, token, lockKey, logPrefix)
  }
}

This pattern ensures that the advisory lock is always released in a finally block, even if the leader function throws an error, preventing deadlocks when instances restart or crash.

Real-World Usage in Background Schedulers

Instatic employs distinct lock keys for different subsystems to prevent cross-scheduler contention. Each scheduler runs on a short interval (every few seconds) but only the instance holding the specific advisory lock executes the business logic.

Publish Scheduler

The tickPublishScheduler in server/publish/publishScheduler.ts uses lock key 0x9a37b7a1n to coordinate content publishing:

// server/publish/publishScheduler.ts
import { withSchedulerLeaderLock } from '../db/advisoryLock'
import { getDueRows } from '../repositories/data/rows/schedule'
import { publishRow } from '../publish/publishRow'

const LOCK_KEY = 0x9a37b7a1n

export async function tickPublishScheduler(db: DbClient) {
  await withSchedulerLeaderLock(db, LOCK_KEY, async () => {
    const dueRows = await getDueRows(db)
    for (const row of dueRows) {
      await publishRow(db, row)
    }
  }, 'publishScheduler')
}

Plugin Scheduler

Similarly, server/plugins/scheduler.ts uses a different lock key (0x7b9c5f3en) to manage plugin-declared periodic tasks:

// server/plugins/scheduler.ts
import { withSchedulerLeaderLock } from '../db/advisoryLock'
import { runAllSchedules } from './pluginScheduleRegistration'

const LOCK_KEY = 0x7b9c5f3en

export async function tickPluginScheduler(db: DbClient) {
  await withSchedulerLeaderLock(db, LOCK_KEY, async () => {
    await runAllSchedules(db)
  }, 'pluginScheduler')
}

Because these keys are hardcoded bigint literals derived from human-readable identifiers, they remain consistent across all application instances while remaining distinct from each other.

Automatic Failover and Crash Safety

PostgreSQL advisory locks are session-bound, meaning they are automatically released when the database connection closes or the application process terminates. This property provides automatic failover without additional heartbeat mechanisms. If the leader instance crashes or is terminated, its lock evaporates with the session, allowing the next scheduler tick from any surviving instance to acquire the lock and assume leadership immediately.

For SQLite deployments, the SQLITE_TOKEN sentinel ensures the code path executes synchronously without database calls, maintaining compatibility while preserving the single-instance semantics inherent to SQLite file-based databases.

Summary

• Leader Election via Postgres: tryAcquireLeader uses pg_try_advisory_lock to guarantee only one instance holds a given lock key at a time.
• SQLite Compatibility: The same code works with SQLite by returning a sentinel token, as SQLite deployments are single-instance by definition.
• Safe Execution Wrapper: withSchedulerLeaderLock handles acquisition, execution, and guaranteed release in a finally block.
• Distinct Lock Keys: Separate schedulers (publish and plugin) use unique bigint keys (0x9a37b7a1n and 0x7b9c5f3en) to avoid contention.
• Session-Level Safety: Locks automatically release on disconnect, enabling instant failover when containers crash or restart.

Frequently Asked Questions

How does Instatic prevent duplicate scheduled jobs when scaling to multiple containers?

Instatic prevents duplication by requiring each background scheduler to acquire a PostgreSQL advisory lock before executing. Only the instance that successfully calls pg_try_advisory_lock for a specific lock key becomes the leader and runs the job. Other instances skip execution until the next tick, when they retry the lock acquisition.

What happens if the leader instance crashes while holding the advisory lock?

If the leader crashes, the PostgreSQL session terminates and the advisory lock is automatically released by the database. This allows any surviving instance to acquire the lock on the next scheduler tick (typically within seconds) and resume processing without manual intervention or external monitoring.

Can this HA mechanism work with SQLite instead of PostgreSQL?

Yes. The advisoryLock.ts module detects SQLite via db.isSqlite and returns a SQLITE_TOKEN sentinel instead of calling PostgreSQL-specific functions. Since SQLite file databases support only one active writer process, this effectively mimics the leader-election behavior without requiring actual locking primitives.

Why use bigint literals like 0x9a37b7a1n for lock keys instead of strings?

PostgreSQL advisory locks operate on 64-bit integers (bigint). Instatic uses hexadecimal bigint literals derived from human-readable identifiers to ensure unique, deterministic lock keys across all instances. These values fit the bigint type expected by pg_try_advisory_lock while remaining collision-resistant across different scheduler subsystems.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}