[prev in list] [next in list] [prev in thread] [next in thread] 

List:       pgsql-performance
Subject:    Re: [PERFORM] Using PK value as a String
From:       Mario Weilguni <mweilguni () sime ! com>
Date:       2008-08-12 9:58:39
Message-ID: 48A15ECF.5030203 () sime ! com
[Download RAW message or body]

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?




-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[prev in list] [next in list] [prev in thread] [next in thread]