What is the advantage of using a Materialized View over a base table in Amazon Redshift?

user_2623144 0
Viewed 2006

Conceptually, I understand that materialized views are static representations of computed values, but I don't understand how that is functionally different from creating a table that contains the same pre-computed data. I would think a table could be even more performant since one could add sortkeys.

amazon-redshift materialized-views
3 Answers

I had the same question myself back in the day... As I understand the main differences are:

  1. REFRESH MATERIALIZED VIEW syntax. To re-fill a table you would have to truncate the table and run that query again in a transaction. So MV is more efficient from the coding standpoint.

  2. MV is a dependent object in the database. Upstream tables (ones that are used in its definition) have to be dropped in a cascade fashion. Changes to upstream tables are also quite limited. A table is independent from the query that generated it at some point of time. So it's a design choice. I'd say going with MV is a more conservative design.

As for keys, you can specify them in the create statement (per official docs)

user_35485190

  1. A materialized view, or snapshot as they were previously known, is a table segment whose contents are periodically refreshed based on a query, either against a local or remote table. Using materialized views against remote tables is the simplest way to achieve replication of data between sites.

  2. It can be used as an aggregate table based on multiple tables using Joins. We can implement row level security Privileges as well

  3. Materialized views can be used to improve the performance of a variety of queries, including those performing aggregations and transformations of the data.

  4. Once the joining tables of MV are loaded, based on the Refresh Mechnism, MVS data gets refreshed automatically

user_100244630

In short, MV is a one time operation accompanied by REFRESH whereas Table CTAS would be DROP & Recreate or Truncate (but insert after truncate would be slower as compared to Drop and recreate ) option. The best part to choose MV is it allows 2 modes Incremental and Full Load in redshift but Incremental is restricted to a huge level no complex query/Joins/Order by/Limit/Aggregations (only basic count,max etc allowed) but still MVs becomes preferable as it has the other mode which recompute full load again using just a same REFRESH command. One sole aspect that is useful to choose CTAS over MVs is Distribution Style AUTO on Table vs Not Such Auto option in MVs meaning it will not allow redshift capability to self decide Distribution Style whenever needed.

user_104974830
Related