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

1 Answer 1


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

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

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

  • @OrangeDog Worked for me without quoting table (now called table_name in the current edit). Commented Nov 5, 2020 at 11:48
  • @Colin'tHart I think my client is more strict than necessary about quoting keywords.
    – OrangeDog
    Commented Nov 5, 2020 at 12:00
  • Can I confirm that this also means there can be around reclaimable bytes of new updates added without increasing the disk usage (assuming there has been a non-full vacuum)? Or is it likely to be a lot less than that?
    – OrangeDog
    Commented Nov 5, 2020 at 15:45
  • 1
    No, because I included dead_tuple_len. The free space is approx_free_space. Commented Nov 5, 2020 at 15:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.