Csaba Nagy wrote: > I have a quite big table (about 200 million records, and ~2-3 million > updates/~1 million inserts/few thousand deletes per day). I started a > vacuum on it on friday evening, and it still runs now (monday > afternoon). I used "vacuum verbose", and the output looks like: > > [vacuums list all the indexes noting how many tuples it cleaned, then > "restarts" and lists all the indexes again, then again ... ad nauseam] What happens is this: the vacuum commands scans the heap and notes which tuples need to be removed. It needs to remember them in memory, but memory is limited; it uses the maintenance_work_mem GUC setting to figure out how much to use. Within this memory it needs to store the TIDs (absolute location) of tuples that need to be deleted. When the memory is filled, it stops scanning the heap and scans the first index, looking for pointers to any of the tuples that were deleted in the heap. Eventually it finds them all and goes to the next index: scan, delete pointers. Next index. And so on, until all the indexes are done. At this point, the first pass is done. Vacuum must then continue scanning the heap for the next set of TIDs, until it finds enough to fill maintenance_work_mem. Scan the indexes to clean them. Start again. And again. So one very effective way of speeding this process up is giving the vacuum process lots of memory, because it will have to do fewer passes at each index. How much do you have? -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
