Materialized Views that Really Work

Learn how to achieve 10-100x (or better!) performance gains from your bottleneck queries. Really.

Complex queries repeated in application code often are recast as views. This makes programming easier, but it does nothing to improve the performance of the underlying query. When performance gains are needed, materializing the view is the next logical step. Views that are materialized are stored in tables rather than computed at request time, so joins, procedure evaluation, and conditions from the original query are pre-computed before queries are run against the materialized view. Because the data is in a table, performance can be further improved by adding indexes. The difference between selecting a single row out of an indexed table or computing it in real-time from a dozen joined or aggregated tables can be staggering.

Many developers shy away from materializing views in Postgres because the functionality is not built in, as it is in Oracle. It can take a lot of meticulous work to build by hand the stored procedures and triggers necessary to keep the materialized view up to date. It turns out there are a variety of materialization techniques you can apply in Postgres that aren't available in Oracle's materialized views, so you're better off building them yourself after all. The time you put in enhancing your database's performance is well worth the orders of magnitude of speed enhancements you will enjoy as a result of your labor.

In this tutorial, I will present a straightforward approach that can be applied to materialize any view. I'll also present two new techniques that have not yet been described for materialization in Postgres. The first shows how to implement eager materialization of a view with a time dependency (e.g., a view based on 'now()'). The second technique shows how to implement lazy materialized views in Postgres. Lazy materialized views are like their eager counterpart, but only one refresh per transaction is guaranteed, making materialization viable for aggregate and other one-to-many views. Both of these problems are currently unsolved in Postgres, as well as in Oracle's built-in view materialization.

After this tutorial, you will be able to materialize any view and see HUGE performance gains.