UUID column as pimrary key?

We're using UUID for primary keys in PG 8.4 without any issues. I have
no real insights into the details or performance issues, but always
figured it was stored as a binary 128-bit value, but with added benefits
of being able to enter and view them using a standard string format. We
don't sort them as they have no real meaning for us.

On Tue, 2011-01-04 at 11:07 -0800, Dennis Gearon wrote:

I haven't been able to find anywhere, easily, in the documentation using google
where a list of allowed data types for primary keys is.

Anything that can be UNIQUE NOT NULL

So, UUIDs can be primary keys?

Yes.

JD

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt

d.wall@computer.org (David Wall) writes:

We're using UUID for primary keys in PG 8.4 without any issues. I
have no real insights into the details or performance issues, but
always figured it was stored as a binary 128-bit value, but with added
benefits of being able to enter and view them using a standard string
format. We don't sort them as they have no real meaning for us.

In principle, this might be a reason to want to do the long-outstanding
work on hash indexes; with UUIDs, it mayn't be useful to sort the
values, but you *do* want to be able to validate that they're unique.
--
output = ("cbbrowne" "@" "gmail.com")
http://www3.sympatico.ca/cbbrowne/
"Computers are like air conditioners: They stop working properly if
you open windows."

On Tue, 4 Jan 2011 11:07:00 -0800 (PST), Dennis Gearon
<gearond@sbcglobal.net> wrote:

I haven't been able to find anywhere, easily, in the documentation
using google
where a list of allowed data types for primary keys is.

So, UUIDs can be primary keys?
Any issues wtih them on sorting or paging of index tables, etc.?

Disadvantage
* aren't ordered in way as records are added to DB.
* 128bit length (PSQL stores them as 128bit value)
* slower to generate you need to use random number generator
* ... if you do select * on table with two uuids you will need to
scroll GUI to see data :)
* ... really unhandy if you want to make manual updates :)

Advantage:
* simple to generate, and 128bit random is almost globally unique,
* you have your id, before executing query, (in contrast to all this
autoincrement) so you may put it in dependant rows
* almost every platform has UUID generator

Advantage / disadvantage
* depending on UUID generator, UUID can store some "privacy"
information e.g. MAC address of your card, such UUID.

Personally I prefer pooled incremental id's. Fast, unique, you have Id
before query - but you need to write "code" by self.

On 01/05/2011 07:31 PM, Radosław Smogura wrote:

* you have your id, before executing query, (in contrast to all this
autoincrement) so you may put it in dependant rows

Do you mean that with a UUID, you don't need to talk to the database at
all, you can generate an ID with no interaction with / involvement with
the database at all? Because other than that, there's not much
difference in how you normally work with them.

With a sequence, you might:

CREATE SEQUENCE x_id_seq;
CREATE TABLE x (
id integer PRIMIARY KEY DEFAULT nextval('x_id_seq'),
y integer
);
INSERT INTO x(y) VALUES (1);

With a uuid, you'd:

CREATE TABLE x (
id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
y integer
);
INSERT INTO x(y) VALUES (1);

In either case, you can explicitly call the generator function for
seq/uuid - nextval(seqname) or uuid_generate_v4() respectively - or you
can omit the PK column in your inserts and let the database generate it.

Personally I prefer pooled incremental id's. Fast, unique, you have Id
before query - but you need to write "code" by self.

Many libraries / ORMs / etc that interact with Pg will happily take care
of this for you. In fact, I had to fight to convince Hibernate that I
*didn't* want it to increment all my counters in steps of 50.

--
Craig Ringer

On Wed, 05 Jan 2011 21:50:11 +1100, Craig Ringer
<craig@postnewspapers.com.au> wrote:

On 01/05/2011 07:31 PM, Radosław Smogura wrote:

* you have your id, before executing query, (in contrast to all this
autoincrement) so you may put it in dependant rows

Do you mean that with a UUID, you don't need to talk to the database
at all, you can generate an ID with no interaction with / involvement
with the database at all? Because other than that, there's not much
difference in how you normally work with them.

