pg_cron Tutorial: Schedule Postgres Jobs and Monitor Them

TL;DR

• pg_cron is an open-source Postgres extension (Citus Data, now Microsoft), PostgreSQL-licensed, at github.com/citusdata/pg_cron. A background worker wakes every minute and runs SQL on a five-field cron schedule.
• Install is three lines and a restart: shared_preload_libraries = 'pg_cron', restart, CREATE EXTENSION pg_cron;.
• First three jobs worth scheduling: retention (batched delete or partition drop), partition maintenance, materialized view refresh. A fourth, the hourly rollup, replaces a surprising amount of pipeline tooling.
• You do not need to build your own job logging. cron.log_run is on by default and every run lands in cron.job_run_details.
• Four things that bite: schedules are UTC, cron.database_name needs a restart, cron.job_run_details grows forever, and VACUUM fails inside a multi-statement job body.
• I covered this in a 5-minute whiteboard video: https://www.youtube.com/watch?v=ku7K7lusTuY. This post is the written, deeper version with SQL you can actually paste.

📖 Read the full guide: pg_cron Monitoring: Failures, Staleness, and Bloat

Why schedule inside the database at all

The default answer is a line in the system crontab calling psql. It works until it doesn't. You now have credentials on a box, a job that keeps firing happily against a demoted standby, and job history in a log file that nobody has read since the person who wrote it left.

The other default answer is Airflow. Airflow is genuinely good at cross-system pipelines with dependencies and retries. For "delete rows older than 90 days" it is a second production system to patch.

pg_cron's pitch is narrow and I like it: the schedule is data in a table, it travels with the cluster under physical replication, and the run history is queryable with the same SQL and dashboards you already point at everything else.

Where pg_cron is the wrong tool: multi-system DAGs, task dependencies, retries with exponential backoff, anything that needs to touch S3 and Postgres in one transactional-ish flow. It has no dependency graph. If you find yourself encoding one in cron expressions, stop.

Install: four lines, and the two settings people get wrong

pg_cron runs as a background worker, so it has to be preloaded before CREATE EXTENSION will work.

ALTER SYSTEM SET shared_preload_libraries = 'pg_cron';
-- restart the server here. Not reload. Restart.
CREATE EXTENSION pg_cron;

Setting one people get wrong: cron.database_name. The pg_cron worker connects to exactly one database, defaulting to postgres, and changing it requires a restart. If your app lives in appdb and you installed the extension there but never changed the setting, nothing runs and nothing errors visibly.

For multi-database clusters, leave the worker where it is and use cron.schedule_in_database() (pg_cron 1.4+):

SELECT cron.schedule_in_database(
  'purge-appdb-events', '17 3 * * *',
  $$SELECT app.purge_old_events()$$,
  'appdb', 'app_maint', true);

Setting two: cron.use_background_workers. By default jobs run over a libpq connection to cron.host (localhost), which eats a regular max_connections slot. Turn background workers on and they draw from max_worker_processes instead. Either way, size the pool. cron.max_running_jobs caps concurrency.

Platform	How to set it
Amazon RDS / Aurora	DB parameter group: shared_preload_libraries, cron.database_name
Azure Flexible Server	Server parameters blade, then CREATE EXTENSION
Cloud SQL	cloudsql.enable_pg_cron flag

How it actually runs your SQL

The worker ticks once a minute, reads cron.job, and launches whatever is due. One-minute granularity is the floor. There is no sub-minute scheduling and no catch-up for missed ticks.

A running job looks like any other backend:

SELECT pid, usename, state, now() - query_start AS runtime, left(query, 60)
FROM pg_stat_activity WHERE application_name LIKE 'pg_cron%' OR query LIKE '%purge_old%';

  pid  | usename   | state  |    runtime      |                    left
-------+-----------+--------+-----------------+---------------------------------------------
 20481 | app_maint | active | 00:22:14.881203 | SELECT app.purge_old_events()

Kill it with SELECT pg_cancel_backend(20481); and it will show up as failed in the run history, which is what you want.

Job 1: retention that doesn't lock the table for 40 minutes

The naive version, straight from the video:

SELECT cron.schedule('nightly-purge', '0 2 * * *',
  $$DELETE FROM events WHERE created_at < now() - interval '90 days'$$);

 schedule
----------
        4

That returns a bigint jobid. Fine for a small table. On 200 million rows it holds one enormous transaction, bloats WAL, and leaves dead tuples for autovacuum to chew through. Deleted space is not returned to the OS, it becomes reusable only after vacuum.

Batch it:

CREATE OR REPLACE FUNCTION app.purge_old_events(p_batch int DEFAULT 10000)
RETURNS bigint LANGUAGE plpgsql
SET statement_timeout = '30min'
SET lock_timeout = '5s'
AS $$
DECLARE removed bigint := 0; n int;
BEGIN
  LOOP
    DELETE FROM events WHERE ctid IN (
      SELECT ctid FROM events
      WHERE created_at < now() - interval '90 days'
      LIMIT p_batch);
    GET DIAGNOSTICS n = ROW_COUNT;
    removed := removed + n;
    EXIT WHEN n = 0;
    COMMIT;
  END LOOP;
  RETURN removed;
