Dan Chak’s Blog

My presentation on materialized views in Postgres, and especially materialized views with time dependencies, has been accepted to PGCon 2008.

Abstract: 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. Materializing the view is the next logical step. Materialized views are stored in standard tables, 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.

Many developers shy away from materializing views in Postgres because the functionality is not built in, as it is in Oracle. In this talk, I will present a straightforward approach that can be applied to materialize any view, even those that Oracle cannot handle. Case in point: views that have a time-dependency (such as those based on the current time, now()) don’t have convenient trigger points (such as commits) to signal that the view should be refreshed, and are a major stumbling block for view materialization. This talk presents a technique to handles those cases as well, bringing materialized views even more powerful than those available in commercial products to Postgres.

Hope to see you in Ottawa!