pg_cron Monitoring: Failures, Staleness, and Bloat

2026-08-10

pg_cron runs scheduled SQL inside Postgres itself — retention purges, materialized view refreshes, partition maintenance, hourly rollups — without a separate scheduler process to babysit. The catch: it ships with almost no observability of its own. cron.job_run_details grows forever, a job that stops running produces no error, and nothing warns you before two overlapping runs deadlock on the same table. This is the monitoring layer most pg_cron tutorials skip.

▶ Watch on YouTube: pg_cron: Scheduled Jobs Inside PostgreSQL

Catching failed and slow jobs

Start with the obvious query: anything that didn't succeed in the last day.

SELECT j.jobname, d.start_time, d.status, d.return_message
FROM   cron.job_run_details d
JOIN   cron.job j USING (jobid)
WHERE  d.start_time > now() - interval '24 hours'
  AND  d.status NOT IN ('succeeded', 'running')
ORDER  BY d.start_time DESC;

That catches outright failures. It won't catch a job that's slowly getting slower until it collides with its own next run.

Runtime distribution and the p95 warning

SELECT j.jobname,
       count(*) AS runs,
       round(avg(extract(epoch FROM d.end_time - d.start_time))::numeric, 2) AS avg_s,
       round(percentile_cont(0.95) WITHIN GROUP (
         ORDER BY extract(epoch FROM d.end_time - d.start_time))::numeric, 2) AS p95_s,
       round(max(extract(epoch FROM d.end_time - d.start_time))::numeric, 2) AS max_s
FROM   cron.job_run_details d
JOIN   cron.job j USING (jobid)
WHERE  d.start_time > now() - interval '7 days'
  AND  d.status = 'succeeded'
GROUP  BY j.jobname
ORDER  BY p95_s DESC;

Watch p95 creeping toward the schedule interval. That's the early warning for the overlap problem, weeks before it becomes an outage.

Staleness: the check that actually saves you

A job that never runs produces no failure row, so a failure-only alert is blind to the worst case: the scheduler stopped ticking, or someone set active = false during an incident and never turned it back on. A single blanket threshold across every job is also a trap — it lets an hourly rollup sit stale for most of a day before anything fires. Give each job its own expectation:

CREATE TABLE maintenance.job_expectation (
  jobname text PRIMARY KEY,
  max_age interval NOT NULL
);
INSERT INTO maintenance.job_expectation VALUES
  ('audit-purge', interval '26 hours'),
  ('refresh-daily-sales', interval '90 minutes'),
  ('rollup-hourly', interval '90 minutes');

CREATE OR REPLACE VIEW maintenance.stale_cron_jobs AS
SELECT e.jobname, j.active, e.max_age,
       max(d.end_time) FILTER (WHERE d.status = 'succeeded') AS last_success,
       now() - max(d.end_time) FILTER (WHERE d.status = 'succeeded') AS age
FROM   maintenance.job_expectation e
LEFT   JOIN cron.job j ON j.jobname = e.jobname
LEFT   JOIN cron.job_run_details d ON d.jobid = j.jobid
GROUP  BY e.jobname, j.active, e.max_age
HAVING max(d.end_time) FILTER (WHERE d.status = 'succeeded') IS NULL
    OR now() - max(d.end_time) FILTER (WHERE d.status = 'succeeded') > e.max_age;

Alert on any rows in that view. Setting application_name inside each function, as in the purge procedure above, means running jobs are identifiable in pg_stat_activity, and with pg_stat_statements you can attribute cost by queryid back to the job that generated it.

The history table is the first thing that fills your disk

cron.job_run_details is never pruned. Not on a schedule, not on a size threshold, never. The README says so plainly and tells you to schedule a job to clean it up, which most people skim past.

Do the arithmetic. A job on /1 * produces 1,440 rows a day. Five such jobs plus a handful of hourlies is comfortably 8,000 rows a day, and each row is inserted as running and then updated to succeeded, so every run also produces a dead tuple. Add a command column carrying a multi-kilobyte inline SQL string, and the table plus its bloat grows steadily for months on a partition sized for a database that was supposed to be static. I have seen this table alone hold tens of gigabytes on a cluster where nobody could explain the disk trend.

SELECT pg_size_pretty(pg_total_relation_size('cron.job_run_details')) AS total,
       (SELECT count(*) FROM cron.job_run_details) AS rows;

The fix is a meta-job: pg_cron pruning its own history.

