Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I would like to build a distributed system. I need to store data in databases and it would be helpful to use an UUID or a GUID as a primary key on some tables. I assume it's a drawbacks with this design since the UUID/GUID is quite large and they are almost random. The alternative is to use an auto-incremented INT or LONG.

What are the drawbacks with using UUID or GUID as a primary key for my tables?

I will probably use Derby/JavaDB (on the clients) and PostgreSQL (on the server) as DBMS.

share|improve this question
    
Why would it be helpful? What drawbacks are you most focused on? The answer to every DB Question this vague is "it depends." Can you give us more details? Are you most interested in read or write performance? what level of distribution are we talking about? –  Brian Ballsun-Stanton Jan 6 '11 at 2:53
    
@Brian: UUIDs in distributed systems is helpful since you can create the primary key on the clients and then upload the data asynchronously to the server. I'm mostly thinking about read performance drawbacks. Using many JOINs on UUIDs isn't maybe that good? In example a client add an item (UUID, name, supplier, creator) to an inventory system, and then the local database is synchronized with the central database on the server. –  Jonas Jan 6 '11 at 3:05
1  
I think that without some more clarifying comments on this that it's going to at most be "it depends". Without those, I'm going to go for VtC. –  jcolebrand Jan 6 '11 at 3:08
    
There is an article that talks about GUID vs. non-GUID affect on clustered indexes in SQL Server that you might find interesting even though it's related to a different SQL product: x.co/Twpp –  Jeff Mar 13 '11 at 4:21

3 Answers 3

up vote 13 down vote accepted

It depends on your generation function and size of the final tables

GUIDs are intended to be globally unique identifiers. As discussed in the Postgres 8.3 documentation there are no methodologies that are universally appropriate to generate these identifiers, but postgreSQL does ship with a few more useful candidates.

From the scope of your problem, and the need for offline writes, you've quite neatly boxed out the use of anything but a GUID, and therefore there are no compensatory advantages of other schemes.

From a functional standpoint, the key length is usually not an issue on any kind of modern system, depending on the number of reads and size of the table. As an alternative methodology, offline clients could batch new records without a primary key and simply insert them when reconnecting. As postgreSQL offers the "Serial" datatype, clients will never need to determine the ID if they can perform a simple write to the database.

share|improve this answer
1  
Damn you sleep, you've gone and let Brian answer the question. Yes, the requirement for "offline updates" completely changed the whole concept there. –  jcolebrand Jan 6 '11 at 12:34
    
Muahahahaah! ::twirls mustache evilly:: –  Brian Ballsun-Stanton Jan 6 '11 at 12:47
    
Even with offline-writes it would be possible to use INTs. E.g. using two columns {Node_ID, Item_ID} where each node has a Node_ID, and an Item_ID that is auto-incremented per node. –  Jonas Jan 6 '11 at 15:16
    
@Jonas ~ Yes, that is feasible. However, one of the reasons most people even contemplate GUIDs is for globally-separated replication of content to other databases. I mean the term itself is rather QED there. –  jcolebrand Jan 7 '11 at 0:17
    
With regards to master/slave architectures or sparse-connection clients + main server architectures, could it be feasible to use a global_id (SERIAL) on the master and a global_id (BIGINT) + local_id (SERIAL) on the slaves. Slaves do their local work using local_id and commit when they can towards the master, the master receives the data and grants it a global_id which it returns to the slave, the slave updates global_id field (for reference use in talking to the server or to other slaves). –  Mihai Stancu May 20 '12 at 21:55

One more advice - never use GUIDs as part of clustered index. GUIDs are not sequential, thus if they are part of clustered index, every time you insert new record, database would need to rearrange all its memory pages to find the right place for insertion, in case with int(bigint) auto-increment, it would be just last page.

Now if we look to some db realizations: 1.) MySQL - primary keys are clustered, with no option to change behavior - the recomendation is not to use GUIDs at all here 2.) Postgres, MS-SQL - you can make GUID as primary key unclustered, and use another field as clustered index, for example autoincrement int.

share|improve this answer
    
What you propose for Postgres can be done in MySQL as well, with slightly different stucture - auto_increment PK (clustered key), GUID with unique index (unclustered). –  ypercube Apr 29 '12 at 23:34
    
This isn't always true. Depending on the disk system throughput, synchronizing access to that last page might be your bottleneck. blog.kejser.org/2011/10/05/… –  mwilson Jan 7 '13 at 6:14

It depends.

Seriously, with all you've given so far, this is about as far as you can go.

Why would it be helpful to use UUIDs? Why won't you use INTs? Why can't you just index on UUIDs later? Do you understand what it means to have a sorted list with the key of a UUID and insert a random (non-sequential) UUID after a few million rows?

What platform will this run on? How many disks? How many users? How many records?

share|improve this answer
1  
As I wrote in my comment, if I use UUID the clients can add rows to the database without a connection to the server, and later synchronize with the server. I can not do that if I use INTs for primary key, because multiple clients may use the same primary key for different items then. Well, it's useless to sort the list on a UUID column, it would be more useful to sort it on a timestamp column. No, I don't know what it means to insert a random non-sequential UUID after a few million rows, that's why I ask this question. –  Jonas Jan 6 '11 at 10:10
    
The application will be written in Java and the clients my use Windows, Mac or Linux. The clients will use common desktop computers that usually have one disk. The number of users and records depends of how many customers I get, but it will be about 5000 per client and customer. –  Jonas Jan 6 '11 at 10:13
1  
The offline comment changed everything. See what more details does? –  jcolebrand Jan 6 '11 at 12:35

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.