Hi Alban, I haven't disabled autovacuum task, it's running fine for other objects. I was also getting "Error: cancelling autovacuum task" on the materialized view when concurrently refreshed so decided to write a script and run vacuum (full, analyze) the MV immediately after concurrent refresh but still it's not working as intended.
On Mon, Jun 25, 2018, 20:02 Alban Hertroys <haram...@gmail.com> wrote: > > > On 25 Jun 2018, at 19:21, Vikas Sharma <shavi...@gmail.com> wrote: > > > > I am looking for advice in a issue where two materialized views are > being refreshed concurrently and dbsize has grown to 150gb from 4gb in two > days. > > > > We use two materialized views to keep processed data for faster query > results for a search function. Earlier materialized views were refreshed > not concurrently and all was good on DB. > > > > We changed mv refresh to concurrently to take advantage of simultaneous > access when mv refreshed. Now the refresh takes slightly longer and but DB > size has grown exponentially. > > > > I ran full vacuum on DB and size again reduced to 4gb from 150gb. > > You did not disable or tune down autovacuum perchance? > With materialized view refreshes that often, you probably need fairly > aggressive autovacuuming on that table - you can tune autovacuum parameters > per table (see Storage parameters). That probably won't put you at 4GB, > more around double that size, but it should stay a reasonable size that way. > > Regards, > > Alban Hertroys > -- > If you can't see the forest for the trees, > cut the trees and you'll find there is no forest. > >
