Reliable Scheduled Jobs

By Michael Samuel Naeem · December 4, 2024 · 5 min read

0 2 * * * /app/nightly-reconcile.sh works on your laptop. In production you have three Kubernetes pods, a deploy at 2:01 AM, and a pod that restarted mid-run. Now reconciliation ran twice, or not at all, and finance finds a $40k discrepancy on Monday. Reliable scheduled jobs need execution tracking, leader election, overlap protection, and explicit policies for missed runs — not crontab entries on every instance.

Failure modes of naive cron

Execution tracking table

CREATE TABLE job_executions (
    id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    job_name     VARCHAR(100) NOT NULL,
    scheduled_at TIMESTAMPTZ NOT NULL,
    started_at   TIMESTAMPTZ,
    finished_at  TIMESTAMPTZ,
    status       VARCHAR(20) DEFAULT 'pending',
    error        TEXT,
    UNIQUE (job_name, scheduled_at)
);

The unique constraint on (job_name, scheduled_at) prevents duplicate runs for the same scheduled window.

Leader election with Postgres

async function tryAcquireLeadership(jobName: string, lockDurationSec: number): Promise<boolean> {
  const result = await db.execute(sql`
    SELECT pg_try_advisory_lock(hashtext(${jobName}))
  `);
  return result.rows[0].pg_try_advisory_lock === true;
}

async function runScheduledJob(jobName: string, scheduledAt: Date, fn: () => Promise<void>) {
  if (!await tryAcquireLeadership(jobName, 300)) return;

  try {
    const inserted = await db
      .insert(jobExecutions)
      .values({ jobName, scheduledAt, status: 'running', startedAt: new Date() })
      .onConflictDoNothing()
      .returning();

    if (inserted.length === 0) return; // already ran

    await fn();

    await db.update(jobExecutions)
      .set({ status: 'completed', finishedAt: new Date() })
      .where(eq(jobExecutions.jobName, jobName));
  } catch (error) {
    await db.update(jobExecutions)
      .set({ status: 'failed', error: String(error), finishedAt: new Date() })
      .where(eq(jobExecutions.jobName, jobName));
    throw error;
  } finally {
    await db.execute(sql`SELECT pg_advisory_unlock(hashtext(${jobName}))`);
  }
}

Skip-if-running (overlap protection)

async function runWithOverlapGuard(jobName: string, fn: () => Promise<void>) {
  const running = await db
    .select()
    .from(jobExecutions)
    .where(and(
      eq(jobExecutions.jobName, jobName),
      eq(jobExecutions.status, 'running'),
      gt(jobExecutions.startedAt, new Date(Date.now() - 3600_000))
    ));

  if (running.length > 0) {
    logger.warn(`${jobName} still running, skipping`);
    return;
  }

  await runScheduledJob(jobName, truncateToHour(new Date()), fn);
}

Missed run catch-up

async function catchUpMissedRuns(jobName: string, intervalMs: number, fn: (scheduledAt: Date) => Promise<void>) {
  const lastRun = await db
    .select({ scheduledAt: jobExecutions.scheduledAt })
    .from(jobExecutions)
    .where(and(eq(jobExecutions.jobName, jobName), eq(jobExecutions.status, 'completed')))
    .orderBy(desc(jobExecutions.scheduledAt))
    .limit(1);

  const lastScheduled = lastRun[0]?.scheduledAt ?? new Date(0);
  const now = new Date();
  let next = new Date(lastScheduled.getTime() + intervalMs);

  while (next <= now) {
    await runScheduledJob(jobName, next, () => fn(next));
    next = new Date(next.getTime() + intervalMs);
  }
}

Cap catch-up iterations — if downtime was 7 days and interval is hourly, process the last N windows and alert on the gap.

Managed alternatives

For simple nightly jobs, Postgres advisory locks + execution table is enough. For jobs with steps, retries, and compensation, use Temporal.

Monitoring

Alert on:

• Job not completed within expected window (+15 min grace)
• Failed status in execution log
• Catch-up backlog > threshold
• Job duration trending up (early warning)

Exactly-once cron execution

Prevent duplicate runs when multiple app instances exist:

import hashlib
from datetime import datetime

def acquire_job_lock(job_name: str, db) -> bool:
    window = datetime.utcnow().strftime("%Y-%m-%d-%H")  # hourly window
    lock_key = hashlib.sha256(f"{job_name}:{window}".encode()).hexdigest()

    try:
        db.execute(
            "INSERT INTO job_locks (lock_key, acquired_at) VALUES (%s, NOW())",
            (lock_key,)
        )
        return True  # acquired
    except IntegrityError:
        return False  # another instance holds lock

def run_scheduled_job(job_name: str, fn):
    if not acquire_job_lock(job_name, db):
        logger.info(f"Skipping {job_name} — already running on another instance")
        return
    try:
        fn()
        db.execute(
            "UPDATE job_executions SET status='completed', completed_at=NOW() WHERE job_name=%s",
            (job_name,)
        )
    except Exception as e:
        db.execute(
            "UPDATE job_executions SET status='failed', error=%s WHERE job_name=%s",
            (str(e), job_name)
        )
        raise

PostgreSQL advisory lock or unique constraint on (job_name, window) — both work. Choose based on existing infrastructure.

Timezone and DST handling

Cron schedules are timezone-sensitive:

import pytz
from croniter import croniter

TZ = pytz.timezone("America/New_York")

def next_run(cron_expr: str) -> datetime:
    now = datetime.now(TZ)
    return croniter(cron_expr, now).get_next(datetime)

# "0 2 * * *" in America/New_York
# DST spring forward: 2 AM doesn't exist → run at 3 AM
# DST fall back: 2 AM happens twice → run once (lock prevents duplicate)

Store cron timezone explicitly in job definition. Never assume UTC without documenting it.

Missed run catch-up policy

Define behavior when scheduler was down:

CATCHUP_POLICY = {
    "nightly_report": {"max_missed": 1, "action": "run_latest"},      # run once on recovery
    "hourly_sync":    {"max_missed": 3, "action": "run_all"},        # catch up all missed
    "weekly_cleanup": {"max_missed": 0, "action": "skip"},           # skip if missed
}

Alert when catch-up queue exceeds threshold — indicates extended downtime or slow job execution.

Failure modes

• No distributed lock — duplicate execution on multi-instance deployment
• Cron in UTC, team expects local time — jobs run at wrong hour twice yearly (DST)
• Unbounded catch-up — 7 days downtime → 168 hourly runs queued on recovery
• No execution log — failed jobs undetected until downstream data missing
• Long-running job overlaps next schedule — double execution; skip if previous still running

Production checklist

• Distributed lock (advisory lock or unique constraint) prevents duplicate execution
• Timezone stored explicitly in job definition
• Catch-up policy defined per job (run_latest vs run_all vs skip)
• Execution log with status, started_at, completed_at, error
• Alert: job not completed within expected window + 15 min grace
• Skip next run if previous execution still in progress

Resources

• PostgreSQL advisory locks
• Kubernetes CronJob documentation
• AWS EventBridge Scheduler
• Sidekiq periodic jobs
• Temporal schedules API