Postgres Pro
Downloads
Home   >   mailing lists

DB size growing exponentially when materialized view refreshedconcurrently (postgres 9.6) - Mailing list pgsql-general

From Vikas Sharma
Subject DB size growing exponentially when materialized view refreshedconcurrently (postgres 9.6)
Date
Msg-id CAN6gwKy6EP4D9O5a1VmPM+YVOKGj7euuGPm9aZVkJHdcwgRtww@mail.gmail.com
Whole thread Raw
Responses Re: DB size growing exponentially when materialized view refreshedconcurrently (postgres 9.6)  (Alban Hertroys <haramrae@gmail.com>)
Re: DB size growing exponentially when materialized view refreshedconcurrently (postgres 9.6)  (Jeff Janes <jeff.janes@gmail.com>)
List pgsql-general
Tree view
Hi All,

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. 

We need to refresh mvs every 5 mins so I created a script to refresh MV concurrently and then running vacuum (full,analyze) on the mv immediately. I hoped it would solve the issue of DB size growing exponentially but it hasn't and size still growing.

Please advice how can I refresh MV concurrently and DB size doesn't grow.

Much appreciated.

Regards
Vikas

pgsql-general by date:

Previous
From: Vijaykumar Jain
Date:
Subject: Re: [External] Re: Schema/Data conversion opensource tools from MySQLto PostgreSQL
Next
From: Andres Freund
Date:
Subject: Re: Too many range table entries error