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?

2
  • 1
    Why was it never vacuumed? You should really (I mean really) enable auto-vacuum.
    – user1822
    Jul 13, 2012 at 9:25
  • 1
    Which version of PostgreSQL?
    – araqnid
    Jul 13, 2012 at 9:25

1 Answer 1

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 and acknowledge you have read our privacy policy.