Re: INSERT ... ON CONFLICT DO UPDATE - Mailing list pgsql-general

I admit it's not really my area.​

​Take it up with Drew Blas, I guess :)

https://blog.starkandwayne.com/2015/05/23/uuid-primary-keys-in-postgresql/ (see the addendum near the bottom).

My first stab at comprehending what he means:

​Imagine if you have around a thousand entries in your index. In a sequential set, the btree could be (with each leaf containing 100 values)

​In a (16-bit, say) random set, the btree could be

Inserting a new value in a sequential key is always going to go into the 900 node. When that fills, ​you can add a new node and just balance the parent branches (assuming postgres doesn't try to keep a proper balance across the nodes too?).

However in the random tree a new value could be inserted into _any_ of the leaf nodes, which means you either have to create more leaf nodes than you require (in order to leave plenty of blank space, and even if you do that you will probably end up with uneven fill, which means some searches will be slower than others) or you end up having to refactor all of your nodes every time you insert a value.

Geoff​