pg_cron: Job Scheduling
The pg_cron extension is a simple cron-based job scheduler for PostgreSQL that runs inside the database.
pg_cron is not fully supported on Fly Postgres. Read more about this Fly Postgres limitation here.
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":
_10 ┌───────────── min (0 - 59)_10 │ ┌────────────── hour (0 - 23)_10 │ │ ┌─────────────── day of month (1 - 31)_10 │ │ │ ┌──────────────── month (1 - 12)_10 │ │ │ │ ┌───────────────── day of week (0 - 6) (0 to 6 are Sunday to_10 │ │ │ │ │ Saturday, or use names; 7 is also Sunday)_10 │ │ │ │ │_10 │ │ │ │ │_10 * * * * *
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):
_10select cron.schedule (_10 'saturday-cleanup', -- name of the cron job_10 '30 3 * * 6', -- Saturday at 3:30am (GMT)_10 $$ delete from events where event_time < now() - interval '1 week' $$_10);
Run a vacuum every day#
Vacuum every day at 3:00am (GMT)
_10select cron.schedule('nightly-vacuum', '0 3 * * *', 'VACUUM');
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.
_13select_13 cron.schedule(_13 'invoke-function-every-minute',_13 '* * * * *', -- every minute_13 $$_13 select_13 net.http_post(_13 url:='https://project-ref.supabase.co/functions/v1/function-name',_13 headers:='{"Content-Type": "application/json", "Authorization": "Bearer YOUR_ANON_KEY"}'::jsonb,_13 body:=concat('{"time": "', now(), '"}')::jsonb_13 ) as request_id;_13 $$_13 );
Edit a job#
Changes the frequency of a job called 'vacuum' to once every 5 minutes.
_10select cron.alter_job(_10 job_id := (select jobid from cron.job where jobname = 'vacuum'),_10 schedule := '*/5 * * * *'_10);
Full options for the cron.alter_job() function are:
_10cron.alter_job(_10 job_id bigint,_10 schedule text default null,_10 command text default null,_10 database text default null,_10 username text default null,_10 active boolean default null_10)
Unschedule a job#
Unschedules a job called 'nightly-vacuum'
_10select cron.unschedule('nightly-vacuum');