40

I am using Postgres 9.4.

I just ran vacuum full. I read about the differences between vacuum and vacuum full and considered a lot if I should run vacuum or vacuum full. As far as I can say, I required vacuum full and my db size came down from 48 GB to 24 GB.

Would the old indexes would have become obsolete after vacuum full and do I need to run reindex?

I ran "vacuum full verbose analyze", so analyze is done along with vacuum full.

I read at several places that for Postgres > 9.0, I don't need reindex after vacuum full, but I want to be sure that it is the case.

Akshar Raaj
  • 14,231
  • 7
  • 51
  • 45
  • 1
    Since you have already ran `analyze`, a re-indexing won't be necessary since that itself collects the statistics. Check documentation http://www.postgresql.org/docs/8.4/static/sql-analyze.html for more information. – Rahul Jun 23 '15 at 17:42

1 Answers1

68

A REINDEX immediately after a VACUUM FULL is useless because VACUUM FULL itself rebuilds the indexes.

This is mentioned in the 9.4 documentation in Recovering Disk Space :

...to reclaim the excess disk space it occupies, you will need to use VACUUM FULL, or alternatively CLUSTER or one of the table-rewriting variants of ALTER TABLE. These commands rewrite an entire new copy of the table and build new indexes for it.

You are correct that this was not the case before version 9.0, which had VACUUM FULL reimplemented differently.

Up to version 8.4, the reference doc for VACUUM mentioned the need to reindex:

The FULL option does not shrink indexes; a periodic REINDEX is still recommended. In fact, it is often faster to drop all indexes, VACUUM FULL, and recreate the indexes.

But this caveat is now obsolete.

Daniel Vérité
  • 58,074
  • 15
  • 129
  • 156