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
shared_preload_librariesneeds a restart. So docron.database_name,cron.timezoneand most othercron.*settings; onlycron.log_min_messagesandcron.launch_active_jobscan be changed with a reload.- The extension can be installed in one database per cluster. To run jobs elsewhere, use
cron.schedule_in_database()instead of installing it twice. - Schedules are evaluated in
cron.timezone, which defaults toGMT, not the server's local time. A job written as0 2 * * *by someone in Riga runs at 04:00 or 05:00 local time depending on the season. We usually keep GMT and write the local time in the job name or a comment. - In the default mode every run is a libpq connection to
localhost, sopg_hba.confmust allow it (local trust over a Unix socket viacron.host, or a.pgpassfile). Alternatively,cron.use_background_workers = onskips the connection, but then concurrency is limited bymax_worker_processes, which defaults to 8.
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.
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'$$);
- Alert on
failed, and also on jobs whose lastsucceededrun is older than their schedule. In our experience a job that cannot start at all (wrong database, brokenpg_hba.conf, extension missing after a rebuild) often leaves nothing more useful than a line in the server log, so check the log too. - Watch duration trends. A purge that took 40 seconds a year ago and takes 25 minutes now is telling you the table outgrew the approach.
cron.job_run_detailsis not cleaned automatically. Both the pg_cron README and AWS recommend a scheduled delete that keeps about a week.
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.
- Overruns queue up. Because a late run is queued rather than skipped, a job scheduled every 5 minutes that now takes 7 minutes starts running back to back, forever. Give every job headroom, and cap it with
statement_timeouton the role it runs as. - One big DELETE is one big transaction. Deleting 80 million rows in a single statement holds locks for its whole duration, generates a burst of WAL that replicas have to replay, and leaves dead tuples for vacuum. Delete in batches inside a procedure that commits between batches. PostgreSQL allows
COMMITin a procedure invoked by a top-levelCALL(transaction management). - Better still, don't delete. If you purge by time, partition by time and let the job detach and drop whole partitions. That is a metadata operation instead of millions of row deletions, see PostgreSQL table partitioning.
- DDL needs a lock timeout. A partition-maintenance job that waits for an
ACCESS EXCLUSIVElock behind a long report will block every query that queues behind it. Setlock_timeoutso the job fails fast and retries on the next run. - Connections and workers are shared. In libpq mode each run is a real connection that counts against
max_connections. In background-worker mode jobs sharemax_worker_processeswith parallel query and logical replication workers (resource settings). Thirty jobs firing at0 * * * *on a busy server is a self-inflicted connection spike; spread the minutes. - The history table becomes a hot table. A job every 5 seconds writes 17,280 rows a day into
cron.job_run_details. Purge it daily, or turncron.log_runoff for very chatty setups and rely on the server log.
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
- pg_cron needs
shared_preload_librariesand a restart, lives in one database (cron.database_name, defaultpostgres) and schedules incron.timezone, default GMT. - Name every job, use
cron.schedule_in_database()for other databases, and never grant write access tocron.job. - Monitor
cron.job_run_detailsfor failures and growing durations, and purge it: it is never cleaned automatically. - Late runs are queued, not skipped, so cap jobs with
statement_timeoutandlock_timeout. - On large tables, batch deletes with
COMMITin a procedure, or drop partitions instead of deleting rows. - Jobs do not run on standbys and resume after promotion, so make sure every node can run them.