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.
---------------------------(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