Re: Visibility map, partial vacuums

Gregory Stark wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
>
>> Would someone tell me why 'autovacuum_freeze_max_age' defaults to 200M
>> when our wraparound limit is around 2B?
>
> I suggested raising it dramatically in the post you quote and Heikki pointed
> it controls the maximum amount of space the clog will take. Raising it to,
> say, 800M will mean up to 200MB of space which might be kind of annoying for a
> small database.
>
> It would be nice if we could ensure the clog got trimmed frequently enough on
> small databases that we could raise the max_age. It's really annoying to see
> all these vacuums running 10x more often than necessary.

Well, if it's a small database, you might as well just vacuum it.

> The rest of the thread is visible at the bottom of:
>
> http://article.gmane.org/gmane.comp.db.postgresql.devel.general/107525
>
>> Also, is anything being done about the concern about 'vacuum storm'
>> explained below?
>
> I'm interested too.

The additional "vacuum_freeze_table_age" (as I'm now calling it) setting
I discussed in a later thread should alleviate that somewhat. When a
table is autovacuumed, the whole table is scanned to freeze tuples if
it's older than vacuum_freeze_table_age, and relfrozenxid is advanced.
When different tables reach the autovacuum threshold at different times,
they will also have their relfrozenxids set to different values. And in
fact no anti-wraparound vacuum is needed.

That doesn't help with read-only or insert-only tables, but that's not a
new problem.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com