Re: partial heap only tuples

On Tue, Feb 9, 2021 at 06:48:21PM +0000, Bossart, Nathan wrote:
> Hello,
>
> I'm hoping to gather some early feedback on a heap optimization I've
> been working on. In short, I'm hoping to add "partial heap only
> tuple" (PHOT) support, which would allow you to skip updating indexes
> for unchanged columns even when other indexes require updates. Today,

I think it is great you are working on this. I think it is a major way
to improve performance and I have been disappointed it has not moved
forward since 2016.

> HOT works wonders when no indexed columns are updated. However, as
> soon as you touch one indexed column, you lose that optimization
> entirely, as you must update every index on the table. The resulting
> performance impact is a pain point for many of our (AWS's) enterprise
> customers, so we'd like to lend a hand for some improvements in this
> area. For workloads involving a lot of columns and a lot of indexes,
> an optimization like PHOT can make a huge difference. I'm aware that
> there was a previous attempt a few years ago to add a similar
> optimization called WARM [0] [1]. However, I only noticed this
> previous effort after coming up with the design for PHOT, so I ended
> up taking a slightly different approach. I am also aware of a couple
> of recent nbtree improvements that may mitigate some of the impact of
> non-HOT updates [2] [3], but I am hoping that PHOT serves as a nice
> complement to those. I've attached a very early proof-of-concept
> patch with the design described below.

How is your approach different from those of [0] and [1]? It is
interesting you still see performance benefits even after the btree
duplication improvements. Did you test with those improvements?

> As far as performance is concerned, it is simple enough to show major
> benefits from PHOT by tacking on a large number of indexes and columns
> to a table. For a short pgbench run where each table had 5 additional
> text columns and indexes on every column, I noticed a ~34% bump in
> TPS with PHOT [4]. Theoretically, the TPS bump should be even higher

That's a big improvement.

> Next, I'll go into the design a bit. I've commandeered the two
> remaining bits in t_infomask2 to use as HEAP_PHOT_UPDATED and
> HEAP_PHOT_TUPLE. These are analogous to the HEAP_HOT_UPDATED and
> HEAP_ONLY_TUPLE bits. (If there are concerns about exhausting the
> t_infomask2 bits, I think we could only use one of the remaining bits
> as a "modifier" bit on the HOT ones. I opted against that for the
> proof-of-concept patch to keep things simple.) When creating a PHOT
> tuple, we only create new index tuples for updated columns. These new
> index tuples point to the PHOT tuple. Following is a simple
> demonstration with a table with two integer columns, each with its own
> index:

Whatever solution you have, you have to be able to handle
adding/removing columns, and adding/removing indexes.

> When it is time to scan through a PHOT chain, there are a couple of
> things to account for. Sequential scans work out-of-the-box thanks to
> the visibility rules, but other types of scans like index scans
> require additional checks. If you encounter a PHOT chain when
> performing an index scan, you should only continue following the chain
> as long as none of the columns the index indexes are modified. If the
> scan does encounter such a modification, we stop following the chain
> and continue with the index scan. Even if there is a tuple in that

I think in patch [0] and [1], if an index column changes, all the
indexes had to be inserted into, while you seem to require inserts only
into the index that needs it. Is that correct?

> PHOT chain that should be returned by our index scan, we will still
> find it, as there will be another matching index tuple that points us
> to later in the PHOT chain. My initial idea for determining which
> columns were modified was to add a new bitmap after the "nulls" bitmap
> in the tuple header. However, the attached patch simply uses
> HeapDetermineModifiedColumns(). I've yet to measure the overhead of
> this approach versus the bitmap approach, but I haven't noticed
> anything too detrimental in the testing I've done so far.

A bitmap is an interesting approach, but you are right it will need
benchmarking.

I wonder if you should create a Postgres wiki page to document all of
this. I agree PG 15 makes sense. I would like to help with this if I
can. I will need to study this email more later.

--
Bruce Momjian <bruce(at)momjian(dot)us> https://momjian.us
EDB https://enterprisedb.com

The usefulness of a cup is in its emptiness, Bruce Lee