END $$;

Better still: partition by time and drop the old partition. That is a catalog and file operation, not a row-by-row scan, and it leaves nothing for autovacuum. Retention by DROP PARTITION beats retention by DELETE every time you can arrange it — this is the pattern behind most postgres data retention jobs that actually scale.

Inspect and remove jobs:

SELECT jobid, schedule, jobname, active FROM cron.job;

 jobid | schedule  |     jobname      | active
-------+-----------+------------------+--------
     4 | 0 2 * * * | nightly-purge    | t
     7 | */5 * * * *| refresh-daily-mv | t

SELECT cron.unschedule(4); or SELECT cron.unschedule('nightly-purge');.

Job 2: partition maintenance (pg_partman does this better than you will)

Hand-rolled works: a function that creates next month's partition and detaches plus drops anything past retention, scheduled 0 3 1 * *. Use ALTER TABLE ... DETACH PARTITION CONCURRENTLY on PG14+ so you aren't holding ACCESS EXCLUSIVE on the parent while a reporting query finishes.

The answer most shops land on is pg_partman, whose documented model is to call run_maintenance_proc() on a schedule:

SELECT cron.schedule('partman-maintenance', '@hourly',
  $$CALL partman.run_maintenance_proc()$$);

That single line handles future partition creation and retention.

Job 3: schedule refresh materialized view postgres without blocking readers

REFRESH MATERIALIZED VIEW CONCURRENTLY lets SELECTs keep running against the view during the refresh. Two prerequisites people forget: it needs at least one UNIQUE index covering all rows, and it will not work on a view that has never been populated.

CREATE UNIQUE INDEX ON reporting.daily_sales (sale_date, region);
REFRESH MATERIALIZED VIEW reporting.daily_sales;  -- populate once, non-concurrently

SELECT cron.schedule('refresh-daily-sales', '*/15 * * * *',
  $$REFRESH MATERIALIZED VIEW CONCURRENTLY reporting.daily_sales$$);

CONCURRENTLY is not free. It does a diff against the existing contents and can be dramatically slower on large views. Time both forms before you commit.

SELECT max(sale_date), now() - max(sale_date) AS staleness
FROM reporting.daily_sales;

Wrap job bodies in functions

Put the job body in a function. Inline SQL in cron.job is how you end up with a 400-character DELETE that nobody can read, nobody can test, and nobody can find in version control.

The SET clause on CREATE FUNCTION pins execution settings for the duration of the call, which makes it the cleanest place to bound a scheduled job. Add an advisory lock for anything you never want overlapping with itself or with a manual run — a fifteen-minute job scheduled every ten minutes will eventually stack two copies on top of each other without one:

CREATE OR REPLACE FUNCTION app.rollup_hourly() RETURNS void LANGUAGE plpgsql
SET statement_timeout = '10min'
SET lock_timeout = '5s'
SET application_name = 'cron:rollup_hourly'
AS $$
BEGIN
  IF NOT pg_try_advisory_lock(88101) THEN
    RAISE NOTICE 'rollup already running, skipping'; RETURN;
  END IF;

  INSERT INTO events_hourly (bucket, event_type, n)
  SELECT date_trunc('hour', created_at), event_type, count(*)
  FROM events
  WHERE created_at >= (SELECT coalesce(max(bucket), '-infinity') FROM events_hourly)
  GROUP BY 1, 2
  ON CONFLICT (bucket, event_type) DO UPDATE SET n = EXCLUDED.n;

  PERFORM pg_advisory_unlock(88101);
END $$;

That watermark makes it idempotent and safely re-runnable. It is an entire ETL pipeline, and you don't need Airflow for it yet.

pg_cron monitoring: cron.job_run_details is your job log

The most common misconception I run into is that pg_cron gives you no visibility and you have to build your own logging table. Not true. cron.log_run is on by default, and every run is recorded in cron.job_run_details with jobid, runid, job_pid, database, username, command, status, return_message, start_time and end_time.

SELECT jobid, runid, status, return_message, start_time
FROM cron.job_run_details WHERE status = 'failed' ORDER BY start_time DESC LIMIT 1;

 jobid | runid | status |                    return_message                     |         start_time
-------+-------+--------+-------------------------------------------------------+----------------------------
    11 | 90422 | failed | ERROR: VACUUM cannot run inside a transaction block    | 2026-08-09 06:00:00.114+00

Failures in the last 24 hours:

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

Slowest jobs, p95:

SELECT jobid,
       percentile_cont(0.95) WITHIN GROUP (ORDER BY end_time - start_time) AS p95
FROM cron.job_run_details
WHERE start_time > now() - interval '7 days' GROUP BY jobid ORDER BY p95 DESC;

Stale jobs, meaning scheduled but not succeeding:

CREATE VIEW ops.stale_cron_jobs AS
SELECT j.jobid, j.jobname, j.schedule, max(d.end_time) FILTER (WHERE d.status='succeeded') AS last_ok
FROM cron.job j LEFT JOIN cron.job_run_details d USING (jobid)
WHERE j.active GROUP BY 1,2,3
HAVING coalesce(max(d.end_time) FILTER (WHERE d.status='succeeded'), '-infinity') < now() - interval '25 hours';

