How to predict how much space a VACUUM FULL would reclaim?

Is there a way to determine in advance how much disk space a VACUUM FULL on a particular table would return to the OS? Therefore you can decide whether it's worth the cost in doing so.

Bonus if there's a simple query to do this for every table in a database/server (rather than doing each one separately).

You need the pgstattuple extension to get the amount of free space.

So you could run

SELECT t.oid::regclass AS table_name,
       s.table_len AS size,
       dead_tuple_len + s.approx_free_space AS reclaimable
FROM pg_class AS t
   CROSS JOIN LATERAL pgstattuple_approx(t.oid) AS s
WHERE t.relkind = 'r'
ORDER BY (s.dead_tuple_len::float8 + s.approx_free_space::float8)
       / (s.table_len::float8 + 1.0) DESC;

where size and reclaimable are in bytes.

Be warned that this query is somewhat expensive.

That should give a pretty good estimate of the space that can be reclaimed, but in practice it will be slightly less, because

-
there is always some free space in each 8kB block that is too small to fit another row

-
some of the dead tuples might not be reclaimable if you have long running transactions

StackExchange Database Administrators Q#279232, answer score: 8