This page describes how Spanner works with primary keys and offers primary key migration strategies for the following use cases:
- Migrating UUID key databases
- Migrating single-instance databases with sequential keys
- Migrating sequential key databases with live cutover support
- Migrating sequential key databases with application logic dependencies
A typical approach to primary keys is to use surrogate keys such as auto-incrementing numbers. Such primary keys provide flexibility to optimize your keys now and in the future, even if your business logic changes. In a single-instance database at low volume, sequential keys perform well. However, in a distributed system, sequential keys don't scale well.
Sequential primary keys in Spanner
In Spanner, every table has a primary key consisting of one or more columns of the table. Your table's primary key uniquely identifies each row in a table. Spanner uses the primary key to distribute groups of rows, called splits, across compute nodes in a Spanner instance. This is called range sharding and allows Spanner to parallelize queries and scale.
When you have rows with primary keys whose values are in close proximity, such as monotonic auto-incrementing keys, they tend to land in the same split. This can create a hotspot, where the split can use all available compute and memory resources. A hotspot might result in increased latency, potentially leading to timeouts and aborted transactions.
To take advantage of Spanner's scalability and to avoid hotspots, Spanner offers built-in solutions as alternatives to auto-incrementing primary keys.
Primary key recommendations
The default recommendation for primary keys in Spanner is to use Universally Unique Identifier Version 4 (UUIDv4) values. UUIDs are 128-bit identifiers that use 122 bits of random data. UUIDv4 values have a huge range of values and are effectively unique regardless of where they are generated. This makes them good candidates for non-hotspotting primary keys in Spanner.
You may want to use integer primary keys as they take less space and reduce the complexity of application changes that you'll have to do. You can use a positive bit-reversed sequence to generate unique primary key values that uniformly distribute across the positive 64-bit integer space.
For more information on choosing a primary key to prevent hotspots, see Schema design best practices.
Migration strategies
Depending on your application use case and needs, you can deploy a primary key migration strategy. Each of these migration strategies:
- Ensure the fidelity and correctness of the migrated primary keys.
- Minimize downstream application changes, such as changing types or primary key values.
- Implement Spanner best practices for performance and scalability.
- Spanner only changes the method for how new data is generated, and doesn't affect existing data.
Migrating UUID key databases
Consider that you are migrating from a database that uses UUID primary keys into Spanner. Configure existing UUID keys as strings in your source database and import them into Spanner as-is. UUID values, v4 in particular, are effectively unique regardless of where they are generated.
You can use the
GENERATE_UUID()
function (GoogleSQL,
PostgreSQL)
on Spanner to migrate UUID key databases.
For instructions on migrating UUID key databases, see Migrate UUID key columns.
Migrating single-instance databases that have sequential keys
Consider that you are migrating from a single-instance database that uses sequential monotonic keys,
such as
AUTO_INCREMENT
in MySQL,
SERIAL
in PostgreSQL, or the standard
IDENTITY
type in SQL Server or Oracle.
Configure the Spanner SEQUENCE
object to skip the values in the range of
existing keys and generate new bit-reversed keys. Bit-reversed keys generated
by the Spanner SEQUENCE
object are always greater than zero,
and are uniformly distributed across the positive 64-bit integer space.
For instructions on migrating databases that have sequential keys, see Migrate auto-generated sequential primary keys.
Migrating sequential key databases that have live cutover support
Consider that you are migrating from a single instance database that uses sequential monotonic keys to Spanner and support replication scenarios,for example, you want to do a live cutover between the database systems.
Configure the Spanner SEQUENCE
object to skip the entire value range
of existing keys in your source database and generate new bit reversed keys
on Spanner. Bit reversed keys generated by the Spanner
SEQUENCE
object are always greater than zero, but not ordered.
For instructions on migrating databases that have live cutover support, see Use Spanner and your source database.
Migrating sequential key databases that have application logic dependencies
Consider that you are migrating from a database that uses sequential monotonic keys and your application logic relies on the primary key order to determine recency or to sequence newly created data.
Create a composite key that combines a uniformly distributed value, such as a shard ID or a hash, as the first component and a sequential number as the second component. This preserves the ordered key values, without causing a hotspot at scale.
For instructions on migrating sequential key databases with application logic dependencies, see Migrate your own primary keys.
What's next
- To view detailed migration workflows, see Migrate primary keys.