UUID in Postgres: PgSQLPhriday #015

This month's PgSQLPhriday #015 topic is about UUIDs. Lætitia Avrot is this month's host, see her post for the full challenge text. The topic is described as a debate between the Database People and Developers. I'm not sure there's such a clean divide on people's opinions on the topic, as I know plenty of Database People that have settled on using UUIDs as their default. Similarly, I know even more developer types that have followed the arguably more conventional choice of using an auto-incrementing ID.

TLDR;

I avoid UUIDs. The only places I have used UUIDs in production are the places where a 3rd party system is involved.

What's the debate?

The basic debate is around the decision of what data type to use for your table's PRIMARY KEY column. No, it's not that boiled down in practice, but that summarizes it well enough for me. I create tables with BIGINT IDENTITY columns for the PRIMARY KEY. The following code shows what that looks like in Postgres.

CREATE TABLE my_table (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    <other columns>
);

I default to BIGINT instead of the smaller INT data type. See my post Use BIGINT in Postgres for more.

On the other hand, UUIDs can be used for the ID column. UUIDs can be generated in Postgres using the gen_random_uuid() function. Further, Postgres has an extension (surprised?) called uuid-ossp to enable more varieties of the the UUID data type. The following syntax shows the basics of using this type.

CREATE TABLE my_table_uuid (
    id uuid PRIMARY KEY,
    <other columns>
);

Developer Experience Matters

The most important reason I prefer BIGINT is because I regularly need to query records by their PRIMARY KEY. When using BIGINT ids you have simple values (1, 2, 983531) that you can easily work with. I can do the following things with these types of values.

• Say it a few times to remember for a short while.
• Write it on a scratch piece of paper.
• Type it.
• Double click the number and select the whole thing for copy/paste.

Using the UUID data type results in values such as d556c9ac-dc30-4024-b0df-fd0e24e2596b. I can't memorize this for any period of time. My handwriting is terrible, and writing 36 random characters isn't a good plan. I can probably (maybe, hopefully?) type it very carefully.

Even the basic copy / paste workflow is harder with UUIDs. I write these posts using VS Code and here, in a developer's IDE, if I double click a part of the UUID it only selects that part between dashes (-). You can see that behavior your your browser too. Double click on the UUID and see if it selects the whole thing. It doesn't select the entire UUID value, just part of it. The same double click on an integer selects the entire integer, ready for immediate copy and paste.

This difference in developer experience is the enough reason for me to avoid UUIDs. Working with UUIDs breaks nearly every one of my quick tricks I can use with integer IDs.

Summary

I haven't considered UUIDs for any production system. BIGINT still works for me! The places I've had to use UUID has added friction into common workflows. It feels like complexity for the sake of complexity in many cases.

That said, I am hoping to hear from the pro-UUID crowd in this round of PgSQL Phriday! I know there are valid use cases for UUID, I just don't have any in my realm.

Need help with your PostgreSQL servers or databases? Contact us to start the conversation!