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

Question

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.

Answer 1

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)

Answer 2

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