How To Store UUIDs in PostgreSQL

by Szymon LipiƄski

This story is a just another example of what I call

Using varchar(255) for everything won’t make you a sorcerer.

Every database has many different types. They are there on purpose, not by accident. I’ve seen that too many times: a programmer (most companies don’t have DBAs for that) creates a database strsucture using only ONE type. Sometimes they use more… but hardly ever they use all those nice features they database engine provides.

This is a common pattern:

Pay thousands of dollars for the database licence – use it like a CSV file.

The UUID Type

PostgreSQL has a very nice type UUID. This can used for storing UUIDs. Uniquness of UUIDs is another nice topic to write about, but not this time.

How would you store the UUID in a database?

In PostgreSQL solutions are maily two:

How do you think: which one is better?

Of course the UUID type isn’t there just for fun. How do you think, would anybody do that just for fun and that all those people just accepted the patch for this?

A Simple Test

Let’s create two similar tables:

      uuid uuid PRIMARY KEY,
      payload character(300)

      uuid text PRIMARY KEY,
      payload character(300)

Payload is here only to have the rows much bigger.

Fill the first table with randomly generated UUIDs:

      SELECT uuid_generate_v4()
      FROM generate_series(1,1000*1000);

and fill the same uuids to another table:

    INSERT INTO t(uuid) SELECT uuid FROM u;

Let’s update the statistics for the tables:

    vacuum analyze u;
    vacuum analyze t;

What about size of the tables?

    SELECT pg_size_pretty(pg_total_relation_size('u'));


    82 MB
    (1 row)

    SELECT pg_size_pretty(pg_total_relation_size('t'));


    138 MB
    (1 row)

Compare The Speed Difference

Let’s check the speed of queries. Test procedure is simple:

It turned out that the time difference is really insignificant. Those two tables are almost equally fast.

So, What is The Difference?

UUID type gives you cure to all those probles for free. You can have many different input formats and only one output format. So there is no need of hand made checks, conversions. You will get no errors when converting the database UUID value in the application to a UUID object. You have really smaller memory requirements for storing the value (yes, you will notice the difference when you have a lot of UUIDs and a database which doesn’t fit in the available RAM).

And now a couple of nice links:

PostgreSQL UUID type

Functions For Generating UUIDs