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.
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,
I default to
BIGINTinstead of the smaller
INTdata 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
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,
Developer Experience Matters
The most important reason I prefer
BIGINT is because I regularly need to query
records by their
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.
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.
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!
Published January 31, 2024
Last Updated January 31, 2024