pg_cron: Scheduling Maintenance Inside PostgreSQL Without Losing the Plot

The 2 a.m. DELETE that lives on a laptop

Every database I inherit has maintenance SQL scattered in at least three places. There is a crontab -l on the primary that nobody has read since the last host migration. There is a Jenkins job called db-cleanup-nightly whose credentials point at a role that was deprecated in 2023. And there is an Airflow DAG that runs a single psql -c task, wrapped in enough Python to make you think it does something clever.

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

▶ Watch the video walkthrough: pg_cron: Scheduled Jobs Inside PostgreSQL
https://www.youtube.com/watch?v=ku7K7lusTuY

None of that is visible from inside the database. You cannot SELECT it. It does not come along when you fail over, restore, or migrate to a new instance class. The gap between "what the box does at 2 a.m." and "what the database thinks is happening" is where a lot of production surprises live.

pg_cron closes that gap by putting the schedule where the data is. Job definitions live in a regular table. Run history lives in another regular table. Both are queryable, both are backed up, both show up in code review if you treat them seriously.

This is the runbook: exact install commands, the jobs worth stealing verbatim, the six things that bite people in production, and the SQL to monitor jobs like any other query.

What pg_cron actually is, and what it is not

pg_cron is an open-source extension originally built by Citus Data, now part of Microsoft, maintained at github.com/citusdata/pg_cron under the PostgreSQL license. It runs as a background worker inside your Postgres cluster. The worker wakes up once a minute, looks at the table cron.job, and starts whatever is due.

That is the entire mental model. A per-minute tick and a table.

Now the boundaries, stated up front, because they are the reason people get hurt:

No DAGs. No dependencies between jobs. Job B does not wait for job A.
No retries. A failed run is a failed run.
No catch-up. If the server was down at 02:00, the 02:00 run is gone. It does not run at 02:07 when you come back up.

One-minute granularity on the classic five-field syntax. pg_cron 1.5 added interval-style schedules like '30 seconds', but before that a minute is the floor.
Run history grows forever unless you prune it yourself.

pg_cron is a scheduler. It is not a workflow engine. If someone on your team is drafting a proposal to replace Airflow with it, stop them now and save everyone a quarter.

What it is genuinely good at: database-local maintenance that should never have needed an external system in the first place. Retention deletes. Partition drops. Materialized view refreshes. Hourly rollups. A targeted vacuum on the one table autovacuum keeps losing to.

Install it correctly the first time

pg_cron must be loaded via shared_preload_libraries. That is a postmaster-level parameter, which means a full restart. Not a reload. Not SELECT pg_reload_conf(). A restart.

In postgresql.conf:

shared_preload_libraries = 'pg_cron'
cron.database_name = 'appdb'

If you already load other libraries, keep them in the list:

shared_preload_libraries = 'pg_stat_statements,pg_cron'

Then restart and create the extension in exactly the database named by cron.database_name:

-- connect to appdb, as superuser
CREATE EXTENSION pg_cron;

Skip the preload step and you get this:

ERROR:  pg_cron can only be loaded via shared_preload_libraries
HINT:  Add pg_cron to shared_preload_libraries configuration variable in postgresql.conf.

That error means the extension's init hook ran outside of the shared preload phase. There is no workaround, no session-level LOAD. Edit the conf, restart, try again. A pg_ctl reload won't get you there either — it re-reads postgresql.conf but never touches postmaster-level settings like this one, so pg_cron will look "installed" and do nothing until the instance actually bounces.

The single-database rule trips people constantly. pg_cron keeps its metadata in one database, controlled by cron.database_name, which defaults to postgres. If you leave the default and then run CREATE EXTENSION pg_cron inside appdb, you have created the extension in the wrong place and the worker will not read your jobs. Pick one, write it down, and use cron.schedule_in_database() when a job needs to touch a different database:

SELECT cron.schedule_in_database(
  'refresh-reporting-mv',
  '15 * * * *',
  $$REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_revenue$$,
  'reporting'   -- target database
);

Managed services

pg_cron is supported on AWS RDS and Aurora PostgreSQL, Azure Database for PostgreSQL Flexible Server, and Google Cloud SQL for PostgreSQL. The mechanics differ slightly, the constraints do not:

