When to use a materialized view in PostgreSQL

A materialized view is defined as a table which is actually physically stored on disk, but is really just a view of other database tables. In PostgreSQL, like many database systems, when data is retrieved from a traditional view it is really executing the underlying query or queries that build that view. This is great for better representation of data for users, but does not do anything to help performance.

Materialized views are different in that they are an actual physical table that is built from the data in other tables. To use another example from my NewsCloud application in order to achieve the performance I needed, I used a materialized view for representing the tag cloud.

In this particular application the data used to build the tag cloud changes very infrequently, but to generate the actual tag cloud the ORDER BY needed to rank the results was terribly slow. They query in question is:

SELECT k.id, k.keyword, c.count FROM news_keywords AS k, news_keyword_total_count AS c WHERE k.id = c.keyword ORDER BY c.count DESC;

This query was taking an average of 2 seconds to complete which would mean, when you figured in all of the other time aspects such as mod_perl, Apache, transporting the HTML back to the browser, etc. this could easily mean the user would see a 3-4 second page load time. However, by creating a new table with:

CREATE TABLE test AS SELECT k.id, k.keyword, c.count FROM news_keywords AS k, news_keyword_total_count AS c WHERE k.id = c.keyword ORDER BY c.count DESC;

And then I dropped my old view table ( named count_mview ) and renamed the test table to the old name. A quick vacuum analyze afterwards and everything is happy. With this simple change I can then directly query the count_mview data and it is returned in the order I need, but this query takes just slightly less than 1 millisecond!
 

If the data in your underlying tables changes more frequently you will be better served by using triggers on those tables that fire when INSERTs, UPDATEs, and/or DELETEs are performed on them and update the materialized view table according. For a good introduction to this check out the PostgreSQL manual section on triggers and PL/pgSQL Trigger Procedures.

Hopefully you can use this technique in the future to speed up some of your slower performing queries. 

Comments

[Shirley Banks]

I thought postgres doesnt have materialized views?

[Frank Wiles]

It doesn't have a internal/automatic method for creating materialized views, but you can do it yourself like I did above. Or use custom built triggers to create the functionality yourself. Hope this helps!