Re: Using PK value as a String

On Aug 11, 2008, at 4:30 AM, Gregory Stark wrote:

> "Jay" <arrival123(at)gmail(dot)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...
>

If you generate UUID's with the UUID function and you are on 8.3,
why not use the UUID type to store it?

Ries

> --
> Gregory Stark
> EnterpriseDB http://www.enterprisedb.com
> Ask me about EnterpriseDB's On-Demand Production Tuning
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org
> )
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance

Ries van Twisk
tags: Freelance TYPO3 Glassfish JasperReports JasperETL Flex Blaze-DS
WebORB PostgreSQL DB-Architect
email: ries(at)vantwisk(dot)nl
web: http://www.rvantwisk.nl/
skype: callto://r.vantwisk