Re: string PK vs. interger PK

Jodi:

This opinion has less to do with Postgres and more to do will a general
philosophy but we always use a 4 byte integer column as the primary key for
almost every table. None of the following reasons means it has to be that
way, but all support Ints over strings:

1) Strings would normally be bigger than 4 byte integers. While I have no
experience to say this size issue could contribute to a perceived
performance issue, theoretically speaking, a smaller, more compact value
would be better.

2) Int processing of equality and inequality comparisons should be much
faster than string compares. However, again I have no experience to say
this size issue could contribute to a perceived performance issue.

3) Ints make it easier to employ Postgres Sequences in generating PK
values. These are very nice and quick.

4) Lastly, I'll just agree with the other replies that I've already seen
that you should avoid putting any kind of meaningful data into PK columns.
That fact that you want to consider strings almost implies meaning to the
values. For example, if these values are dictated by an outside source
which happened to use an alphanumeric value, then they carry meaning to an
outside source even if that meaning is that it is simply their own unique
identifier.

We've been faced with this question many a time. We almost always chose an
int PK and more often than not when we've strayed from this course, it's
come back to haunt us.

Hope this is helpful.

Marc Mitchell - Senior Application Architect
Enterprise Information Solutions, Inc.
4910 Main Street
Downers Grove, IL 60515
(630) 512-0570
marcm(at)eisolution(dot)com

----- Original Message -----
From: "Jodi Kanter" <jkanter(at)virginia(dot)edu>
To: "Postgres Admin List" <pgsql-admin(at)postgresql(dot)org>
Sent: Monday, April 15, 2002 1:56 PM
Subject: [ADMIN] string PK vs. interger PK

Can anyone offer some insight as to what should be considered when choosing
between a primary key that is an integer vs. a primary key that is a string
value? Does one or the other affect indexing speed? update speed?
Is a serial integer value better than using a PK that has some "value" in
its meaning but is a string?
Thanks
Jodi
_______________________________
Jodi L Kanter
BioInformatics Database Administrator
University of Virginia
(434) 924-2846
jkanter(at)virginia(dot)edu