Postgresql – Does VACUUM FULL need ANALYZE

postgresqlpostgresql-9.3statisticsvacuum

I'd be grateful for some insight into the VACUUM functionality within PostgreSQL 9.3. I read through the documentation and searched around a bit but could not find a definitive answer to this.

I'm setting up a weekly DB maintenance job for a 9.3 server.

Part of this will be a VACUUM FULL. It's a smallish DB and I have a decent weekend maintenance window so I can run the FULL without any issues.

Is there any point in adding the ANALYZE option to the command?

According to the 9.3 documentation:

VACUUM FULL rewrites the entire contents of the table into a new disk
file with no extra space, allowing unused space to be returned to the operating system.

If the table is completely recreated then do I need to specifically ask for ANALYZE to happen or are the statistics updated automatically as the rows are written to the new version of the table?

Best Answer

The statistics are produced from the values in the columns. As these values are not changed by VACUUM FULL or even by VACUUM, a fresh ANALYZE is not specifically required.

If the database is in a maintenance window, it's a good opportunity to run it, though. Its execution time is likely to be insignificant compared to VACUUM FULL.

By default, a DBA will rely on autovacuum/auto-analyze rather that doing this manually, with the exception of the manual ANALYZE for when a new table that was just bulk-loaded or heavily modified is immediately involved in a query for which a good planning can't be obtained without good statistics.

Related Question