Job scheduler

Before any worker loop, model the queue as a Postgres table and decide what 'durable' means. Each job carries state (pending → claimed → done → failed → dead), a run_at timestamp, an attempt count, and a payload. The whole point of using Postgres-as-queue is that state is inspectable with a SELECT — no black-box broker — but it means the due-time scan is your hottest query, so it must ride a partial index on (run_at) WHERE state = 'pending', not a sequential scan over a growing table. Size it: at 500 jobs/s enqueue with a 7-day retention you carry ~300M rows, so the index — and a partitioning or archival plan — is not optional. Enqueue must be a single committed INSERT so a crash between accept and persist loses nothing.

Make delivery survive a worker dying mid-job. A worker claims a due job by flipping it to 'claimed' with a lease that expires (the visibility timeout) — concurrent workers must not grab the same row, so the claim is an atomic UPDATE ... WHERE state='pending' ... RETURNING with FOR UPDATE SKIP LOCKED. If the worker crashes before finishing, the lease expires and the job becomes claimable again: that is at-least-once, and it is the only honest guarantee a durable queue can cheaply offer. Tune the timeout against your p99 job duration — too short and a slow-but-alive worker gets its job stolen and run twice; too long and a real crash leaves the job stuck for minutes. Long jobs extend the lease with a heartbeat (e.g. renew every 10s on a 30s visibility timeout) so liveness, not a fixed guess, drives reclaim.

A failed job must retry — but naive retries are a self-inflicted outage. Fixed-interval retries from thousands of jobs synchronize into a thundering herd that re-saturates the dependency that just failed; exponential backoff (e.g. 1s, 2s, 4s, 8s, capped at 5min) spreads them, and full jitter (sleep = random(0, backoff)) breaks the synchronization so retries don't all fire on the same tick. Cap the attempt budget: after N attempts (commonly 5–8) the job is poison and must move to a dead-letter queue rather than retry forever and clog the lane. The DLQ is a queryable table you alert on by depth — a rising DLQ depth is your earliest signal that a downstream is broken or a payload is malformed.

Add recurring schedules and you import the hardest problem in the system: time. A cron entry (e.g. '*/5 * * * *') must materialize into concrete run_at rows, and the materializer itself runs on more than one node for availability — so two schedulers can compute the same fire time and enqueue the same minute twice. Clock skew between nodes makes 'is it due yet?' ambiguous near the boundary, and a daylight-saving jump or a leap second can skip or double a window. The fix is to make the fire deterministic and deduplicated: derive a per-occurrence key (schedule_id + the bucketed fire timestamp) and enqueue with a unique constraint so a double-compute collapses to one row. Decide your timezone and DST policy explicitly — 'run at 02:30 local' is a question, not an instant, on the night the clock springs forward.

At-least-once delivery is settled; now make duplicate delivery harmless. You cannot get exactly-once delivery cheaply, but you can get exactly-once effect: every handler takes an idempotency key and records it in a dedup store inside the same transaction as its side-effect, so a replay is a no-op. The subtlety is the atomicity boundary — if the work and the dedup-key write are in separate commits, a crash between them either double-applies (key written after effect, but effect not idempotent) or replays forever (effect done, key not written). For effects outside Postgres (sending an email, calling a payment API) the dedup record and the local state must commit together and the external call must itself be keyed, or you fall back to the outbox pattern: commit intent locally, then deliver. Prove it: replaying any job twice produces exactly one side-effect.

Run it under load and make it legible, then break it. Many workers polling one table contend on the same hot rows, so claims must use SKIP LOCKED and you must reason about poll interval versus latency (tight polling burns DB CPU; long polling adds delay). Add priority lanes — high-priority jobs jump the queue — but a pure priority column starves low-priority work, so promote by age (weighted fair queuing or oldest-eligible-within-budget) so nothing waits forever. Instrument RED on the worker loop: claim rate, failure rate, and job duration, plus queue depth and oldest-unclaimed-age as the SLO that actually matters. Then run the incident: a poison-pill job (a payload that crashes the handler every attempt) combined with a clock change that fires a thundering herd of due jobs at once — workers crash-loop on the poison job while the herd backs up the lane. Detect it from oldest-unclaimed-age and DLQ depth, mitigate live (route the poison to DLQ fast, fairness keeps the lane moving), then write the root cause.