Re: Insert vs Update

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.

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.

David J.