Skip site navigation (1) Skip section navigation (2)

Re: pervasiveness of surrogate (also called synthetic) keys

From: Greg Smith <greg(at)2ndquadrant(dot)com>
To: David Johnston <polobo(at)yahoo(dot)com>
Cc: Merlin Moncure <mmoncure(at)gmail(dot)com>, Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>, Rob Sargent <robjsargent(at)gmail(dot)com>, Jeff Davis <pgsql(at)j-davis(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: pervasiveness of surrogate (also called synthetic) keys
Date: 2011-05-04 07:25:54
Message-ID: 4DC0FF82.50502@2ndquadrant.com (view raw or flat)
Thread:
Lists: pgsql-general
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


In response to

Responses

pgsql-general by date

Next:From: Greg SmithDate: 2011-05-04 07:29:35
Subject: Re: Bidirectional replication
Previous:From: kosnaDate: 2011-05-04 07:10:25
Subject: postgresql not updating the sequence

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group