1

I have a table called TABLE_A that's partitioned by month. Each partition is named according to it's month, TABLE_A_201810, TABLE_A_201811 etc

I've noticed that if I attempt to VACUUM an old partition

VACUUM FULL TABLE_A_201810;

I can no longer do inserts against TABLE_A (all the INSERTS timeout)

The VACUUM FULL takes a while, and I can't really afford to stop the other process that INSERTing to the newer partition.

My current solution is to detach TABLE_A_201810, do the VACUUM and then re-attach.

Is there a better solution?


UPDATE Using version 10.5, partitioning by range. I'm doing a VACUUM FULL because thats the only way (that I know of) of returning free space to the OS. It also provides the best way of managing disk space (or in otherwords, it's the only way of checking how much free space is really available)

  • 1
    Please start any such question by disclosing the version of Postgres you use. And the partitioning technique in use. – Erwin Brandstetter Nov 22 '18 at 0:47
  • Why are you doing a VACUUM FULL? The better solution is not to get into a situation where VACUUM FULL is needed. Barring that, what is wrong with the detach method? – jjanes Nov 23 '18 at 16:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.