Re: pervasiveness of surrogate (also called synthetic) keys

David Johnston wrote:
> Is there any rules-of-thumb on the performance of a PK as a function of key length? I like using varchar based identifiers since I tend to query tables directly and writing where clauses is much easier if you can avoid the joins. I'm likely better off creating views and querying those but am still curious on any basic thoughts on having a 100+ length primary key.
>

The shorter the better, but it may not be as bad as you fear. The way
B-tree indexes are built, it isn't that expensive to hold a longer key
so long as the unique part doesn't average out to be that long. So if
you insert "123456666666666666666" and "12345777777777777777", that's
not going to be much different than navigating "123456" and "123457",
because once you get that far you've already reached a unique prefix.
But if your entries have a really long common prefix, like
"111111111111111112" and "111111111111111113", that's going to be more
expensive to deal with--even though the strings are the same length.

If your identifiers become unique after only a few characters, it may
not be so bad. But if they go many characters before you can
distinguish between any two entries, you're probably not going to be
happy with the performance or size of the indexes, relative to simple
integer keys.

--
Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books