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

Reply via email to