Analytics Vidhya
Published in

Analytics Vidhya

Difference between view and materialized view?

Tracking issue of materialized view

  1. The addition of the materialized views feature in Postgres represents a substantial benefit on read-queries performance if our application can tolerate stale(old)data.
  2. It helps to reduce disk access and complex query computations by flattening a View’s result set into a physical table.
  3. Auditing the creation, modification, and refresh operations for the materialized views can be time-consuming.

Postgres extension called mv_stats, which allows the DataOps to track and query this information in a more practical manner.

In our database execute:


Note:- Previously created views will be automatically added to the stats on blank, and they’ll update on the next refresh

select * from mv_stats;

As a output, we will get various parameters such as mv_name,refresh_mv_last as refresh_last, refresh_count, refresh_mv_time_last as refresh_time_last ,refresh_mv_time_total as refresh_time_total, refresh_mv_time_min as refresh_time_min,refresh_mv_time_max as refresh_time_max, reset_last

Using the mv_stats extension it is possible to provide an easy way to collect and query the statistics of materialized view related to the creation, modification, and the time to refresh, for auditing or performance analysis purposes. By using the extensibility capabilities of Postgres, it avoids the process of exhaustive log search and matching data from pg_stat_statements.

Do clap if find useful🙂.

Analytics Vidhya is a community of Analytics and Data Science professionals. We are building the next-gen data science ecosystem

Recommended from Medium

Google Cloud Platform — Associate Cloud Engineer (GCP ACE) Certification | Preparation Guide

What is Superfetch and How to Disable or Enable Superfetch • TechLila

LeetCode Problem : Sign of the Product of an Array



A Boy and His 286

Flutter Stream Builder

A gist of my experiences and learning outcomes

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Nidhi Gupta

Nidhi Gupta

Database Engineer having working experience in SQL and NO-SQL databases. 👨‍💻

More from Medium

Postgresql: Caching with pg_cron and Materialized Views

SQL vs. NoSQL vs. CQL: Comparisons & Tested Scenarios

MongoDB — Unions 🇬🇧

Analyzing Application Logs With SQL