Schedule Recurring Jobs with Cron Syntax in Postgres

Supabase Cron is a Postgres Module that simplifies scheduling recurring Jobs with cron syntax and monitoring Job runs inside Postgres.

Cron Jobs can be created via SQL or the Cron interface inside of Supabase Dashboard and can run anywhere from every second to once a year depending on your use case.

Every Job can run SQL snippets or database functions with zero network latency or make an HTTP request, such as invoking a Supabase Edge Function, with ease.

For best performance, we recommend no more than 8 Jobs run concurrently. Each Job should run no more than 10 minutes.

How does Cron work?#

Under the hood, Supabase Cron uses the pg_cron Postgres database extension which is the scheduling and execution engine for your Jobs.

pg_cron is not fully supported on Fly Postgres. Learn more about Fly Postgres limitations.

The extension creates a cron schema in your database and all Jobs are stored on the cron.job table. Every Job's run and its status is recorded on the cron.job_run_details table.

The Supabase Dashboard provides and interface for you to schedule Jobs and monitor Job runs. You can also do the same with SQL.

Resources#

• pg_cron GitHub Repository

Install

Install the Supabase Cron Postgres Module to begin scheduling recurring Jobs.

DashboardSQL

1. Go to the Cron Postgres Module under Integrations in the Dashboard.
2. Enable the pg_cron extension.

Uninstall#

Uninstall Supabase Cron by disabling the pg_cron extension:

1

drop extension if exists pg_cron;

Disabling the pg_cron extension will permanently delete all Jobs.

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#

DashboardSQL

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

Cron syntax

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#

DashboardSQL

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

Activate/Deactivate a job#

DashboardSQL

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

Unschedule a job#

DashboardSQL

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

Inspecting job runs#

DashboardSQL

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

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

);

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', 'Authorization', 'Bearer ' || 'YOUR_ANON_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.