On Tue, 30 Aug 2016 09:40:33 -0700, "Mike Sofen" <msofen@xxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
