0

Why does running VACUUM FULL ANALYZE not update the last_vacuum column in pg_stat_user_tables in Postgres?

I have a moderately large database that requires some custom vacuuming and analyze procedures so those routines don't interfere with production performance. Therefore I scheduling a VACUUM FULL ANALYZE to be run at certain "safe" periods. Previously, I was just running VACUUM FULL, but this wasn't updating table statistics and was slowing down some queries, so I added ANALYZE, and that fixed the performance issues.

However, I noticed something strange. I already had a monitor setup to alert me for when vacuum hadn't been run in a while, just in case someone changes some config that stops the cronjob from running, and after I added ANALYZE, it started alerting me that vacuum was no longer being run.

Why is this?

There's nothing in the docs that says analyze disables vacuuming.

"VACUUM ANALYZE performs a VACUUM and then an ANALYZE for each selected table."

"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."

Granted, there's not explicit mention of what the expected behavior should be when FULL and ANALYZE are used together with VACUUM.

I have a lot of inserts and deletions, so I want VACUUM FULL to physically remove all dead tuples. I also want all table statistics to be updated.

Do I need to run FULL and ANALYZE separately?

0

1 Answer 1

1

For all intents and purposes, VACUUM FULL ANALYZE performs both a VACUUM and ANALYZE, though the "VACUUM" is more a side-effect of the VACUUM FULL table rewrite. As such, one does not need to run VACUUM FULL and ANALYZE separately, if both are desired.

Whereas VACUUM FULL effectively performs a VACUUM by eliminating dead tuples, VACUUM FULL rewrites the table along the lines of a CLUSTER or some ALTER TABLEs. Given that last_vacuum is intended to indicate the last time dead tuples were eliminated, as opposed to when the table was "created," some have argued that last_vacuum should not be updated following a VACUUM FULL.

Following PostgreSQL "bug" #5722 (here), during which Tom Lane chimed in, documentation was clarified to describe this behavior: see PostgreSQL: Statistics Collector, which describes that last_vacuum as the "Last time at which this table was manually vacuumed (not counting VACUUM FULL)"). Likewise, vacuum_count is not incremented following a VACUUM FULL.

If INSERTs and DELETEs are regularly and proportionally being performed on your table(s), VACUUM FULLs shouldn't be necessary. Alternatively, consider letting the VACUUMs reclaim dead tuples for reuse by subsequent INSERTs (UPDATEs and DELETEs create dead tuples). VACUUM FULL is costly in many ways, not to mention requiring of an ACCESS EXCLUSIVE lock not required by the non-FULL VACUUM variety.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.