Re: Is it possible to have a "fast-write" Index?

deavid <deavidsedice(at)gmail(dot)com> writes:
> Some people already asked for "delayed write" indexes, but the idea gets
> discarded because the index could get out of sync, so it can omit results
> and this is unacceptable. But i think maybe that could be fixed in several
> ways and we can have a fast and reliable index (but maybe not so fast on
> selects).

FWIW, GIN indexes already implement something that's like your mode 2 but
a bit better: there's an unordered "pending insertions" list that has to
be scanned by every search in addition to checking the main index body.
Every so often the pending insertions list is automatically flushed into
the main index body.

The reason we do this for GIN is that that index type puts a huge premium
on doing inserts "in bulk"; it's a lot more efficient if you push many
rows into the index at once, because frequently they'll be inserting into
the same per-key posting lists. I do not see much opportunity for a
corresponding gain for btree.

So I really doubt that anyone would have any enthusiasm for saddling btree
with a similar mechanism. It's complicated (and has been the cause of
multiple bugs); it's hard to figure out when is the optimal time to flush
the pending insertions; and it slows down searches in favor of making
inserts cheap, which is generally not the way to bet --- if that's the
tradeoff you want, why not drop the index altogether?

But anyway, since you can use contrib/btree_gin to get more or less btree
semantics for GIN indexes (except for uniqueness enforcement), you might
try whether just replacing your btree indexes with GIN indexes provides
any win for your insertions.

regards, tom lane