I'm starting to perform some basic housekeeping to try to trim some big tables (~200 million rows - ~50GB+indexes) into separate partitions (via inheritance). The current issue which prompted me to do such housekeeping is due to long database wide vacuum time. (it went from 2 hours to 4 hours to 7 hours) My current strategy is to keep only 1 month of data in the base table and make a job to automatically insert/delete 1 days worth of data into the child partition tables. Currently, I've moved 3 such large tables into separate partitions and my vacuum time is still slow. (I suspect it's because I bulk insert/deleted the tuples from the main_tables in 1 go and then the vacuum had to deal with vacuum off the MVCC for the past few months's data) I'm already batching my housekeep into 6 hours timeframes (eg: insert into foo_child select * from fooo from hour1 to hour6) So, my question is now 1. Should I perform a vacuum after each 6 hour batch? or 2. perform a vacuum after 1 day batch? (4x 6hour sessions) and what should I do with the few tables which I've not started to partition? There's 4 months worth of data which is still sitting in the respective main tables. Appreciate pointers etc. ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
