Scheduled and Delayed Work
Cron, a delay queue and a due-at column solve three different scheduling problems; picking the wrong one is the usual mistake.
schedule: "0 3 * * *" // nicknames expand to the same thing: @daily = 0 0 * * *, @hourly = 0 * * * *Three different problems get called scheduling. A settlement report runs at a fixed wall-clock time. A reminder three days from now is one future instant belonging to one entity. A retry in thirty seconds is a short deferral. They want different mechanisms.
Picking the mechanism
Cron — a Kubernetes CronJob, a systemd timer, a hosted scheduler — fits fixed times and nothing else; it has no notion of per-entity state. A delay queue fits short deferrals: SQS DelaySeconds hides a message for up to 900 seconds, which covers retries but not days, and on FIFO queues the delay can only be set on the queue, not per message. Anything further out, cancellable, reschedulable or visible to users belongs in a table with a due_at column and a poller, because a row can be queried, edited and audited and a message in flight cannot.
Exactly one instance should run it
Three replicas running the same schedule send three reports. Either make the scheduler a singleton or make the work claim itself: SELECT ... FOR UPDATE SKIP LOCKED over due rows, or an advisory lock around the run. In Kubernetes, concurrencyPolicy: Forbid skips a run whose predecessor is still going, and .spec.timeZone (stable since Kubernetes 1.27) stops a schedule drifting an hour when the clocks change.
When the run outlasts the interval
A five-minute job on a one-minute schedule overlaps itself and competes with itself for the same rows. Decide explicitly between skipping and queueing, and give the job a timeout shorter than its interval.
Example
// A due_at table rather than a delayed message, because reminders can be
// cancelled, rescheduled and listed - all ordinary SQL on a row.
//
// CREATE TABLE scheduled_jobs (
// id bigserial PRIMARY KEY, kind text NOT NULL, payload jsonb NOT NULL,
// due_at timestamptz NOT NULL, state text NOT NULL DEFAULT 'pending',
// claimed_at timestamptz, attempts int NOT NULL DEFAULT 0
// );
// CREATE INDEX ON scheduled_jobs (due_at) WHERE state = 'pending';
async function claimDueJobs(limit = 50) {
const client = await pool.connect();
try {
await client.query("BEGIN");
// SKIP LOCKED (PostgreSQL 9.5 and later) lets every poller instance run this
// at the same time and get disjoint rows, with no coordination at all.
const { rows } = await client.query(
`SELECT id, kind, payload
FROM scheduled_jobs
WHERE state = 'pending'
AND due_at <= now()
ORDER BY due_at
LIMIT $1
FOR UPDATE SKIP LOCKED`,
[limit]
);
if (rows.length > 0) {
await client.query(
`UPDATE scheduled_jobs
SET state = 'claimed', claimed_at = now(), attempts = attempts + 1
WHERE id = ANY($1)`,
[rows.map((r) => r.id)]
);
}
await client.query("COMMIT");
return rows;
} catch (err) {
await client.query("ROLLBACK");
throw err;
} finally {
client.release();
}
}
// A sweeper is mandatory, not optional: a row left in 'claimed' by a crashed
// worker is stranded for ever without one.
async function reclaimStuckJobs() {
await pool.query(
`UPDATE scheduled_jobs
SET state = 'pending', claimed_at = NULL
WHERE state = 'claimed'
AND claimed_at < now() - interval '15 minutes'`
);
}
// Alarm on due_at: the number of rows where state = 'pending' AND due_at < now()
// is the scheduler's backlog, and it should be close to zero.When to use it
- Send a trial-ending reminder three days out that must be cancelled if the user upgrades.
- Stop three replicas of a worker each emailing the same nightly report.
- Retry a failed payment in thirty seconds without a scheduler or a table.
- Keep a five-minute aggregation job on a one-minute schedule from overlapping itself.
More examples
A Kubernetes CronJob with the overlap settings
Every field here exists because of an incident. Forbid prevents two settlements racing over the same rows; activeDeadlineSeconds stops a hung run blocking all later ones forever; the history limits stop finished pods accumulating until the namespace is unreadable. Use Replace instead of Forbid only when the newest run supersedes the old one entirely, such as rebuilding a cache.
apiVersion: batch/v1
kind: CronJob
metadata:
name: nightly-settlement
spec:
# Nicknames are accepted and expand to ordinary expressions - they are the same
# schedule written two ways:
# @hourly = 0 * * * * @daily = 0 0 * * * @weekly = 0 0 * * 0
schedule: "0 3 * * *"
# .spec.timeZone is stable from Kubernetes 1.27. Without it the schedule is
# interpreted in the controller manager's zone, so a job set up in summer runs an
# hour out after the clocks change.
timeZone: "Europe/Lisbon"
concurrencyPolicy: Forbid # skip a run while the previous one is still going
startingDeadlineSeconds: 600 # a run more than 10 minutes late is abandoned
successfulJobsHistoryLimit: 3
failedJobsHistoryLimit: 3
jobTemplate:
spec:
backoffLimit: 2 # pod-level retries before the Job is failed
activeDeadlineSeconds: 3000 # shorter than the interval: kill an overrun
template:
spec:
restartPolicy: Never
containers:
- name: settlement
image: registry.example.com/settlement:1.14.0
args: ["node", "jobs/settlement.js"]A short deferral belongs to the broker
Use this for retries and for anything under a quarter of an hour, where a table and a polling loop are pure overhead. The 900-second ceiling is the thing to check before designing around delay queues; the FIFO restriction is the thing that breaks a working design when someone converts a standard queue to FIFO for ordering reasons.
// Short deferrals are the broker's job: no table, no poller, no clock of your own.
// DelaySeconds hides the message for up to 900 seconds (15 minutes). There is no
// larger value, so "remind me in three days" cannot be expressed this way - and
// chaining 288 delayed messages to get there is a design smell, not a workaround.
await sqs.send(new SendMessageCommand({
QueueUrl: process.env.RETRY_QUEUE_URL,
MessageBody: JSON.stringify({ orderId, attempt: attempt + 1 }),
DelaySeconds: 30,
}));
// On a FIFO queue this per-message field is rejected. FIFO supports only a
// queue-level delay, set as the DelaySeconds queue attribute - and changing that
// attribute is retroactive, so it also moves messages that are already waiting.
// Delayed messages are counted separately from the backlog, as
// ApproximateNumberOfMessagesDelayed. A dashboard that only plots visible messages
// shows an empty queue while thousands of deferred retries are pending.One instance of a schedule, without a scheduler
The cheapest mutual exclusion available when the database is already there, and it needs no extra component — unlike a Redis lease, where lock expiry during a long job is its own well-documented problem. Use it to wrap any run that must happen once: a nightly report, a migration backfill, a reconciliation sweep. The pooling caveat is the part that turns it from a safeguard into a false sense of safety.
-- pg_try_advisory_lock returns false instead of waiting, so losing instances exit
-- immediately rather than queueing up to run the same report.
SELECT pg_try_advisory_lock(hashtext('nightly-settlement')) AS acquired;
-- ... run the job only when that returned true ...
SELECT pg_advisory_unlock(hashtext('nightly-settlement'));
-- The lock is session-scoped. That cuts both ways:
-- * a crashed runner drops its connection and releases the lock, which is what
-- you want;
-- * a connection pool may hand your next statement to a different connection,
-- which does not hold the lock, and the unlock then fails silently.
-- Take one dedicated connection for the whole run, outside the pool.
Discussion