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

From: Vikas Sharma <shavikas(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: DB size growing exponentially when materialized view refreshed concurrently (postgres 9.6)
Date: 2018-06-25 17:21:51
Message-ID: CAN6gwKy6EP4D9O5a1VmPM+YVOKGj7euuGPm9aZVkJHdcwgRtww@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andres Freund 2018-06-25 18:14:10 Re: Too many range table entries error
Previous Message Vijaykumar Jain 2018-06-25 17:01:13 Re: [External] Re: Schema/Data conversion opensource tools from MySQL to PostgreSQL