1

What I want is to update a table every night and cache it so it doesn't have to run each time we run a query based on it. So I figure I need a materialised view (not a view).

Top answer to below question is spot on what I need.

How can I ensure that a materialized view is always up to date?

So, I searched around about materialised views for Postgresql and it seems perfect. All I need is a scheduler.

Pg_cron looks to be popular but from what I understand it is not compatible with Amazon Redshift(See https://github.com/citusdata/pg_cron/)(?)

Is there some other scheduling tool that is useable or some work around to the problem?

Many thanks! Hannes

Hannes
  • 33
  • 4

3 Answers3

0

Redshift has no inbuilt support for materialised views yet. You will need to have external service do it for you. We are using airflow, where we have written templates DAG which fills materialised views.

nitzien
  • 1,117
  • 9
  • 23
0

Redshift now supports Materialized Views

https://docs.aws.amazon.com/redshift/latest/dg/materialized-view-overview.html

There is however currently no way to schedule the refresh from within RedShift, so you will have to invoke the REFRESH command from some external timer. https://docs.aws.amazon.com/redshift/latest/dg/materialized-view-refresh-sql-command.html

Since 2020/11 it is also possible to allow Redshift to have control of refreshing the materialized view with the AUTO REFRESH YES option. https://docs.amazonaws.cn/en_us/redshift/latest/dg/materialized-view-create-sql-command.html

mtkopone
  • 5,955
  • 2
  • 27
  • 30
0

If you want to keep it serverless, you can use Redshift data API and invoke MV REFRESH from Lambda. https://boto3.amazonaws.com/v1/documentation/api/latest/reference/services/redshift-data.html

hadooper
  • 726
  • 1
  • 6
  • 18