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.

For storing a 128 bits UUID there are multiple storage options:

  1. a byte[16] column
  2. two bigint/long(64 bits) columns
  3. a CHAR(36) column - 32 hex digits + 4 dashes.
  4. a UUID database specific column, if db supports it

From an indexing point of view which of those are the most efficient? If the db doesn't support a dedicated uuid type which of 1, 2, 3 are the best candidates?

share|improve this question
1  
This is a bit too "it depends" - lots of implementation specifics. –  Craig Ringer Jun 28 at 11:25
1  
I would never choose 3: never store something in 36 bytes when it can be done in 16. I use raw(16) in Oracle and uuid in PostgreSQL. –  Colin 't Hart Jun 28 at 11:31
1  
the simpler the better. –  akuzminsky Jun 28 at 14:13

3 Answers 3

up vote 3 down vote accepted

A dedicated uuid type is your best bet for PostgreSQL. Hard to say with other DBs - it's not impossible for someone to impliment a uuid type that's stored less efficiently than a simple byte type.

Again in PostgreSQL, bytea would be a reasonable way to store UUIDs if you didn't have the uuid type. For other DBs it depends on how they store binary data.

Where possible I'd strongly avoid using hex-with-dashes. It's way less efficient to compare, sort, and store.

So really, "not (2) or (3)". Ever. Use (4) where supported, (1) otherwise.

share|improve this answer

In preference order: 4,1,2,3 Do not use UUIDs as the clustering key if using SQL server as, not only will it fragment badly, the clustering key is used in all non clustered indexes and you'd add those bytes to each index row. Fragmentation can be mitigated by using NEWSEQUENTIALID but usually prefer a bingint identity for your Clustering Key over a GUID to prevent bloat in other indexes.

The difference between choosing 1 over 2 will depend how more efficient the database handles two columns of basic types over a single column fixed array. It should be easy enough to test with dummy data. Look at the speed of your queries as well as the size of indexes and data. Small+fast is the best!

share|improve this answer

One would have to suppose that any natively-supported data type would be better optimised in the product than anything that could be put together as a client of that product. After that, whatever has the smallest byte count so you get the maximum rows per page.

share|improve this answer
    
True, but is it that just the byte size that matters? Doesn't the type affect the indexing algorithm? –  Vlad Mihalcea Jun 28 at 14:37
    
@Vlad I use SQL Server. AFAIK all data types are handled the same when constructing a B-tree (or a hash index for 2104 in-memory). There are good reasons to keep this as narrow as possible. –  Michael Green Jun 29 at 5:26

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.