Reading the PostgreSQL documentation there is an excerpt that mentions that you should run a VACUUM FULL when there are many row lines.

What percentage of bloat is considered necessary to perform VACUUM FULL? Source

Still on this question. My rows change frequently, probably affecting my indexes. Because of this constant change should I also run a VACUUM FULL?

Note: For performance reasons, usually once a week I run a VACUUM for each table in the database, and after the VACUUM I run an ANALYZE for each table. Is there anything else I can do?

  • There is no definite answer. Some people would consider a table that is 50% full badly bloated, some people are happy with tables that are much more bloated. It depends on your requirements and the performance impact you experience. Nov 10 '21 at 8:09
  • @a_horse_with_no_name I think the only meaningful load reduction is on your fingers, having to type the table name twice.
    – jjanes
    Nov 10 '21 at 17:34
  • @a_horse_with_no_name there is no provision for sharing block reads between vacuum and analyze. They don't even visit the same set of blocks, one being for not-all-visible, the other being for random ones.
    – jjanes
    Nov 10 '21 at 17:51

Some cases where I would do VACUUM FULL:

The system is about the run out of disk space and fall over. And that system is dominated by a very bloated table, but without bloated indexes. This should be followed up with some navel gazing about how we get into this mess in the first place.

Or, I fixed what was causing the bloat, now I want to restore the table to a nonbloated state for (hopefully) the last time.

Or, I took over a bloated system, no one from the ancien regime knows why. I want to debloat it and watch it rebloat to make it easier to figure out what is going on.

If you only have bloated indexes, it would be better to REINDEX, not VACUUM FULL. On the most recent versions, this can be done concurrently.

  • How do you identify bloated indexes?
    – Tom
    Nov 11 '21 at 0:19

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.