From: | Gregory Stark <stark(at)enterprisedb(dot)com> |
---|---|
To: | "Jay" <arrival123(at)gmail(dot)com> |
Cc: | <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Using PK value as a String |
Date: | 2008-08-11 09:30:31 |
Message-ID: | 874p5ruceg.fsf@oxford.xeocode.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
"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...
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's On-Demand Production Tuning
From | Date | Subject | |
---|---|---|---|
Next Message | Valentin Bogdanov | 2008-08-11 09:46:01 | Re: Using PK value as a String |
Previous Message | Henrik | 2008-08-11 09:17:55 | Re: Filesystem benchmarking for pg 8.3.3 server |