RDS / Aurora: add pg_cron to shared_preload_libraries in the custom parameter group, reboot the instance.
Azure Flexible Server: enable it under server parameters (shared_preload_libraries), restart.
Cloud SQL: set the cloudsql.enable_pg_cron flag, restart.

All three require a restart. All three default the metadata database to postgres. On managed platforms you usually cannot make the extension owner a true superuser, so read the permissions section below before you plan your roles.

Anatomy of a job

Three functions cover 95% of daily use.

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

The three-argument form (name, schedule, command) landed in pg_cron 1.4 and it is the only form I use. Named jobs are greppable, they are stable across environments, and re-running cron.schedule() with the same name updates the existing job instead of creating a duplicate.

cron.schedule() returns the integer jobid. You can use it, but you rarely need to, because everything accepts the name too.

Dollar quoting ($$...$$) is not optional in practice. The moment your command contains a string literal (and it will, every interval literal is one) you are into escape-quote hell. Use $$, or $job$ if the body itself contains $$.

Inspect what is scheduled:

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

Pause without deleting:

SELECT cron.alter_job(job_id := (SELECT jobid FROM cron.job WHERE jobname = 'purge-events'),
                      active := false);

cron.alter_job() can change the schedule, the command, or the active flag. I strongly prefer setting active = false over unscheduling. A disabled job is a documented decision; a deleted job is a hole in the runbook that someone fills back in six months later with slightly different SQL.

Remove for real:

SELECT cron.unschedule('purge-events');
-- or
SELECT cron.unschedule(42);

The schedule field

Standard five-field cron: minute, hour, day-of-month, month, day-of-week.

Schedule	Meaning
`* * * * *`	Every minute
`5 * * * *`	Hourly, at five past
`0 2 * * *`	Daily at 02:00
`30 3 * * 0`	Sundays at 03:30
`0 4 1 * *`	04:00 on the first of the month
`/15 * * *`	Every fifteen minutes
`'30 seconds'`	Every 30 seconds (pg_cron 1.5+ interval form)

Remember what "hourly" means here: the worker ticks once a minute and fires what is due. There is no sub-minute scheduling on the five-field syntax, and there never will be.

The four jobs worth stealing

1. Retention delete with guardrails

SELECT cron.schedule(
  'purge-audit-log',
  '20 4 * * *',
  $$
  SET statement_timeout = '10min';
  SET lock_timeout = '5s';
  DELETE FROM audit_log
  WHERE ctid IN (
    SELECT ctid FROM audit_log
    WHERE created_at < now() - interval '180 days'
    LIMIT 50000
  );
  $$
);

Two settings, both non-negotiable. statement_timeout bounds how long the job can run at all. lock_timeout bounds how long it waits for a lock before giving up, which is the difference between "the purge failed tonight" and "the purge queued behind an idle transaction and stalled every write for eleven minutes." I have never regretted setting both. More on that in the war story.

The LIMIT-by-ctid pattern deletes one chunk per run. Run it every ten minutes instead of once a day if you have a big backlog to work off. Deleting 40 million rows in one statement produces one enormous transaction, a WAL spike, and a bloat problem that autovacuum will be chewing on for days.

2. Drop the oldest partition, do not delete from it

If the table is time-partitioned, retention should be a catalog operation, not a row operation.

SELECT cron.schedule(
  'drop-old-order-partitions',
  '45 4 * * *',
  $$CALL maintenance.drop_partitions_older_than('orders', interval '13 months')$$
);

The reason to prefer this: DELETE marks tuples dead, writes WAL for every one of them, and leaves the space occupied until vacuum reclaims it. On a hundred-million-row month that is hours of background work and no immediate disk relief. ALTER TABLE ... DETACH PARTITION followed by DROP TABLE orders_2026_08 is a catalog change plus a file unlink. It returns the space at once and costs almost nothing.

Detach first if you want a grace period:

ALTER TABLE orders DETACH PARTITION orders_2026_08;
-- keep it around for a week, then:
DROP TABLE orders_2026_08;

If you are still doing DELETE-based retention on a table that is a candidate for partitioning, fixing that will do more for you than any scheduler.

