This page discusses strategies to use to generate primary key values in your table using default value expressions. These strategies have the following benefits:
- Prevent hotspotting
- Simplify migrations from other databases
- Encapsulate the key logic in the database so that you don't need to worry about managing it in your application
You can use the following strategies in a column that has
- A UUID function that generates UUID Version 4 values.
- A schema object,
SEQUENCE, that has a
SEQUENCEis available for both GoogleSQL and PostgreSQL.
Methods to automatically generate primary keys
Universally Unique Identifier (UUID)
Spanner can automatically generate a UUID Version 4 string to use as a primary key. UUIDs work well for new applications and tables with many rows. They are roughly uniformly distributed across the key space which prevents hotspotting at scale. UUID generation can create a large number of values (2122) and each value is effectively unique. For example, you would need 2.71×1018 values for a 50% probability of collision, or 1 billion per second for 86 years. This ensures unique values when you use it in large tables. UUIDs are unique whether you generate them in the database or the client. We recommend that you use UUIDs when possible. You can safely mix client- and Spanner-generated UUIDs in the same table if the client-generated UUIDs are serialized as lower case, in accordance with RFC 4122.
For a column that needs default values, you can use the
function to generate them. The following example shows how to create
a table where the
FanId key column has
GENERATE_UUID in the value
column as its default value. The example uses 36 characters for the GoogleSQL
STRING and PostgreSQL
varchar attributes because UUIDs have 36 characters.
When you use the
INSERT with THEN RETURN statement to insert into the
GENERATE_UUID generates and returns a UUID value for
CREATE TABLE Fans ( FanId STRING(36) DEFAULT (GENERATE_UUID()), Name STRING(MAX), ) PRIMARY KEY (FanId);
CREATE TABLE Fans ( FanId varchar(36) DEFAULT spanner.generate_uuid(), Name text, PRIMARY KEY (FanId) );
INSERT INTO Fans (Name) VALUES ('Melissa Garcia') THEN RETURN FanId;
INSERT INTO fans (name) VALUES ('Melissa Garcia') RETURNING (fanid);
This statement returns a result similar to the following:
A bit-reversed sequence is a schema object that produces a sequence of integers and bit-reverses them. This object uses bit reversal on a private, internal Spanner counter to ensure uniqueness. The resulting bit-reversed values helps avoid hotspotting at scale when used in a primary key.
Each sequence maintains a set of internal counters and uses them to generate a value. The sequence counter provides the input to the bit-reversing algorithm.
When you define a column with a
DEFAULT expression that uses the GoogleSQL
GET-NEXT-SEQUENCE-VALUE or the
nextval function as its default value, Spanner
automatically calls the function and puts the bit-reversed output values into
the column. Bit-reversed sequences are especially useful for primary keys,
because bit-reversed values are evenly distributed across the key
space so that they don't cause hotspotting.
The following example shows how to create a bit-reversed sequence and a table where its key column uses the sequence as the default value:
CREATE SEQUENCE SingerIdSequence OPTIONS ( sequence_kind="bit_reversed_positive" ); CREATE TABLE Singers ( SingerId INT64 DEFAULT (GET_NEXT_SEQUENCE_VALUE(SEQUENCE SingerIdSequence)), Name STRING(MAX), Rank INT64, ) PRIMARY KEY (SingerId);
CREATE SEQUENCE SingerIdSequence bit_reversed_positive; CREATE TABLE Singers ( SingerId bigint DEFAULT nextval('SingerIdSequence'), Name text, PRIMARY KEY (SingerId) );
You can then use the following SQL statement to insert and return the primary key value:
INSERT INTO Singers (Name) VALUES ('Melissa Garcia') THEN RETURN SingerId;
INSERT INTO Singers (name) VALUES ('Melissa Garcia') RETURNING (SingerId);
This statement returns a result similar to the following:
Scenarios for using UUIDs and sequences as default values for primary keys
The scenarios for UUIDs and sequences include the following:
- New applications
The following sections describe each scenario.
If your existing application requires
INT64 keys in GoogleSQL, or
keys in PostgreSQL, Spanner offers the bit-reversed positive
sequence schema object (PostgreSQL or GoogleSQL). Otherwise, for new
applications, we recommend that you use
Universally Unique Identifier (UUID). For
more information, see Use a Universally Unique Identifier (UUID).
For migrations of tables to Spanner, you have a few options:
- If you are using UUIDs in your source database, on Spanner,
you can use a key column in the
STRINGtype and the
GENERATE_UUID()function (GoogleSQL or PostgreSQL) as its default value.
- If you are using an integer primary key, and your application only needs the
key to be unique, you can use a key column in
INT64and use a bit-reversed positive sequence for the default value for the primary key. See Migrating bit-reversed key columns.
Spanner doesn't support a way to generate monotonic values.
If you're using a monotonic key, such as the PostgreSQL
SERIALtype, or MySQL
AUTO_INCREMENTattribute, and you need new monotonic keys on Spanner, you can use a composite key. See Swap the order of keys and Hash the unique key and spread the writes across logical shards.
If your application is manually bit-reversing your
INT64key in GoogleSQL or
bigintkey in PostgreSQL, you can use a bit-reversed positive sequence (GoogleSQL or PostgreSQL) and have it generate new key values for you. See Migrating bit-reversed key columns.
- Learn more about using sequences with fine-grained access control.
- Learn about DDL
SEQUENCEstatements for GoogleSQL or PostgreSQL.
- Learn about sequence functions in GoogleSQL or PostgreSQL.
- Learn about sequences in the INFORMATION_SCHEMA in GoogleSQL or PostgreSQL.
- Learn about sequence options in the INFORMATION_SCHEMA for GoogleSQL.