Question 298635 in PostgreSQL Question posted in
The official documentation can be found here.

What is the use of Materialized View in PostgreSQL

I am making a dashboard system feature where i have a view for a specific report, the database I’m using is PostgreSQL. I plan to turn it to a materialized view to optimize it, but it will make the dashboard’s data not real time, so i plan to refresh the materialized view every time the source table’s data is updated. But, after reading the documentation here:
https://www.postgresql.org/docs/current/sql-refreshmaterializedview.html

It said that REFRESH MATERIALIZED VIEW just completely replaces the contents of a materialized view, thus lowering the performance when updating the base table. That made me wonder, is there actually a good use for materialized views?

Tags:

2

Answers


  1. 0

    A materialized view is a tool that allows queries that would otherwise take too long to complete quickly, at the price of working with slightly stale data. You use it if you cannot find a better solution.

    Login or Signup to reply.
  2. 0

    A Materialized View in your situation (a dashboard) has plenty of value.

    A View takes a query and allows you to call that query Q and retrieve its results as somewhat of a shortcut. However, when you call the View, it executes query Q every time and gets the real-time results, meaning that if the query Q takes a long time (i.e., 5-10 seconds, or however you would define "a long time"), you could see users of your dashboard getting frustrated at the slowness.

    In the case of a Materialized View, the query Q is executed once, and its results are saved. When you retrieve information from the Materialized View, it can take (for example) <1 second every time, depending on your data set instead of 5-10 seconds. This would be a faster experience for your dashbaord users. And then refreshing the Materialized View would take the amount of time to execute query Q (5-10 seconds). In this hypothetical example, an occasional call to Q that takes 5-10 seconds so that users can get data in <1 second is far more efficient than those users having to wait 5-10 seconds every time.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search