3

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.

  • How many rows / size does each materialized view has? – Imanol Y. Mar 22 '18 at 8:00
  • 1
    Does the size reduce when you do a vacuum full on the mview? What is your exact Postgres version? – a_horse_with_no_name Mar 22 '18 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 Mar 22 '18 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 Mar 22 '18 at 18:52
6

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.

  • I'm having the same issue, going to try this, thanks – John Smith Apr 19 '18 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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