pg_cron: Job Scheduling
The pg_cron
extension is a simple cron-based job scheduler for PostgreSQL that runs inside the database.
Usage#
Enable the extension#
- Go to the Database page in the Dashboard.
- Click on Extensions in the sidebar.
- Search for "pg_cron" and enable the extension.
Syntax#
The schedule uses the standard cron syntax, in which * means "run every time period", and a specific number means "but only at this time":
You can use crontab.guru to help validate your cron schedules.
Scheduling System Maintenance#
Be extremely careful when setting up pg_cron jobs for system maintenance tasks as they can have unintended consequences. For instance, scheduling a command to terminate idle connections with pg_terminate_backend(pid)
can disrupt critical background processes like nightly backups. Often, there is an existing Postgres setting e.g. idle_session_timeout
that can perform these common maintenance tasks without the risk.
Reach out to Supabase Support if you're unsure if that applies to your use case.
Examples#
Delete data every week#
Delete old data on Saturday at 3:30am (GMT):
Run a vacuum every day#
Vacuum every day at 3:00am (GMT)
Invoke Supabase Edge Function every minute#
Make a POST request to a Supabase Edge Function every minute. Note: this requires the pg_net
extension to be enabled.
Unschedule a job#
Unschedules a job called 'nightly-vacuum'