Tell me more ×
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.
up vote 0 down vote favorite

I have a very big table in my postgreSQL (over 300 million rows) that has never been vacuumed. Yesterday I tried a vacuum and analyze (not a full one) and it took about 7 hours to complete. Will it take 7 hours if I do it every day, or will the duration become smaller due to frequent maintenance?

share|improve this question
1  
Why was it never vacuumed? You should really (I mean really) enable auto-vacuum. – a_horse_with_no_name Jul 13 '12 at 9:25
1  
Which version of PostgreSQL? – araqnid Jul 13 '12 at 9:25

migrated from stackoverflow.com Jul 13 '12 at 9:17

1 Answer

up vote 2 down vote

Because you haven't specified your PostgreSQL version it's hard to answer. VACUUM has been improved in many ways on newer versions.

Presuming you're on 9.0 or 9.1 - so you don't need a max_fsm_pages setting, you have the improved VACUUM FULL, you have the visibility map, etc - then subsequent VACUUM operations should be quite fast. However, you should not be doing them.

Turn autovaccum on. Set it to run frequently. If this table is updated a lot, set autovaccum to process it more often. Vaguely modern PostgreSQL versions work best with lots of very frequent vacuum runs.

share|improve this answer

Your Answer

 
discard

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