Amit Kapila wrote:
> On Tue, Jul 11, 2017 at 6:51 AM, AP <ap@zip.com.au> wrote:
> > On Fri, Jul 07, 2017 at 05:58:25PM +0530, Amit Kapila wrote:
> >> I can understand your concerns. To address first concern we need to
> >> work on one or more of following work items: (a) work on vacuums that
> >> can be triggered on insert only workload (it should perform index
> >> vacuum as well) (b) separate utility statement/function to squeeze
> >> hash index (c) db internally does squeezing like after each split, so
> >> that chances of such a problem can be reduced, but that will be at the
> >> cost of performance reduction in other workloads, so not sure if it is
> >> advisable. Among these (b) is simplest to do but may not be
> >> convenient for the user.
> >
> > (a) seems like a good compromise on (c) if it can be done without disruption
> > and in time.
> > (b) seems analogous to the path autovcauum took. Unless I misremember, before
> > autovacuum we had a cronjob to do similar work. It's probably a sane path
> > to take as a first step on the way to (a)
> > (c) may not be worth the effort if it compromises general use, though perhaps
> > it could be used to indicate to (a) that now is a good time to handle
> > this bit?
>
> Nice summarization! I think before doing anything of that sort we
> need opinions from others as well. If some other community members
> also see value in doing one or multiple of above things, then I can
> write a patch.
I haven't read the thread, but in late PG10 autovacuum gained the idea
of "work items" that can be plugged from other parts of the server;
currently BRIN uses it to cause a range to be summarized right after the
next one starts being filled. This is activated separately for each
index via a reloption. Perhaps something like that can be used for hash
indexes? See commit 7526e10224f0792201e99631567bbe44492bbde4.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services