Estimating the disk space needed for a VACUUM FULL on PostgreSQL
If you find yourself in the position where you need a full vacuum, you’re probably already looking at the number of dead tuples to have reached that conclusion. The trouble is that bloated tables and (nearly) full disks tend to go hand in hand. In this case, you might be wondering: how much disk space do I need to complete a full vacuum?
Now we have an example table containing one million rows, with the autovacuum turned off, for the sake of this experiment. Let’s have a look at the statistics for our table:
We have a table, occupying 50 MB on disk and containing one million rows, of which half are pending a cleanup. To estimate the disk space needed for a VACUUM FULL command, we have to take into account that PostgreSQL will make a complete copy of the table, only containing the live tuples, before deleting the old version of the table. Our estimation function is, therefore:
Where $s_{max}$ represents the total estimated disk space needed for the operation, $n_d$ and $n_l$ represents the number of dead and live tuples, respectively, and $s_{current}$ the current disk space occupied by the table:
