0

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.

1 Answer 1

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

3
  • Log of vacuum full.... ERROR: could not extend file "base/63212/4702406.54": No space left on device D :-( Commented May 3, 2022 at 11:48
  • 1
    VACUUM FULL basically writes a new copy of the table without all the "dead" stuff. That needs space.
    – Phill W.
    Commented May 4, 2022 at 14:15
  • 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.... Commented May 4, 2022 at 16:26

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.