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

> Discover how Instatic leverages PostgreSQL advisory locks for multi-instance high availability. Ensure one leader instance handles scheduled tasks ensuring reliable background job execution.

- Repository: [CoreBunch/Instatic](https://github.com/CoreBunch/Instatic)
- Tags: architecture
- Published: 2026-08-01

---

**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`](https://github.com/CoreBunch/Instatic/blob/main/server/db/advisoryLock.ts)

The core utility resides in [`server/db/advisoryLock.ts`](https://github.com/CoreBunch/Instatic/blob/main/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.

```typescript
// 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.

```typescript
// 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.

```typescript
// 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`](https://github.com/CoreBunch/Instatic/blob/main/server/publish/publishScheduler.ts) uses lock key `0x9a37b7a1n` to coordinate content publishing:

```typescript
// 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`](https://github.com/CoreBunch/Instatic/blob/main/server/plugins/scheduler.ts) uses a different lock key (`0x7b9c5f3en`) to manage plugin-declared periodic tasks:

```typescript
// 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`](https://github.com/CoreBunch/Instatic/blob/main/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.