Re: Abnormal Growth of Index Size - Index Size 3x large than tablesize.

On 5/7/20 6:34 AM, Ashish Chugh wrote:
> Hi Ravi,
> 
> Thanks for your reply. One more query from our side.
> 
> To improve performance and release index space from database, We are 
> running FULL Vacuum on monthly basis.

As I recently learned:

https://www.postgresql.org/message-id/1392022649.706483.1587523402642%40mail.yahoo.com

To release index space index without a FULL vacuum you need to REINDEX. 
Look at the message above for more information.

> 
> On PostgreSQL website it is not recommended to run FULL Vacuum on 
> Production Database and this also requires long downtime along with huge 
> log space requirement.

> 
> What are the recommendations regarding vacuum. Can we run FULL Vacuum on 
> monthly basis or we should be running Online Auto Vacuum instead.
> 
> Regards,
> 
> Ashish
> 
> *From:*Ravi Krishna [mailto:srkrishna1@comcast.net]
> *Sent:* Wednesday, May 06, 2020 9:07 PM
> *To:* Ashish Chugh <ashish.chugh@lavainternational.in>
> *Cc:* pgsql-general@postgresql.org; Ram Pratap Maurya 
> <ram.maurya@lavainternational.in>
> *Subject:* Re: Abnormal Growth of Index Size - Index Size 3x large than 
> table size.
> 
> 
> 
>     On May 6, 2020, at 10:52 AM, Ashish Chugh
>     <ashish.chugh@lavainternational.in
>     <mailto:ashish.chugh@lavainternational.in>> wrote:
> 
>     Hello Ravi,
> 
>     Total number of indexes are 10 and size is 65 GB. Shall we consider
>     this as a normal scenario or we need to look into the growth of the
>     indexes as this is increasing day by day and table data is not
>     increasing so drastically. Due to this performance degradation is
>     there and we have to run full vacuum on monthly basis.
> 
>     Table size is only 25 gb.
> 
>     Any help in this regard is appreciable.
> 
> Indexes are stored just like tables. From storage perspective there is 
> no difference between a table and an index.
> 
> So the sum of 10 different tables to 65GB, compared to 25GB of one table 
> sounds possible.
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com