Heroku Postgres is used by the majority of Heroku apps to store state and data about users, customers, events, transactions, and any logical object worth storing. A fully managed database as a service, Heroku Postgres has everything taken care of including backups, warm backup servers (that is, followers in Heroku Postgres lingo), security, and even health checks.
However, there are cases that require you to run scheduled queries or statements as part of your own maintenance. These tasks include aggregating data, cleaning up abandoned carts, archiving old data, refreshing materialized views, or processing incoming data as part of your ETL or ELT processes.
In this post, we’ll go through the steps required to schedule SQL statements and commands in Heroku in a simple, economical and cost efficient manner.
Install Cron To Go, the scheduler add-on
Cron is a flexible time-based job scheduler used in *nix systems to schedule command line processes using a cron expression, which defines when and how often a command runs. You can read more about cron expressions here and how to take them to the extreme here.
Cron To Go is a Heroku add-on that provides a fully managed, accurate, reliable, flexible, and scalable cloud process scheduling solution. Cron To Go’s Heroku add-on allows you to utilize cron expressions to automate tasks, track job execution, and receive email and webhook notifications regarding job status.
To install it in your app, you may use the Heroku CLI in your terminal:
heroku addons:create crontogo
Another option is to use the UI to install Cron To Go:
- Go to your Heroku app dashboard.
- Click the resources tab.
- Type in “Cron To Go” under the addons section.
- Choose your plan and click submit order form.
Add jobs to automate your Heroku Postgres database tasks
After Cron To Go is all set up, it’s time to give it a go and schedule Heroku Postgres statements. Heroku Buildpacks come bundled with the
psql command line interface which allows you to connect your database and run commands. To connect to the database, use the environment variable
$DATABASE_URL that Heroku maintains with the connection string to your database. We will then use the -c argument to execute an inline query. You may also add sql files to your app repo and then execute them using the -f argument.
In the following example, we’ll be adding 2 jobs that serve different purposes:
- Refreshes a materialized view every 10 minutes.
- Delete old data every night.
NOTE: Unlike regular views, which only query the underlying stored data and not the actual tables (or views) referenced by the view, materialized views also store the result set of that query. This can speed up queries on the view if your view’s query is very complex, but on the other hand, the data that the view contains is not up-to-date. In order to obtain fresh data, you need to run a command to refresh the materialized view every once in a while.
To open Cron To Go’s dashboard, use the following command or click Cron To Go Scheduler from your Heroku app dashboard:
$ heroku addons:open crontogo
Opening crontogo for sharp-mountain-4005
Next, click the
Add job button:
Name the first job “Refresh materialized view” and use the following cron expression and command:
- Cron expression:
*/10 * * * *
psql $DATABASE_URL -c "REFRESH MATERIALIZED VIEW CONCURRENTLY daily_active_users;"
This job will run every 10 minutes and refresh a materialized view called daily_active_users. Click
Add job to add this job and create another job. This time, name it “Archive old data” and use the following cron expression and command:
- Cron expression:
0 3 * * *
psql $DATABASE_URL -c "DELETE FROM all_events WHERE created_at < current_timestamp - ‘30 days’::interval"
This job will run every day at 3am (UTC) and delete any record in the all_events table, that is 30 days old (or more).
All that’s left is to sit back and relax - Cron To Go will take care of automatically running the jobs for you and can also notify you in case they fail.
If you have any further questions, suggestions, or concerns, please don’t hesitate to reach out to us via the chat button.