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 [1-59] seconds
as the cron syntax to schedule sub-minute jobs. This is available on pg_cron
v1.5.0+; upgrade your existing Supabase project to use this syntax.
Head over to crontab.guru to 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 every 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');
Call a database function every 5 minutes
Create a hello_world()
database function and then call it every 5 minutes:
_10select cron.schedule('call-db-function', '*/5 * * * *', 'SELECT hello_world()');
Call a database stored procedure
To use a stored procedure, you can call it like this:
_10select cron.schedule('call-db-procedure', '*/5 * * * *', 'CALL my_procedure()');
Invoke Supabase Edge Function every 30 seconds
This requires pg_cron
v1.5.0+ and the pg_net
extension to be enabled.
Make a POST request to a Supabase Edge Function every 30 seconds:
_14select_14 cron.schedule(_14 'invoke-function-every-half-minute',_14 '30 seconds',_14 $$_14 select_14 net.http_post(_14 url:='https://project-ref.supabase.co/functions/v1/function-name',_14 headers:=jsonb_build_object('Content-Type','application/json', 'Authorization', 'Bearer ' || 'YOUR_ANON_KEY'),_14 body:=jsonb_build_object('time', now() ),_14 timeout_milliseconds:=5000_14 ) as request_id;_14 $$_14 );
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)
It is also possible to modify a job by using the cron.schedule()
function, with the same job name. This will replace the existing job, in the manner of an upsert.
Unschedule a job
Unschedules a job called 'nightly-vacuum'
_10select cron.unschedule('nightly-vacuum');
Viewing previously ran jobs
View the last ten jobs that have ran
_10select_10 *_10from cron.job_run_details_10order by start_time desc_10limit 10;
The records in cron.job_run_details are not cleaned automatically which will take up disk space in your database.