Re: bloat indicator using n_dead_tup column

[Guillaume Lelarge <guillaume@xxxxxxxxxxxx>]

Hi,

Le mar. 25 mai 2021 à 06:38, Yambu <hyambu@xxxxxxxxx> 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.