Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Well, I would say 'no', but elaborating this answer (setting the autovacuum parameters for example) would need some experiments on a replica DB. –  dezso Jun 1 '12 at 9:27

3 Answers 3

up vote 14 down vote accepted

VACUUM is only needed on updated or deleted rows in non-temporary tables. Obviously you're doing lots of INSERTs but it's not obvious from the description that you're also doing lots of UPDATEs or DELETEs.

These operations can be tracked with the pg_stat_all_tables view, specifically the n_tup_upd and n_tup_del columns. Also, even more to the point, there is a n_dead_tup column that tells, per table, how much rows need to be vacuumed. (see Monitoring statistics in the doc for functions and views related to statistics gathering).

A possible strategy in your case would be to suppress the scheduled VACUUM, keeping an eye on this view and checking on which tables the n_dead_tup is going up significantly. Then apply the aggressive VACUUM to these tables only. This will be a win if there are large tables whose rows never get deleted nor updated and the aggressive VACUUM is really necessary only on smaller tables.

But keep running the ANALYZE for the optimizer to always have fresh statistics.

share|improve this answer
2  
Autovacuum also takes care of ANALYZE. It is still a good idea to run a manual ANALYZE between a bulk UPDATE / INSERT / DELETE and immediately following big queries. +1 for the good advice, though. –  Erwin Brandstetter Jun 1 '12 at 17:00
    
Thanks for the pointer to n_dead_tup and friends. I do have rollup tables on which I frequently (hourly) destroy and recreate thousands of rows. I'll check the values and schedule appropriately. The answer is always "monitor, think, act" anyway :) –  François Beausoleil Jun 2 '12 at 12:44

I see nothing in your question that auto-vacuum would not take care of. It largely depends on the pattern or your writing activities. You mention 3 million new rows per week, but INSERT (or COPY) is largely irrelevant for VACUUM. UPDATE and DELETE are relevant, especially if they pick random rows (as opposed to bulks of data most likely occupying pages together). I don't see any of that in your question.

Autovacuum has come a long way and is doing a great job in version 9.1. I would have a closer look at the autovacuum settings. If vacuuming tends to interfere with your work load, also have a look at Cost-based Vacuum Delay. Manual vacuuming should not be needed at all.

If you have a lot of random UPDATES, you may want to set the FILLFACTOR to something lower than 100, so that HOT updates have a chance to partially remove the need to vacuum altogether.
Details on HOT in this related answer on stackoverflow.

Note also, that temporary tables need manual VACUUM & ANALYZE. I quote the manual on CREATE TABLE:

The autovacuum daemon cannot access and therefore cannot vacuum or analyze temporary tables. For this reason, appropriate vacuum and analyze operations should be performed via session SQL commands. For example, if a temporary table is going to be used in complex queries, it is wise to run ANALYZE on the temporary table after it is populated.

share|improve this answer

While I agree that using the auto features is best instead of running it database wide, in most cases per table tuning is necessary.

I don't quite agree with the design choice of postgres to tie together vacuum and analyze, I have seen several instances where databases that do a lot of insert/update but little delete never get analyze done and start to perform badly.

The solution is to go into the tables that get used heavily and are subject to large queries and set the auto analyze settings for those tables down to something to where they are getting analyzed once or every other day.

You can get to the per table settings in the gui on the auto vacuum tab and you will see analyze settings there which you can set independently of vacuum.

The settings end up in the reloptions table and can be seen with the query

SELECT c.relname, c.reloptions FROM pg_class c where reloptions is not null

and a sample value there of an agressive analyze might be

{autovacuum_enabled=true,autovacuum_analyze_threshold=10,autovacuum_analyze_scale_factor=.01}

To see when the last time your tables got auto analyzed query

select 
    relname, 
    n_dead_tup, 
    n_tup_ins, 
    n_tup_upd, 
    n_tup_del, 
    last_autoanalyze, 
    autoanalyze_count 
from pg_stat_user_tables 
where last_autoanalyze is not null 
order by last_autoanalyze desc;
share|improve this answer
    
If you don't ANALYZE, how PostgreSQL will know that the stats changed? And how can you determine that it is ANALYZE which takes a long time? At the same time, while it is not quite clear which GUI you mention above, you are right in that specific per-table settings may be useful. –  dezso Jun 13 '13 at 20:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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