Re: Visibility map and freezing

ITAGAKI Takahiro wrote:
> Gregory Stark <stark@enterprisedb.com> wrote:
> 
>>>> I don't think we can perfectly capture the meaning of these GUCs in the
>>>> name. I think our goal should be to avoid confusion between them.
>> I was thinking it would be clearer if the options which control *when*
>> autovacuum fires off a worker consistently had some action word in them like
>> "trigger" or "start" or "launch".
> 
> I think we need more explanations about those variables,
> not only "how to work" but also "how to tune" them.
> I feel they are un-tunable parameters.
> 
> Our documentation says:
> | Larger values of these settings
> | preserve transactional information longer, while smaller values increase
> | the number of transactions that can elapse before the table must be
> | vacuumed again.
> i.e, we are explaining the variables only as "Larger is better",
> but is it really true?

Yes, that is explicitly explained in the docs:

> The sole disadvantage of increasing <varname>vacuum_freeze_table_age</>
>     and <varname>autovacuum_freeze_max_age</>
>     is that the <filename>pg_clog</> subdirectory of the database cluster
>     will take more space, because it must store the commit status for all
>     transactions back to the <varname>autovacuum_freeze_max_age</> horizon.> ...


> - What relation are there between autovacuum_freeze_max_age,
>   vacuum_freeze_min_age and vacuum_freeze_table_age? If we increase
>   one of them, should we also increase the others?

Yeah, that's a fair question. I'll try to work a doc patch to explain 
that better.

> - Is it ok to increase the variables to maximum values?
>   Are there any trade-off?

Disk space.

> - Are there some conditions where whole-table-scanning vacuum is more
>   effective than vacuums using visibility map? If so, we should switch
>   to full-scan *automatically*, without relying on user configurations.

Hmm, the only downside I can see is that skipping a page here and there 
could defeat the OS read-ahead. Perhaps we should call 
posix_fadvise(SEQUENTIAL) to compensate. Or, we could modify the logic 
to only skip pages when there's at least N consecutive pages that can be 
skipped.

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