Prepare for the PgBouncer and IPv4 deprecations on 26th January 2024

Home

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


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


_10
select 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)


_10
select 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.


_13
select
_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.


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


_10
cron.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'


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

Viewing previously ran jobs

View the last ten jobs that have ran


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

Resources