Tom Lane wrote:
* It might seem that there's no point in per-table adjustment of critical_age, since only the system-wide maximum means anything for resource consumption. I'm not so sure though --- for a really large table, the time needed to finish vacuuming it could be significant, meaning it would need a lower critical age than other tables. With the current one-process-at-a-time autovac infrastructure, this probably isn't very important, but we've been talking about allowing multiple parallel autovacuums specifically to deal with the problem of some tables being much larger than others.
I think a global critical_age parameter is just fine. If you have one huge table that takes a long time to vacuum, just adjust critical_age so that there's enough time for the huge table vacuum to finish before wrap-around. That means that other smaller tables are vacuumed more frequently than would otherwise be necessary, but that's not a big deal if the other tables really are much smaller.
pg_autovacuum.freeze_distance: per-table vacuum_freeze_distance setting for autovacuum to use.
Shouldn't this be used for manual vacuums as well?
I'd propose default values of 200 million for autovacuum_freeze_limit and half that for vacuum_freeze_distance, resulting in a maximum pg_clog size of 50MB and forced autovacs about every 100 million transactions.
Sounds fine to me.
One minor point is that while the values of these variables have to have sane relationships to each other, the GUC infrastructure doesn't really allow us to enforce such a constraint directly (the behavior would be too dependent on which variable got set first). I'd suggest making vacuum just silently limit the effective freeze_distance to not more than half of the system's autovacuum_freeze_limit, rather than trying to enforce any relationship within GUC.
Makes sense. -- Heikki Linnakangas 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