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?
PostgreSQL vacuum duration on a big table
postgresqlvacuum
Related Question
- Postgresql – ANALYZE strategy for big tables in PostgreSQL
- Postgresql – Postgres auto analyze performance
- Postgresql – Debug query on big table that is sometimes slow
- PostgreSQL auto-vacuum: “skipped frozen” pages causing massive bloating
- Postgresql – Avoid Vacuum (to prevent wraparound) on read-only databases
- Postgresql – How to maintain visibility of all new transactions in append-only PostgreSQL DB without scanning the whole table
- Postgresql – Calculate the duration in an interval and outside of events that can last over several days
- PostgreSQL – 1 billion rows table cannot get vacuumed despite no errors
Best Answer
Because you haven't specified your PostgreSQL version it's hard to answer.
VACUUMhas 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_pagessetting, you have the improvedVACUUM FULL, you have the visibility map, etc - then subsequentVACUUMoperations 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.