Views v.s. materialized Views v.s. Rollup Tables with PostgreSQL

For applications it’s often really annoying to aggregate data in memory with a self written command/program instead of having this already done by the storage engine, which holds the data. The point is, it’s really cumbersome to fetch data from a database, aggregate it within a program and write it to another table, which will be used as data source for another program. When you see something like this, please always try to get rid of this aggregation work and let your storage engine handle it. So let’s clearify how materialized views can help in this situation.

What are Views?

First of all we need to clearify what is a View? A View is something like a virtual table/structure that pulls data from one or more tables and you can query against it.

Views help to not copy & paste a complex query around. Yes it can be solved by introducing a repository in your application, but it’s better to keep your application as simple as possible. The disadvantage of a view is, that for every query hitting the view, the data will be recomputed. This might affect your application performance.

What are materialized Views?

To fix the recomputation problem with views, PostgreSQL offers materialized views. In general it’s a…