pg_cron in Production: The Job That Ran Three Times at Once

The hourly roll-up had been running at seven minutes past the hour for eight months, twenty minutes a run, without a single page. Then one Thursday a deploy made the underlying query slower — a missing index, unrelated, the kind of thing that happens — and the run time stretched past the hour mark. pg_cron did not care. At the next tick it started a second copy, and an hour later a third, and by 04:00 three copies of the same aggregation were overwriting the same summary table, each one slower than the last because they were now competing for it. The summary data that morning was a palimpsest of three half-finished runs, and the lock waits on the summary table were the only alert that fired. Weeks later, while writing the runbook for that incident, I found the second problem: two other jobs in cron.job had been failing on every run for six weeks, and nobody knew, because pg_cron records failures in a table and tells no one.

Neither of these is a bug. Both are the documented behavior of a tool that does exactly what it says and no more. This is what running it for real taught us: the overlap guard, the monitoring query, and the upgrade checklist.

How does pg_cron actually run a job?

A background worker — the cron launcher — lives inside the postmaster, wakes up once a minute, and starts any job whose schedule is due. Each run is a real database session against the database the job was scheduled in, authenticated as the role that scheduled it, with that role's privileges and, critically, that role's search_path. Two operational facts fall out of this immediately. First, every running job consumes a connection slot against max_connections, and cron.max_running_jobs — default 32 — is a ceiling on how many it will start at once; on a database already near its connection ceiling, a pile-up of cron jobs can be the straw that starts refusing application connections, which is the same failure shape as a connection storm with a different arsonist. Second, because a run is an ordinary session, it shows up in pg_stat_activity like any other backend, it holds locks like any other backend, and it can block and be blocked. There is nothing magical protecting your job from the rest of your traffic, or your traffic from your job.

The launcher itself only runs in one database — cron.database_name, postgres by default — and that is where cron.job and cron.job_run_details live. Jobs, however, can be scheduled to execute against other databases on the cluster, which is the normal multi-tenant setup. Worth knowing before you go looking for your job definitions in the wrong database's catalog.

How do you stop a job from overlapping itself?

You build the guard yourself, because pg_cron deliberately does not: if a run is still active when the next tick arrives, a second run starts, full stop. The pattern we settled on is a session-scoped advisory lock taken at the top of the job, with an explicit skip if the previous run still holds it:

SELECT cron.schedule(
  'rollup-hourly',
  '7 * * * *',
  $job$
    DO $do$
    BEGIN
      IF NOT pg_try_advisory_lock(hashtext('rollup-hourly')) THEN
        RAISE NOTICE 'previous rollup still running, skipping this tick';
        RETURN;
      END IF;
      PERFORM public.rollup_hourly();
    END
    $do$;
  $job$
);

The lock is session-scoped, so it is released automatically when the job's session ends — no cleanup path to get wrong, no stale lock after a crash. The decision you have to make is skip-versus-wait. pg_try_advisory_lock skips the tick, which is right for idempotent roll-ups where a missed hour is self-healing on the next run. For jobs where every tick must eventually execute, take a blocking pg_advisory_lock instead and let the runs serialize — but then you have accepted an unbounded queue, and the run time of the job becomes a hard operational budget, because a job that consistently runs longer than its interval will queue forever. Whichever you pick, make the skip visible: the RAISE NOTICE lands in the log and in job_run_details, and "we skipped 40 percent of ticks this week" is a capacity signal worth graphing, not a non-event.

Why are pg_cron failures silent, and how do you watch them?

Because the only place a failure is durably recorded is cron.job_run_details — a table, in a database, that nobody queries unless they already suspect something. The run's status column says succeeded or failed, return_message holds the error text, and with cron.log_run on, every run leaves a row. The gap is that nothing pushes failed runs anywhere; pg_cron is a scheduler, not an alerting system. The fix is a monitoring query so simple it feels silly to have learned it from an incident:

SELECT j.jobname,
       r.status,
       r.start_time,
       r.end_time,
       left(r.return_message, 200) AS error
FROM cron.job_run_details r
JOIN cron.job j USING (jobid)
WHERE r.status <> 'succeeded'
  AND r.start_time > now() - interval '24 hours'
ORDER BY r.start_time DESC;

Run the same shape for slow runs — end_time minus start_time against a per-job budget — and you have both of my opening incidents covered by two queries. One maintenance note: job_run_details grows forever if you let it. We keep ninety days with a daily delete that is itself a pg_cron job, which is either elegant or the setup for a future post about the day the cleanup job failed silently.

What breaks around pg_cron: roles, paths, time zones, upgrades?

The role question first, because it is a security foot-gun, not just an operational one. A job executes as the role that scheduled it, so jobs scheduled by a migration superuser run with superuser rights forever, long after anyone remembers scheduling them. We schedule under a dedicated app_cron role that owns exactly the procedures the jobs call, and every job body sets its search_path explicitly — the failing jobs from my second incident died because they referenced tables unqualified, and a well-meaning change to the scheduling role's search_path broke resolution in a way that only showed up in return_message. Fully qualify, or SET search_path inside the job command, every time.

Time zones second. cron.timezone defaults to GMT, not your server's timezone, and a job written as "02:30 daily" in GMT will walk through your local night as daylight saving shifts — we had a statistics job land in the middle of the morning batch twice a year until we set the value explicitly. Upgrades third: pg_cron is an extension with shared libraries, so a major version upgrade requires the new build installed before pg_upgrade will proceed, and ALTER EXTENSION pg_cron UPDATE after cutover — the same extension dance as any upgrade, but easier to forget because cron jobs are invisible until they miss. And failover: the launcher follows the primary, so after any promotion, verify the extension is installed and enabled on the new primary and that cron.job contains what you think it does. That verification is two queries and belongs in the promotion runbook.

What should not live in pg_cron at all?

Anything that needs exactly-once semantics, sub-minute precision, or a retry policy. pg_cron's tick granularity is one minute, its failure handling is a row in a table, and its execution guarantee is at-least-once with a side of maybe-overlapping. Vacuum and analyze scheduling, partition maintenance, roll-ups, materialized view refreshes, and cleanup deletes are its home turf — periodic, idempotent, tolerant of a skipped beat. Billing runs, customer-facing notifications, and anything where a duplicate execution is an incident belong in an external scheduler with real delivery semantics. The materialized view refresh case deserves the overlap guard above by default: a refresh that runs past the next tick is exactly how our Thursday started.

Watching scheduled jobs with MonPG

A pg_cron job is a database session doing database work, so everything that makes it dangerous is already counter-shaped: connection count against max_connections when runs pile up, lock waits on the tables jobs touch, per-statement latency for the procedures jobs call, and the job_run_details failure and duration queries above exported as checks. MonPG tracks exactly these series — backend counts, lock contention, per-statement timing from pg_stat_statements — as part of its PostgreSQL monitoring, so the three-copies-at-04:00 scenario shows up as three concurrent executions of the same statement before it shows up as corrupted summaries. Schedule the jobs in the database if that keeps operations simple — just monitor them like the production workload they are.