In backup and restore its taking 1 hour extra time than usual postgres

Question

Hi Guys, In backup and restore its taking 1 hour extra time than usual. Cause is materialized view. But, when excluded that materialized view, then no issues with backup and restore. What are the things to be checked in postgresql

Rajanikant Hawaldar · Answer

You need to check (make sure) that you stop using wrong tools (like dumps) for DR. ;) So, do you? Or, why / what for do you use dumps?

Pranam Bhat · Answer

If it is a complicated query, maybe the problem is that autoanalyze didn't have time yet to calculate table statistics. You can interrupt the statement with pg_cancel_backend() and recreate the materialized view later, perhaps after an ANALYZE, if it helps you to bring the rest of the database up quickly. Materialized views don't necessarily have better performance than the underlying query for the first access. The complexity of assembly of the data may be trivial. A number of joins that use indices would be much less intensive than a complex set of criteria, or something that requires functional processing or sorting for example. What you have found in this instance, is that the underlying query is just as good as a materialized view for the same dataset. You might want to consider a simple view for this case.

In backup and restore its taking 1 hour extra time than usual postgres

Answers (2)