pg_cron

If you have any large relational databases there is a good chance that you are using materialized views. One way that you can refresh your materialized views is via trigger on insert or on uppdate to a table. This can often be too often and cause issues. Until recently when using Spring I would use the @Scheduled annotation and run a refresh materialized view directly from the application. This is fine but you may end up with a large number of @Scheduled methods and that can cause collisions and other unfortunate circumstances. I had always wondered what if I could just schedule a query to be executed on the database itself. It turns out that this exists and is very easy to setup and utilize.

I found this AWS post that gives you everything you need to get started. My use case was eliminating certain refresh materialized view and other queries that did not require any application logic. Simple aggregates or updates that need to run on an ongoing scheduled basis. If you need heavy application or business logic I would avoid using this. Also keep in mind that the cron jobs are stored in the postres database, not your application database. So you will likely use a different use to invoke these commands as the postgres database should be off limits to an application database user.

This is a great and handy little tool to put on your tool-belt that can cleanup applications and streamline some operations that are long run that normally require a dedicated thread. I’ve found that the @Scheduled annotation is very quirky if there are multiple jobs scheduled at overlapping intervals. Theoretically you can create a more comprehensive service that will run the scheduled tasks that are at the same time in parallel or in serial. But I found that operations that really don’t require any application logic at all are most appropriate for this use case.

Make sure you that you add the scheduled job that will automatically clear your jobdetails table for you, otherwise that could cause issues down the road.

Be aware you need to be using PostgreSQL vesrion 12.5 and higher and this is now supported on RDS and Aurora.