Skip to main content

ADR 0033 — Postgres Claim-Queue Scheduling with SKIP LOCKED and a Unique-Occurrence Index

Status: Accepted Context date: 2026-08-06 Builds on: ADR 0032 (service boundary)

Context​

The scheduler must fire due schedules from multiple identical replicas, with no duplicate fires, no lost fires across crashes and deploys, and a durable, tenant-queryable run history (the history is a product feature, not telemetry). The core architectural choice is where the "what is due, who runs it" state lives and how replicas coordinate.

Decision​

Two-stage, Postgres-anchored, claim-based, leaderless​

Stage 1 — Planner. A 1-second tick in every replica claims due schedules and materialises occurrences:

BEGIN
  SELECT … FROM schedule
   WHERE status='active' AND next_fire_at <= now() + interval '5 seconds'
   ORDER BY next_fire_at
   LIMIT 500
   FOR UPDATE SKIP LOCKED;
  -- per row, in the same tx:
  INSERT INTO job_run (…, scheduled_for, idempotency_key) … ON CONFLICT DO NOTHING;
  UPDATE schedule SET next_fire_at = <next occurrence>, last_fire_at = …;
COMMIT

Lease maintenance (amended 2026-08-17, Zero-Trust review). A lease is a statement that this delivery is in progress, not a deadline for a whole batch, and three rules keep it honest:

  1. Claim no more than the pool can start. The lease clock starts at the claim, so a batch larger than the number of idle workers guarantees that its tail loses the lease while queued — the reaper re-queues runs the process is still about to deliver, and every one of them goes out twice. The claim size is bounded by idle workers, not by SCHEDULER_EXECUTOR_BATCH alone.
  2. Renew while in flight, abandon when lost. Ownership is re-asserted before the request leaves and refreshed for its duration; losing it cancels the request in flight and discards the result rather than writing a second executor's outcome.
  3. Every write on a leased run carries claimed_by. Terminal, retry, release, and defer writes all predicate on the lease still being ours (release additionally, so a draining instance cannot decrement the attempt count of a run another replica re-claimed). A run whose terminal transaction fails is driven to failed by a minimal write instead of being left running — otherwise the reap → claim → redeliver cycle is self-sustaining and no terminal state ever reaches the breaker. Attempt numbers are clamped to the 1..8 evidence-row ceiling for the same reason.

Stage 2 — Executor. A worker pool claims job_run rows where status IN ('pending','retrying') AND fire_after <= now() AND (claim_expires_at IS NULL OR claim_expires_at < now()) with FOR UPDATE SKIP LOCKED, takes a lease (claimed_by, claim_expires_at = now() + 2×delivery timeout), delivers, and writes the terminal state plus attempt rows in one transaction.

Every replica runs the same loops. No leader election, no lock service, no shard assignment. Duplicate-fire prevention rests on two independent guards:

  1. FOR UPDATE SKIP LOCKED + the next_fire_at advance in one transaction — only one replica can advance a given occurrence.
  2. UNIQUE (schedule_id, scheduled_for) on job_run — the hard guard against double-claim, backwards clock jumps, or operator replans.

All due-time decisions use Postgres now() — one clock authority; pods export scheduler_clock_skew_seconds and alert at > 2 s.

Documented escape hatch (and its trigger)​

Postgres-as-queue tops out at roughly a few thousand claims/second on one primary. The expected worst case — 100k schedules all on 0 * * * * — is ~1.7k runs/second sustained for a minute, comfortably inside that with batched claims. If growth exceeds it: the planner keeps writing job_run rows (source of truth is unchanged) and additionally publishes run IDs to a Kafka topic keyed by tenant_id; executors become consumers; only the claim step changes, the domain model does not.

Alternatives Considered​

  • In-memory timers per schedule (Quartz/gocron style). Rejected: requires shard assignment and rebalancing on pod churn, warm-up after restart, and loses pending fires on crash. Durability would have to be re-invented on top.
  • Redis ZSET delay queue. Rejected: Redis is "never a source of truth" (docs/03), and durable run history is a product feature — we would write the same Postgres rows anyway, plus a second coordination layer.
  • Kafka delayed-message / retry-topic ladder. Rejected: retries need per-run backoff and mutable, queryable state; topic ladders are an operational tarpit. Kafka remains the escape hatch for claim distribution, not the state store.
  • Kubernetes CronJob per tenant schedule. Rejected outright: unbounded cluster objects controlled by untrusted tenant input. Never.

Consequences​

  • Positive: Crash-safety comes free from transactional semantics — a planner or executor dying mid-work leaves either a durable next_fire_at or a reclaimable lease, never a lost occurrence.
  • Positive: Leaderless replicas mean HA is replicaCount: 2 and a PDB, with no coordination infrastructure to operate.
  • Positive: The run table doubles as the tenant-facing history API's storage — no separate audit pipeline.
  • Negative / accepted: Throughput is bounded by one Postgres primary. Bounded, measured, and with a pre-designed exit that changes only the claim step.
  • Negative / accepted: job_run is the highest-volume table in the service; it is range-partitioned monthly with detach-based retention (docs/03) to keep the claim index hot and vacuum sane.
  • Operational: Autoscaling keys on scheduler_runs_due_backlog (runs due, not yet terminal), not CPU — the executor is IO-bound (see docs/11 conventions).