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.

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&gt; 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

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.

[cid:image001.png@01D623D7.65173990]

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

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

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.

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.

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

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.

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:

/messages/by-id/1392022649.706483.1587523402642@mail.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