Re: "Write amplification" is made worse by "getting tired" while inserting into nbtree secondary indexes (Was: Why B-Tree suffix truncation matters)

On Sun, Jul 8, 2018 at 7:59 PM, Peter Geoghegan <pg(at)bowt(dot)ie> wrote:
> The whole "getting tired" thing is the root of the problem here, which
> is why the pending v3 of my patch will remove that code completely
> (_bt_findinsertloc() is streamlined).

Peter,

This seems like really interesting and important work. I wouldn't
have foreseen that the "getting tired" code would have led to this
kind of bloat (even if I had known about it at all). I wonder,
though, whether it's possible that the reverse could happen in some
other scenario. It seems to me that with the existing code, if you
reinsert a value many copies of which have been deleted, you'll
probably find partially-empty pages whose free space can be reused,
but if there's one specific place where each tuple needs to go, you
might end up having to split pages if the new TIDs are all larger or
smaller than the old TIDs.

I'm really glad that you are working on this.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company