Re: questions about PG update performance

On Mon, Oct 26, 2015 at 4:31 PM, Ashutosh Bapat <
ashutosh(dot)bapat(at)enterprisedb(dot)com> wrote:
>
> On Mon, Oct 26, 2015 at 4:09 PM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>
> wrote:
>
>> On Mon, Oct 26, 2015 at 12:07 PM, Ashutosh Bapat <
>> ashutosh(dot)bapat(at)enterprisedb(dot)com> wrote:
>>
>>>
>>>
>>> On Mon, Oct 26, 2015 at 10:19 AM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>
>>> wrote:
>>>>
>>>>
>>>> I think Oracle just copies the changed part of old row to rollback
>>>> segment.
>>>> Also in Redo logs, it just writes the changed column value (both old and
>>>> new). So for the case we are discussing in this thread (one changed
>>>> column out of 200 columns), Oracle will just write the old value of that
>>>> column in Redo and then in rollback segment, and write the new value
>>>> in Redo and then do the in-place update in heap row.
>>>>
>>>>
>>> In that case, readers would pay the penalty for constructing the row.
>>>
>>
>> Readers that have snapshot older than update-transaction needs to
>> pay such cost, otherwise all newer transactions can directly read from
>> page. Also not all old-transaction readers have to pay any such cost.
>>
>>
> Can you please explain your last sentence?
>
>
At broad level, it works this way: when the transaction starts, it
makes a note of the current SCN and then while reading a table or
an index page, it uses the SCN number to determine if the page contains
the effects of transactions that should not be visible to the current
transaction. If the page is found to contain the effects of invisible
transactions, then it recreates an older version of the page by undoing
the effects of each such transaction. Now once this older version
of page is recreated, this can be used to fetch the rows for transactions
older than the current transaction which has updated the page and
newer than the transaction which has recreated the page. For details
you can read the blog [1] written by sometime back.

[1] -
http://amitkapila16.blogspot.in/2015/03/different-approaches-for-mvcc-used-in.html

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com