Creating the most unique column

Hi Roachers,

I am trying to create a very unique column. I’ve been using:

UUID PRIMARY KEY NOT NULL DEFAULT gen_random_uuid()

as my unique column. I am not really sure how unique it can be. I’ve been checking on the Postgres side as well where i encountered:

BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY

which got me thinking if there is something close to this in Cockroach, or what could be the ideal way of achieving a unique column?

Regards,
Moses King’ori

Hey @kingmoses,

UUID’s are unique enough, while it is true that a chance of generating a duplicate UUID exists it is a small chance that borders on the impossible for the practical purposes of almost every application.

Another option would be unique_rowid() which returns a value that is a combination of the insert timestamp and the ID of the node executing the statement, which guarantees this combination is globally unique. However, there can be gaps and the order is not completely guaranteed.

Regarding BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY, we currently do not have a similar feature. Also after doing some quick reading it appears that it uses the Sequence object and here are some considerations to take into account:

Sequences are based on bigint arithmetic, so the range cannot exceed the range of an eight-byte integer (-9223372036854775808 to 9223372036854775807).

Because nextval and setval calls are never rolled back, sequence objects cannot be used if “gapless” assignment of sequence numbers is needed. It is possible to build gapless assignment by using exclusive locking of a table containing a counter; but this solution is much more expensive than sequence objects, especially if many transactions need sequence numbers concurrently.

Let me know if you have any other questions.

Thanks!