Re: [WIP] Performance Improvement by reducing WAL for Update Operation - Mailing list pgsql-hackers

> On Thursday, September 27, 2012 9:12 AM Noah Misch wrote:
> On Mon, Sep 24, 2012 at 10:57:02AM +0000, Amit kapila wrote:
> > Rebased version of patch based on latest code.
> 
> I like the direction you're taking with this patch; the gains are
> striking,
> especially considering the isolation of the changes.
 Thank you for a detailed review of the patch.

> You cannot assume executor-unmodified columns are also unmodified from
> heap_update()'s perspective.  Expansion in one column may instigate
> TOAST
> compression of a logically-unmodified column, and that counts as a
> change for
> xlog delta purposes.  You do currently skip the optimization for
> relations
> having a TOAST table, but TOAST compression can still apply.  Observe
> this
> with text columns of storage mode PLAIN.  I see two ways out: skip the
> new
> behavior when need_toast=true, or compare all inline column data, not
> just
> what the executor modified.  One can probably construct a benchmark
> favoring
> either choice.  I'd lean toward the latter; wide tuples are the kind
> this
> change can most help.  If the marginal advantage of ignoring known-
> unmodified
> columns proves important, we can always bring it back after designing a
> way to
> track which columns changed in the toaster.

You are right that it can give benefit for both ways, but we should also see
which approach can 
give better results for most of the scenario's. 
As in most cases of Update I have observed, the change in values will not
increase the length of value to too much.
OTOH I am not sure may be there are many more scenario's which change the
length of updated value which can lead to scenario explained by you above.

> 
> Given that, why not treat the tuple as an opaque series of bytes and
> not worry
> about datum boundaries?  When several narrow columns change together,
> say a
> sequence of sixteen smallint columns, you will use fewer binary delta
> commands
> by representing the change with a single 32-byte substitution.  If an
> UPDATE
> changes just part of a long datum, the delta encoding algorithm will
> still be
> able to save considerable space.  That case arises in many forms:
> changing
> one word in a long string, changing one element in a long array,
> changing one
> field of a composite-typed column.  Granted, this makes the choice of
> delta
> encoding algorithm more important.
> 
> Like Heikki, I'm left wondering why your custom delta encoding is
> preferable
> to an encoding from the literature.  Your encoding has much in common
> with
> VCDIFF, even sharing two exact command names.  If a custom encoding is
> the
> right thing, code comments or a README section should at least discuss
> the
> advantages over an established alternative.  Idle thought: it might pay
> off to
> use 1-byte sizes and offsets most of the time.  Tuples shorter than 256
> bytes
> are common; for longer tuples, we can afford wider offsets.

My apprehension was that it can affect the performance if do more work by
holding the lock. 
If we use any standard technique like LZ of VCDiff, it has overhead of
comparison
and other things pertaining to their algorithm. 
However using updated patch by Heikki, I can run the various performance
tests both for update operation as well as recovery.

With Regards,
Amit Kapila.