3. Materialized view refresh, concurrently

-- prerequisite, run once, by hand:
CREATE UNIQUE INDEX mv_daily_revenue_pk
  ON mv_daily_revenue (revenue_date, region_id);

SELECT cron.schedule(
  'refresh-mv-daily-revenue',
  '10 * * * *',
  $$REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_revenue$$
);

Spell out the prerequisite because it catches everyone: CONCURRENTLY requires at least one UNIQUE index on the view that covers every row and has no WHERE clause. Without it you get an error, and the natural reaction is to drop CONCURRENTLY, which is exactly the wrong fix. A plain REFRESH MATERIALIZED VIEW takes an ACCESS EXCLUSIVE lock and blocks every reader for the whole refresh. On a dashboard-backing view at 10 past the hour, that is a visible outage.

Two more constraints: CONCURRENTLY cannot be used on a view that has never been populated (do the first refresh without it, at a quiet moment), and only one refresh can run against a given view at a time — which pg_cron's own no-overlap behavior happens to enforce for free.

4. Idempotent hourly rollup

Write rollups so a missed run repairs itself on the next one. This matters precisely because pg_cron has no catch-up.

SELECT cron.schedule(
  'rollup-events-hourly',
  '4 * * * *',
  $$
  INSERT INTO events_hourly (bucket, event_type, event_count)
  SELECT date_trunc('hour', created_at), event_type, count(*)
  FROM events
  WHERE created_at >= (SELECT coalesce(max(bucket), now() - interval '7 days')
                       FROM events_hourly)
    AND created_at <  date_trunc('hour', now())
  GROUP BY 1, 2
  ON CONFLICT (bucket, event_type)
  DO UPDATE SET event_count = EXCLUDED.event_count;
  $$
);

The watermark comes from the target table, not from now(). Miss six hours because of a failover and the next run backfills all six. ON CONFLICT DO UPDATE makes recomputing a bucket harmless, so a skipped or overlapping run self-heals instead of leaving a permanent gap. That single design choice removes most of the pain of a scheduler with no retry semantics.

Honorable mention: targeted vacuum

Autovacuum tuned globally sometimes loses to one hot table. Give it a nudge:

SELECT cron.schedule(
  'vacuum-events',
  '*/30 * * * *',
  $$VACUUM (ANALYZE) events$$
);

Note what is missing: nothing else in the command string. That is deliberate, and it is the next section. Use this sparingly, on the one or two tables that actually need it, not as a substitute for tuning autovacuum properly.

Wrap the logic in a function, schedule the call

I put every non-trivial job behind a procedure. A 400-character DELETE sitting in cron.job.command is a piece of code review nobody ever does. Nobody diffs a table row in a pull request; a procedure in a migrations directory gets a real diff, and you can test it by hand with CALL maintenance.purge_events(); in a psql session before it's ever scheduled.

CREATE PROCEDURE maintenance.purge_events(p_keep interval DEFAULT interval '90 days',
                                          p_batch int DEFAULT 50000)
LANGUAGE plpgsql AS $proc$
DECLARE
  deleted int;
BEGIN
  SET LOCAL lock_timeout = '5s';
  LOOP
    DELETE FROM events
    WHERE ctid IN (SELECT ctid FROM events
                   WHERE created_at < now() - p_keep
                   LIMIT p_batch);
    GET DIAGNOSTICS deleted = ROW_COUNT;
    EXIT WHEN deleted = 0;
    COMMIT;   -- release locks and WAL pressure between chunks
  END LOOP;
END;
$proc$;

SELECT cron.schedule('purge-events', '0 3 * * *',
                     $$CALL maintenance.purge_events()$$);

Now the logic is version-controlled, testable by hand in a psql session, and the schedule row reads as one line.

One caveat on transaction control: COMMIT inside a procedure only works when the CALL is not already inside a transaction block. Keep the command a single CALL and nothing else. The moment you write SET something; CALL ...; in the same command string, the whole string runs as one implicit transaction and your COMMIT throws.

Six edge cases that bite in production

1. Schedules are evaluated in GMT/UTC by default. '0 2 * * *' is 02:00 UTC, which for a lot of teams is the middle of the business afternoon somewhere. Check what timezone handling your pg_cron version exposes, and if in doubt, write the schedule in UTC deliberately and put a comment next to it in your repo. Reasoning in local time also drags DST into your maintenance window, where an hour either appears twice or does not exist — a second, independent way for "just convert to local time" to go wrong.

2. A multi-statement command runs as one implicit transaction. This is the gotcha behind the most-searched pg_cron error:

ERROR:  VACUUM cannot run inside a transaction block

You get it from a command like SET statement_timeout = '5min'; VACUUM events;. The simple-query protocol executes the whole string as a single transaction, and VACUUM refuses to run there. Same for CREATE DATABASE and a few others. Give VACUUM its own job, alone in the command string.

3. Overlapping runs are skipped, silently. pg_cron will not start a second instance of a job while the previous one is still running. If your five-minute job starts taking seven minutes, you quietly get half the runs you think you do. Nothing errors. Nothing warns. This is why the duration query below matters.

4. No catch-up, no retry. If the instance was down, or the job errored, that occurrence is gone. It will not run late. It will not run twice. Design idempotent, self-healing jobs and this becomes a non-issue.

5. Replicas and clones inherit the schedule. cron.job is an ordinary user table. It is in your physical replication stream, your base backups, and your pg_dump. Promote a standby and it comes up with the same jobs. Restore last night's production snapshot into staging and, unless the extension is unloaded there, staging starts deleting rows and refreshing views on its own schedule. I have watched a staging clone happily purge data while the team was using it to reproduce a bug. Bake this into your restore procedure:

-- first thing after any restore into a non-prod environment
UPDATE cron.job SET active = false;

6. Jobs run as the role that scheduled them, evaluated at execution time. By default only superusers can schedule jobs. Grant USAGE ON SCHEMA cron to a role and that role can schedule and manage its own jobs — but the job then executes with whatever privileges that role holds at the moment it runs, not the privileges it had when you wrote the schedule. This has a governance consequence: never leave a retention job owned by a departing employee's role. The day their access is revoked, the job starts failing on a permissions error that nobody is watching for. Create a dedicated, documented owner instead.

CREATE ROLE db_maint LOGIN;
GRANT USAGE ON SCHEMA cron TO db_maint;
GRANT USAGE ON SCHEMA maintenance TO db_maint;
GRANT EXECUTE ON ALL ROUTINES IN SCHEMA maintenance TO db_maint;

Two settings worth knowing while you are here: cron.use_background_workers runs jobs in background worker processes instead of via libpq connections to the local server, which avoids needing a local connection path. Those workers count against max_worker_processes, and cron.max_running_jobs caps concurrency.

Job history: what pg_cron logs, and what it does not

Current pg_cron writes run history automatically. The cron.log_run setting is on by default, and every run lands in cron.job_run_details. Separately, cron.log_statement (also on by default) echoes each executed command into the regular PostgreSQL server log.

The columns:

Column	What it holds
`runid`	Unique id per run
`jobid`	Foreign key to `cron.job`
`job_pid`	Backend PID, joinable to `pg_stat_activity`
`database`	Where the command ran
`username`	Role the job ran as
`command`	The exact SQL executed
`status`	`starting`, `running`, `succeeded`, `failed`
`return_message`	The real error text on failure
`start_time` / `end_time`	Timestamps

return_message is the field you actually want during an incident. It carries the Postgres error, verbatim.

What pg_cron does not do is clean up after itself. cron.job_run_details grows forever. The README's own recommendation is to schedule a pg_cron job to prune it, so:

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

Thirty days is my default. Keep more if your auditors care, but keep something finite. I have found this table at 40 GB on an instance where nobody had ever queried it.

Monitoring jobs like any other query

Failures in the last 24 hours:

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

Jobs that have not succeeded recently. This is the failure mode that actually hurts, because a job that silently stops producing no output looks exactly like a job that is fine.

SELECT j.jobname,
       max(d.end_time) FILTER (WHERE d.status = 'succeeded') AS last_success
FROM cron.job j
LEFT JOIN cron.job_run_details d USING (jobid)
WHERE j.active
GROUP BY j.jobname
HAVING coalesce(max(d.end_time) FILTER (WHERE d.status = 'succeeded'),
                '-infinity') < now() - interval '26 hours';

