7

I have a production postgres 10 database. Every hour I run "refresh materialized view concurrently" on my materialized view to recalculate the values; only a few new rows get added each day.

All the materialized views (and their indexes) are increasing in size dramatically. The view that should be 102 MB is now 1700 MB and the indexes have suffered similar inflation. And the refresh time has increased from 17 seconds to 10 minutes. The overall DB size has grown from 4 GB to 21 GB in the last day due to the views and their indexes growing. I can't do a non-concurrent refresh as these views are constantly being read from.

Running a refresh triggers a large amount of disk I/O.

I have no idea what is causing this issue. I have autovacuum enabled and configured. I have 8GB of memory, and work_mem is set to 256 MB.

Improve this question
4
  • How many rows / size does each materialized view has?
    – Imanol Y.
    Commented Mar 22, 2018 at 8:00
  • 1
    Does the size reduce when you do a vacuum full on the mview? What is your exact Postgres version?
    – user1822
    Commented Mar 22, 2018 at 8:13
  • Each row is three bigints. There are two million rows. I can’t run a vacuum full without taking the production site down.
    – FluxDipole
    Commented Mar 22, 2018 at 15:08
  • Surely you have a test environment, right? Run the non-concurrent refresh against that and see if it shrinks the size.
    – jjanes
    Commented Mar 22, 2018 at 18:52

2 Answers 2

Reset to default
11

My issue was running a "refresh materialized view concurrently" before the autovacuum had finished running, leading to table bloat. Now I manually vacuum analyze after every refresh.

Improve this answer
1
  • I'm having the same issue, going to try this, thanks
    – John Smith
    Commented Apr 19, 2018 at 19:56
1

You haven't provided any way of telling what the problem really is.

One thing to check though are whether you have any long running transactions. They might be idle but still able to see old versions of the view rows. That would mean that each refresh would effectively add another copy of you view's materialized rows.

See what is in the system view pg_stat_activity and pay close attention to the timestamps.

Improve this answer

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.