One of the issues with using UUIDs for primary keys, or even in a secondary key
using a unique index,would be paging of the index because of the randomness of
the values. This is what I have read, is that true for Posgres?
On the tests in this article, doing large amounts of consecutive inserts,
generating new UUIDs, and having the indexes getting updated caused a 30
(THIRTY) times increase in time to do inserts. So he came up with a function
that caused the lowest characters of the UUIDs to change slowly according to
time in microseconds. This reduced the time to using UUIDs for PKs for inserts
and joins BOTH by only 10% relative to SEQUENCES and their integer primary keys.
The theory being that most of hte distribution in the index pages was due to the
lowest characters in the string.
I've made a function in PHP that makes this same 'COMB UUID' as he coined the
term. The output of a sequence of them, as fast as my box could make them and
ECHO them, and put them below. When NOT echoing them, the lowest characters
change VERY slowly. 1 MILLION of them can be generated in 3 milliseconds on my
home box, (which is not doing much else). to make 1 MILLION of them and store
them in PHP arrays takes 3 seconds.
My question, I guess repeated, is does Postgres do index distribution the same
way as the article suggests? So keeping the lower 12 characters sequential and
slowly changing would keep index pages in memory longer?
<sample values>
58cf3d06-3d94-401d-9b34-498e87a3e469
981ab472-21fe-4b59-a224-498e87a3e473
df2d2298-b549-4b8e-86d5-498e87a3e478
5cb92685-f9d3-473a-a5e8-498e87a3e47c
6c4b1712-dfe8-447b-8e94-498e87a3e480
e9ab677a-812c-484e-ae71-498e87a3e484
2812c003-6e0b-4974-94ef-498e87a3e489
851dc2d8-9125-4410-a952-498e87a3e48d
a8c695c9-6c43-4847-9bda-498e87a3e491
c2d0d9f4-37e3-417a-9611-498e87a3e495
3ddfd416-6568-4c2c-8ffe-498e87a3e49a
471bf59a-f926-40b5-9990-498e87a3e49e
9d6a9876-3ba7-4133-a60f-498e87a3e4a2
01859d5f-3cde-4b83-8a1b-498e87a3e4a6
85321a94-5ae1-4b92-93f2-498e87a3e4aa
e74e75ae-d30f-4ba3-b8b0-498e87a3e4ae
62ac5bc8-1498-4d2a-b8ef-498e87a3e4b3
fa597e9c-ae35-461e-b15f-498e87a3e4b7
f3c46abf-a6b0-4c9c-b22d-498e87a3e4bb
fc868307-d1b1-4253-91d7-498e87a3e4bf
b79679a9-4359-42a1-bf46-498e87a3e4c3
d91bf8cb-e3be-4446-be73-498e87a3e4c7
bec9e0a1-cd85-4f0c-b35b-498e87a3e4cc
0e0ea724-e145-4932-b0df-498e87a3e4d0
30ab3e05-26e3-44af-a82f-498e87a3e4d4
</sample values>
Dennis Gearon
Signature Warning
----------------
It is always a good idea to learn from your own mistakes. It is usually a better
idea to learn from others’ mistakes, so you do not have to make them yourself.
from 'http://blogs.techrepublic.com.com/security/?p=4501&tag=nl.e036'
EARTH has a Right To Life,
otherwise we all die.