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.?

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? — Laurenz Albe, Nov 12 '21 at 07:01
pgstattuple is a "built-in" extension and available in every Postgres installation (at least installable in the same way as Postgres itself) — , Nov 12 '21 at 08:10
@a_horse_with_no_name: Postgres 13 added index deduplication, which can compress indexes for duplicative data dramatically. Seems all four posted resources are outdated (at least) in this regard. — Erwin Brandstetter, Nov 12 '21 at 23:48

score 5 · Accepted Answer · answered Nov 15 '21 at 11:47

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.

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, — Fabrice Chapuis, Jan 18 '23 at 14:24

Find bloated tables and indexes in PostgreSQL without extensions

1 Answers1