REFRESH MATERIALIZED VIEW suddenly taking more time to complete

user_2316672 0
Viewed 1755

We have a materialized view in our Postgres DB (11.12, managed by AWS RDS). We have a scheduled task that updates it every 5 minutes using REFRESH MATERIALIZED VIEW <view_name>. At some specific point last week, the time needed to refresh the view suddenly went from ~1s to ~20s. The view contains ~70k rows, with around 15 columns, all of them being integers, booleans or UUIDs.

Prior to this:

  • There were no changes in the server configuration.
  • There were no changes to the view itself. In fact, running EXPLAIN ANALYZE <expression used to create the view> returns that the query still gets executed in less than a second. If the query is ran with a client like Postico, it takes ~20s to fetch all the results (a bit consistent with the time needed to materialize it, although we assume this is due to the time needed for network transmission).
  • There were no changes in the schema or any significant record increase in the contents of the tables needed to compute the view.
  • RDS Performance Insights indicate that the query is mostly using CPU resources

I know this is probably not enough to get a solution, but:

  • Are there any server performance metrics or logs that could lead us to understand better this situation?
  • Is this just the normal time the server needs to persist the view to disk? If so, any idea of possible reasons why it started to take so long recently?

Here is a link to the execution plan.

EDIT: creating another materialized view with the same JOINS but selecting less columns performs as expected (~1s).

EDIT 2: setting enable_nestloop = false greatly speeds up the REFRESH operation (same performance as before). Would this indicate that refactoring the underlying query could solve the issue?

postgresql
2 Answers

Updates materialized view every time (or every 5 minutes) this is not a good way to refresh materialized. Then the meaning of using materialized view does not remain. Let me explain to you one of the ways I found with my own logic, based on my own experience, so you can find a more optimal way later. Assumed, we used two tables in our materialized view, and we need that is a changed data one of the two tables we will refresh materialized view. To do this during the update or delete table we must insert to the table (for example refresh_materialized table) one record (you can also use the trigger), through which will be performed refreshing materialized view

For example:

insert into refresh_materialized 
(
    refresh_status, 
    insert_date, 
    executed_date 
)
values (
    false,
    now(), 
    null 
)

And so in our schedule task, we can use this query:

select count(*) from refresh_materialized 
where refresh_status = false

if the count(*) will be > 0 then we must refresh materialized view else do nothing. After the refreshing materialized view we must update this table:

update refresh_materialized 
set 
    refresh_status = true, 
    executed_date = now() 
where 
    refresh_status = false;
user_172960840

Try REFRESH materialized view concurrently. When you refresh data for a materialized view, PostgreSQL locks the entire table therefore you cannot query data against it. To avoid this, you can use the CONCURRENTLY option.

REFRESH MATERIALIZED VIEW CONCURRENTLY view_name;

With CONCURRENTLY option, PostgreSQL creates a temporary updated version of the materialized view, compares two versions, and performs INSERT and UPDATE only the differences.

You can query against a materialized view while it is being updated. One requirement for using CONCURRENTLY option is that the materialized view must have a UNIQUE index.

user_12131190
Related