Postgres as a CRON Server

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 script¹ 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:

If it returns a result then the extension is supported and you can turn it on by running:

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.

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

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

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

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:

Scheduling the job#

The HTTP function is wrapped with the CRON scheduler:

The second parameter uses cron syntax:

If you're unfamiliar with the cron syntax, useful shortcuts can be found on crontab.guru

Managing your cron jobs#

To see a list of all your cron jobs, run:

And if you need to see the results of each cron iterations, you can find them in cron.job_run_details:

To stop a running cron job, you can run:

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 selects all 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?

More Postgres resources#

• Implementing "seen by" functionality with Postgres
• Partial data dumps using Postgres Row Level Security
• Postgres Views
• Postgres Auditing in 150 lines of SQL
• Cracking PostgreSQL Interview Questions
• What are PostgreSQL Templates?
• Realtime Postgres RLS on Supabase

Footnotes#

1. Not necessarily a script. The cron is really a scheduler which triggers a job (of some sort, usually a bash script). ↩