Best practices for migrating auto-incrementing keys to Spanner

The requiredbit_reversed_positiveoption indicates that the numbers generated by the sequence will be greater than zero, but not ordered (see theintroductory postfor more information on bit-reversed sequences). Generated values are of type INT64.

As you migrate existing rows from your source database to Spanner, the rows’ keys remain unchanged. For new inserts that don’t specify a primary key, Spanner automatically calls theGET_NEXT_SEQUENCE_VALUE()function to retrieve a new number. Since these values distribute uniformly across the range[1, 263],there could be collisions with the existing keys. If this occurred your insert would fail with a “key already exists” error. To prevent this, you can configure the sequence toskipthe range of values covered by the existing keys. 

For example, assuming that the tablesingerswas migrated from PostgreSQL, where its key,singer_id, was inSERIALtype:

The column values are monotonically increasing. After migration, we retrieve the maximum value of thesinger_id:

Assuming the returned value is 20,000, you configure the sequence in Spanner to skip the range[1, 21000]. The extra 1,000 serves as a buffer to accommodate writes to the source database after the initial bulk migration. These values would typically be replicated later and we want to ensure they also will not conflict.

Now new keys generated in Spanner are guaranteed to not conflict with the range of keys generated in the source PostgreSQL database.

Multi-database usage

You can take this skipped range concept one step further to support scenarios where either Spanner or the upstream database generates primary keys, for example to enable replication in either direction for disaster recovery during a migration cutover. 

To support this, you can configure each database to have a non-overlapping key value range. When you define a range for the other database, you can tell the Spanner sequence to skip over that range with the skipped range syntax. 

For example, after the bulk migration of our music tracking application, we’ll replicate data from PostgreSQL to Spanner to reduce the amount of time it takes to cut over. Once we’ve updated and tested the application on Spanner, we’ll cut over from PostgreSQL to Spanner, making it the system of record for updates and new primary keys. When we do, we‘ll reverse the flow of data between databases and replicate data back to the PostgreSQL instance just in case we need to revert if there’s a problem. 

In this scenario, sinceSERIALkeys in PostgreSQL are 32-bit signed integers, while our keys in Spanner are larger 64-bit numbers, we will do the following steps:

1. In PostgreSQL, alter the key column to be a 64-bit column, orbigint,

2. Since the sequencesingers_singer_id_seqused bysinger_idis still of typeint, its maximum value is already 231-1. To be safe, we can optionally set a CHECK constraint to the table in the source PostgreSQL database to ensure thatsinger_idvalues are always smaller or equal to 231-1:

3. In Spanner, we’ll alter the sequence to skip the range [1, 231-1].

4. Deploy and test your usage, including from PostgreSQL to Spanner and vice versa.

Using this technique, PostgreSQL will always generate keys in the 32-bit integer space, while Spanner’s keys are restricted to the 64-bit integer space, larger than all of the 32-bit numbers and wide enough for future growth. This ensures that both systems can independently generate keys that are guaranteed not to conflict.

Migrating UUIDs

UUID primary keys are generally easier to migrate than sequential integer keys.UUIDs, v4 in particular, are effectively unique regardless of where they are generated. (The math behind this is an interesting application of thebirthday problemin statistics.) As a result, UUID keys generated elsewhere will integrate easily with new UUID keys generated in Spanner and vice versa.

The high-level strategy for migrating UUID keys is as follows:

Define your UUID keys in Spanner using string columns with a default expression,GENERATE_UUID()orspanner.generate_uuid()in the PostgreSQL dialect.

Export data from the source system, serializing the UUID keys as strings.

Import the keys into Spanner as-is.

Optionally enable foreign keys.

In Spanner, you define a UUID primary key column as aSTRINGorTEXTtype, and assignGENERATE_UUID()as its default value. During migration, you bring all values of existing rows from the source database to Spanner, including key values. (See thismigration guidefor more details.) After migration, as new rows are inserted, Spanner callsGENERATE_UUID()to generate new UUID values for them. For example, the primary keyFanClubIdwill get a UUIDv4 value when we insert a new row into the table,FanClubs:

Migrating your own primary keys

Bit-reversed sequences and UUIDs provide unique values that won’t hotspot at scale when used as a primary key in Spanner. But they don’t provide any guarantees on the ordering of their values… by design! However, some applications rely on the order of the keys to determine recency or to sequence newly created data. Databases manually sharded for scale typically rely on a global counter, coordinated outside of any independent database instances.

To use ordered keys generated externally in Spanner you create acomposite keythat 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, but won’t hotspot at scale. 

In this example, we are migrating a MySQL table with anAUTO_INCREMENTprimary key,students, to Spanner. The downstream application generates student IDs, and the IDs are shared to end users (students, faculty, etc.)

In Spanner, we add a generated column containing a hash of theStudentIdcolumn: