Discussion on: Explain Postgresql Views Like I'm Five

kirillian profile image
John Epperson

These answers are partly true. Everything said so far is pretty correct, especially considering the open source community used MySQL for so long which only has this type of view. However, in postgres, you have the option of creating a materialized view which is everything said above but then the database runs the query and stores the data to disk so that running queries against it doesn't spend the time running again. 9 times out of 10 in postgres, you will want to use materialized views.

The benefits of basic views are pretty much barely anything at all. You can get the benefits of a pre-planned and stored query by using prepared statements (which, you probably already do automatically if you use a modern framework as this is a standard defense against SQL injection).

At the end of the day, all features, views included are tradeoffs. I rarely find basic views to be worth it (though others on this page clearly have found reason for it). I DO find that materialized views give me enough value for the added complexity to justify their use when needed. Note that materialized views must be refreshed as they are a kind of caching of your data. But, then again, I really find that views make the most sense on the reporting side of things where caching is a respected and legitimate pattern.