Primary key migration overview

This page describes how Spanner works with primary keys and offers primary key migration strategies for the following use cases:

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