Re: Insert vs Update

Le 15 juil. 2015 11:16 PM, "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>
a écrit :
>
> On Wed, Jul 15, 2015 at 4:53 PM, Michael Nolan <htfoot(at)gmail(dot)com> wrote:
>>
>> On Wed, Jul 15, 2015 at 3:16 PM, Robert DiFalco <robert(dot)difalco(at)gmail(dot)com>
wrote:
>>>
>>>
>>> Thanks David, my example was a big simplification, but I appreciate
your guidance. The different event types have differing amounts of related
data. Query speed on this schema is not important, it's really the write
speed that matters. So I was just wondering given the INSERT or UPDATE
approach (with no indexed data being changed) if one is likely to be
substantially faster than the other.
>>>
>>
>> As I understand how ACID compliance is done, updating a record will
require updating any indexes for that record, even if the index keys are
not changing. That's because any pending transactions still need to be
able to find the 'old' data, while new transactions need to be able to find
the 'new' data. And ACID also means an update is essentially a
delete-and-insert.
>
>
> ​I might be a bit pedantic here but what you describe is a byproduct of
the specific​ implementation that PostgreSQL uses to affect Consistency
(the C in ACID) as opposed to a forgone outcome in being ACID compliant.
>
> http://www.postgresql.org/docs/9.4/static/mvcc-intro.html
>
> I'm out of my comfort zone here but the HOT optimization is designed to
leverage the fact that an update to a row that does not affect indexed
values is able to leave the index alone and instead during index lookup the
index points to the old tuple, notices that there is a chain present, and
walks that chain to find the currently active tuple.
>

That's true as long as the old and new tuples are stored in the same block.

> In short, if the only index is a PK an update of the row can avoid
touching that index.
>
> I mentioned that going from NULL to Not NULL may disrupt this but I'm
thinking I may have mis-spoken.
>
> Also, with separate tables the amount of data to write is going to be
less because you'd have fewer columns on the affected tables.
>
> While an update is a delete+insert a delete is mostly just a bit-flip
action - at least mid-transaction. Depending on volume, though, the
periodic impact of vaccuming may want to be taken into consideration.

--
Guillaume