Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

PostgreSQL UUID type performance

I'm not trying to restart the UUID vs serial integer key debate. I know there are valid points to either side. I'm using UUID's as the primary key in several of my tables.

  • Column type: "uuidKey" text NOT NULL
  • Index: CREATE UNIQUE INDEX grand_pkey ON grand USING btree ("uuidKey")
  • Primary Key Constraint: ADD CONSTRAINT grand_pkey PRIMARY KEY ("uuidKey");

Here is my first question; with PostgreSQL 9.4 is there any performance benefit to setting the column type to UUID?

The documentation http://www.postgresql.org/docs/9.4/static/datatype-uuid.html describes UUID's, but is there any benefit aside from type safety for using this type instead of text type? In the character types documentation it indicates that char(n) would not have any advantage over text in PostgreSQL.

Tip: There is no performance difference among these three types, apart from increased storage space when using the blank-padded type, and a few extra CPU cycles to check the length when storing into a length-constrained column. While character(n) has performance advantages in some other database systems, there is no such advantage in PostgreSQL; in fact character(n) is usually the slowest of the three because of its additional storage costs. In most situations text or character varying should be used instead.

I'm not worried about disk space, I'm just wondering if it's worth my time benchmarking UUID vs text column types?

Second question, hash vs b-tree indexes. No sense in sorting UUID keys so would b-tree have any other advantages over hash index?

like image 432
adamek Avatar asked Apr 26 '15 16:04

adamek


People also ask

Are UUIDs slow?

This is your primary key, you don't want it to be slow. At its bit level, a UUID is 128 bits, which means it will fit into 16 bytes, note this is not very human readable, but it will keep storage low, and is only 4 times larger than a 32-bit int, or 2 times larger than a 64-bit int.

Is UUID good for primary key Postgres?

You can use UUID as primary key in your table as it will be unique. However do keep in mind that UUID will occupy a bit more space as compared to SEQUENCE. And also they are not very fast. But yes they are for sure unique and hence you are guaranteed to get a consistent data.

What is UUID data type in PostgreSQL?

The data type uuid stores Universally Unique Identifiers (UUID) as defined by RFC 4122, ISO/IEC 9834-8:2005, and related standards. (Some systems refer to this data type as a globally unique identifier, or GUID, instead.)

Is it good to use UUID as primary key?

Pros. Using UUID for a primary key brings the following advantages: UUID values are unique across tables, databases, and even servers that allow you to merge rows from different databases or distribute databases across servers. UUID values do not expose the information about your data so they are safer to use in a URL.


1 Answers

We had a table with about 30k rows that (for a specific unrelated architectural reason) had UUIDs stored in a text field and indexed. I noticed that the query perf was slower than I'd have expected. I created a new UUID column, copied in the text uuid primary key and compared below. 2.652ms vs 0.029ms. Quite a difference!

 -- With text index     QUERY PLAN     Index Scan using tmptable_pkey on tmptable (cost=0.41..1024.34 rows=1 width=1797) (actual time=0.183..2.632 rows=1 loops=1)       Index Cond: (primarykey = '755ad490-9a34-4c9f-8027-45fa37632b04'::text)     Planning time: 0.121 ms     Execution time: 2.652 ms      -- With a uuid index      QUERY PLAN     Index Scan using idx_tmptable on tmptable (cost=0.29..2.51 rows=1 width=1797) (actual time=0.012..0.013 rows=1 loops=1)       Index Cond: (uuidkey = '755ad490-9a34-4c9f-8027-45fa37632b04'::uuid)     Planning time: 0.109 ms     Execution time: 0.029 ms 
like image 176
Damien Sawyer Avatar answered Sep 18 '22 12:09

Damien Sawyer