I'm trying to understand what is going on internally when doing a VACUUM FULL on a table in 8.3.
I have a table that is 1GB in size, 500M is used, and 500M is free space. When I do a vacuum full on this table, will it either: -
1) Compact all of the used tuples into free space within the existing disk file and then shrink the file to 500M. Therefore simply freeing up 500M in the disk file.
2) Rewrite the table data to a new disk file with no free space. Once it has finished, then removes the old copy of the table. I took this from http://developer.postgresql.org/pgdocs/postgres/sql-vacuum.html , but I don't know whether this is how it worked prior to version 9.
In the case of 2) I would therefore need at least 500M free space in the filesystem whist it writes out the new table, and once complete, 1GB would then be freed up when the old copy of the table is deleted.