Tune the interval per job if your schedules vary; a small lookup table of jobname, max_staleness beside this query works well.

Duration trend, to catch the retention delete that crept from 40 seconds to 9 minutes:

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

Inspect a job that is running right now:

SELECT j.jobname, a.pid, a.state, a.wait_event_type, a.wait_event,
       now() - a.query_start AS running_for, a.query
FROM cron.job_run_details d
JOIN cron.job j USING (jobid)
JOIN pg_stat_activity a ON a.pid = d.job_pid
WHERE d.status = 'running';

Then wrap the important one in a view and point your monitoring at it:

CREATE VIEW maintenance.v_cron_health AS
SELECT count(*) AS failed_24h
FROM cron.job_run_details
WHERE status = 'failed' AND start_time > now() - interval '24 hours';

One view, one check, whatever scrapes it — Prometheus, Nagios, a five-minute cron job that emails you. The tooling is the least interesting part; having the check at all is the whole point. If you'd rather have this kind of drift and failure pattern flagged automatically instead of writing the queries yourself, tools like MyDBA are built for exactly that kind of ongoing database review.

War story: the job that ran perfectly and still lost data

An ecommerce database, partitioned orders, thirteen-month retention. The retention job was a DELETE from the oldest partition, scheduled at 02:00, and it had run cleanly for months. No lock_timeout.

One night a reporting session went idle in transaction while holding a lock on the parent table. The retention job started, queued behind it, and sat there. Because it held its own locks while waiting, application writes began stacking up behind the retention job. Around 02:40 the on-call engineer saw connection saturation, found the long-running maintenance query at the top of pg_stat_activity, and killed it. Correct call under pressure.

The run recorded failed in cron.job_run_details with a perfectly clear return_message. Nobody was querying that table. There was no alert on it. The schedule kept firing every night, every night it hit contention or timed out against a partition that was now much larger than the batch size assumed, and every night it failed the same way.

Six weeks later, disk pressure. Partitions had been accumulating the whole time. The scheduler had been telling us in plain English, in a table, the entire time.

Two fixes came out of it. Every job got SET lock_timeout so it fails fast instead of becoming the head of a queue. And the failure view got wired to the same alerting as everything else. A scheduler you do not monitor is worse than a crontab you do, because the crontab at least mails you.

When not to use pg_cron

Be honest about the boundaries:

Cross-system orchestration. If step two is an S3 upload or an HTTP call, this is not your tool.
Dependencies. No way to express "run B after A succeeds" beyond putting both in one procedure.

Retry and backoff policy. There is none. You get one attempt.
Jobs that must not run twice across an HA pair. The schedule replicates. Have a plan for what a promoted standby does.
Sub-second work. Even with 1.5's interval schedules, this is not a task queue.

Alternatives, briefly: pg_timetable if you want chains and retries while staying close to Postgres; plain system cron plus psql if you genuinely need shell access and file handling; your existing orchestrator if you already run one.

The hybrid pattern is the one I recommend most often. The orchestrator owns anything that crosses a system boundary and calls a Postgres procedure to do the database part. pg_cron owns only database-local maintenance — retention, partition drops, refreshes, rollups, targeted vacuums — where its lack of features is a virtue rather than a gap. Keep the two responsibilities separate and neither tool has to pretend to be the other.

A starting checklist

Copy this into your runbook:

shared_preload_libraries = 'pg_cron', then a full restart. Not a reload.
Set cron.database_name explicitly and run CREATE EXTENSION pg_cron in that one database.
Every non-trivial job is a CALL to a version-controlled procedure, not inline SQL.

Every job sets statement_timeout and lock_timeout.
Schedule the cron.job_run_details purge job on day one.
Write schedules in UTC and comment the local-time intent next to them.

Build the "hasn't succeeded recently" view and alert on it.
Add UPDATE cron.job SET active = false; to the restore-to-non-prod procedure.
Review cron.job contents in code review, the same as any migration.

Document the owning role. Never a person's login.

Nine of those ten take under an hour. The tenth, the alerting, is the one that pays for the whole exercise.

pg_cron Guide: Scheduled Jobs, Monitoring & Gotchas