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

Unschedule a job#

Unschedules a job called 'nightly-vacuum'


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

Resources#