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

by

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.

Leave a Comment