Thread: Query to monitor index bloat

On 07/16/2018 05:16 PM, Alessandro Aste wrote:
> Hello,  I am trying to put togheter a query to monitor the index bloat 
> for a database I maintain.
> Is there a "SQL" way to obtain  bloated index ? I googled around but I 
> found nothing working.
> 
> I'm currently running 9.6 but I'm looking for something compatible with 
> version 10 too.
> 
> Thank you very much in advance,
> 
> 
> Alessandro.

Hello,

You should look at : https://github.com/ioguix/pgsql-bloat-estimation

Regards,

On 07/16/2018 11:50 PM, Alessandro Aste wrote:
> Thanks much, I'll check that out.  I see the queries are 3 years old so I'm
> wondering if they still work for 9.6.x or 10

AFAIK they work. You can try yourself by comparing bloated table/index size
before and after vacuum full or reindex ;)

If you monitor using nagios (or if you want to make a wrapper around it):

https://bucardo.org/check_postgres/check_postgres.pl.html#bloat

works like a charm.

regards,

fabio pardi


On 07/16/2018 05:16 PM, Alessandro Aste wrote:
> Hello,  I am trying to put togheter a query to monitor the index bloat
> for a database I maintain.
> Is there a "SQL" way to obtain  bloated index ? I googled around but I
> found nothing working.
> 
> I'm currently running 9.6 but I'm looking for something compatible with
> version 10 too.
> 
> Thank you very much in advance,
> 
> 
> Alessandro. 

On Tue, 17 Jul 2018 10:11:50 +0200
Fabio Pardi <f.pardi@portavita.eu> wrote:

> If you monitor using nagios (or if you want to make a wrapper around it):
>
> https://bucardo.org/check_postgres/check_postgres.pl.html#bloat

...and check this one: https://github.com/OPMDG/check_pgactivity/

It uses bloat queries for tables and btree indexes Adrien Nayrat was pointing
earlier in this thread.

In fact, both queries in check_pgactivity were written because the bloat check
in check_postgres was considering **all** fields were in **all***
indexes...Which is quite a large approximation...I don't know if this is still
the case though.

> works like a charm.
>
> regards,
>
> fabio pardi
>
>
> On 07/16/2018 05:16 PM, Alessandro Aste wrote:
> > Hello,  I am trying to put togheter a query to monitor the index bloat
> > for a database I maintain.
> > Is there a "SQL" way to obtain  bloated index ? I googled around but I
> > found nothing working.
> >
> > I'm currently running 9.6 but I'm looking for something compatible with
> > version 10 too.
> >
> > Thank you very much in advance,

On 07/17/2018 10:21 AM, Jehan-Guillaume (ioguix) de Rorthais wrote:
> On Tue, 17 Jul 2018 10:11:50 +0200

> ...and check this one: https://github.com/OPMDG/check_pgactivity/
> 
> It uses bloat queries for tables and btree indexes Adrien Nayrat was pointing
> earlier in this thread.
> 
> In fact, both queries in check_pgactivity were written because the bloat check
> in check_postgres was considering **all** fields were in **all***
> indexes..

not accurately, since it is excluding a few things.

from the docs:
'Tables must have at least 10 pages, and indexes at least 15, before
they can be considered by this test.'

+ you can include and exclude objects based on your taste, same as in
check_pgactivity.

The only 'drawback' of check_postgres.pl is that it checks indexes and
tables's bloat in one go. (but: if your object's names are normalized,
it should not be difficult to include or exclude them)
I do not consider it a drawback, but you are free to pick your poison...


.Which is quite a large approximation...I don't know if this is still
> the case though.

While i think both tools might fit Alessandro's purpose, please note
that check_pgactivity is **only** checking for btree indexes (which are
the default ones, and the proven-to-get-bloated-quickly)

If I were you (both), I would monitor **all** indexes (and yes! tables
too), since one day you might realize it was actually a good idea to do so.

regards,

fabio pardi

On Tue, 17 Jul 2018 11:03:08 +0200
Fabio Pardi <f.pardi@portavita.eu> wrote:

