MATERIALIZED VIEW vs VIEW in PostgreSQL

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:

CREATE EXTENSION mv_stats;

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

CONCLUSION

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🙂.