How To Create A Postgres Scheduled Job On Heroku

Scheduling jobs in Postgres is essential for automating maintenance tasks such as data cleanup, aggregation, and archiving.

In this guide, we’ll explore how to set up a Postgres scheduled job on Heroku using Cron To Go, a powerful job scheduling add-on. By following these steps, you can ensure your Postgres database operates efficiently and reliably.

How to automate Heroku Postgres tasks and queries

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 takes care of backups, warm backup servers (followers in Heroku Postgres lingo), security, and 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 set up a Postgres scheduled job in Heroku in a simple, economical, and cost-efficient manner.

Run and monitor scheduled tasks on your apps

Cron is a flexible time-based job scheduler used in Unix 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 Postgres scheduled jobs, track job execution, and receive email and webhook notifications regarding job status.

Install Cron To Go, the scheduler add-on

To install it in your app, you may use the Heroku CLI in your terminal:

heroku addons:create crontogo

Shell commands allow you to integrate Cron To Go seamlessly, making it an ideal choice for setting up Postgres scheduled jobs.

Another option is to use the UI to install Cron To Go:

1. Go to your Heroku app dashboard.
2. Click the resources tab.
3. Type in “Cron To Go” under the addons section.
4. 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 Postgres jobs on Heroku.

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.

You can then use the -c argument to execute an inline query or add SQL files to your app repo and execute them using the -f argument.

In the following example, we’ll be adding 2 Postgress scheduled jobs that serve different purposes:

1. One refreshes a materialized view every 10 minutes.
2. One deletes 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.

Examples of scheduling Postgres jobs

Schedule a Postgres job to refresh a materialized view every 10 minutes:

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 * * * *
• Command: 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.

Schedule a Postgres job to delete old data every night:

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 * * *
• Command: 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 Postgres scheduled 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.

Benefits of scheduling postgres jobs

Automating postgres scheduled jobs offers numerous advantages for your database management.

Here are some key benefits:

1. Efficiency: Automated jobs save time by handling routine tasks without manual intervention. This allows your team to focus on more critical work.
2. Performance: Regularly scheduled tasks, such as data cleanup and aggregation, help maintain optimal database performance by removing outdated information and ensuring data is up-to-date.
3. Reliability: Scheduled jobs ensure tasks are executed consistently at predefined intervals. This reliability is important for tasks like refreshing materialized views or processing incoming data.
4. Scalability: With tools like Cron To Go, you can easily scale your scheduled jobs to match your database's growing needs. This flexibility ensures that your postgres database remains efficient and responsive, even as your application scales.
5. Notifications: Receive notifications for job statuses, including successes and failures. This feature helps you stay informed and promptly address any issues that may arise.

Example use cases for postgres scheduled jobs

Here are some common use cases where postgres scheduled jobs can significantly improve your database operations:

1. Data cleanup: Schedule jobs to delete old or irrelevant data regularly, maintaining a clean and efficient database.
2. Data aggregation: Automate the aggregation of data at regular intervals to provide up-to-date summaries and reports.
3. Materialized view refresh: Regularly refresh materialized views to ensure they contain the latest data, improving query performance.
4. ETL/ELT processes: Schedule extract, transform, load (ETL) or extract, load, transform (ELT) processes to run at optimal times, reducing the load on your database during peak hours.

Implementing postgres scheduled jobs with Cron To Go

Setting up and managing postgres scheduled jobs with Cron To Go is straightforward and provides you with a reliable way to automate essential database tasks.

By leveraging the power of Cron To Go, you can ensure your Postgres database remains efficient, reliable, and scalable.