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 disc-stored view that can be refreshed whenever you need it and also supports indices. Since version 9.4, materialized views can be refreshed concurrently without holding a lock on a table.
Project setup
Let’s see how we can profit from a materialized view by building a daily report for advertisement clicks on a website. We create a table to track all the ad clicks on a website. I prepared a postgreSQL Docker setup for version 12 with the pg_cron extension enabled: https://github.com/stefpe/postgres_cron_rollup
So let’s get our hands dirty and boot our postgreSQL and a pgadmin interface:
### postgresql 12 with pg_cron extension
git clone https://github.com/stefpe/postgres_cron_rollup.gitcd postgres_cron_rollup && docker build -t postgre .docker run -e POSTGRES_PASSWORD=test -p 5432:5432 -d postgre