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

On 19 June 2015 at 14:30, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> > 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?
>
> I'm not sure you're right about that. MySQL has a feature called
> secondary index buffering:
>
> https://dev.mysql.com/doc/refman/5.0/en/innodb-insert-buffering.html
>
> Now that might not be exactly what we want to do for one reason or
> another, but I think it would be silly to think that they implemented
> that for any reason other than performance, so there may be some
> performance to be gained there.
>
> Consider that on a table with multiple indexes, we've got to insert
> into all of them. If it turns out that the first leaf page we need
> isn't in shared buffers, we'll wait for it to be read in. We won't
> start the second index insertion until we've completed the first one,
> and so on. So the whole thing is serial. In the system MySQL has
> implemented, the foreground process would proceed unimpeded and any
> indexes whose pages were not in the buffer pool would get updated in
> the background.
>
> Ignoring for the moment the complexities of whether they've got the
> right design and how to implement it, that's sort of cool.
>

Interesting.

Reading that URL it shows that they would need to write WAL to insert into
the buffer and then again to insert into the index. You might get away with
skipping WAL logs on the index buffer if you had a special WAL record to
record the event "all indexes updated for xid NNNN", but since that would
be written lazily it would significantly complicate the lazy update
mechanism to track that.

It doesn't say anything about their being only one index buffer per table,
nor do I think it would make sense to do it that way. So ISTM that the
foreground process still has to insert serially into N index buffers, with
each insert being WAL logged.

So the only saving for the foreground process is the random I/O from
inserting into the indexes, which means the value of the technique is in
the case where you have many very large secondary indexes - which is now
covered by BRIN.

--
Simon Riggs http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services