Re: varchar as primary key

On 5/3/07, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> On Thu, 2007-05-03 at 08:58 -0700, Matthew Hixson wrote:
> > I'm investigating the usage of a UUID primary key generator using
> > Hibernate and Postgres. The reason for using a UUID is that we will
> > have an application hosted at different sites in different
> > databases. We will need to aggregate the data back into a single
> > database from time to time and we want to avoid PK collisions.
> > Is there a significant performance difference between using int
> > primary keys and string primary keys in Postgres?
>
> If the only thing you need to do is avoid primary key collisions, why
> not just store an extra int that represents the "site ID" and have a
> primary key on (the_key, site_id)?
>
> That way you're *sure* no collisions happen. A UUID has a lot of bits,
> but if the random generator is flawed a collision can still happen.

Also, why not simply use sequences?
You can declare sequence to START WITH 1 and INCREMENT BY 10,
and on the other site START WITH 2 INCREMENT BY 10.
There is no chance these will collide (unless human intervenes ;)),
and you can safely add 8 more similar servers, each with similar
sequence.

Regards,
Dawid