> On 07/17/2018 10:21 AM, Jehan-Guillaume (ioguix) de Rorthais wrote:
> > On Tue, 17 Jul 2018 10:11:50 +0200  
> 
> > ...and check this one: https://github.com/OPMDG/check_pgactivity/
> > 
> > It uses bloat queries for tables and btree indexes Adrien Nayrat was
> > pointing earlier in this thread.
> > 
> > In fact, both queries in check_pgactivity were written because the bloat
> > check in check_postgres was considering **all** fields were in **all***
> > indexes..  
> 
> not accurately, since it is excluding a few things.
> 
> from the docs:
> 'Tables must have at least 10 pages, and indexes at least 15, before
> they can be considered by this test.'

well I agree with this. What the point of computing bloat for small objects? I
would raise this way higher.

> + you can include and exclude objects based on your taste, same as in
> check_pgactivity.
> 
> The only 'drawback' of check_postgres.pl is that it checks indexes and
> tables's bloat in one go. (but: if your object's names are normalized,
> it should not be difficult to include or exclude them)
> I do not consider it a drawback, but you are free to pick your poison...

Well, again, the btree approximation is quite large in check_postgres. I would
not rely on it detect bloat quickly. **If this is still true**, as it considers
all fields are in the index, the estimated index size might be veeeeery
large compared to the real one.

But, again, this is a few years I did not digg in this query, I mmight be wrong.

> > Which is quite a large approximation...I don't know if this is still
> > the case though.  
> 
> While i think both tools might fit Alessandro's purpose, please note
> that check_pgactivity is **only** checking for btree indexes (which are
> the default ones, and the proven-to-get-bloated-quickly)
> 
> If I were you (both), I would monitor **all** indexes (and yes! tables
> too), since one day you might realize it was actually a good idea to do so.

I agree, we should monitor all indexes. If you have some formula to quickly
estimate ideal size of a GIN, GiST, hash or sp-gist indexes, please share. But,
unfortunately, as far as I know, this is way more complex than just summing the
average size of the fields in the index :/

you have your points, my friend.


On 07/17/2018 11:23 AM, Jehan-Guillaume (ioguix) de Rorthais wrote:
> On Tue, 17 Jul 2018 11:03:08 +0200
> Fabio Pardi <f.pardi@portavita.eu> wrote:
> 
>> On 07/17/2018 10:21 AM, Jehan-Guillaume (ioguix) de Rorthais wrote:
>>> On Tue, 17 Jul 2018 10:11:50 +0200  
>>
>>> ...and check this one: https://github.com/OPMDG/check_pgactivity/
>>>
>>> It uses bloat queries for tables and btree indexes Adrien Nayrat was
>>> pointing earlier in this thread.
>>>
>>> In fact, both queries in check_pgactivity were written because the bloat
>>> check in check_postgres was considering **all** fields were in **all***
>>> indexes..  
>>
>> not accurately, since it is excluding a few things.
>>
>> from the docs:
>> 'Tables must have at least 10 pages, and indexes at least 15, before
>> they can be considered by this test.'
> 
> well I agree with this. What the point of computing bloat for small objects? I
> would raise this way higher.
> 
>> + you can include and exclude objects based on your taste, same as in
>> check_pgactivity.
>>
>> The only 'drawback' of check_postgres.pl is that it checks indexes and
>> tables's bloat in one go. (but: if your object's names are normalized,
>> it should not be difficult to include or exclude them)
>> I do not consider it a drawback, but you are free to pick your poison...
> 
> Well, again, the btree approximation is quite large in check_postgres. I would
> not rely on it detect bloat quickly. **If this is still true**, as it considers
> all fields are in the index, the estimated index size might be veeeeery
> large compared to the real one.
> 
> But, again, this is a few years I did not digg in this query, I mmight be wrong.
> 
>>> Which is quite a large approximation...I don't know if this is still
>>> the case though.  
>>
>> While i think both tools might fit Alessandro's purpose, please note
>> that check_pgactivity is **only** checking for btree indexes (which are
>> the default ones, and the proven-to-get-bloated-quickly)
>>
>> If I were you (both), I would monitor **all** indexes (and yes! tables
>> too), since one day you might realize it was actually a good idea to do so.
> 
> I agree, we should monitor all indexes. If you have some formula to quickly
> estimate ideal size of a GIN, GiST, hash or sp-gist indexes, please share. But,
> unfortunately, as far as I know, this is way more complex than just summing the
> average size of the fields in the index :/
>