Re: bloat indicator using n_dead_tup column

Hi,

Le mar. 25 mai 2021 à 06:38, Yambu <hyambu(at)gmail(dot)com> a écrit :

> Hello
>
> I would like to know if the below query can be used as a bloat indicator.
>
> select
> n_dead_tup
> / (n_live_tup * current_setting('autovacuum_vacuum_scale_factor')::float8
> + current_setting('autovacuum_vacuum_threshold')::float8) > 1 then true
> else false end as bloated_indicator
> FROM pg_stat_user_tables
>
>
The above query will tell you when the autovacuum will kick in, and start
vacuuming some tables.

> i want to use column n_dead_tup to get an estimate or indicator of
> impending bloat
>
>
No. A relation has live tuples, dead tuples, and free space. The bloat is
the dead tuples and the free space. The above query won't tell you how much
free space there is in the table. If you want an estimate of the bloat, you
should use a query such as those here:
https://github.com/ioguix/pgsql-bloat-estimation. And if you want a more
precise information, you should use pgstattuple (which will be slower
because it reads the whole table).

--
Guillaume.