2

My postgres DB has 20 GB of storage space available but when running a DELETE the space decreases to ~4 GB before increasing again to ~14 GB. I understand that deletions will not return reclaimed space because of the MVCC (want to eventually run a FULL VACUUM on that table) but am confused as to why it went down to ~14 GB from 20 GB ultimately. Additionally, the auto-vacuum did run and was assuming this is why it went down to ~4GB first then went back up..still the return of available storage space happened a good amount of time after the auto vacuum ran so is this an incorrect assumption? I would've assumed some space may still be taken from the DELETEs but 6GB was more than I was expecting.

Steps

  1. postgres DB on AWS has 20 GB of available storage space
  2. Ran DELETE
  3. Storage space decreases to ~4 GB
  4. Auto Vacuum runs
  5. Time delay but some time after returns to ~14 GB

Unsure why it only returned to 14 GB instead of 20 GB.

1 Answer 1

3

DELETE generates WAL, possibly large amounts of WAL. How long this WAL sticks around will depend on your settings and archiving and slots.

1
  • 1
    This ended up being it, the WAL got quite large but then was cleaned. Much appreciated!
    – Jon L
    Commented Nov 29, 2022 at 17:33

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.