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

On Tue, Jul 17, 2018 at 5:16 PM, Peter Geoghegan <pg@bowt.ie> wrote:
> There is actually a flipside to that downside, though (i.e. the
> downside is also an upside): While not filling up leaf pages that have
> free space on them is bad, it's only bad when it doesn't leave the
> pages completely empty. Leaving the pages completely empty is actually
> good, because then VACUUM is in a position to delete entire pages,
> removing their downlinks from parent pages. That's a variety of bloat
> that we can reverse completely. I suspect that you'll see far more of
> that favorable case in the real world with my patch. It's pretty much
> impossible to do page deletions with pages full of duplicates today,
> because the roughly-uniform distribution of still-live tuples among
> leaf pages fails to exhibit any temporal locality. So, maybe my patch
> would still come out ahead of simply ripping out "getting tired" in
> this parallel universe where latency doesn't matter, and space
> utilization is everything.

Yes, that's a good point.  Also, and I think pretty importantly, this
seems essential if we want to allow retail index tuple deletion, which
has its own set of advantages.

I don't think you're going to be able to rip out the getting-tired
code, though, because presumably we'll have to continue support
existing btree indexes that don't include TIDs for some
probably-not-small number of future releases, even if we decide that
all future btree indexes (and any that are reindexed) should have
TIDs.

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