In an ideal world I think you want precisely one vacuum process running per tablespace on the assumption that each tablespace represents a distinct physical device.
The cases where we currently find ourselves wanting more are where small tables are due for vacuuming more frequently than the time it takes for a large table to receive a single full pass. If we could have autovacuum interrupt a vacuum in mid-sweep, perform a cycle of vacuums on smaller tables, then resume, that problem would go away. That sounds too difficult though, but perhaps we could do something nearly as good. One option that I've heard before is to have vacuum after a single iteration (ie, after it fills maintenance_work_mem and does the index cleanup and the second heap pass), remember where it was and pick up from that point next time. If instead autovacuum could tell vacuum exactly how long to run for (or calculated how many pages that represented based on cost_delay) then it could calculate when it will next need to schedule another table in the same tablespace and try to arrange for the vacuum of the large table to be done by then. Once there are no smaller more frequently vacuumed small tables due to be scheduled it would start vacuum for the large table again and it would resume from where the first one left off. This only works if the large tables really don't need to be vacuumed so often that autovacuum can't keep up. Our current situation is that there is a size at which this happens. But arranging to have only one vacuum process per tablespace will only make that less likely to happen rather than more. I think the changes to vacuum itself are pretty small to get it to remember where it left off last time and start from mid-table. I'm not sure how easy it would be to get autovacuum to juggle all these variables though. Of course users may not create separate tablespaces for physical devices, or they may set cost_delay so high you really do need more vacuum processes, etc. So you probably still need a num_vacuum_daemons but the recommended setting would be the same as the number of physical devices and autovacuum could try to divide them equally between tablespaces which would amount to the same thing. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
