Thread: numerical primary key vs alphanumerical primary key
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
2013/2/4 Anne Rosset <arosset@collab.net>: > 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. Please, take a look at this blog post: http://www.depesz.com/2012/06/07/123-vs-depesz-what-is-faster/ -- Victor Y. Yegorov
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
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.
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@amadensor.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@ulb.ac.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
On Tue, Feb 12, 2013 at 12:05 PM, Grant Johnson <grant@amadensor.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. The advantage in PG also exists, only tied to size. It's not really whether it's numeric or not, but whether values are big or not. Int or other primitive types tend to be far faster to join because of their fixed, small size. If you have a varchar, and if you have big values from time to time, joining becomes heavy because the index is huge (it has to contain the values).