Cheat sheet · Whole systems
Design a job scheduler
The problem
"Send the digest at 09:00", "remind me tomorrow", "sync every 5 minutes": millions of delayed and recurring jobs for many tenants. We go from one cron box to a jobs table claimed with `SKIP LOCKED`, leases with heartbeats, idempotent at-least-once runs, retries with backoff and a dead-letter table, then survive the 09:00 spike with time partitions and per-tenant fairness.
What you learn
- Why a single cron box both misses jobs and runs them twice after failover
- How workers claim due rows safely with
FOR UPDATE SKIP LOCKED - Leases and heartbeats: why they give at-least-once, and why that needs idempotency keys
- Backoff with jitter, a dead-letter table, time partitions and fairness between tenants
Diagram as code
The lesson's final design as text: paste it into Jira, GitLab, GitHub, Notion or your docs, where Mermaid renders by itself. There are D2 and PlantUML (Confluence, IntelliJ) versions too.
Mermaid: flowchart
flowchart LR
n_app(["Tenant app"])
n_mail>"Email provider"]
n_api["Scheduler API"]
n_db[("Jobs table")]
n_w1("Worker 1")
n_w2("Worker 2")
n_dlq[("Dead jobs")]
n_app2(["Big tenant"])
n_app --> n_api
n_api --> n_db
n_w1 --> n_db
n_w2 --> n_db
n_w1 --> n_mail
n_w2 --> n_mail
n_w2 --x n_dlq
n_app2 --> n_apiMermaid: request sequence
sequenceDiagram
participant n_app as Tenant app
participant n_cron as Cron box A
participant n_cron2 as Cron box B
participant n_mail as Email provider
participant n_api as Scheduler API
participant n_db as Jobs table
participant n_w1 as Worker 1
participant n_w2 as Worker 2
participant n_dlq as Dead jobs
Note over n_app,n_cron: One machine, one crontab
n_app->>n_cron: $ crontab -l #35; on cron-a
Note over n_cron2,n_mail: The box dies, then comes back twice
n_cron2->>n_mail: 08:12 cron-a unreachable, jobs 08:15..08:50 never ran
Note over n_api,n_db: The schedule becomes rows with a due time
n_api->>n_db: INSERT INTO jobs -- the row, as JSON
n_db-->>n_api: INSERT 0 1
Note over n_w1,n_db: Claim rows with FOR UPDATE SKIP LOCKED
n_w1->>n_db: WITH picked AS (
n_db-->>n_w1: 50 rows, 3.1 ms
Note over n_w1,n_db: A dead worker strands its jobs
n_w1->>n_db: SELECT id, locked_by, now() - updated_at AS stuck
n_db-->>n_w1: j_8840 #124; w-2 #124; 00:47:12
Note over n_w1,n_db: A lease that expires unless renewed
n_w1->>n_db: UPDATE jobs
n_db-->>n_w1: lease_until = 09:00:41+05
Note over n_w1,n_mail: Every run carries an idempotency key
n_w1->>n_mail: POST /v1/messages HTTP/1.1
n_mail-->>n_w1: HTTP/1.1 200 OK
Note over n_w2,n_mail: The provider fails, workers hammer it
n_w2->>n_mail: POST /v1/messages HTTP/1.1
n_mail-->>n_w2: HTTP/1.1 503 Service Unavailable
Note over n_w2,n_dlq: Exponential backoff, jitter and a dead-letter table
n_w2->>n_dlq: 09:41:07 WARN job j_9310 attempt 8/8: 550 mailbox does not exist
Note over n_w2,n_db: Everyone wants 09:00 sharp
n_w2->>n_db: SELECT tenant, count(*) AS due, max(now() - run_at) AS late
n_db-->>n_w2: t_9 1 480 220 00:14:51
Note over n_w1,n_db: Hourly partitions and a slice per tenant
n_w1->>n_db: SELECT j.id FROM active_tenants t
n_db-->>n_w1: 50 rows from 10 tenants, 2.4 msD2
direction: right
n_app: "Tenant app" {
shape: rectangle
}
n_mail: "Email provider" {
shape: cloud
}
n_api: "Scheduler API" {
shape: rectangle
}
n_db: "Jobs table" {
shape: cylinder
}
n_w1: "Worker 1" {
shape: rectangle
style.multiple: true
}
n_w2: "Worker 2" {
shape: rectangle
style.multiple: true
}
n_dlq: "Dead jobs" {
shape: cylinder
}
n_app2: "Big tenant" {
shape: rectangle
}
n_app -> n_api
n_api -> n_db
n_w1 -> n_db
n_w2 -> n_db
n_w1 -> n_mail
n_w2 -> n_mail
n_w2 -> n_dlq {
target-arrowhead.shape: cross
}
n_app2 -> n_apiPlantUML: request sequence
@startuml
participant "Tenant app" as n_app
participant "Cron box A" as n_cron
participant "Cron box B" as n_cron2
boundary "Email provider" as n_mail
participant "Scheduler API" as n_api
database "Jobs table" as n_db
participant "Worker 1" as n_w1
participant "Worker 2" as n_w2
database "Dead jobs" as n_dlq
note over n_app, n_cron : One machine, one crontab
n_app -> n_cron : $ crontab -l # on cron-a
note over n_cron2, n_mail : The box dies, then comes back twice
n_cron2 -> n_mail : [duplicate] 08:12 cron-a unreachable, jobs 08:15..08:50 never ran
note over n_api, n_db : The schedule becomes rows with a due time
n_api -> n_db : INSERT INTO jobs -- the row, as JSON
n_db --> n_api : INSERT 0 1
note over n_w1, n_db : Claim rows with FOR UPDATE SKIP LOCKED
n_w1 -> n_db : WITH picked AS (
n_db --> n_w1 : 50 rows, 3.1 ms
note over n_w1, n_db : A dead worker strands its jobs
n_w1 -> n_db : SELECT id, locked_by, now() - updated_at AS stuck
n_db --> n_w1 : j_8840 | w-2 | 00:47:12
note over n_w1, n_db : A lease that expires unless renewed
n_w1 -> n_db : UPDATE jobs
n_db --> n_w1 : lease_until = 09:00:41+05
note over n_w1, n_mail : Every run carries an idempotency key
n_w1 -> n_mail : POST /v1/messages HTTP/1.1
n_mail --> n_w1 : HTTP/1.1 200 OK
note over n_w2, n_mail : The provider fails, workers hammer it
n_w2 -> n_mail : [retry] POST /v1/messages HTTP/1.1
n_mail --> n_w2 : HTTP/1.1 503 Service Unavailable
note over n_w2, n_dlq : Exponential backoff, jitter and a dead-letter table
n_w2 -> n_dlq : 09:41:07 WARN job j_9310 attempt 8/8: 550 mailbox does not exist
note over n_w2, n_db : Everyone wants 09:00 sharp
n_w2 -> n_db : SELECT tenant, count(*) AS due, max(now() - run_at) AS late
n_db --> n_w2 : t_9 1 480 220 00:14:51
note over n_w1, n_db : Hourly partitions and a slice per tenant
n_w1 -> n_db : SELECT j.id FROM active_tenants t
n_db --> n_w1 : 50 rows from 10 tenants, 2.4 ms
@endumlPlantUML: components
@startuml
left to right direction
agent "Tenant app" as n_app
cloud "Email provider" as n_mail
component "Scheduler API" as n_api
database "Jobs table" as n_db
collections "Worker 1" as n_w1
collections "Worker 2" as n_w2
database "Dead jobs" as n_dlq
agent "Big tenant" as n_app2
n_app --> n_api
n_api --> n_db
n_w1 --> n_db
n_w2 --> n_db
n_w1 --> n_mail
n_w2 --> n_mail
n_w2 -[#c62828]-> n_dlq
n_app2 --> n_api
@endumlRecap
A scheduler is a database of due times plus workers that cannot be trusted to stay alive.
1) One cron box misses jobs while down and runs them twice after failover. 2) Store jobs as rows with run_at in UTC; workers claim with FOR UPDATE SKIP LOCKED. 3) Claims are leases renewed by heartbeat, on the database clock; expiry brings stranded jobs back. 4) That makes runs at least once, so every side effect carries an idempotency key. 5) Retry by rescheduling with exponential backoff and jitter; park the rest in a dead-letter table and alert on it. 6) Partition by time, drop old partitions, and claim a slice per tenant so 09:00 is fair.
Numbers: at the start and at the end
| Metric | Start | End |
|---|---|---|
| Start lag p99 | 1 s | 3 s |
| Jobs not run | 0/day | 0/day |
| Jobs run twice | 0/day | 0/day |
Steps
- Case One machine, one crontab
Tenants schedule work through the app: a daily digest at 09:00, a rate sync every five minutes, a one-off reminder for tomorrow.
- Problem The box dies, then comes back twice
At 08:12 cron-a loses its network.
- Fix The schedule becomes rows with a due time
The API now inserts each job as a row: what to run, for which tenant, and
run_at, stored in UTC. - Fix Claim rows with
FOR UPDATE SKIP LOCKEDEach worker claims a batch in one statement.
- Problem A dead worker strands its jobs
Worker 2 is killed at 09:00:05 with 50 jobs claimed.
- Fix A lease that expires unless renewed
A claim now gives a lease:
lease_until30 seconds ahead. - Fix Every run carries an idempotency key
The worker sends each email with a key built from the job id and the scheduled time:
j_8812:2026-11-19T04:00Z. - Problem The provider fails, workers hammer it
At 09:00 the email provider starts answering
503for ten minutes. - Fix Exponential backoff, jitter and a dead-letter table
A failed job goes back to
queuedwithrun_atpushed out: 30 s, 1 min, 2 min, doubling up to 32 min, each delay shifted randomly by up to 20%. - Problem Everyone wants 09:00 sharp
Most people pick round times, and one big tenant
t_9scheduled 1.5 million reminders for 09:00. - Fix Hourly partitions and a slice per tenant
The jobs table is partitioned by
run_athour. - Recap Recap
A scheduler is a database of due times plus workers that cannot be trusted to stay alive.
Terms
- SKIP LOCKED
- An addition to FOR UPDATE that skips rows another transaction has locked instead of waiting, so workers never block each other.
- Lease
- A time-limited claim on a job that expires unless the owner renews it with heartbeats, so a dead worker's job is handed to another.
- Time partitioning
- Splitting a table into hourly or daily pieces; queries touch only the current piece and old pieces are dropped whole.