Materialized view refreshing problem

From: Hellen Jiang <hjiang(at)federatedwireless(dot)com>
To: "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Materialized view refreshing problem
Date: 2023-08-24 16:54:15
Message-ID: BLAPR17MB4193164502E8A94C0A2A7080D21DA@BLAPR17MB4193.namprd17.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi All,

We have one business critical materialized view( let’s called materialized_view_1) which contains around 16M records.
This materialized view is refreshed CONCURRENTLY every 1 minute, and got refreshed normally (without CONCURRENTLY) every 6 hours.

But from time and time, we found that it took forever to refresh materialized view( normally or concurrently), and there is no response to query from this materialized view.
In this case, we have to create new materialized view(materialized_view_1_new) with exactly the same definition, and the new materialized view(materialized_view_1_new) works well and the refresh finishes in 15 seconds.
And after about 1 months, we have to do this again: I mean use freshly new materialized view to replace the old one.

It looks like there are too much garbage in the old materialized view as time going.
Any idea how we can fix this materialized view refreshing problem?

Thanks

Hellen Jiang

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2023-08-24 19:02:29 Re: Materialized view refreshing problem
Previous Message duc hiep ha 2023-08-24 16:45:18 Re: ora2pg -c ora2pg.conf -t COPY -a tablename not working properly