Re: [HACKERS] GUC for cleanup indexes threshold.

On Mon, Mar 19, 2018 at 8:45 AM, Kyotaro HORIGUCHI <
horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp> wrote:

> At Mon, 19 Mar 2018 11:12:58 +0900, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com>
> wrote in <CAD21AoAB8tQg9xwojupUJjKD=fMhtx6thDEPENDdhftVLWcR8A(at)mail(dot)gm
> ail.com>
> > require the bulk-delete method of scanning whole index and of logging
> > WAL. But it leads some extra overhead. With this patch we no longer
> > need to depend on the full scan on b-tree index. This might be useful
> > for a future when we make the bulk-delete of b-tree index not scan
> > whole index.
>
> Perhaps I'm taking something incorrectly, but is it just the
> result of skipping 'maybe needed' scans without condiering the
> actual necessity?
>
> I also don't like extra WAL logging, but it happens once (or
> twice?) per vaccum cycle (for every index).

In my version of patch WAL logging doesn't happen every vacuum cycle.
WAL-logging of meta-page happens only in two cases:
1) After first vacuum which didn't delete any heap tuples. I.e. after we
switch from update workload to append-only workload.
2) During append-only workload when cleanup is triggered (either
deleted pages become recyclable or statistics considered as stalled).

Typically in both update and append-only workloads, WAL logging
isn't happening during vacuum cycle.

Masahiko Sawada has proposed to update meta-information during
btbulkdelete [1]. That would lead to WAL logging in almost very
vacuum cycle. I was slightly opposed to that saying that this overhead
need to be tested [2]. However this concern is not related to
current shape of my version of patch.

> On the other hand I
> want to put the on-the-fly upgrade path out of the ordinary
> path. (Reviving the pg_upgrade's custom module?)
>

I don't know. Previously, we successfully did on-fly upgrade of
GIN posting lists. Our pg_upgrade machinery is probably already
very complicated and overloaded. Should we burden it with
upgrade of every meta-page of every B-tree index assuming that
we can handle it very well inside B-tree itself on the fly?

1.
https://www.postgresql.org/message-id/CAD21AoBpD1zZ8%2BkmN-6sCb0WkVe7X4ON31Oy-JsgafOC5DOYxA%40mail.gmail.com
2.
https://www.postgresql.org/message-id/CAPpHfdsiDUXKXzfc%2BNHrAvjv3nekR-4WfwgSNBBXJYxuhBcCFA%40mail.gmail.com

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