Materialized Views vs. Rollup Tables in PostgreSQL: Choosing the Right Approach

Materialized views have long been a sought‑after feature in PostgreSQL, becoming usable in version 9.3 with limited capabilities and gaining concurrent refresh support in 9.4, though they may still be unsuitable for all workloads.

A regular view is a stored query that can be queried like a table, useful for simplifying complex reporting logic, but it recomputes its result on every execution, which can be costly for large datasets.

To illustrate a materialized view, the article defines a pageviews table that records page, timestamp, and session information, then creates a materialized view that aggregates daily page view counts:

CREATE MATERIALIZED VIEW rollups AS SELECT date_trunc('day') AS day, page, count(*) AS views FROM pageviews GROUP BY date_trunc('day'), page;

Refreshing the view with REFRESH MATERIALIZED VIEW rollups; recomputes the entire result set, which can be wasteful if only new data needs to be added.

For a more scalable solution, the article proposes an incremental rollup using an upsert pattern: a regular table with a unique constraint on (day, page) is created, and new data is inserted or merged with existing rows:

CREATE TABLE rollups ( day timestamptz, page text, views bigint, CONSTRAINT unq_page_per_day UNIQUE (day, page) );

New records are added with:

INSERT INTO rollups SELECT day, page, count(*) AS views FROM pageviews WHERE event_id > e GROUP BY day, page ON CONFLICT (day, page) DO UPDATE SET views = rollups.views + EXCLUDED.views;

The article concludes that materialized views are simple and quick to set up, making them ideal for low‑traffic scenarios, whereas incremental rollup tables with upsert are more efficient for high‑traffic, large‑scale environments, allowing you to process only the net new data.

Original source: https://www.citusdata.com/blog/2018/10/31/materialized-views-vs-rollup-tables/.