Thread: Primary keys and speed

Hello.

I am looking at useing uuid's as primary keys rather than a normal
sequence of numbers.

The uuids are long text strings like so:

7559e648-a29c-11d5-952f-00c026a18838

The reason for useing them is that it's almost gaurenteed that
imported data from another site is going to have a unique identifier.

But I was wondering if this will impact on the speed of the database.

In the long run the application does not need to be blindingly fast as 99%
of the time it is waiting on human interaction.

Any ideas?

--

  Rob Brown-Bayliss
 ---======o======---

Hi, I have not yet seen an answer to the following, can I assume it's
not a problem?

On Thu, 2001-09-06 at 19:58, Rob Brown-Bayliss wrote:
>
> Hello.
>
> I am looking at useing uuid's as primary keys rather than a normal
> sequence of numbers.
>
> The uuids are long text strings like so:
>
> 7559e648-a29c-11d5-952f-00c026a18838
>
> The reason for useing them is that it's almost gaurenteed that
> imported data from another site is going to have a unique identifier.
>
> But I was wondering if this will impact on the speed of the database.
>
> In the long run the application does not need to be blindingly fast as 99%
> of the time it is waiting on human interaction.
>
> Any ideas?
>
--

  Rob Brown-Bayliss
 ---======o======---

> > I am looking at useing uuid's as primary keys rather than a normal
> > sequence of numbers.
> >
> > The uuids are long text strings like so:
> >
> > 7559e648-a29c-11d5-952f-00c026a18838
> >
> > The reason for useing them is that it's almost gaurenteed that
> > imported data from another site is going to have a unique identifier.
> >
> > But I was wondering if this will impact on the speed of the database.
> >
> > In the long run the application does not need to be blindingly fast as
99%
> > of the time it is waiting on human interaction.
> >
> > Any ideas?
> >

I have not done *comparative* studies of using these kind of strings versus
sequence numbers, but I have done a fair amount of this sort of thing in one
of the applications I've been working on. On old hardware (dual ppro 200
with 512MB and RAID5) I got lookups times at around 10 milliseconds, from a
table with 20 million records. The key was a 40 byte hex string. The timing
was from running "set show_query_stats = true;" and then looking at the tail
of the postgres serverlog.

My best advice is to load some sample data in your schema on your hardware
and give it a try.

Hope this helps,

-- Joe