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?


4 Answers 4


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.

  • One thing to note is that the PostgreSQL UUID type is not supported natively in arrays or has this been fixed ? postgresql.org/message-id/… Commented Nov 5, 2019 at 15:09
  • 2
    @ChristopheRoussy That's from 2013. It was a minor oversight. SELECT ARRAY['ef1e0638-072e-4caa-88b3-97bfa5b2e8c3']::uuid[] Commented Nov 8, 2019 at 9:53

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!


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.

  • True, but is it that just the byte size that matters? Doesn't the type affect the indexing algorithm? Commented Jun 28, 2014 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. Commented Jun 29, 2014 at 5:26

Very late, I know, Vlad. UUIDv7 is excellent for database inserts as it supports decentralized generation as well as efficient clustered index insertion.

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.