Re: 64-bit XIDs in deleted nbtree pages

On Tue, Mar 2, 2021 at 1:42 PM Peter Geoghegan <pg@bowt.ie> wrote:
>
> On Mon, Mar 1, 2021 at 8:06 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> > I think that removing vacuum_cleanup_index_scale_factor in the back
> > branches would affect the existing installation much. It would be
> > better to have btree indexes not use this parameter while not changing
> > the contents of meta page. That is, just remove the check related to
> > vacuum_cleanup_index_scale_factor from _bt_vacuum_needs_cleanup().
>
> That's really what I meant -- we cannot just remove a GUC or storage
> param in the backbranches, of course (it breaks postgresql.conf, stuff
> like that). But we can disable GUCs at the code level.

Oh ok, I misunderstood.

>
> > And
> > I personally prefer to fix the "IndexVacuumInfo.num_heap_tuples is
> > inaccurate outside of btvacuumcleanup-only VACUUMs" bug separately.
>
> I have not decided on my own position on the backbranches. Hopefully
> there will be clear guidance from other hackers.

+1

>
> > Yeah, this argument makes sense to me. The default values of
> > autovacuum_vacuum_insert_scale_factor/threshold are 0.2 and 1000
> > respectively whereas one of vacuum_cleanup_index_scale_factor is 0.1.
> > It means that in insert-only workload with default settings,
> > autovacuums triggered by autovacuum_vacuum_insert_scale_factor always
> > scan the all btree index to update the index statistics. I think most
> > users would not expect this behavior. As I mentioned above, I think we
> > can have nbtree not use this parameter or increase the default value
> > of vacuum_cleanup_index_scale_factor in back branches.
>
> It's not just a problem when autovacuum_vacuum_insert_scale_factor
> triggers a cleanup-only VACUUM in all indexes. It's also a problem
> with cases where there is a small number of dead tuples by an
> autovacuum VACUUM triggered by autovacuum_vacuum_insert_scale_factor.
> It will get index scans done by btbulkdeletes() -- which are more
> expensive than a VACUUM that only calls btvacuumcleanup().
>
> Of course this is exactly what the patch you're working on for
> Postgres 14 helps with. It's actually not very different (1 dead tuple
> and 0 dead tuples are not very different). So it makes sense that we
> ended up here -- vacuumlazy.c alone should be in control of this
> stuff, because only vacuumlazy.c has the authority to see that 1 dead
> tuple and 0 dead tuples should be considered the same thing (or almost
> the same). So...maybe we can only truly fix the problem in Postgres 14
> anyway, and should just accept that?

Yeah, I think that's right.

Perhaps we can do something so that autovacuums triggered by
autovacuum_vacuum_insert_scale_factor are triggered on only a true
insert-only case (e.g., by checking if n_dead_tup is 0).

>
> OTOH scanning the indexes for no reason when
> autovacuum_vacuum_insert_scale_factor triggers an autovacuum VACUUM
> does seem *particularly* silly.

Agreed.

Regards,

--
Masahiko Sawada
EDB:  https://www.enterprisedb.com/