Postgres bloat query

When an existing row is updated or deleted in a table, it results in new version of the row, while also keeping the previous version of the row for read consistency. Once the consistenant version (or old version) of the row is no longer needed, the old version becomes a “dead tuple”. These dead tuples are normally removed by the autovacuum process. But for other reason it may not be.

SELECT relname AS table_name, n_live_tup, n_dead_tup, ROUND((n_dead_tup::numeric / NULLIF(n_live_tup, 0)) * 100, 2) AS dead_tuple_pct, last_autovacuum, last_vacuum FROM pg_stat_user_tables WHERE n_live_tup > 0 AND (n_dead_tup::numeric / n_live_tup) > 0.10 ORDER BY dead_tuple_pct DESC;

****************************************** 

keywords:Postgres bloat 

****************************************** 

rdbms version: Postgres v15 

******************************************