Airflow task to refresh PostgreSQL Materialized Views 06 Jul 2018
You need more performance from some PostgreSQL query. This could be an OLAP oriented query that doesn’t need to respond with the most updated data. This is a common scenario for analytic queries.
A solution could be to create a Materialized View, which will store both the data generated by the query and the query itself. Then, you can just refresh the view and the new data will replace the old content.
Now, after you create your materialized view, you are in charge of schedule when it will be refreshed. You could use a simple Cron, but maybe you want to use something a bit more sophisticated like Airflow. This could be achieved via a simple PostgresOperator:
This simple DAG will run a refresh command on a daily basis. In order to use the PostgresOperator, first you need to create a connection using the Airflow connections tab:
And then use that connection id for the PostgresOperator postgres_conn_id parameter. You can execute any SQL command by using this operator.
Thanks for reading!