Re: Using PK value as a String - Mailing list pgsql-performance

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
surrogateones is a better design strategy, even when it comes to performance considerations and even more so if there
arecomplex 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?