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#

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

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 crontab.guru to help validate your cron schedules.

Examples#

Delete data every week#

Delete old data on 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');

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.

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

Unschedule a job#

Unschedules a job called 'nightly-vacuum'

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

Resources#

• pg_cron GitHub Repository