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

Re: numerical primary key vs alphanumerical primary key

From: Florent Guillaume <fg(at)nuxeo(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: numerical primary key vs alphanumerical primary key
Date: 2013-02-12 15:14:15
Message-ID: CAF-4BpMgC=W08T8L7fRa2g5Ui03nsFiD6JFnXG_KA+qvaUDO3g@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
For SQL Server, having a clustered index on a numeric incrementing key
is much better than having a semi-random uuid primary key used as the
clustered index itself.

Florent

On Tue, Feb 12, 2013 at 4:05 PM, Grant Johnson <grant(at)amadensor(dot)com> wrote:
> My experience has been that the performance advantage for numeric keys is
> primarily an Oracle thing. However, Oracle is popular enough for people to
> assume that it applies to databases in general.
>
>
> Julien Cigar <jcigar(at)ulb(dot)ac(dot)be> wrote:
>>
>> The biggest difference in performance between text and integer keys is
>> usually down to whether you're inserting in order or not. Inserting in order
>> is tons faster regardless of the type, since it keeps the index unfragmented
>> and doesn't cause page splits.
>>
>> On 02/04/2013 22:52, Anne Rosset wrote:
>>
>> Hi,
>>
>> I have read a lot of different information about the benefits of using
>> numerical primary key vs alphanumerical primary key(small size). And what I
>> am gathering is that for performance there is no more great advantage.
>>
>> It seems like now RDBMS in general, postgres in particular handles pretty
>> well joins on text indexes.
>>
>> Did I understand correctly?
>>
>> Thanks,
>>
>> Anne
>>
>>
>
> --
> Sent from my Android phone with K-9 Mail. Please excuse my brevity.



--
Florent Guillaume, Director of R&D, Nuxeo
Open Source, Java EE based, Enterprise Content Management (ECM)
http://www.nuxeo.com   http://www.nuxeo.org   +33 1 40 33 79 87


In response to

pgsql-performance by date

Next:From: Claudio FreireDate: 2013-02-12 16:26:11
Subject: Re: numerical primary key vs alphanumerical primary key
Previous:From: Grant JohnsonDate: 2013-02-12 15:05:29
Subject: Re: numerical primary key vs alphanumerical primary key

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