> On Feb 21, 2023, at 10:48, Brad White <b55white@gmail.com> wrote:
>
> Running the table_bloat_check query from here
> https://github.com/pgexperts/pgx_scripts/blob/master/bloat/table_bloat_check.sql
> shows some tables with over 20MB and over 20% bloat while my threshold is set to 0.1.
Apples-to-oranges comparison. That query attempts to calculate a percentage of the overall table filespace that is not
occupiedby live data. A perfectly 100% vacuumed table with only live tuples can (and probably will) still have bloat,
sincefree space is counted as bloat. (It's just an approximation, so it changing after a vacuum isn't particularly
surprising.)
The autovacuum threshold doesn't use the same calculations as the bloat query. That happens when n_dead_tup exceeds
threshold+ pg_class.reltuples * scale_factor. If there are more than 21,651 or more rows in the table, 2215 dead
tuplesis below that, and autovacuum won't run on the table.
The output from vacuum didn't have anything it in that would indicate that it couldn't recover dead tuples (like a
long-runningtransaction or something), so there's no reason that autovacuum wouldn't run on the table in the future
whenthe number of dead tuples reaches the threshold.
By the way, you really should check to see if all those indexes are being used. That's a *lot* of indexes, which will
greatlyslow down inserts, take up filespace, and slow down autovacuum (since it has to scan each index before it can
reclaimspace in the heap).
