1

I have big database which contains a table of 2.7TB of data. I deleted some records, and I want to reclaim the disk space.

However, i only have about 200 gb free disk space left. How can I do this? The physical files of the 2.7 TB file are all splitted in 1 gb files.

For the vacuum full, will I need 2.7 TB of free disk space or 1 gb ? How will I ever be able to run the full vacuum? I read something about the symbolic link to an other server drive but i'm not sure an other server has 3 TB of storage left.

Can anyone help me out? Thanks!

Improve this question
6
  • It will takes as much extra space as the size of the new table and all of it's indexes. How much data did you delete?
    – jjanes
    Aug 6, 2022 at 12:45
  • thanks for your answer! I think i deleted few gb's, nothing to crazy. could i make a table dump to an other server and restore it in production to get rid of the deleted tuples?
    – Morph
    Aug 6, 2022 at 13:27
  • Yes. And it should compress pretty well while in the dumped state. But you will need to drop or truncate the table before the reload, so you will have downtime for as long as it takes to load and index that amount of data. Hardly seems worthwhile for a few gig.
    – jjanes
    Aug 6, 2022 at 15:08
  • We are planning on deleting old documents so it could mean a +/- 500 gb difference. Thanks JJanes!
    – Morph
    Aug 6, 2022 at 17:10
  • 1
    No, pg_repack won't help you. Both vacuum full and pg_repack will first create a new table, copy all the necessary data into it, and build the indexes. Therefore, both methods are not suitable if there is not enough free space for a copy of the table. Perhaps pgcompacttable could help, but I haven't tested it on windows.
    – Melkij
    Aug 7, 2022 at 18:18

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.