Best practice for XID primary keys

(Brian) #1

@charl brought up a similar topic a year ago around ULID as a primary key. (https://forum.cockroachlabs.com/t/best-practice-for-uuid-primary-keys/1520)

In there, @david talked about making ulid a UUID instead of type string, to store into 16 bytes to save space.

xid's are similar to ULID's but xid's are stored in 12 bytes, not 16. However, they are not backward compatible with UUID's, so they’re not a drop in replacement.

Would you recommend a similar path for using xid's (github.com/rs/xid)? I was planning on using a bytea to store xid's as my PRIMARY KEY, and foreign key’s. Do you see any downsides to this approach, or would it be safer to use as a STRING albeit with more data storage bloat.

I’ve read the reference in relation to Postgres (github.com/rs/xid/issues/14), but I’m curious the best practice with how CockroachDB is structured.

I know you’ve looked into adding ULIDs, (github.com/cockroachdb/cockroach/issues/24733) but my vote is for xid's; solves the same problem in less space and doens’t require a lock when used concurrently (github.com/rs/xid/issues/42).

(Ron Arévalo) #2

Hey @brian,

When you say:

I assume you’re referring to @david’s answer about changing the type from STRING to UUID in order to stored at no more than 16 bytes. Is that correct?

As far as I know, you can use xid, however it would need to be as a STRING and it would be stored at 20 or 26 bytes instead of 12 or 16, we only store UUID’s at 16 bytes at the moment.

As for considering using xid as a built-in, I can certainly bring this up to our product team.

Thanks,

Ron

(Brian) #3

@ronarev Thank you for the quick response!

I was hoping to store xid's in a 12-byte BYTEA as the PRIMARY KEY, opposed to using the 20-byte STRING.

Seems to be quite the workaround though, might not be worth the hassle it introduces into the code to make it work with CockroachDB.