Search code examples
amazon-redshiftbigdata

Do you need to VACUUM SORT on Redshift Materialized Views?

I can not seem to find any documentation that indicates how the sort order of an incrementally updated materialized view is maintained. Based on the lack of docs I assume this is just taken care of on REFRESH.

Does anyone know if you should be running VACUUM SORT on views?

Solution

  • I would do so, to be safer. On Materialized View Refresh docummentation it also mentions that the autorefresh can be automatically stopped by Redshift internal processes. We can also see some misleading information such as the vacuum_sort_benefit column for that view being NULL.

    But after running the vacuum sort only my-mv-view, where my-mv-view is the name returned on svv_table_info it showed that got improvements on its sort.

    It is also suggested to vacuum Postgresql materialized views due frozen ids; this behaviour are on versions after 9.4. More on the Routine Vacuuming at Postgres Official Page.

    I hope this helps!

    Cheers!