Do I need to reindex after vacuum full on Postgres 9.4

postgresql vacuum
Akshar Raaj picture Akshar Raaj · Jun 23, 2015 · Viewed 17.9k times · Source

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.

Answer

Daniel Vérité picture Daniel Vérité · Jun 23, 2015

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.

Related questions

How do I know if the statistics of a Postgres table are up to date?

In pgAdmin, whenever a table's statistics are out-of-date, it prompts: Running VACUUM recommended The estimated rowcount on the table schema.table deviates significantly from the actual rowcount. You should run VACUUM ANALYZE on this table. I've tested it using pgAdmin 3 …

Read More
Why is it a vacuum not needed with Mysql compared to the PostgreSQL?

I am more familiar with PostgreSQL than MySQL. Have encountered wraparound Id failure once with the PostgreSQL db and then understood the importance of vacuuming in the db. Actually, that was such a massive overhead work to deal with(and …

Read More
PostgreSQL - how to run VACUUM from code outside transaction block?

I am using Python with psycopg2 and I'm trying to run a full VACUUM after a daily operation which inserts several thousand rows. The problem is that when I try to run the VACUUM command within my code I get …

Read More