SELECT cron.schedule('prune-cron-history', '30 3 * * *', $
  DELETE FROM cron.job_run_details
  WHERE end_time < now() - interval '30 days'
$);

Thirty days is my default: long enough to see a monthly job's last few runs, short enough to stay small. You can set cron.log_run = off, but understand what you're buying. That trades your entire cron observability story for disk you could have reclaimed with one scheduled delete.

Overlap, timeouts and connection budget

pg_cron has no retries, no backoff, no per-job timeout, no dependency graph. It fires SQL on a schedule. Everything else is on you.

Self-exclusion: pg_try_advisory_lock at the top of the function. It returns true or false immediately rather than waiting, which is precisely what you want when a run should be skipped rather than queued.
Timeouts: set statement_timeout and lock_timeout inside the function, or on a dedicated role: ALTER ROLE cron_runner SET statement_timeout = '10min';
Connections: keep cron.max_running_jobs well under max_connections. Every concurrently running job holds a session, and those sessions compete with your application for the same pool.

A pile-up is easy to spot once you're tagging sessions:

SELECT pid, application_name, state, wait_event_type, wait_event,
       now() - query_start AS runtime, left(query, 60)
FROM   pg_stat_activity
WHERE  application_name LIKE 'cron:%'
ORDER  BY query_start;

Ten sessions running the same purge, nine of them waiting on a lock held by the first, is the classic shape. Cancel with pg_cancel_backend(pid) first; it lets the transaction unwind cleanly. Only escalate to pg_terminate_backend if cancel doesn't take. Then pause the job with cron.alter_job(..., active := false) before you start diagnosing, or it will fire again while you're typing.

Replicas, failover and managed services

cron.job is an ordinary heap table in a regular database, which means physical streaming replication and base backups carry it for free. Jobs execute on whichever node is the primary, so after a promotion the new primary already holds the schedule. That's the behaviour you want, and it's the main reason pg_cron beats a crontab on a single app server.

It also means a split brain double-runs your deletes. If an old primary comes back up still thinking it's primary, its pg_cron background worker starts firing too. Your fencing needs to be genuine, not aspirational.

Logical replication does not carry cron.job unless you explicitly add it to a publication, which you almost certainly do not want, because then the subscriber would run the same schedule against its own copy.

On managed platforms, pg_cron is available on Amazon RDS and Aurora for PostgreSQL, Azure Database for PostgreSQL Flexible Server, Google Cloud SQL and Supabase. On all of them you enable it through a parameter group, server parameter or database flag rather than ALTER SYSTEM, and the equivalent of cron.database_name is set the same way. Check which pg_cron version your provider ships before you rely on cron.timezone (1.6+) or sub-minute schedules (1.5+).

Where pg_cron ends

| | pg_cron | system crontab | Airflow / Dagster | pgAgent | |---|---|---|---|---| | Lives with the database | yes, in cron.job | no, on a host | no, external | in a Postgres schema, external agent | | Survives failover | yes, follows the primary | no | needs connection reconfig | agent must be moved | | Retries and backoff | none | none | yes | limited | | Cross-system dependencies | none | none | yes | none | | Multi-step jobs with branching | no | scripted | yes | step sequences | | Backfills | no | no | yes | no | | Runs when the DB is down | no | yes | yes | no | | Operational cost | near zero | low | a service to run | an agent to run |

pg_cron wins for single-database, idempotent, one-statement-or-one-function maintenance. It loses the moment you need retries with backoff, a dependency graph across systems, backfills, sub-second timing guarantees, or a job that must run precisely when the database is unavailable. Backup verification belongs outside the database, always. Don't try to make pg_cron into an orchestrator; run both and let each do what it's good at.

The pre-production checklist

1. cron.timezone set explicitly, or every schedule written in GMT on purpose. 2. Every job calls a function or procedure, no inline SQL strings over one line. 3. statement_timeout and lock_timeout set inside each function or on the cron role. 4. pg_try_advisory_lock guard on anything that could outlive its interval. 5. A pruning job on cron.job_run_details, and a size check on it in your disk monitoring. 6. Alert on failed runs in the last 24 hours. 7. Alert on staleness, so a job that never ran raises something. 8. Failover behaviour understood, and fencing that actually fences. 9. A dedicated role with USAGE ON SCHEMA cron and only the privileges its jobs need. 10. All cron.schedule() calls in migrations, so the schedule is code and rebuilds are reproducible.

If you would rather have failed and stalled cron jobs show up next to your vacuum backlog and disk-capacity trends without writing the alerting yourself, that's the estate view MyDBA gives you.