pg_cron in Production: Queued Runs, Duplicate Jobs, and Failure Monitoring

pg_cron runs at most one instance of a particular job at a time. If its next scheduled run arrives before the current run finishes, that run waits. A slow hourly job therefore builds a backlog; it does not automatically create three concurrent copies of the same job.

Duplicate job definitions and separate callers are a different problem. Two job IDs can execute the same procedure, and an application or operator can call it while a scheduled job is active. Diagnose which case you have before adding a lock or changing the schedule.

Is the job slow, queued, or duplicated?

Start with the definitions in cron.job and the history in cron.job_run_details. Compare runtime with the intended interval, inspect active sessions in pg_stat_activity, and look for multiple job IDs calling the same operation. A growing backlog calls for query tuning, a longer interval, or a smaller batch; another lock does not make the work faster.

SELECT jobid, jobname, schedule, database, username, active, command
FROM cron.job
ORDER BY jobid;

SELECT jobid, status, start_time, end_time,
       end_time - start_time AS duration,
       left(return_message, 200) AS result
FROM cron.job_run_details
WHERE start_time > now() - interval '24 hours'
ORDER BY start_time DESC;

Different jobs can run concurrently. With connection-based execution they consume database connection capacity; background-worker mode has a different worker budget. Check the installed version and cron.use_background_workers before sizing concurrency. The extension catalog lives in the configured scheduling database, while jobs can target other databases.

When does an advisory lock help?

An advisory lock can coordinate different job IDs or other callers that voluntarily use the same lock. It is additional protection for a shared operation, not a replacement for pg_cron’s per-job serialization. This example runs a transaction-scoped guard inside the job:

SELECT cron.schedule(
  'rollup-hourly',
  '7 * * * *',
  $job$
    DO $do$
    BEGIN
      IF NOT pg_try_advisory_xact_lock(74123, 1) THEN
        RETURN;
      END IF;
      PERFORM public.rollup_hourly();
    END
    $do$;
  $job$
);

Reserve the example lock key for this operation and require every participating caller to use it. The transaction releases the lock when it ends. The example assumes public.rollup_hourly() is an existing function that can run within this transaction. It does not suppress later queued runs of the same job. Record skipped work in an application-owned status table if operators need to distinguish a skip from a successful roll-up.

How do you detect failures and missing runs?

With run logging enabled, inspect failed executions and alert on stale successful completion times. Checking only errors misses a disabled job or a scheduler that never started. Do not classify every non-success status as a failure: a currently running job has not yet finished.

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

Set a retention policy for run history after deciding how much incident evidence you need. Run the alerting check from a separate monitoring path so a broken scheduler cannot also silence its own alarm.

What should the deployment runbook verify?

Use a dedicated scheduling role, qualify object names, review the configured time zone, and check connection authentication. After extension upgrades or a promotion, verify job definitions and recent successful executions. Rehearse these checks alongside the PostgreSQL upgrade procedure.

Current pg_cron supports second-based schedules as well as cron expressions; the old claim that every schedule has one-minute granularity is incorrect. Frequent scheduling still does not provide a business-level exactly-once contract. Operations such as billing need idempotency and recovery rules regardless of the scheduler.

Rehearse the failure cases before changing the schedule

Use a staging copy or a disposable database to test the job’s recovery contract. First, make one invocation deliberately take longer than the interval and observe the history for that job ID. Measure how long queued work takes to catch up after the slow operation ends. If every invocation keeps exceeding the interval, increasing the number of application replicas cannot fix the scheduler backlog: reduce the amount of work per invocation, improve the query, or choose a schedule the workload can sustain.

Next, test two separately named jobs that call the same operation. This is the case an application-level lock is meant to coordinate. Confirm that a caller which cannot obtain the lock leaves the target data unchanged, records an understandable outcome, and does not leave a transaction open. Then invoke the operation manually through the same entry point. A lock inside only one scheduling wrapper cannot protect another caller that bypasses that wrapper.

Test interruption at a realistic transaction boundary as well. If a job updates several tables in one transaction, confirm the intended rollback behavior after cancellation. If it commits in batches, record a durable checkpoint and prove that replaying the last batch does not double-count rows. These are properties of the job implementation; a successful scheduling API call is not evidence that they hold.

Finally, separate three monitoring questions: did the scheduler attempt the work, did the database command finish, and did the expected data become available? A successful SQL statement can process zero rows because its input is missing. Track a business-level watermark, such as the latest completed reporting period, alongside execution duration and error counts. Give each alert an owner and a runbook link so a missed refresh does not remain a green scheduler chart and an outdated report.

Write down the rollback before adjusting a live schedule. Keep the previous definition, avoid leaving a second enabled job behind during a rename, and check the job inventory after a deployment. Review dependencies between cleanup, ingestion, and roll-up jobs explicitly: running them at different clock times does not prove that one has finished before the next begins.

Keep credentials and privileges out of the job body

Treat a scheduled command as deployed application code. Review it in version control, give its database role only the privileges the operation requires, and avoid embedding credentials or customer data in literal SQL. Job definitions and error messages are operational records that other administrators may need to inspect; they are a poor place to store secrets.

Before promoting a revised job, record the installed extension version, its execution mode, the expected owner, and the monitoring query used to verify completion. Preserve enough history to compare the previous and revised runtime under similar load. If the revised job processes less data, a shorter duration alone does not demonstrate an improvement: compare the completed workload and data watermark too.

Connect job history to database evidence

Correlate slow runs with query plans, waits, connection pressure, and locks. Use PostgreSQL monitoring alongside the job-history queries above, and inspect connection storms when scheduled workloads compete with application traffic.

Related documentation

• pg_cron: scheduling and single-job concurrency