Re: Do non-sequential primary keys slow performance

On 29/9/2006 14:59, "Damian C" <jamianb(at)gmail(dot)com> wrote:

> Hello,
> The most difficult part of this question is justifying WHY we would
> want to use random primary keys! There is a very strong reason for
> doing so, although not quite compelling.

Either you want it that way because that's your first thought of how to do
it or you have reasoning behind why you want to do it that way.

> We are Java developers developing desktop applications that persist
> data in postgres. This is a pretty "low spec" database as it will only
> servicing a few PCs. We do this via Hibernate so our SQL & Postrges
> skills and insights are relatively lacking. I certainly don't really
> understand the gory internal details of postgres.
>
> We have an internal proposal to use what are virtually random 128 bit
> numbers for our primary keys. These are not truley random in any
> mathematical sense, and they will be unique, but they are certainly
> NOT sequential.

Specifying it as the primary key or a unique index will ensure the
uniqueness of entries.

> In my ignorant bliss I would suspect that postgres will run more
> slowly using random primary keys. Can anyone provide any "rules of
> thumb" for how this may effect performance?? Is it a plain dumb
> idea?? Or maybe it would have only modest impact??
>
> Any comments, insights, pointers are very much appreciated,
>

As for being non-sequential this is how a live database will end up (to some
degree) - as rows get deleted the auto assigned sequential numbers get
deleted along with them and don't get re-used leaving gaps.

I believe the only performance different will stem from using a 128bit
column for the key against say a 32 or 64 bit serial. As in larger data to
save and search through and compare.

When you say that you want it as your primary key, how much will you use it
to link to other tables? If it is creating a link to 3 other tables it
wouldn't be a problem but if you use it to link 50 tables with complex joins
in your searches then you may want to reconsider or develop a work around.
Or is it simply just your unique row identifier?

The fact that you say it will only service a few pc's would lead me to think
that you aren't expecting millions of rows to be entered, this would suggest
that the performance difference would not be intolerable even if it is
noticeable to your application and you could get better performance with a
different design.

--

Shane Ambler
Postgres(at)007Marketing(dot)com

Get Sheeky @ http://Sheeky.Biz