1

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?

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

This question came from our site for professional and enthusiast programmers.

  • 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
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy