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

sql postgresql database-performance sqldatatypes
Pensierinmusica picture Pensierinmusica · Aug 24, 2015 · Viewed 11.6k times · Source

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!

Answer

Craig Ringer picture Craig Ringer · Aug 25, 2015

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.

Related questions

Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More
How do I (or can I) SELECT DISTINCT on multiple columns?

I need to retrieve all rows from a table where 2 columns combined are all different. So I want all the sales that do not have any other sales that happened on the same day for the same price. The sales …

Read More
How to select the nth row in a SQL database table?

I'm interested in learning some (ideally) database agnostic ways of selecting the nth row from a database table. It would also be interesting to see how this can be achieved using the native functionality of the following databases: SQL Server …

Read More