13

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?

Erwin Brandstetter
  • 185,527
  • 28
  • 463
  • 633
Eoin O'Hehir
  • 133
  • 1
  • 5

1 Answers1

11

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.

Daniel Vérité
  • 32,662
  • 3
  • 78
  • 84