With a sequence, you might:

CREATE SEQUENCE x_id_seq;
CREATE TABLE x (
id integer PRIMIARY KEY DEFAULT nextval('x_id_seq'),
y integer
);
INSERT INTO x(y) VALUES (1);

I mean situation, when You create e.g. in one transaction, book and
chapters, in some way You need retrieve book's id, by returning clause
of insert, or by obtaining id form sequence.
It's simpler to write:
book_id = new uuid();
insert into book values(book_id,....);
insert into chapters values(new uuid(), book_id, ...);
isn't it?

On 5 January 2011 15:28, Radosław Smogura <rsmogura@softperience.eu> wrote:

On Wed, 05 Jan 2011 21:50:11 +1100, Craig Ringer <
craig@postnewspapers.com.au> wrote:

On 01/05/2011 07:31 PM, Radosław Smogura wrote:

* you have your id, before executing query, (in contrast to all this

autoincrement) so you may put it in dependant rows

Do you mean that with a UUID, you don't need to talk to the database
at all, you can generate an ID with no interaction with / involvement
with the database at all? Because other than that, there's not much
difference in how you normally work with them.

With a sequence, you might:

CREATE SEQUENCE x_id_seq;
CREATE TABLE x (
id integer PRIMIARY KEY DEFAULT nextval('x_id_seq'),
y integer
);
INSERT INTO x(y) VALUES (1);

I mean situation, when You create e.g. in one transaction, book and

chapters, in some way You need retrieve book's id, by returning clause of
insert, or by obtaining id form sequence.
It's simpler to write:
book_id = new uuid();
insert into book values(book_id,....);
insert into chapters values(new uuid(), book_id, ...);
isn't it?

For me it is simpler just to write this:

bookid = insert into books(...) values(...) returning book_id;
insert into chapters(book_id, ...) values( bookid, ...);

but it's a matter of taste, I think.

regards
Szymon

On Jan 5, 2011, at 1:31 AM, Radosław Smogura wrote:

* simple to generate, and 128bit random is almost globally unique,

Almost? Should be totally unique, as long as your random source is decent quality.

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice

On Jan 5, 2011, at 7:28 AM, Radosław Smogura wrote:

It's simpler to write:

...

isn't it?

Depends on the situation, the libraries you're using, and so on.

Now, if you're generating records in a distributed system, where your node might be disconnected when it's creating a record, it is *much* simpler in that case ;-)

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice

On Wed, Jan 5, 2011 at 2:37 PM, Scott Ribe <scott_ribe@elevated-dev.com> wrote:

On Jan 5, 2011, at 1:31 AM, Radosław Smogura wrote:

* simple to generate, and 128bit random is almost globally unique,

Almost? Should be totally unique, as long as your random source is decent quality.

But I would never rely on that alone. You always have a strategy in
place, in case there's a duplicate.

--
GJ

2011/1/5 Grzegorz Jaśkiewicz <gryzman@gmail.com>:

On Wed, Jan 5, 2011 at 2:37 PM, Scott Ribe <scott_ribe@elevated-dev.com> wrote:

On Jan 5, 2011, at 1:31 AM, Radosław Smogura wrote:

* simple to generate, and 128bit random is almost globally unique,

Almost? Should be totally unique, as long as your random source is decent quality.

But I would never rely on that alone. You always have a strategy in
place, in case there's a duplicate.

As long as all your UUIDs are generated with the same algorithm, they
are guaranteed to be unique.

On Jan 5, 2011, at 7:55 AM, Grzegorz Jaśkiewicz wrote:

But I would never rely on that alone. You always have a strategy in
place, in case there's a duplicate.

That's really unnecessary, basically a total waste of effort.

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice

In response to Scott Ribe <scott_ribe@elevated-dev.com>:

On Jan 5, 2011, at 1:31 AM, Radosław Smogura wrote:

* simple to generate, and 128bit random is almost globally unique,

