Re: DB size growing exponentially when materialized view refreshed concurrently (postgres 9.6)

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 <haramrae(at)gmail(dot)com> wrote:

>
> > On 25 Jun 2018, at 19:21, Vikas Sharma <shavikas(at)gmail(dot)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.
>
>