Encoding a Postgres UUID in Amazon Redshift

1
votes

We have a couple of entities which are being persisted into Amazon Redshift for reporting purposes, and these entities have a relationship between them. The source tables in Postgres are related via a foreign key with a UUID datatype, which is not supported in Redshift.

One option is to encode the UUID as a 128 bit signed integer. The Redshift documentation refers to the ability to create NUMBER(38,0), and to the ability to create 128 bit numbers.

But 2^128 = 340,282,366,920,938,463,463,374,607,431,768,211,456 which is 39 digits. (thanks Wikipedia). So despite what the docs say, you cannot store the full 128 bits / 39 digits of precision in Redshift. How do you actually make a full 128 bit number column in Redshift?

In short, the real question behind this is - what is Redshift best practice for storing & joining tables which have UUID primary keys?

1
postgresqlamazon-redshiftuuid
you'll probably have bite the bullet and use varchar(36) - a_horse_with_no_name
Surely joining tables via varchar(38) is a bad idea? - Pete
It's not as bad as you think. I would even argue that there are many more other factors that influence the performance that will overshadow the string comparison. Make sure the column is defined with local "C" (not sure if Redshift supports that though) - a_horse_with_no_name
Will give it a whirl I guess, and maybe try chucking a ton of data at it and see if it causes a problem. Apparently there's no locale support. - Pete

1 Answers

1
votes

Redshift joins will perform well even with a VARCHAR key, so that's where I would start.

The main factor for join performance will be co-locating the rows onto the same compute node. To achieve this you should declare the UUID column as the distribution key on both tables.

Alternatively, if one of the tables is fairly small (<= ~1 million rows), then you can declare that table as DISTSTYLE ALL and choose some other dist key for the larger table.

If you have co-located the join and wish to optimize further then you could try splitting the UUID value into 2 BIGINT columns, one for the top 64 bits and another for the bottom 64. Even half of the UUID is likely to be unique and then you can use the second column as a "tie breaker".

c.f. "Amazon Redshift Engineering’s Advanced Table Design Playbook: Preamble, Prerequisites, and Prioritization"