Now the mandatory part. Nothing prunes cron.job_run_details. The README says so plainly, and I once inherited a cluster where it had reached the low millions of rows and was the largest table in the maintenance database — the nightly backup job was slower because it was scanning that table too. Schedule the prune before you schedule anything else.

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

If you don't want history at all, set cron.log_run = off, though you lose the audit trail along with it. cron.log_statement additionally writes the command to the server log if you want it there too.

Gotchas I've been bitten by

Schedules are UTC. Not server local time, not your session TimeZone. 0 2 * * * fires at 02:00 UTC, which is 21:00 the previous day in America/New_York during EDT and 22:00 during EST. My "2am maintenance window" ran during Tuesday evening peak for three weeks before anyone connected the dots. Write the UTC offset into the job name if it helps.

VACUUM in a multi-statement body fails. VACUUM ANALYZE t alone is fine. ANALYZE a; VACUUM b; in one job body runs as one implicit transaction and errors with VACUUM cannot run inside a transaction block. One statement per job.

Long jobs don't double-fire. Schedule SELECT pg_sleep(300) every minute and verify for yourself; you get one run, not five.

Jobs run as the role that scheduled them. Schedule as a dedicated maintenance role with the grants it needs. Since 1.4 a superuser can GRANT USAGE ON SCHEMA cron to a non-superuser so that role manages its own jobs.

The extension lives in one database. See cron.database_name above.

Standbys are read-only. cron.job and cron.job_run_details replicate physically, but only the primary executes. After a failover, re-verify shared_preload_libraries and cron.database_name on the promoted node — a failover with no shared_preload_libraries entry on the new primary means silence, not an error. I keep that as an explicit step in the runbook.

Errors are invisible unless someone queries for them. Point your alerting at the failures query. pg_cron will not page you.

When to reach for something else

Tool	Dependencies	Retries	Cross-system	Failover aware	Observability
pg_cron	no	no	no	follows the primary	SQL over job_run_details
cron + psql	no	no	yes	no	log files
pgAgent	steps, sequential	limited	shell steps	no	its own tables
pg_timetable	chains	yes	shell/HTTP	no	its own tables
Airflow / Dagster	full DAG	yes, backoff	yes	external	rich UI
K8s CronJob	no	pod-level	yes	external	cluster logs

The starter kit

-- postgresql.conf: shared_preload_libraries = 'pg_cron', cron.database_name = 'appdb'; restart
CREATE EXTENSION IF NOT EXISTS pg_cron;

-- 1. history prune. Do this first, always.
SELECT cron.schedule('purge-cron-history', '5 4 * * *',
  $$DELETE FROM cron.job_run_details WHERE end_time < now() - interval '30 days'$$);

-- 2. retention (function from above), 02:00 UTC
SELECT cron.schedule('purge-old-events', '0 2 * * *', $$SELECT app.purge_old_events()$$);

-- 3. matview refresh, needs a unique index and one prior non-concurrent refresh
SELECT cron.schedule('refresh-daily-sales', '*/15 * * * *',
  $$REFRESH MATERIALIZED VIEW CONCURRENTLY reporting.daily_sales$$);

-- 4. hourly rollup
SELECT cron.schedule('rollup-hourly', '7 * * * *', $$SELECT app.rollup_hourly()$$);

-- 5. monitoring
CREATE OR REPLACE VIEW ops.cron_failures_24h AS
SELECT j.jobname, d.status, d.return_message, d.start_time
FROM cron.job_run_details d JOIN cron.job j USING (jobid)
WHERE d.start_time > now() - interval '24 hours' AND d.status <> 'succeeded';

SELECT * FROM ops.cron_failures_24h;

If you'd rather not build these dashboards yourself, tools like MyDBA (https://mydba.dev/?utm_source=devto&utm_medium=platform&utm_campaign=pg-cron-tutorial-schedule-monitor-postgres-jobs) wrap this kind of cron.job_run_details monitoring into something you can hand to the team.

What is the worst thing you have scheduled in cron.job? Mine involved a 400-character DELETE and a comma in the wrong place.

Tags: postgres database sql devops ## Where this leaves you

pg_cron won't build you a DAG, retry a failed API call, or coordinate a multi-service pipeline — and it shouldn't try to. What it does is take the boring 80% of database housekeeping (retention, partition rotation, refreshes, rollups) and keep it exactly where that logic belongs: in the database, versioned as functions, visible in cron.job_run_details, replicated with the cluster. Start with the four jobs above, prune your own history table before you forget, and point something at the failures view so you find out about a broken job before your users do.

pgdba Editorial builds MyDBA, a Postgres monitoring and health-check tool — https://mydba.dev/?utm_source=devto&utm_medium=platform&utm_campaign=pg-cron-tutorial-schedule-monitor-postgres-jobs

If you're already scheduling jobs with pg_cron, give MyDBA a look — it'll flag stale jobs, bloated cron.job_run_details tables, and the other things this post just told you to watch for, without you writing the queries by hand.