Re: varchar as primary key - Mailing list pgsql-general

From William Garrison
Subject Re: varchar as primary key
Date
Msg-id 463A25ED.3070006@mobydisk.com
Whole thread Raw
In response to varchar as primary key  (Matthew Hixson <hixson@poindextrose.org>)
Responses Re: varchar as primary key
List pgsql-general
I don't recommend it.  There are better ways to store UUIDs:

char(32)    <-- Easy to work with, fixed length, inefficient
varchar(32) <-- 4 bytes larger due to variable size
bytea()     <-- 20 bytes, variable length
bit(128)    <-- 16 bytes, optimal

I don't like char() or varchar() because of case-senstivity and
inefficiency.

We used bytea, and created a small function byte2guid() and guid2byte()
to handle converting to/from strings when working at a SQL prompt.  But
the production code doesn't use those.  In retrospect, I would like to
have tried BIT(128) since I think fixed-length columns perform better
than variable-length columns.

Matthew Hixson wrote:
> I'm investigating the usage of a UUID primary key generator using
> Hibernate and Postgres.  The reason for using a UUID is that we will
> have an application hosted at different sites in different databases.
> We will need to aggregate the data back into a single database from time
> to time and we want to avoid PK collisions.
>   Is there a significant performance difference between using int
> primary keys and string primary keys in Postgres?
>   Thanks,
>    -M@
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>


pgsql-general by date:

Previous
From: Magnus Hagander
Date:
Subject: Re: C functions under windows
Next
From: Alvaro Herrera
Date:
Subject: Re: varchar as primary key