We want to run standard vacuum process on our production database which is over 100 GB and have millions of dead tuples.

Can anyone suggest what system parameters we need to keep in mind for setting cost-based vacuum settings? I mean like CPU/IO/Memory/Disk.

We cannot run vacuum full as the database should be up and running continuously so we just want to attain most appropriate value without affecting the system much.

  • You might get more mileage out of pg_repack (github.com/reorg/pg_repack) or create-table-as-select-from-src-then-rename-table steps. – bma Jul 29 '13 at 20:50
  • @bma The documentation of pg_repack contains a small line: 'You cannot perform DDL commands of the target table(s) except VACUUM and ANALYZE during pg_repack. In many cases pg_repack will fail and rollback correctly, but there are some cases which may result in data corruption.' Now this means for me that I wouldn't use it under any circumstances, not to mention a production system. CLUSTER and VACUUM FULL take their locks with a reason. – dezso Jul 31 '13 at 10:05
  • Which PostgreSQL version do you use? – dezso Jul 31 '13 at 10:22
  • @dezso I agree with your assessment of pg_repack. However, I know people who have used it in production (I wouldn't), but they were only working with databases in the 500-800GB range. My other suggestion is the route I would normally take if I couldn't afford the long blocking operation that ensues from using CLUSTER. – bma Jul 31 '13 at 14:34
  • 1
    @bma Well, since writing my comment I was warned that there will be DDL triggers in 9.3 - that way it can be a viable option. – dezso Jul 31 '13 at 16:32

The default settings will probably be fine.

That will limit the IO write usage to about 4MB/sec, that is, 4kB/msec = (8KB * vacuum_cost_limit / vacuum_cost_page_dirty / autovacuum_vacuum_cost_delay)

And RAM usage to about 48 MB (autovacuum_max_workers * maintenance_work_mem)

Your Answer

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

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