Postgres Vacuum doesnt free up space

Question

I have a table in my database which is occupying 161GB hard disk space. Only 5 gb free space is left out of 200Gb harddisk.

1. The following command shows that my table is consuming 161GB harddisk space,
   select pg_size_pretty(pg_total_relation_size('Employee'));

2. There are close to 527 rows in the table. Now I deleted 250 rows. Again I checked the pg_total_relation_size of Employee. Still the size is 161GB.

3. After seeing the output of the above query, I ran the vacuum command:
   VACUUM VERBOSE ANALYZE Employee;

4. I checked if the VACUUM actually happened using,
   SELECT relname, last_vacuum, last_autovacuum FROM pg_stat_user_tables; I can see the last vacuum time matching the time I ran the VACUUM command.

5. I also ran the below command to see if there any dead tuples,
   SELECT relname, n_dead_tup FROM pg_stat_user_tables; n_dead_tup count for Employee table is 0.

6. Still after all these above commands if I run,
   select pg_size_pretty(pg_total_relation_size('Employee')); it still shows 161GB.

May I please know the reason behind this? Also please correct me on how to free interface_list.

Answer 1

vacuum doesn't physically "free" space. It only marks no longer used space as re-usable. So subsequent UPDATE or INSERT statements can use that space instead of appending to the table.

Quote from the manual

The standard form of VACUUM removes dead row versions in tables and indexes and marks the space available for future reuse. However, it will not return the space to the operating system, except in the special case where one or more pages at the end of a table become entirely free and an exclusive table lock can be easily obtained

^{(emphasis mine)}

If you re-insert the 250 deleted rows, you will see that the table doesn't grow again, as the newly inserted rows simply use the space that was marked free by vacuum.

If you actually want to physically reduce the size of the table to size that is "needed", you need to run vacuum full.

Quote from the manual

VACUUM FULL actively compacts tables by writing a complete new version of the table file with no dead space. This minimizes the size of the table, but can take a long time. It also requires extra disk space for the new copy of the table, until the operation completes

^{(emphasis mine)}