A Supabase user asked recently if they can trigger a webhook periodically. We haven't yet released Functions yet, so we checked whether it's possible with Postgres.
It is. Here's how.
What's cron?
A "cron job" is a script1 that runs periodically at fixed times, dates, or intervals. Traditionally you'd set it up on a Linux server. An example might be an hourly script that downloads emails to your computer.
These days, cron jobs are set up on a remote servers and in the cloud to run internet-related tasks. Like checking an endpoint every hour, or scraping a website every day.
Postgres + cron
Postgres has "extensions" which allow you to, well, extend the database with "non-core" features. Extensions essentially turn Postgres into an application server.
The team at Citus created pg_cron to run periodic jobs within your Postgres database.
Enabling the extension
If you're using a cloud-hosted Postgres database, make sure that pg_cron is installed first. The easiest way to do this is to run this command:
1select name, comment, default_version, installed_version
2from pg_available_extensions
3where name = 'pg_cron';
4
If it returns a result then the extension is supported and you can turn it on by running:
1create extension if not exists pg_cron; 2
If you're using Supabase you can also enable it in the Dashboard.
Granting access to the extension
If you're planning to use a non-superuser role to schedule jobs, ensure that they are granted access to the cron schema and its underlying objects beforehand.
1grant usage on schema cron to {{DB user}}; 2grant all privileges on all tables in schema cron to {{DB user}}; 3
Failure to do so would result in jobs by these roles to not run at all.
Postgres + webhooks
The Supabase customer wanted to call external endpoints every day. How would we do this? Another extension of course. This time we're going to use pgsql-http by @pramsey. Using the same technique, we can enable the extension (if it exists in your cloud provider).
1create extension if not exists http; 2
This extension can now be used for sending GET, POST, PATCH, and DELETE requests.
For example, this function would get all the people in Star Wars (using the Star Wars API):
1select content::json->'results'
2from http_get('https://swapi.dev/api/people');
3
Postgres + cron + webhooks
Now the fun stuff. For this example we're going to call webhook.site every minute with the payload { "hello": "world" }.
Here's the code (with comments --like this).
1select 2 cron.schedule( 3 'webhook-every-minute', -- name of the cron job 4 '* * * * *', -- every minute 5 $$ 6 select status 7 from 8 http_post( 9 'https://webhook.site/223c8a43-725b-4cbd-b1fe-d0da73353a6b', -- webhook URL, replace the ID(223c8..) with your own 10 '{"hello": "world"}', -- payload 11 'application/json' 12 ) 13 $$ 14 ); 15
Now when we see that the payload is sent every minute, exactly on the minute.
And that's it! We've built a cron webhook. Breaking down the code example above we have 2 key parts:
POSTing data
This is the part that sends the data to the website:
1select status 2from 3 http_post( 4 'https://webhook.site/223c8a43-725b-4cbd-b1fe-d0da73353a6b', -- webhook URL 5 '{"hello": "world"}', -- payload 6 'application/json' 7 ) 8
Scheduling the job
The HTTP function is wrapped with the CRON scheduler:
1select 2 cron.schedule( 3 'cron-name', -- name of the cron job 4 '* * * * *', -- every minute 5 $$ 6 -- Put your code between two dollar signs so that you can create full statements. 7 -- Alternatively, you can write you code in a Postgres Function and call it here. 8 $$ 9 ); 10
The second parameter uses 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
If you're unfamiliar with the cron syntax, useful shortcuts can be found on crontab.guru
1* * * * * # every minute
2*/5 * * * * # every 5th minute
30 * * * * # every hour
40 0 * * * # every day
5
Managing your cron jobs
To see a list of all your cron jobs, run:
1select * from cron.job;
2
And if you need to see the results of each cron iterations, you can find them in cron.job_run_details:
1select * from cron.job_run_details;
2
To stop a running cron job, you can run:
1select cron.unschedule('webhook-every-minute'); -- pass the name of the cron job 2
What can I do with this?
There are plenty use-cases for this. For example:
- Sending welcome emails. If you use an email provider with an HTTP API, then you batch emails to that service. Write a function that
selectsall your signups yesterday, then sends them to your favorite transactional email service. Schedule it every day to run at midnight. - Aggregating data. If you're providing analytical data, you might want to aggregate it into time periods for faster querying (which serves a similar purpose as a Materialized View).
- Deleting old data. Need to free up space? Run a scheduled job to delete data you no longer need.
See a detailed list in the pg_cron README.
Addendum
Postgres background workers
You might have noticed this notice the warning at the bottom of the http readme:
"What happens if the web page takes a long time to return?" Your SQL call will just wait there until it does. Make sure your web service fails fast.
Luckily pg_cron implements Background Workers:
Care is taken that these extra processes do not interfere with other postmaster tasks: only one such process is started on each ServerLoop iteration. This means a large number of them could be waiting to be started up and postmaster is still able to quickly service external connection requests.
This means that even if your endpoint takes a long time to return, it's not going to be blocking your core Postgres functions. Either way, you should probably only call endpoints that will return a response quickly, or set the http extension to fail fast (http.timeout_msec = 300).
If you're familiar with C, you could also help @pramsey to implement async functions: https://github.com/pramsey/pgsql-http/issues/105
Should I use Postgres as a cron server?
There are plenty of ways to run cron jobs these days. You can trigger them from your local machine. You can install them on a VPS. You can schedule Serverless functions. You can use a paid service. You can use GitHub Actions.
Is Postgres the best place to put your cron jobs? ¯\_(ツ)_/¯. Postgres databases are free on Supabase and since it takes only one minute to get started, why not make your next cron server a Postgres database?
- Not necessarily a script. The cron is really a scheduler which triggers a job (of some sort, usually a bash script).↩