Performance difference between UUID, CHAR, and VARCHAR in PostgreSql table?

Question

I'm storing UUID v4 values in a PostgreSQL v9.4 table, under column "id".

When I create the table, is there any difference in following write or read performance whether I define the "id" column as VARCHAR(36), CHAR(36), or UUID data type?

Thanks!

Solution 1

Use uuid. PostgreSQL has the native type for a reason.

It stores the uuid internally as a 128-bit binary field. Your other proposed options store it as hexadecimal, which is very inefficient in comparison.

Not only that, but:

• uuid does a simple bytewise sort for ordering. text, char and varchar consider collations and locales, which is nonsensical for a uuid.

• There is only one canonical respresentation of a uuid. The same is not true for text etc; you have to consider upper vs lower case hex, presence or absence of {...-...}s etc.

There's just no question. Use uuid.

The only other type that makes any sense is bytea, which at least can be used to store the 16 bytes of the uuid directly. This is what I'd do if I was using systems that couldn't cope with data types outside the basic set, like a really dumb ORM of some kind.

Solution 2

UUID would be the fastest because its 128 bits -> 16 bytes and comparisons are done numerically.

Char(36) and varchar(36) seems to be the same and slow: http://www.depesz.com/2010/03/02/charx-vs-varcharx-vs-varchar-vs-text/.

The server should check EOF to determine the job of reading the value has finished or not for each character.

Also text comparison is slower than numerical comparison. And because UUID consists of 16 bytes, comparing UUID is much faster than comparing two texts of 36 characters.

Use native UUID for performance.

Solution 3

The index size is maybe the most notable difference: almost 86% more for VARCHAR.

From a performance perspective I didn't notice significant differences in PostgreSQL 9.5.