What's the most efficient UUID column type

Question

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

a byte[16] column
two bigint/long(64 bits) columns
a CHAR(36) column - 32 hex digits + 4 dashes.
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?

This is a bit too "it depends" - lots of implementation specifics. — Craig Ringer, Jun 28 '14 at 11:25
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 '14 at 11:31
uuid >> bytea >> text with CHECK constraint > varchar(36) >> char(36). See: https://dba.stackexchange.com/a/89433/3684 and https://dba.stackexchange.com/a/115316/3684. — Erwin Brandstetter, Dec 20 '19 at 19:02

Craig Ringer · Accepted Answer · 2014-06-30T07:24:51.590

22

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.

edited Jun 30 '14 at 07:24

answered Jun 28 '14 at 11:25

Craig Ringer

56,343
5
158
190

One thing to note is that the PostgreSQL UUID type is not supported natively in arrays or has this been fixed ? https://www.postgresql.org/message-id/CE7DAF78.583FD%25martin.petras%40coresystems.ch – Christophe Roussy Nov 05 '19 at 15:09
2

@ChristopheRoussy That's from 2013. It was a minor oversight. SELECT ARRAY['ef1e0638-072e-4caa-88b3-97bfa5b2e8c3']::uuid[] – Craig Ringer Nov 08 '19 at 09:53

GilesDMiddleton · Answer 2 · 2014-06-28T15:37:05.307

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!

score 1 · Answer 3 · answered Jun 28 '14 at 13:28

1

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.

answered Jun 28 '14 at 13:28

Michael Green

24,839
13
51
96

True, but is it that just the byte size that matters? Doesn't the type affect the indexing algorithm? – Vlad Mihalcea Jun 28 '14 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 '14 at 05:26

score 0 · Answer 4 · answered Feb 28 '24 at 02:28

0

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

answered Feb 28 '24 at 02:28

Andrew G

61
3

What's the most efficient UUID column type

4 Answers4