Postgres Pro
Downloads
Home   >   mailing lists

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

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

From
Ram Pratap Maurya
Date:

Hi Team,

 

We are facing a problem in our PostgreSQL production database related to abnormal growth of index size. Some of the indexes are having abnormal growth and index size is larger than table data size.

One table is having 75 G.B of index though table size is only 25 G.B. On monthly basis we are performing vacuum to release the used space.

 

I am attaching the screen shot for your reference. Could you please help us in resolving the same as this is degrading performance drastically.

 

 

 

Regards,

Ram Pratap.

Lava International Limited.

Tel+  91-120-4637148

cid:image001.jpg@01CFD804.C427DF90

 

 

Attachment

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

From
Ravi Krishna
Date:


On May 6, 2020, at 5:48 AM, Ram Pratap Maurya <ram.maurya@lavainternational.in> wrote:

Hi Team,
 
We are facing a problem in our PostgreSQL production database related to abnormal growth of index size. Some of the indexes are having abnormal growth and index size is larger than table data size.
One table is having 75 G.B of index though table size is only 25 G.B. On monthly basis we are performing vacuum to release the used space.
 
I am attaching the screen shot for your reference. Could you please help us in resolving the same as this is degrading performance drastically.
 
<image002.png>


How many indexes are there in the table tstock_movement?  Could it be that 65GB is the sum total of index size of all indexes.

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

From
Christian Ramseyer
Date:
Hi

On 06.05.20 11:48, Ram Pratap Maurya wrote:

> We are facing a problem in our PostgreSQL production database related to
> abnormal growth of index size. Some of the indexes are having abnormal
> growth and index size is larger than table data size.
> 
> One table is having 75 G.B of index though table size is only 25 G.B. On
> monthly basis we are performing vacuum to release the used space.
> 
> 
> I am attaching the screen shot for your reference. Could you please help
> us in resolving the same as this is degrading performance drastically.
> 

Under some usage patterns, a periodic REINDEX might be advisible. See
<https://www.postgresql.org/docs/current/routine-reindex.html> for more
details, it might free up a lot of space for you.

If it doesn't, you'll need to dive deeper into what this indexes
actually are, if they are really used etc. But in cases of abnormal
growth that gets worse and worse over time, the above is the first thing
to try in my experience.


Cheers
Christian



-- 
Christian Ramseyer, netnea ag
Network Management. Security. OpenSource.
https://www.netnea.com

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

From
Ram Pratap Maurya
Date:

Hello Ravi,

 

Total number of index is 10 and 65GB is the sum total of index size of all indexes for table “tstock_movement”

I am attaching the screen shot for your reference.

 

 

 

Regards,

Ram Pratap.

Lava International Limited.

Tel+  91-120-4637148

cid:image001.jpg@01CFD804.C427DF90

 

 

From: Ravi Krishna [mailto:srkrishna1@comcast.net]
Sent: 06 May 2020 16:28
To: Ram Pratap Maurya
Cc: pgsql-general@postgresql.org; Ashish Chugh
Subject: Re: Abnormal Growth of Index Size - Index Size 3x large than table size.

 

 



On May 6, 2020, at 5:48 AM, Ram Pratap Maurya <ram.maurya@lavainternational.in> wrote:

 

Hi Team,

 

We are facing a problem in our PostgreSQL production database related to abnormal growth of index size. Some of the indexes are having abnormal growth and index size is larger than table data size.

One table is having 75 G.B of index though table size is only 25 G.B. On monthly basis we are performing vacuum to release the used space.

 

I am attaching the screen shot for your reference. Could you please help us in resolving the same as this is degrading performance drastically.

 

<image002.png>

 

 

How many indexes are there in the table tstock_movement?  Could it be that 65GB is the sum total of index size of all indexes.

 

Attachment

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

From
Michael Lewis
Date:
Indexes larger than the table may be expected if there are many. It may be prudent to check if they are being used in pg_stat_all_indexes.

If there are just a few indexes that are becoming bloated quickly, you'd want to ensure your autovacuum settings are tuned more aggressively, and consider lowering FILLFACTOR on the table to better support heap-only tuples (HOT) updates such that the index isn't touched when other columns are updated in the table. If you are on PG12, you can reindex concurrently assuming you have the space. If not, you can do the same manually like below-

CREATE INDEX CONCURRENTLY idx_new...
DROP INDEX CONCURRENTLY idx_old...
ALTER INDEX idx_new... RENAME TO idx_old...

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

From
Ravi Krishna
Date:

Hello Ravi,
 
Total number of index is 10 and 65GB is the sum total of index size of all indexes for table “tstock_movement”
I am attaching the screen shot for your reference.

 
In that case 65GB is not surprising.

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

From
Ravi Krishna
Date:


On May 6, 2020, at 10:52 AM, Ashish Chugh <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.

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

From
"Ashish Chugh"
Date:

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.

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> 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.

 

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

From
Michael Lewis
Date:
On Thu, May 7, 2020 at 8:50 AM Ashish Chugh <ashish.chugh@lavainternational.in> wrote

To improve performance and release index space from database, We are running FULL Vacuum on monthly basis.

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.



Autovacuum should be tuned to be more aggressive if it is not keeping up. Never turn it off. Decrease scale factor and cost_delay to get more throughput. Perhaps increase the number of workers, particularly if there are multiple databases in the cluster. Be aware that the cost limit is shared among workers so that cost limit may need to be increased when increasing workers or else you will be working on more tables concurrently, but not getting anymore work done in total per minute. Consider customizing parameters on very large tables (100 million rows or more?) to have a smaller scale factor than your new default even. Your goal should be to reach a "steady state" with rows being removed, that space marked as re-usable by autovacuum, and then the new updates/inserts using that space. If you are oscillating between 1GB and 10GB for storing a table as it bloats and then vacuum full is done periodically, then you are doing things wrong. If it hurts to clean up, do it more often and a little at a time.

Oh, and an old blog post I read mentioned that autovacuum reserves the full maintenance_work_mem at the start. I don't know if that is changed (fixed) now, but I like to have maintenance_work_mem high for index creation and such, but set autovacuum_work_mem to be lower such that perhaps it has to re-scan some large indexes multiple times to finish its work, but I'm not constantly holding large amounts of memory when doing vacuum on smaller tables.

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

From
Adrian Klaver
Date:
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