I'm using PostgreSQL 9.1 on Ubuntu. Are scheduled VACUUM ANALYZE still recommended, or is autovacuum enough to take care of all needs?
If the answer is "it depends", then:
- I have a largish database (30 GiB compressed dump size, 200 GiB data directory)
- I do ETL into the database, importing close to 3 million rows per week
- The tables with the most frequent changes are all inherited from a master table, with no data in the master table (data is partitioned by week)
- I create hourly rollups, and from there, daily, weekly and monthly reports
I'm asking because the scheduled VACUUM ANALYZE is impacting my reporting. It runs for more than 5 hours, and I've had to kill it twice this week, because it was impacting regular database imports.
check_postgres doesn't report any significant bloat on the database, so that's not really an issue.
From the docs, autovacuum should take care of transaction ID wrap around as well. The question stands: do I still need a VACUUM ANALYZE?