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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
