Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Storing a UUID in Cloud Spanner

I would like to use a UUID as a primary key in Cloud Spanner. What is the best way to read and write UUIDs? Is there a UUID type, or client library support?

like image 202
willwilson Avatar asked Feb 16 '17 22:02

willwilson


2 Answers

The simplest solution is just to store it as a STRING in the standard RFC 4122 format. E.g.:

"d1a0ce61-b9dd-4169-96a8-d0d7789b61d9"

This will take 37 bytes to store (36 bytes plus a length byte). If you really want to save every possible byte, you could store your UUID as two INT64's. However, you would need to write your own libraries for serializing/deserializing the values, and they wouldn't appear very pretty in your SQL queries. In most cases, the extra ~21 bytes of savings per row is probably not worth it.

Note that some UUID generation algorithms generate the UUID sequentially based on a timestamp. If the UUID values generated by a machine are monotonically increasing, then this can lead to hot-spotting in Cloud Spanner (this is analogous to the anti-pattern of using timestamps as the beginning of a primary key), so it is best to avoid these variants (e.g. UUID version 1 is not recommended).

This Stackoverflow answer provides more details about the various UUID versions. (TL;DR: use Version 4 with Cloud Spanner since a psuedo-ranndom number is used in the generation)

like image 97
willwilson Avatar answered Oct 17 '22 22:10

willwilson


As per Cloud Spanner documentation:

There are several ways to store the UUID as the primary key:

  • In a STRING(36) column.
  • In a pair of INT64 columns.
  • In a BYTES(16) column.
like image 1
quangh Avatar answered Oct 17 '22 22:10

quangh