Re: Using PK value as a String

Valentin Bogdanov schrieb:

--- On Mon, 11/8/08, Gregory Stark <stark@enterprisedb.com> wrote:

 

From: Gregory Stark <stark@enterprisedb.com>
Subject: Re: [PERFORM] Using PK value as a String
To: "Jay" <arrival123@gmail.com>
Cc: pgsql-performance@postgresql.org
Date: Monday, 11 August, 2008, 10:30 AM
"Jay" <arrival123@gmail.com> writes:

   

I have a table named table_Users:

CREATE TABLE table_Users (
  UserID       character(40)  NOT NULL default
     

'',
   

  Username   varchar(256)  NOT NULL default
     

'',
   

  Email          varchar(256) NOT NULL default
     

''
   

  etc...
);

The UserID is a character(40) and is generated using
     

UUID function. We
   

started making making other tables and ended up not
     

really using
   

UserID, but instead using Username as the unique
     

identifier for the
   

other tables. Now, we pass and insert the Username to
     

for discussions,
   

wikis, etc, for all the modules we have developed. I
     

was wondering if
   

it would be a performance improvement to use the 40
     

Character UserID
   

instead of Username when querying the other tables, or
     

if we should
   

change the UserID to a serial value and use that to
     

query the other
   

tables. Or just keep the way things are because it
     

doesn't really make
   

much a difference.
     

Username would not be any slower than UserID unless you
have a lot of
usernames longer than 40 characters.

However making UserID an integer would be quite a bit more
efficient. It would
take 4 bytes instead of as the length of the Username which
adds up when it's
in all your other tables... Also internationalized text
collations are quite a
bit more expensive than a simple integer comparison.

But the real question here is what's the better design.
If you use Username
you'll be cursing if you ever want to provide a
facility to allow people to
change their usernames. You may not want such a facility
now but one day...

   

I don't understand Gregory's suggestion about the design. I thought using natural primary keys as opposed to surrogate ones is a better design strategy, even when it comes to performance considerations and even more so if there are complex relationships within the database.

Regards,
Valentin

 

UUID is already a surrogate key not a natural key, in no aspect better than a numeric key, just taking a lot more space.

So why not use int4/int8?