2

I have a large database running with PostgreSQL v11, and the disk space is very limited, so I have to VACUUM (FULL) the database frequently (e.g. every day).

But vacuuming is a very time-consuming operation. Furthermore, I often encounter vacuum error message such as

ERROR:  canceling autovacuum task
CONTEXT:  automatic vacuum of table ***

Fortunately, the service can be shutdown at a particular time point of the day, so I can dump the database after shutting it down, restore it immediately and restart the service again.

In addition, I found that "dumping/restoring" is faster than "vacuuming".

My question: Do the things that are done by "dumping/restoring" include the things what are done by "vacuuming full"?

If so, I can do it instead of vacuuming.

2

Yes, dumping and restoring the database will have the same effect as VACUUM (FULL). But having to do either of these every day is painful.

You have too little storage for your database. Artificially keeping it compact conserves space and speeds up queries, but it may slow down data modifications. The proper solution is to increase your storage and give your database some “breathing space”. That will make your problems go away, and probably reorganization like this is unnecessary.

1
  • Thank you very much, not only for the answer, but also for correcting my ugly English expression. Sorry, I'm a Chinese. – Leon Jun 15 '20 at 4:28

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.