How To Store UUIDs in PostgreSQL

Author: Szymon Lipiński
Published at: 2011-10-11

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:

    CREATE TABLE u (
      uuid uuid PRIMARY KEY,
      payload character(300)
    ); 

    CREATE TABLE t (
      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:

    INSERT INTO u
      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 vacuum everything:

    vacuum analyze u;
    vacuum analyze t;

What about sie of the tables?

    SELECT pg_size_pretty(pg_total_relation_size('u'));

    pg_size_pretty

    82 MB
    (1 row)

    SELECT pg_size_pretty(pg_total_relation_size('t'));

    pg_size_pretty

    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 in formats, and only one output format. So there is no need of hand made checks, conversions, and no errors with converting the database UUID value in application to some UUID object. You have really smaller memory needs 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

The comments are disabled. If you want to write something to me, you can use e.g. Twitter.