0

The refresh time for a PostgreSQL materialized view is longer than creating a new one.

For example, the data select query executes in 10 minutes, and creating a new materialized view takes 15 minutes. However, refreshing the same materialized view takes almost 30-35 minutes.

It has 34 million records.

asked Jul 24, 2024 at 15:50
1
  • 1
    What's your question? Commented Jul 24, 2024 at 17:23

1 Answer 1

1

Since you didn't share any details, I can only guess: you are using REFRESH MATERIALIZED VIEW CONCURRENTLY.

Without CONCURRENTLY, the speed would be the same as for CREATE MATERIALIZED VIEW: the query is executed and the result materialized; the existing data file is removed.

A concurrent refresh proceeds differently: it executes the query and then performs INSERTs, UPDATEs and DELETEs on the existing materialized view data as appropriate. That is more effort, but allows the view to be read while it is being refreshed.

answered Jul 25, 2024 at 6:09

Your Answer

Draft saved
Draft discarded

Sign up or log in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

Post as a guest

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.