Re: INSERT ... ON CONFLICT DO UPDATE

On Mon, Jul 20, 2015 at 7:01 AM, Geoff Winkless <pgsqladmin(at)geoff(dot)dj> wrote:

> On 20 July 2015 at 14:33, Rafal Pietrak <rafal(at)ztk-rp(dot)eu> wrote:
>
>> If I'm not mistaken, the conclusions from posts in this thread are:
>>
>> 3. there are methods (like cryptographic "random" sequence), which
>> guarantee no conflicts. So one should resort to that.
>>
>>
> Some web research suggests that random sequences are not great for indexes
> because of the resultant "keyspace fragmentation". I'm assuming that means
> a low number of nodes in the btree leafs, so an increase in memory usage
> for the index?
>
> Just a thought.
>

If you are inserting random values into a very large btree index, you dirty
at least one randomly-located block (the leaf page of the index) per
inserted row. This can cause some serious IO pain when that dirty data
needs to be written out without the benefit of write-combining or
sequential writes.

However, this is usually only a concern for bulk loading operations, not
real time operations. If you are generating money-equivalent vouchers at a
rate of thousands per second, hopefully you can afford some nice hardware
to run it on.

Cheers,

Jeff