Hi! On Sat, Jun 16, 2018 at 11:23 PM Darafei "Komяpa" Praliaskouski <m...@komzpa.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
vacuum_cleanup_index_scale_factor-max.patch
Description: Binary data
