Postgresql – Postgres Materialized View Dramatically Increasing In Size


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.

Best Answer

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.