MATERIALIZED VIEW

I can say I’m a kinda fan of PostgreSQL, every application I build mostly used PostgreSQL as the database, but TBH I don’t really know deeply about it.

Recently I’m struggling with tweaking searching data mechanism, the goal is how to fetch data rows from a table with some clauses as fast as possible. I’ve been using indexing, partitioning, caching, even grouping data, but it doesn’t satisfies me enough. (caching is OK tho, but it’s not that good practice in my case)

Then I tried to exploring to look at other alternatives, I was asking ChatGPT with this kinda question “How if I create a new brand table from a query with some criterias applied to reduce recalculation every time?”, the AI gave me insight to do it at the first time but then it also recommended me a better way, it’s MATERIALIZED VIEW, one of the fab features that PostgreSQL has.

At the time I thought it is the same as regular VIEW until I got the point of its comprehensive explanation. Let’s dive in a bit.

What is it❓

In essence MATERIALIZED VIEW or MV is a result of a query that saved physically in a database while regular VIEW will act as only an alias of defined query that will recalculate the query’s logic every time it’s executed, the MATERIALIZED VIEW will stay same as the data resulted when it’s built or refreshed.

If VIEW:
➡️ Every SELECT -> the logic of query is recalculated

Then MATERIALIZED VIEW:
➡️ A query is executed once to get the result
➡️ The result is saved
➡️ Next SELECT will read the result without adding any other logic (fast)

When MATERIALIZED VIEW properly used❓

MV is not a silver bullet, but you can get its benefit when:
✅ Data read heavy
✅ Costly query (a lot of joins, aggregate, sort, etc)
✅ Not serving a really realtime data
✅ Less latency is a must

Examples:
🔹 Rank Leaderboard 🔹 Report
🔹 Analythic Dashboard

• How to create a new MATERIALIZED VIEW:

CREATE MATERIALIZED VIEW sales_summary_2026
AS
SELECT * FROM sales
WHERE transaction_year = '2026';

• How to refresh (update data from the “source of truth” table)

REFRESH MATERIALIZED VIEW sales_summary_2026;

⚠️ Things to consider:

🔹 The size of MV depends on how much data it contains, it can be larger than you expected, but it’s usually smaller (because of filtering applied).
🔹 You can’t find data outside the MVs filtered result, it is fixed and permanent until you recreate or refresh the MV.
🔹 You can’t automate (in database side) refresh the MV, so you should do it manually or programmatically.
🔹 When an MV is refreshing, it will be freeze, you can’t read data from it, sounds awful, right?. Just chill out, there’s a solution will be explained below.

When source tables change, the MV you created doesn’t change/refresh automatically, it needs you to do the refresh, but be careful refreshing the MV will freezes the MV itself.

There’s a solution to avoid such a nightmare, it is REFRESH CONCURRENTLY keyword.

REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary_2026;

With REFRESH MATERIALIZED VIEW CONCURRENTLY:
✅ You can still read the old version of MV
✅ The database creates a new version of MV’s with new results updated
✅ The database swaps the data atomically

Note: to use REFRESH MATERIALIZED VIEW CONCURRENTLY you should assign unique index into the related MV, like:

CREATE UNIQUE INDEX
ON sales_summary_2026 (user_id);

Tips:
❌ Do not refresh too much MVs at the same time, 2 or 3 is still OK if the data result is not huge.
❌ Do not use trigger of UPDATE to do the refresh, it’s unpredictable (IMHO)
❌ Do not use MATERIALIZED VIEW if your case can be solved with only indexing, it only adds unnecessary complexity

MATERIALIZED VIEW is not a solution for all cases, it is not free (still costs), it is not realtime, and you have to operate it properly.

But if you manage it right, you’ll get performance increment, simpler query, less resource usage. Just use it when you need, not only when you want to.