> 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:
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/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services