pg_cron — Scheduled Jobs

pg_cron is purely application-side. It runs SQL statements on a schedule — VACUUM at 3am, refresh a materialized view hourly, purge old rows weekly. MonPG doesn't install it during onboarding (it's not load-bearing for monitoring), but if you have it, we surface job history on a small "Scheduled Jobs" card under System: each job's last run, status, duration, average runtime. Useful when a "why was the cluster slow at 3am" investigation turns out to be a heavy scheduled job.

Enabling

SPL + restart bound. Same provider differences as auto_explain.

• AWS RDS: Parameter Group → add pg_cron to shared_preload_libraries → reboot. Then CREATE EXTENSION pg_cron; in the postgres database. Jobs are stored there but can target any database.
• Azure Flex: Server parameters → SPL → restart. CREATE EXTENSION pg_cron;.
• Cloud SQL: Flag cloudsql.enable_pg_cron=on. Cloud SQL handles the restart.
• Self-hosted: shared_preload_libraries = '...,pg_cron' plus cron.database_name = 'mydb' in postgresql.conf, then restart.

Common patterns

-- Daily at 3am UTC: VACUUM the orders table
SELECT cron.schedule('vacuum-orders', '0 3 * * *', $$VACUUM (ANALYZE) orders$$);

-- Hourly: refresh a materialized view
SELECT cron.schedule('refresh-mv', '0 * * * *', $$REFRESH MATERIALIZED VIEW CONCURRENTLY daily_summary$$);

-- Weekly: clean up old rows
SELECT cron.schedule('purge-old', '0 2 * * 0', $$DELETE FROM events WHERE created_at < NOW() - INTERVAL '90 days'$$);

Things to know

Jobs run as the user who scheduled them. Use a dedicated low-privilege role rather than a superuser-with-all-the-keys for this — pg_cron under a superuser becomes a footgun. On Cloud SQL with auto-pause, jobs miss fires while the instance is suspended; if you have hourly jobs, the auto-pause is essentially incompatible. Failed jobs land in cron.job_run_details and MonPG surfaces the latest failure on the job's row in the Scheduled Jobs card.