Re: UUIDs & Clustered Indexes

On Tue, 30 Aug 2016 09:40:33 -0700, "Mike Sofen" <msofen(at)runbox(dot)com>
wrote:

>From: Tom Lane Sent: Tuesday, August 30, 2016 7:16 AM
>
>>Do you actually *need* UUID keys, and if so why? A plain old bigint column
>>is smaller, cheaper to index, and the natural mechanism for generating it
>>(ie a sequence) will tend to preserve ordering for free.
>
>I agree with Tom for a "normal" application - I would always use bigints
>(bigserial) as a PK column. The app I currently working on is a high
>security web app for which the app coders require guids for all identifiers
>flowing around the system. So in this scenario, I'm using BOTH bigserials
>as the PK and uuids as AKs in the core tables. I reference the bigints for
>all joins and (have to) use the uuids for the filters. It's been working ok
>so far, lookup performance on a table with a few million rows, using the
>uuid (indexed) is instantaneous. I'll soon have a 100 million+ rows loaded
>into a single table and know a bit more.
>
>
>
>The uuids are also design insurance for me in case I need to shard, since
>I'll need/want that uniqueness across servers.

FYI: articles about sharding using bigint keys.

http://instagram-engineering.tumblr.com/post/10853187575/sharding-ids-at-instagram
http://rob.conery.io/2014/05/29/a-better-id-generator-for-postgresql/

George