PostgreSQL UUID type performance
Solution 1
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
Solution 2
A UUID
is a 16 bytes value. The same as text
is a 32 bytes value. The storage sizes are:
select
pg_column_size('a0eebc999c0b4ef8bb6d6bb9bd380a11'::text) as text_size,
pg_column_size('a0eebc999c0b4ef8bb6d6bb9bd380a11'::uuid) as uuid_size;
text_size | uuid_size
-----------+-----------
36 | 16
Smaller tables lead to faster operations.
adamek
Updated on June 05, 2020Comments
-
adamek about 4 years
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 thatchar(n)
would not have any advantage overtext
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?
- Column type:
-
Rahul Jha almost 4 yearsBut how it react when it comes to uuid comparison. Is there any benefit using uuid over int
-
Allain Lalonde almost 3 yearsMAX(uuid_column) isn't supported, so that's a real difference.
-
Navin over 2 years@AllainLalonde Why would you want that?
-
scravy over 2 yearsA discussion of max(uuid) can be found here: dba.stackexchange.com/questions/275251/… – there are some use cases and it can easily be added as an aggregate function