Cron quickstart

Job names are case sensitive and cannot be edited once created.

Attempting to create a second Job with the same name (and case) will overwrite the first Job.

Schedule a job

Go to the Jobs section to schedule your first Job.
Click on Create job button or navigate to the new Cron Job form here.
Name your Cron Job.
Choose a schedule for your Job by inputting cron syntax (refer to the syntax chart in the form) or natural language.
Input SQL snippet or select a Database function, HTTP request, or Supabase Edge Function.

1
-- Cron Job name cannot be edited
2
select cron.schedule('permanent-cron-job-name', '30 seconds', 'CALL do_something()');

Cron syntax

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

You can use [1-59] seconds (e.g. 30 seconds) as the cron syntax to schedule sub-minute Jobs.

You can input seconds for your Job schedule interval as long as you're on Postgres version 15.1.1.61 or later.

Edit a job

Go to the Jobs section and find the Job you'd like to edit.
Click on the three vertical dots menu on the right side of the Job and click Edit cron job.
Make your changes and then click Save cron job.

1
select cron.alter_job(
2
  job_id := (select jobid from cron.job where jobname = 'permanent-cron-job-name'),
3
  schedule := '*/5 * * * *'
4
);

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

1
cron.alter_job(
2
  job_id bigint,
3
  schedule text default null,
4
  command text default null,
5
  database text default null,
6
  username text default null,
7
  active boolean default null
8
)

It is also possible to modify a job by using the cron.schedule() function by inputting the same job name. This will replace the existing job via upsert.

Activate/Deactivate a job

Go to the Jobs section and find the Job you'd like to unschedule.
Toggle the Active/Inactive switch next to Job name.

1
-- Activate Job
2
select cron.alter_job(
3
  job_id := (select jobid from cron.job where jobname = 'permanent-cron-job-name'),
4
  active := true
5
);
6
7
-- Deactivate Job
8
select cron.alter_job(
9
  job_id := (select jobid from cron.job where jobname = 'permanent-cron-job-name'),
10
  active := false
11
);

Unschedule a job

Go to the Jobs section and find the Job you'd like to delete.
Click on the three vertical dots menu on the right side of the Job and click Delete cron job.
Confirm deletion by entering the Job name.

1
select cron.unschedule('permanent-cron-job-name');

Unscheduling a Job will permanently delete the Job from cron.job table but its run history remain in cron.job_run_details table.

Inspecting job runs

Go to the Jobs section and find the Job you want to see the runs of.
Click on the History button next to the Job name.

1
select
2
  *
3
from cron.job_run_details
4
where jobid = (select jobid from cron.job where jobname = 'permanent-cron-job-name')
5
order by start_time desc
6
limit 10;

The records in the cron.job_run_details table are not cleaned up automatically. They are also not removed when jobs are unscheduled, which will take up disk space in your database. Schedule a cleanup Job to remove old records (see Clean up job run history below).

If a Job doesn't run as expected, see pg_cron debugging guide.

Examples

Delete data every week

Delete old data every Saturday at 3:30AM (GMT):

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

Clean up job run history

cron.job_run_details grows with every Job run and is never cleaned up automatically, even after a Job is unscheduled. Schedule a Job to delete old records, keeping only the last 7 days:

1
select cron.schedule(
2
  'job-run-details-cleanup', -- name of the cron job
3
  '0 0 * * *', -- daily at midnight (GMT)
4
  $$ delete from cron.job_run_details where end_time < now() - interval '7 days' $$
5
);

Run a vacuum every day

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

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

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

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

1
select
2
  cron.schedule(
3
    'invoke-function-every-half-minute',
4
    '30 seconds',
5
    $$
6
    select
7
      net.http_post(
8
          url:='https://project-ref.supabase.co/functions/v1/function-name',
9
          headers:=jsonb_build_object('Content-Type','application/json', 'apikey', 'YOUR_PUBLISHABLE_KEY'),
10
          body:=jsonb_build_object('time', now() ),
11
          timeout_milliseconds:=5000
12
      ) as request_id;
13
    $$
14
  );

This requires the pg_net extension to be enabled.

Caution: Scheduling system maintenance

Be extremely careful when setting up 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, such as 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.