PostgreSQL did not released space "UPDATE set column = null", vacuum did not worked too

Question

I have a huge table that I cannot delete the rows, only update the columns that are storing huge base64 data, that I should update to null to try to release space.

So I programmed a script that are able to set all the images in base64 expecting that the space will be released after Vacuum!

The images are set to null, vacuum executed, but the table is still with the exactly same size, and I am pretty sure the space must be released immediately, so what I am doing wrong?

Will vacuum full able to release space from huge varchar data that I updated to null? (Because I will need to lock the table if I will do this and I need to be sure)

The dump size decreased by 10 times so I expect similar behavior on the database size.

Answer 1

Short answer - Yes.

When you update rows, you create a new version of each row that is held inside the table.

• Vacuum does not remove these row versions.
  It does make the space occupied by these "dead" rows available for reuse, but that doesn't reduce the overall size.
• Vacuum Full removes the dead rows completely.
  That's what releases space back to the operating system.
  And yes; the table will get locked up while this is happening.

See the PostgreSQL Wiki page on "Vacuum Full".

• Luciano Andress Martini: Log of vacuum full.... ERROR: could not extend file "base/63212/4702406.54": No space left on device D :-(
• Phill W.: VACUUM FULL basically writes a new copy of the table without all the "dead" stuff. That needs space.
• Luciano Andress Martini: Phill W that helped a lot because I know now the exact space that I would need to release to run vacuum full; Thank you for your help! It would be wonderful if it was possible to work in a different tablespace with vacuum full....