SQL: The PostgreSQL Way - PostgreSQL: Up and Running - page 124

Name the index to cluster on. Needed only during view creation.
Each time you refresh, you must recluster the data.
The advantage of using ORDER BY in the materialized view over using the CLUSTER
approach is that the sort is maintained with each REFRESH MATERIALIZED VIEW call,
leaving no need to recluster. The downside is that ORDER BY generally adds more pro‐
cessing time to the REFRESH step of the view. You should test the effect of ORDER BY on
performance of REFRESH before using it. One way to test is just to run the underlying
query of the view with an ORDER BY clause.
To refresh the view in PostgreSQL 9.3 you must use:
REFRESH MATERIALIZED VIEW census . vw_facts_2011_materialized ;
In PostgreSQL 9.4, to avoid locking tables that the views draw from during the refresh,
you can use:
REFRESH MATERIALIZED VIEW CONCURRENTLY census . vw_facts_2011_materialized ;
Limitations of materialized views include:
• You can't use CREATE OR REPLACE to edit an existing materialized view. You must
drop and recreate the view even for the most trivial of changes. Use DROP MATERI
ALIZED VIEW name_of_view . Sadly, you'll lose all your indexes.
• You need to run REFRESH MATERIALIZED VIEW to rebuild the cache. PostgreSQL
doesn't perform automatic recaching of any kind. You need to resort to a mechanism
such as a crontab , pgAgent job, or trigger to automate any kind of refresh. We have
an example using triggers in Caching Data with Materialized Views and Statement-
Level Triggers .
• Refreshing materialized views in version 9.3 is a blocking operation, meaning that
the view will not be accessible during the refresh process. In version 9.4 you can lift
this quarantine by adding the CONCURRENTLY keyword to your REFRESH command,
provided that you have established a unique index on your view. The trade-off is
that a concurrent refresh will take longer to complete.
Handy Constructions
In our many years of writing SQL, we have come to appreciate the little things that make
better use of our typing. Only PostgreSQL offers some of the gems we present in this
section. Often this means that the construction is not ANSI-compliant. If thy God de‐
mands strict observance to the ANSI SQL standard or if you need to compose SQL that
you can port to other database products, abstain from the shortcuts that we'll be show‐
ing.