Almost? Should be totally unique, as long as your random source is decent quality.

This is going off-topic, but I did some research on this because we
were considering using UUIDs for various keys ...

Fact is, UUIDs are not _guaranteed_ to be unique. If you use the generating
system that includes a MAC address, then in theory, they are guaranteed
to be unique, but in practice, MAC addresses aren't guaranteed to be
unique either, so that's not 100% either.

Beyond that, the namespace size for a UUID is so incomprehensibly huge
that the chance of two randomly generated UUIDs having the same value
is incomprehensibly unlikely ... it is, however, not a 100% guarantee.

Anyway, in our case, we determined that the chance of UUID collision
for the dataset in question was extremely unlikely, however, the
consequences of such a collision were pretty bad. We also determined
that we were able to control a "unit ID" for each independent system
that would generate IDs, which could (a) be part of a unique seed for
UUIDs, or (b) be a prefix to a autonumber ID that would be a lot easier
to read and work with manually. In the end, we chose b for the human
factor.

Face it, reading, remembering, and typing UUIDs kinda sucks.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

On Jan 5, 2011, at 8:03 AM, Mike Christensen wrote:

As long as all your UUIDs are generated with the same algorithm, they
are guaranteed to be unique.

There is no requirement that they be generated with the same algorithm in order to be unique. A MAC/time-based UUID cannot duplicate a random one, and vice versa. (Also applies to the 3rd flavor of UUID whose details I do not remember.)

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice

On Jan 5, 2011, at 8:05 AM, Bill Moran wrote:

Beyond that, the namespace size for a UUID is so incomprehensibly huge
that the chance of two randomly generated UUIDs having the same value
is incomprehensibly unlikely

Yes, as in: it is *far* more likely that all of your team members and all of your client contacts will be simultaneously struck by lightning and killed in their sleep, and it is *far* more likely that all life on earth will be wiped out by an asteroid impact, and it is *far* more likely that the solar system orbits are not actually stable and earth will fly off into space... If you're worried about UUID collisions, then either your priorities are completely wrong, or you live in a bomb shelter--that's not sarcasm by the way, it's simply true, the chance of a collision is so vanishingly small that it is dwarfed by all sorts of risks that we all ignore because the chances are so low, including the chance that all drives in all your RAIDs across all your replicas will simultaneously fail on the same day that fires start in all the locations where your tapes are stored and all the sprinkler systems fail... (By "far" more likely, I mean many many many orders of magnitude...)

In the end, we chose b for the human
factor.

A very good decision, in the case where you're actually able to control each independent system.

Face it, reading, remembering, and typing UUIDs kinda sucks.

Lots of copy & paste, or custom GUI tools for devs & DBAs, or abuse like '...%', all of them painful in their own way.

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice

In response to Scott Ribe <scott_ribe@elevated-dev.com>:

On Jan 5, 2011, at 8:05 AM, Bill Moran wrote:

Beyond that, the namespace size for a UUID is so incomprehensibly huge
that the chance of two randomly generated UUIDs having the same value
is incomprehensibly unlikely

Yes, as in: it is *far* more likely that all of your team members and all of your client contacts will be simultaneously struck by lightning and killed in their sleep, and it is *far* more likely that all life on earth will be wiped out by an asteroid impact, and it is *far* more likely that the solar system orbits are not actually stable and earth will fly off into space... If you're worried about UUID collisions, then either your priorities are completely wrong, or you live in a bomb shelter--that's not sarcasm by the way, it's simply true, the chance of a collision is so vanishingly small that it is dwarfed by all sorts of risks that we all ignore because the chances are so low, including the chance that all drives in all your RAIDs across all your replicas will simultaneously fail on the same day that fires start in all the locations where your tapes are stored and all the sprinkler systems fail... (By "far" more likely, I mean many many many orders of magnitude...)

That statement demonstrates a lack of investigation and/or consideration
of the circumstances.

I can't find my math or I'd reproduce it here, but consider this:

