Architecture

pg_cron: Scheduling Jobs Inside PostgreSQL Without Surprises

pg_cron turns PostgreSQL into its own scheduler: nightly purges, partition maintenance, refreshes, VACUUM at a quiet hour. It is simple to install and easy to get subtly wrong. Here is the setup, the monitoring, and what changes when the tables are large.

· 15 min read · AG Data Team

pg_cron is a cron-style job scheduler that runs inside PostgreSQL as an extension. You write a schedule and an SQL command, and a background worker runs it. No crontab on the database host, no separate scheduler box, and on most managed services it is the only scheduler you get. It is also easy to end up with jobs that silently fail, pile up behind each other, or lock a large table at the worst moment.

What pg_cron does, in one paragraph

The extension starts a background worker that reads jobs from the cron.job table. When a job is due, it either opens a normal client connection to the local server through libpq (the default) or starts a background worker, then runs the command with the permissions of the user who scheduled it. Many jobs can run in parallel, but only one instance of a given job runs at a time: if a job is still running when its next run is due, the next run is queued and starts as soon as the first one finishes (pg_cron README). That last detail matters more than anything else on this page.

Installing it

# postgresql.conf (restart required)
shared_preload_libraries = 'pg_cron'
cron.database_name = 'postgres'   # default; pg_cron lives in one database per cluster
cron.timezone = 'GMT'             # default

-- after the restart, as superuser, in cron.database_name
CREATE EXTENSION pg_cron;
GRANT USAGE ON SCHEMA cron TO app_maint;   -- optional

On Amazon RDS for PostgreSQL (12.5 and later) you add pg_cron to shared_preload_libraries in the parameter group, reboot, and run CREATE EXTENSION as a user with rds_superuser. AWS recommends keeping max_worker_processes larger than cron.max_running_jobs, because a job fails when it runs out of background workers (RDS pg_cron docs).

Scheduling jobs

-- named job: purge old rows every night at 03:30 (cron.timezone)
SELECT cron.schedule('purge-events', '30 3 * * *',
  $$CALL purge_old_events()$$);

-- a job that runs in another database
SELECT cron.schedule_in_database('vacuum-orders', '15 2 * * *',
  'VACUUM (ANALYZE) orders', 'shop');

-- last day of the month, and an interval job
SELECT cron.schedule('month-close', '0 12 $ * *', 'CALL close_month()');
SELECT cron.schedule('queue-tick', '30 seconds', 'CALL process_queue()');

-- pause, change, remove
SELECT cron.alter_job(42, active := false);
SELECT cron.alter_job(42, schedule := '0 4 * * *');
SELECT cron.unschedule('purge-events');

The schedule uses standard five-field cron syntax (the parser comes from Vixie cron), plus two extras: $ in the day-of-month field means the last day of the month, and 'N seconds' with N from 1 to 59 gives an interval job. Seconds cannot be combined with other fields.

Always name your jobs. cron.unschedule('purge-events') is much safer than remembering that job 42 is the purge and job 43 is the partition rollover. An RLS policy means regular users only see and change their own jobs; superusers and roles with bypassrls see all of them.

Do not grant write access to cron.job. AWS points out that a user who can update the table directly can change the username column and run commands as a more privileged role. Give people USAGE on the schema and let them use the functions.

Monitoring: the part most teams skip

A failed pg_cron job does not page anyone. With cron.log_run = on (the default) every run lands in cron.job_run_details with a status (running, succeeded, failed), the return message and start and end times. Read it, alert on it, and clean it up:

-- what ran in the last 24 hours, slowest first
SELECT j.jobname, d.status, d.return_message,
       d.start_time, d.end_time - d.start_time AS duration
FROM cron.job_run_details d
JOIN cron.job j USING (jobid)
WHERE d.start_time > now() - interval '1 day'
ORDER BY duration DESC NULLS FIRST;

-- failures to alert on
SELECT jobid, start_time, return_message
FROM cron.job_run_details
WHERE status = 'failed' AND start_time > now() - interval '1 hour';

-- the history table is never cleaned automatically
SELECT cron.schedule('cron-history-purge', '0 12 * * *',
  $$DELETE FROM cron.job_run_details WHERE end_time < now() - interval '7 days'$$);

What to schedule, and what not to

Good fits: deleting expired rows in batches, creating next month's partitions and detaching old ones, REFRESH MATERIALIZED VIEW CONCURRENTLY, rolling up statistics, a targeted VACUUM (ANALYZE) on a table that autovacuum keeps missing at peak hours. Poor fits: anything that must reach outside the database (files, HTTP calls, other servers), and long chains of dependent steps. Put those in a real orchestrator.

A scheduled VACUUM is a patch, not a replacement for autovacuum. If you need it, the real fix is usually per-table autovacuum settings, see tuning autovacuum to stop bloat.

On large databases: where pg_cron jobs go wrong

Everything above works on a 5 GB test database. These are the things that only show up when tables have hundreds of millions of rows and the server is busy around the clock.

CREATE OR REPLACE PROCEDURE purge_old_events(batch int DEFAULT 10000)
LANGUAGE plpgsql AS $$
DECLARE n int;
BEGIN
  LOOP
    DELETE FROM events
    WHERE id IN (SELECT id FROM events
                 WHERE event_time < now() - interval '90 days'
                 LIMIT batch);
    GET DIAGNOSTICS n = ROW_COUNT;
    EXIT WHEN n = 0;
    COMMIT;              -- allowed in a top-level CALL
  END LOOP;
END $$;

-- cap every job run as this role
ALTER ROLE app_maint SET statement_timeout = '30min';
ALTER ROLE app_maint SET lock_timeout = '5s';

Failover and replicas

pg_cron does not run jobs while the server is in hot standby, and starts automatically when the server is promoted. Since cron.job is an ordinary table, physical replicas already have the job list, so after a Patroni or managed failover the jobs keep running on the new primary, provided the new primary can actually run them. That means pg_cron in shared_preload_libraries and the same pg_hba.conf or .pgpass on every node. We check this on every HA review, because it is the kind of thing that is correct on the original primary and missing on the replica built a year later.

The README does not promise that runs missed while the server was down are executed later. In our experience it is safer to write every job so that it can run late, or twice, without harm: purge by age, create partitions with IF NOT EXISTS, refresh rather than append.

Key takeaways

PostgreSQL pg_cron Maintenance Scheduling
Work with us

Want your database maintenance jobs reviewed?

We audit pg_cron and other scheduled maintenance on production PostgreSQL: what runs, how long it takes, what it locks, and what happens to it on failover.

Get in touch More from the blog