Member-only story
Materialized view is a widely supported feature in RDBMS like Postgres, Oracle, MYSql. However, it is only recently supported in Redshift to solve performance challenges by complex queries in data warehouse environment. Here we will introduce why we need materialized view in Redshift, how to smartly use it and when to use it.
Why & When We Need Materialized View In Redshift
Redshift is built on top of Postgres and designed for data warehouse. Its columnar storage optimized the query performance greatly. However when there is highly complex queries and billions of rows involved in the query, processing these queries will be very expensive.
Nowadays, real time analytics is tailored to provide more relevant data insights to collaborate with day-to-day basis operational analysis. Having slow query performance will result slow dashboard performance when engineer wants to interact with the dashboard to get customized data insights.
Materialized view helps to re-compute the query and return result sets into a physical table. Without accessing to the base tables and process the results using complex JOINs and Aggregations, retrieving data directly from materialized view has faster query performance.
How To Use Materialized View In Redshift
- create materialized view: CREATE MATERIALIZED VIEW … AS …
- refresh materialized view: REFRESH MATERIALIZED VIEW…