If you have 50 devices, each generating 100 UUIDs per hour, and you'll
keep records for 1 year, then your argument above is probably accurate.

However, if there are 5000 devices generating 100 UUIDs per hour, and you'll
be keeping those records for 10+ years, the chances of collisions near
the end of that 10 year span get high enough to actually make developers
nervous.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

Scott Ribe <scott_ribe@elevated-dev.com> writes:

On Jan 5, 2011, at 8:05 AM, Bill Moran wrote:

Beyond that, the namespace size for a UUID is so incomprehensibly huge
that the chance of two randomly generated UUIDs having the same value
is incomprehensibly unlikely

Yes, as in: it is *far* more likely that all of your team members and all of your client contacts will be simultaneously struck by lightning and killed in their sleep, and it is *far* more likely that all life on earth will be wiped out by an asteroid impact,

I see those sorts of arguments all the time, and I consider them pure BS.
Yes, the namespace is theoretically large. However, the questions you
really have to answer are (a) how much of the namespace is actually
being used by the UUID generation methods in use in a particular
application; (b) how sure can you be that there is not correlation
between UUIDs generated in different places/sessions.

In practical use I think the odds of a collision are *far* higher than
you are suggesting, unless the UUID generation is being done with a lot
more care than is likely if the user takes these sorts of claims at face
value. The odds may still be low enough to be a very good risk, but
you need to think about it not just bet your database on it without
thinking.

Being paranoid is a good thing. It's what DBAs are paid for.

regards, tom lane

On 01/05/2011 08:55 AM, Bill Moran wrote:

In response to Scott Ribe <scott_ribe@elevated-dev.com>:

On Jan 5, 2011, at 8:05 AM, Bill Moran wrote:

Beyond that, the namespace size for a UUID is so incomprehensibly huge
that the chance of two randomly generated UUIDs having the same value
is incomprehensibly unlikely

Yes, as in: it is *far* more likely that all of your team members and all of your client contacts will be simultaneously struck by lightning and killed in their sleep, and it is *far* more likely that all life on earth will be wiped out by an asteroid impact, and it is *far* more likely that the solar system orbits are not actually stable and earth will fly off into space... If you're worried about UUID collisions, then either your priorities are completely wrong, or you live in a bomb shelter--that's not sarcasm by the way, it's simply true, the chance of a collision is so vanishingly small that it is dwarfed by all sorts of risks that we all ignore because the chances are so low, including the chance that all drives in all your RAIDs across all your replicas will simultaneously fail on the same day that fires start in all the locations where your tapes are stored and all the sprinkler systems fail... (By "far" more likely, I mean many many many orders of magnitude...)

That statement demonstrates a lack of investigation and/or consideration
of the circumstances.

I can't find my math or I'd reproduce it here, but consider this:

If you have 50 devices, each generating 100 UUIDs per hour, and you'll
keep records for 1 year, then your argument above is probably accurate.

However, if there are 5000 devices generating 100 UUIDs per hour, and you'll
be keeping those records for 10+ years, the chances of collisions near
the end of that 10 year span get high enough to actually make developers
nervous.

But we're talking about a primary key. Postgres guarantees the
uniqueness. 1 transaction in 10^^100 rolls back due to a second
instance of an (otherwise/almost) uuid. Big deal.

On Jan 5, 2011, at 8:55 AM, Bill Moran wrote:

That statement demonstrates a lack of investigation and/or consideration
of the circumstances.

No, it doesn't.

However, if there are 5000 devices generating 100 UUIDs per hour, and you'll
be keeping those records for 10+ years, the chances of collisions near
the end of that 10 year span get high enough to actually make developers
nervous.

No, they don't. At the end of your hypothetical 10-year period, you will have used about 43,000,000,000 UUIDs, or about 1/100,000,000,000,000,000,000,000,000th of the UUID space (assuming random UUIDs). Leaving you with a chance of a single collision of about 1/18,000,000,000,000,000.

Assuming of course good entropy. If the generation of random numbers is bad, then UUIDs are not so useful ;-)

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice