Re: Converting uuid primary key column to serial int - Mailing list pgsql-general

On Wed, Jun 8, 2011 at 1:06 PM, David Johnston <polobo@yahoo.com> wrote:
>> -----Original Message-----
>> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
>> owner@postgresql.org] On Behalf Of Mike Christensen
>> Sent: Wednesday, June 08, 2011 2:57 PM
>> To: pgsql-general@postgresql.org
>> Subject: [GENERAL] Converting uuid primary key column to serial int
>
>> for each ingredient are kinda a hassle to manage though, and I'm thinking
> in
>> this case I might just want to use a 32bit integer for the primary key..
>
>> 2) Once I do this, I obviously need to fix up all the foreign keys and
> convert
>> them from the old UUIDs to the new generated numeric value.
>> Is there an easy way to manage this sort of thing?  I'm thinking about
> keeping
>> the old UUID as a non-PK column to use as a "mapping" as I migrate the
> rest
>> of the schema over.  Is that a good approach?
>>
>
> What specific hassles are you encountering?  Seems like a lot of effort to
> go through to make the data less-unique.  Since you already have the field
> setup for PK/FK use try to leave that alone and just add a secondary
> identifier that you use for queries.  Like you said in #2 leave the UUID in
> place as a non-PK (but still unique/not-null) field and leave all the
> foreign keys in place as well.  Add your "integer identity" column as the
> new Primary Key and use that field when you want to specify a record.  Then,
> for those cases where you have or want to use the integer PK but the field
> being compared is the UUID you can write a simple function to return the
> UUID associated with the given integer.
>
> Otherwise you probably should just add the integer field to EVERY TABLE and
> establish relational links.  Add the field as null-able, perform the update
> using the UUID, change to not null, add FK constraint.  As you then attempt
> to remove the UUID field from the tables the system will tell you where
> different function and views are relying upon the UUID field and you can
> change the object to use the new integer field instead.
>
> The main risk really is in the application since the database will not be
> able to enforce consistency.
>
> Serial is implemented via "DEFAULT" and sequences; and if you dump/restore a
> sequence it is done consistently.

All very good points.  A few reasons why I'm considering this:

1) On some pages, the user can pass in a search query (keywords to
find, ingredients to exclude, etc)..  I have a Base64 representation
of this search query, and it gets pretty long if a bunch of UUIDs are
included.  Making them ints would make for much shorter URLs.  There's
a few other pages that have an ingredient ID URL parameter directly.
I think URLs with UUIDs are kinda ugly.

2) I deal with a lot of this data through internal web based admin
tools as well as Excel.  There's a lot of places where I need to link
some metadata to an existing ingredient.  I'd like to just be able to
refer to "eggs" as, say, 53, rather than some UUID for eggs.  Sure,
the tools could have auto-complete and auto-lookup stuff but I haven't
had much time to work on these tools so they're super hacky right now.

Both of these requirements, as you said, could be satisfied by using a
non-primary key though.  In fact, there's then no reason to change any
other tables - internally the database would link to the ingredient
UUIDs.

I'm assuming I can still have a "Serial" column that is NOT a primary
key, and it'll incremement just the same as I add rows?  If that's the
case, I think that's a superior approach..

BTW, this table is too small to worry about disk space of UUIDs and/or
perhaps any sort of performance benefits to using int over uuid (if
there are any)..

Mike