Search code examples
amazon-redshiftmaterialized-views

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


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.


Solution

  • 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)