75

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.

Improve this question
5
  • 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
    Commented Jan 6, 2011 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
    Commented Jan 6, 2011 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 ♦
    Commented Jan 6, 2011 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
    Commented Mar 13, 2011 at 4:21
  • I noticed that Derby doc does not list UUID as a data type. You might want to consider an alternative such as the H2 Database Engine (a pure Java database like Derby) which does list a UUID data type. Of course Postgres does have excellent support for efficiently storing, indexing, and generating UUID values.
    – Basil Bourque
    Commented Aug 18, 2015 at 23:40

3 Answers 3

Reset to default
33

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.

Improve this answer
6
  • 3
    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 ♦
    Commented Jan 6, 2011 at 12:34
  • Muahahahaah! ::twirls mustache evilly::
    – Brian Ballsun-Stanton
    Commented Jan 6, 2011 at 12:47
  • 1
    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
    Commented Jan 6, 2011 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 ♦
    Commented Jan 7, 2011 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
    Commented May 20, 2012 at 21:55
23

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.

Improve this answer
5
  • 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ᵀᴹ
    Commented Apr 29, 2012 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
    Commented Jan 7, 2013 at 6:14
  • 3
    "Unlike Microsoft SQL Server, clustering on an index in PostgreSQL does not maintain that order. You have to reapply the CLUSTER process to maintain the order." How does CLUSTER ON improve index performance
    – bartolo-otrit
    Commented Jun 16, 2015 at 10:06
  • A more condensed version of the information @bartolo-otrit linked to: stackoverflow.com/a/4796685/1394393. This answer really doesn't seem relevant to me, as this question is about PG and it seems to assume similarities to SQL Server and MySQL that don't exist.
    – jpmc26
    Commented Oct 29, 2015 at 18:11
  • database would need to rearrange all its memory pages to find the right place for insertion => I don't think that is the case with Postgres, as clustering is optional and new rows are stored unordered.
    – Flavien
    Commented Mar 17, 2016 at 12:33
2

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?

Improve this answer
3
  • 9
    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
    Commented Jan 6, 2011 at 10:10
  • 1
    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
    Commented Jan 6, 2011 at 10:13
  • 3
    The offline comment changed everything. See what more details does?
    – jcolebrand ♦
    Commented Jan 6, 2011 at 12:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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