Find bloated tables and indexes in PostgreSQL without extensions

Question

I have tables that change a lot during the day, lots of data is deleted, modified and inserted.

I suspect that the tables and indexes on those tables might be bloated.

I've seen that there are extensions options for PostgreSQL that can check this, but I would like to avoid creating extensions in my database.

How can I get this information (table/index is bloated) without having to use PostgreSQL extensions (eg.:pgstattuple), using only native PostgreSQL 12 features.?

• Laurenz Albe: You cannot do that without pgstattuple. Why do people so often ask questions like "how can I do X without Y", where Y is the proper solution for X?
• user1822: pgstattuple is a "built-in" extension and available in every Postgres installation (at least installable in the same way as Postgres itself)
• user1822: See here or here or here or here
• Vérace: This might be of help.
• Laurenz Albe: @ALZ Yes, it scans the whole table.

Answer 1

As you have already been answered in the comments, it is best to use the standard pgstattuple extension. If you do not want to use it, for some reason, you can see the approximate bloat values as follows:

-- Perform ANALYZE on your table
ANALYZE <table_name>;
-- Get the number of deadlines in your tables.
select schemaname,
relname,
pg_size_pretty(pg_relation_size(schemaname|| '.' || relname)) as size,
n_live_tup,
n_dead_tup,
CASE WHEN n_live_tup > 0 THEN round((n_dead_tup::float / 
n_live_tup::float)::numeric, 4) END AS dead_tup_ratio,
last_autovacuum,
last_autoanalyze
from pg_stat_user_tables
order by dead_tup_ratio desc NULLS LAST;

The higher the dead_tup_ratio, the higher the bloat of your table. But these are approximate data collected in the course of collecting statistics! The best way to know for sure about bloat is to use the pgstattuple extension.

• Fabrice Chapuis: Shouldn't instead use the following formula to have the number of dead tuples above the total number of tuples: CASE WHEN n_live_tup > 0 THEN round((n_dead_tup::float / dn_live_tup::float+dn_dead_tup::float)::numeric, 4) END AS dead_tup_ratio,