How Multi-Instance HA with Postgres Advisory Locks Prevents Duplicate Scheduled Work in Instatic

Instatic uses Postgres advisory locks via the withSchedulerLeaderLock helper in server/db/advisoryLock.ts to guarantee that only one host instance executes scheduled tasks at a time, automatically releasing the lock when the leader crashes to enable seamless failover without duplicate operations.

Instatic supports high-availability (HA) deployments where multiple host processes run identical tick loops for plugins and scheduled publishes. Without coordination, every instance would perform the same work, causing duplicate content publishes and plugin executions. The CoreBunch/Instatic codebase solves this by implementing Postgres advisory locks as a lightweight, database-native leader election mechanism that ensures exactly-once execution across the cluster.

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

The core implementation resides in server/db/advisoryLock.ts, which exports withSchedulerLeaderLock. This function wraps scheduled work with atomic lock acquisition and guaranteed release.

Attempting Lock Acquisition with pg_try_advisory_lock

When a tick loop starts, withSchedulerLeaderLock internally calls tryAcquireLeader (lines 42‑51) to execute pg_try_advisory_lock using a unique bigint lockKey. If the lock is available, Postgres grants it immediately and returns a token ('pg-advisory'). If another instance holds the lock, the function returns null, causing the caller to skip the scheduled work for that tick cycle.

Automatic Release and Failover

The acquired lock is held for the entire duration of the scheduled work. The withSchedulerLeaderLock function uses a try/finally block to ensure releaseLeader (lines 69‑73) calls pg_advisory_unlock even if the worker throws an error. This guarantees the lock releases when the work completes or the instance crashes, allowing another host to acquire leadership immediately.

Scheduler Implementation Across the Codebase

Both the plugin and publish schedulers use the same lock helper with unique advisory lock keys to prevent cross-contention while ensuring single-leader execution per scheduler type.

Plugin Scheduler (server/plugins/scheduler.ts)

The plugin scheduler defines its own ADVISORY_LOCK_KEY (derived as 712830541) and wraps its tick loop with:

await withSchedulerLeaderLock(db, ADVISORY_LOCK_KEY, '[plugin-scheduler]', async () => {
  const due = await selectDueSchedules(db, nowIso, BATCH_LIMIT);
  for (const sched of due) {
    await fireSchedule(db, sched, 'tick');
  }
});

This ensures only the leader instance fires due plugin schedules (lines 41‑44).

Publish Scheduler (server/publish/publishScheduler.ts)

Similarly, the publish scheduler uses a distinct lock key (982410937) to isolate its operations:

await withSchedulerLeaderLock(db, ADVISORY_LOCK_KEY, '[publish-scheduler]', async () => {
  // Execute scheduled publishes
});

Located at lines 94‑96, this prevents duplicate scheduled publishes while allowing both schedulers to run concurrently under different locks if needed.

SQLite Fallback for Development Environments

When running against SQLite—which lacks advisory lock support—the withSchedulerLeaderLock catch block returns a sentinel token ('sqlite-leader') instead of throwing. This allows the same code to execute scheduled work in single-process development environments while maintaining HA safety guarantees in production Postgres deployments.

Summary

• Postgres advisory locks in server/db/advisoryLock.ts provide database-native leader election without external coordinators.
• The withSchedulerLeaderLock helper ensures exactly-once execution by running scheduled work only when pg_try_advisory_lock succeeds.
• Automatic failover occurs when the leader crashes because Postgres releases the advisory lock automatically when the session terminates, allowing the next instance to acquire it on the following tick.
• Unique lock keys per scheduler (e.g., 712830541 for plugins, 982410937 for publishes) prevent cross-scheduler contention while isolating failure domains.
• SQLite fallback supports local development without code branching by catching lock errors and returning 'sqlite-leader'.

Frequently Asked Questions

What happens when the leader instance crashes in an Instatic HA deployment?

Postgres automatically releases advisory locks held by terminated sessions. When the crashed instance's database connection closes, the advisory lock releases immediately, allowing another healthy instance to acquire the pg_try_advisory_lock on its next tick and assume leadership without manual intervention.

Why does Instatic use advisory locks instead of application-level locks?

Advisory locks are cluster-wide and managed by Postgres, making them resilient to application crashes and network partitions. Unlike distributed locks built on Redis or in-memory stores, advisory locks do not require additional infrastructure and are atomically released when the database session ends, eliminating the risk of stale locks that could stall the scheduler indefinitely.

How does Instatic prevent the plugin and publish schedulers from blocking each other?

Each scheduler defines a unique ADVISORY_LOCK_KEY bigint (e.g., 712830541 for plugins, 982410937 for publishes). Because Postgres advisory locks are namespaced by their key values, these schedulers acquire different locks and can execute concurrently on separate instances without contention, though each scheduler type still maintains exactly-one leader across the cluster.

Can the advisory lock mechanism work with database providers other than Postgres?

The current implementation in server/db/advisoryLock.ts specifically uses Postgres-specific functions pg_try_advisory_lock and pg_advisory_unlock. While the code includes a SQLite fallback for development environments, production HA deployments require Postgres to utilize the advisory lock feature for distributed leader election.

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