Re: [HACKERS] GUC for cleanup indexes threshold.

Hi!

On Sat, Jun 16, 2018 at 11:23 PM Darafei "Komяpa" Praliaskouski
<me(at)komzpa(dot)net> wrote:
> It is cool to see this in Postgres 11. However:
>
>>
>> 4) vacuum_cleanup_index_scale_factor can be set either by GUC or reloption.
>> Default value is 0.1. So, by default cleanup scan is triggered after increasing of
>> table size by 10%.
>
>
> vacuum_cleanup_index_scale_factor can be set to the maximum of 100.
> I imagine that on a large append-only table with IOPS storage system budget it may happen that I would want to never perform a full scan on index. Roughly, with parameter set to 100, if we vacuum the table first time with 1 tuple and 130 byte wide rows, we'll have a full scan at 130 bytes, 12 kbytes, 1.2MB, 123MB, 12 GB, 1.2TB.
>
> If we happen to perform the first vacuum when there are 4 tuples in the table, it becomes 52kb, 5MB, 495MB, 48GB - and both 12GB and 48GB will exhaust any storage spike IOPS budget, slowing everything down rather suddenly.
>
> Can the upper limit for this GUC be lifted, or have a value for "never"?

I have some further exploration of how statistics obtained by B-tree
index vacuum cleanup is used.

1) Collected pages and tuples numbers are not directly used, but used
for an estimation of tuples density per page, while current number of
page is estimated using smgr (see btcostestimate()). So, unless
density of tuples significantly changes, having index statistics
stalled doesn't affect query plans.
2) Our optimization for skipping B-tree index vacuum cleanup works
only in case when use manually vacuums table in order to update
visibility map. Autovacuum is not triggered for append-only tables.
So, if user doesn't have special care about append-only tables,
they're not vacuumed until "autovacuum to prevent wraparound". Thus,
index statistics could be very stalled. And I don't think we have
many occurrences of issues with stalled index statistics.
3) We have very safe defaul of vacuum_cleanup_index_scale_factor equal
to 1.1. But as Darafei claimed, 100 maximum value is probably too low
for advanced users, who really need benefits of this optimization.

So, I'm proposing to raise maximum valus of
vacuum_cleanup_index_scale_factor to DBL_MAX. Any objections?

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company