Database

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

  1. Go to the Database page in the Dashboard.
  2. Click on Extensions in the sidebar.
  3. 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":

┌───────────── min (0 - 59)
│ ┌────────────── hour (0 - 23)
│ │ ┌─────────────── day of month (1 - 31)
│ │ │ ┌──────────────── month (1 - 12)
│ │ │ │ ┌───────────────── day of week (0 - 6) (0 to 6 are Sunday to
│ │ │ │ │ Saturday, or use names; 7 is also Sunday)
│ │ │ │ │
│ │ │ │ │
* * * * *

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):

select cron.schedule (
'saturday-cleanup', -- name of the cron job
'30 3 * * 6', -- Saturday at 3:30am (GMT)
$$ delete from events where event_time < now() - interval '1 week' $$
);

Run a vacuum every day

Vacuum every day at 3:00am (GMT):

select 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:

select 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:

select cron.schedule('call-db-procedure', '*/5 * * * *', 'CALL my_procedure()');

Invoke Supabase Edge Function every 30 seconds

Make a POST request to a Supabase Edge Function every 30 seconds:

select
cron.schedule(
'invoke-function-every-half-minute',
'30 seconds',
$$
select
net.http_post(
url:='https://project-ref.supabase.co/functions/v1/function-name',
headers:='{"Content-Type": "application/json", "Authorization": "Bearer YOUR_ANON_KEY"}'::jsonb,
body:=concat('{"time": "', now(), '"}')::jsonb
) as request_id;
$$
);

Edit a job

Changes the frequency of a job called 'vacuum' to once every 5 minutes.

select cron.alter_job(
job_id := (select jobid from cron.job where jobname = 'vacuum'),
schedule := '*/5 * * * *'
);

Full options for the cron.alter_job() function are:

cron.alter_job(
job_id bigint,
schedule text default null,
command text default null,
database text default null,
username text default null,
active boolean default null
)

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'

select cron.unschedule('nightly-vacuum');

Viewing previously ran jobs

View the last ten jobs that have ran

select
*
from cron.job_run_details
order by start_time desc
limit 10;

Resources