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

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(at)comcast(dot)net]
> *Sent:* Wednesday, May 06, 2020 9:07 PM
> *To:* Ashish Chugh <ashish(dot)chugh(at)lavainternational(dot)in>
> *Cc:* pgsql-general(at)postgresql(dot)org; Ram Pratap Maurya
> <ram(dot)maurya(at)lavainternational(dot)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(dot)chugh(at)lavainternational(dot)in
> <mailto:ashish(dot)chugh(at)lavainternational(dot)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(dot)klaver(at)aklaver(dot)com