Spanner lets you make schema updates with no downtime. You can updatethe schema of an existing database in several ways:

• In the Google Cloud console

  Submit anALTER TABLE command on theSpanner Studio page.

  To access theSpanner Studio page, clickSpanner Studio from theDatabase overview or Table overview page.

• Using thegcloud spanner command-line tool

  Submit anALTER TABLE command by using thegcloud spanner databases ddlupdate command.

• Using theclient libraries

• Using theprojects.instances.databases.updateDdl RESTAPI

• Using theUpdateDatabaseDdl RPC API

Supported schema updates

Spanner supports the following schema updates of an existingdatabase:

• Add or drop a named schema.
• Create a new table. Columns in new tables can beNOT NULL.
• Delete a table, if no other tables are interleaved within it, and it has no secondary indexes.
• Create or delete a table with a foreign key.
• Add or remove a foreign key from an existing table.
• Add a non-key column to any table. New non-key columns cannot beNOT NULL.
  • Drop a non-key column from any table, unless it is used by asecondaryindex, foreign key, stored generated column, orcheck constraint.
• AddNOT NULL to a non-key column, excludingARRAY columns.
• RemoveNOT NULL from a non-key column.
• Change aSTRING column to aBYTES column or aBYTES column to aSTRING column.
• Change aPROTO column to aBYTES column or aBYTES column to aPROTOcolumn.
• Change the proto message type of aPROTO column.
• Add new values to anENUM definition and rename existing values usingALTER PROTO BUNDLE.
• Change messages defined in aPROTO BUNDLE in arbitrary ways, provided thatmodified fields of those messages are not used as keys in any table and thatthe existing data satisfies the new constraints.
• Increase or decrease the length limit for aSTRING orBYTES type(including toMAX), unless it is a primary keycolumn inherited by one or more child tables.
• Increase or decrease the length limit for anARRAY<STRING>,ARRAY<BYTES>, orARRAY<PROTO> column to the maximum allowed.
• Enable or disablecommit timestamps in value and primarykey columns.
• Add or remove a secondary index.
• Add or remove a check constraint from an existing table.
• Add or remove a stored generated column from an existing table.
• Construct a newoptimizer statistics package.
• Create and manageviews.
• Create and managesequences.
• Create database roles and grant privileges.
• Set, change, or drop the default value of a column.
• Change the database options (default_leader orversion_retention_periodfor example).
• Create and managechange streams.
• Create and manage ML models.

Unsupported schema updates

Spanner doesn't support the following schema updates of anexisting database:

• If there is aPROTO field of theENUM type that is referenced by a tableor index key, you can't removeENUM values from the proto enums. (RemovalofENUM values from enums used byENUM<> columns is supported, includingwhen those columns are used as keys.)

Schema update performance

Schema updates in Spanner don't require downtime. When you issuea batch of DDL statements to a Spanner database, you can continuewriting and reading from the database without interruption whileSpanner applies the update as along-runningoperation.

The time it takes to execute a DDL statement depends on whether the updaterequires validation of the existing data or backfill of any data. For example,if you add theNOT NULL annotation to an existing column,Spanner must read all the values in the column to make sure thatthe column does not contain anyNULL values. This step can take a long time ifthere is a lot of data to validate. Another example is if you're adding an indexto a database: Spanner backfills the index using existing data,and that process can take a long time depending on how the index's definitionand the size of the corresponding base table. However, if you add a new columnto a table, there is no existing data to validate, so Spanner canmake the update more quickly.

In summary, schema updates that don't require Spanner to validateexisting data can happen in minutes. Schema updates that require validation cantake longer, depending on the amount of existing data that needs to bevalidated, but data validation happens in the background at a lower prioritythan production traffic. Schema updates that require data validation arediscussed in more detail in the next section.

Schema updates validated against view definitions

When you make a schema update, Spanner validates that the updatewon't invalidate the queries used to define existing views. If validation issuccessful, the schema update succeeds. If validation is not successful, theschema update fails. CheckBest practices when creatingviews for details.

Schema updates that require data validation

You can make schema updates that require validating that the existing data meetsthe new constraints. When a schema update requires data validation,Spanner disallows conflicting schema updates to the affectedschema entities and validates the data in the background. If validation issuccessful, the schema update succeeds. If validation is not successful, theschema update does not succeed. Validation operations are executed aslong-running operations. You cancheck the status of these operations to determine if they succeeded or failed.

For example, suppose you have defined the followingmusic.proto file with aRecordLabel enum andSongwriter protocol message:

enumRecordLabel{COOL_MUSIC_INC=0;PACIFIC_ENTERTAINMENT=1;XYZ_RECORDS=2;}messageSongwriter{requiredstringnationality=1;optionalint64year_of_birth=2;}

To add aSongwriters table in your schema:

CREATEPROTOBUNDLE(googlesql.example.music.Songwriter,googlesql.example.music.RecordLabel,);CREATETABLESongwriters(IdINT64NOTNULL,FirstNameSTRING(1024),LastNameSTRING(1024),NicknameSTRING(MAX),OpaqueDataBYTES(MAX),SongWritergooglesql.example.music.Songwriter)PRIMARYKEY(Id);CREATETABLEAlbums(SongwriterIdINT64NOTNULL,AlbumIdINT64NOTNULL,AlbumTitleSTRING(MAX),LabelINT32)PRIMARYKEY(SongwriterId,AlbumId);

The following schema updates are allowed, but they require validation and mighttake longer to complete, depending on the amount of existing data:

• Adding theNOT NULL annotation to a non-key column. For example:

  ALTERTABLESongwritersALTERCOLUMNNicknameSTRING(MAX)NOTNULL;

• Reducing the length of a column. For example:

  ALTERTABLESongwritersALTERCOLUMNFirstNameSTRING(10);

• Altering fromBYTES toSTRING. For example:

  ALTERTABLESongwritersALTERCOLUMNOpaqueDataSTRING(MAX);

• Altering fromINT64/INT32 toENUM. For example:

  ALTERTABLEAlbumsALTERCOLUMNLabelgooglesql.example.music.RecordLabel;

• Removing existing values from theRecordLabel enum definition.

• Enablingcommit timestampson an existingTIMESTAMP column. For example:

  ALTERTABLEAlbumsALTERCOLUMNLastUpdateTimeSETOPTIONS(allow_commit_timestamp=true);

• Adding a check constraint to an existing table.

• Adding a stored generated column to an existing table.

• Creating a new table with a foreign key.

• Adding a foreign key to an existing table.

These schema updates fail if the underlying data does not satisfy the newconstraints. For example, theALTER TABLE Songwriters ALTER COLUMN NicknameSTRING(MAX) NOT NULL statement fails if any value in theNickname column isNULL, because the existing data does not meet theNOT NULL constraint of thenew definition.

Data validation can take from several minutes to many hours. The time tocomplete data validation depends on:

• The size of the dataset
• The compute capacity of the instance
• The load on the instance

Some schema updates can change the behavior of requests to the database beforethe schema update completes. For example, if you're addingNOT NULL to acolumn, Spanner almost immediately begins rejecting writes fornew requests that useNULL for the column. If the new schema update ultimatelyfails for data validation, there will have been a period of time when writeswere blocked, even if they would have been accepted by the old schema.

You can cancel a long-running data validation operation using theprojects.instances.databases.operations.cancelmethod or usinggcloud spanneroperations.

Order of execution of statements in batches

If you use the Google Cloud CLI, REST API, or the RPC API, you can issue a batchof one or moreCREATE,ALTER, orDROPstatements.

Spanner applies statements from the same batch in order, stoppingat the first error. If applying a statement results in an error, that statementis rolled back. The results of any previously applied statements in the batchare not rolled back. This in-order statement application means that if you wouldlike certain statements to run in parallel, like index backfills that could eachtake a long time, you should submit those statements in separate batches.

Spanner might combine and reorder statements from differentbatches, potentially mixing statements from different batches into one atomicchange that is applied to the database. Within each atomic change, statementsfrom different batches happen in an arbitrary order. For example, if one batchof statements containsALTER TABLE MyTable ALTER COLUMN MyColumn STRING(50)and another batch of statements containsALTER TABLE MyTable ALTER COLUMNMyColumn STRING(20), Spanner will leave that column in one ofthose two states, but it's not specified which.

Schema versions created during schema updates

Spanner uses schema versioning so that there is no downtimeduring a schema update to a large database. Spanner maintains theolder schema version to support reads while the schema update is processed.Spanner then creates one or more new versions of the schema toprocess the schema update. Each version contains the result of a collection ofstatements in a single atomic change.

The schema versions don't necessarily correspond one-to-one with either batchesof DDL statements or individual DDL statements. Some individual DDL statements,such as index creation for existing base tables or statements that require datavalidation, result in multiple schema versions. In other cases, several DDLstatements can be batched together in a single version. Old schema versions canconsume significant server and storage resources, and they are retained untilthey expire (no longer needed to serve reads of earlier versions of data).

The following table shows how long it takes Spanner to update aschema.

Data type changes and change streams

If you change the data type of a column that achangestream watches, thecolumn_types field ofrelevant subsequentchange streamrecords reflects itsnew type, as does theold_values JSON data within the records'mods field.

Thenew_values of a change stream record'smods field always matches acolumn's current type. Changing a watched column's data type does not affect anychange stream records predating that change.

In the particular case of aBYTES-to-STRING change, Spannervalidates the column's old values as part ofthe schema update. As a result, Spanner has safely decoded theoldBYTES-type values into strings by the time it writes any subsequent changestream records.

Best practices for schema updates

The following sections describe best practices for updating schemas.

Procedures before you issue the schema update

Before you issue a schema update:

• Verify that all of the existing data in the database that you're changingmeets the constraints that the schema update is imposing. Because thesuccess of some types of schema updates depends on the data in the databaseand not just its current schema, a successful schema update of a testdatabase does not guarantee a successful schema update of a productiondatabase. Here are some common examples:

  • If you're adding aNOT NULL annotation to an existing column, checkthat the column does not contain any existingNULL values.
  • If you're shortening the allowed length of aSTRING orBYTES column,check that all existing values in that column meet the length constraint.

• If you're writing to a column, table, or index that is undergoing a schemaupdate, ensure that the values that you're writing meet the new constraints.

• If you're dropping a column, table, or index, make sure you are not stillwriting to or reading from it.

Limit the frequency of schema updates

If you perform too many schema updates in a short period of time,Spanner maythrottle theprocessing of queued schema updates. This is because Spannerlimits the amount of space for storing schema versions. Your schema update maybe throttled if there are too many old schema versions within the retentionperiod. The maximum rate of schema changes depends on manyfactors, one of them being the totalnumber of columns in the database. For example, a database with 2000 columns(roughly 2000 rows inINFORMATION_SCHEMA.COLUMNS)is able to perform at most 1500 schema changes (fewer if the schema changerequires multiple versions) within the retention period. To see the state ofongoing schema updates, use thegcloud spanner operationslist command and filter byoperations of typeDATABASE_UPDATE_DDL. To cancel an ongoing schema update,use thegcloud spanner operationscancel command and specifythe operation ID.

How your DDL statements are batched, and their order within each batch, canaffect the number of schema versions that result. To maximize the number ofschema updates you can perform over any given period of time, you should usebatching that minimizes the number of schema versions. Some rules of thumb aredescribed inlarge updates.

As described inschema versions, some DDL statements willcreate multiple schema versions, and these ones are important when consideringbatching and order within each batch. There are two main types of statementsthat might create multiple schema versions:

• Statements that might need to backfill index data, likeCREATE INDEX
• Statements that might need to validate existing data, like addingNOT NULL

These types of statements don'talways create multiple schema versions,though. Spanner will try to detect when these types of statementscan be optimized to avoid using multiple schema versions, which depends onbatching. For example, aCREATE INDEX statement that occurs in the same batchas aCREATE TABLE statement for the index's base table, without anyintervening statements for other tables, can avoid needing to backfill the indexdata because Spanner can guarantee that the base table is emptyat the time the index is created. Thelarge updates sectiondescribes how to use this property to create many indexes efficiently.

If you cannot batch your DDL statements to avoid creating many schema versions,you should limit the number of schema updates to a single database's schemawithin its retention period. Increase the time window in which you make schemaupdates to allow Spanner to remove earlier versions of the schemabefore new versions are created.

• For some relational database management systems, there are software packagesthat make a long series of upgrade and downgrade schema updates to thedatabase on every production deployment. These types of processes are notrecommended for Spanner.
• Spanner is optimized to use primary keys to partition dataformulti-tenancysolutions. Multi-tenancysolutions that use separate tables for each customer can result in a largebacklog of schema update operations that take a long time to complete.
• Schema updates that require validation or index backfill use more serverresources because each statement creates multiple versions of the schemainternally.

Options for large schema updates

The best way to create a table and a large number of indexes on that table is tocreate all of them at the same time, so that there will be only a single schemaversion created. It's best practice to create the indexes immediately followingthe table in the list of DDL statements. You can create the table and itsindexes when you create the database, or in a single large batch of statements.If you need to create many tables, each with many indexes, you can include allthe statements in a single batch. You can include several thousand statements ina single batch when all the statements can be executed together using a singleschema version.

When a statement requires backfilling index data or performing data validation,it can't be executed in a single schema version. This happens forCREATE INDEXstatements when the index's base table already exists (either because it wascreated in a previous batch of DDL statements, or because there was a statementin the batch between theCREATE TABLE andCREATE INDEX statements thatrequired multiple schema versions). Spanner requires that thereare no more than 10 such statements in a single batch. Index creation thatrequires backfilling, in particular, uses several schema versions per index, andso it is a good rule of thumb to create no more than3 new indexes requiring backfilling per day (nomatter how they are batched, unless such batching can avoid backfilling).

For example, this batch of statements will use a single schema version:

CREATETABLESingers(SingerIdINT64NOTNULL,FirstNameSTRING(1024),LastNameSTRING(1024),)PRIMARYKEY(SingerId);CREATEINDEXSingersByFirstNameONSingers(FirstName);CREATEINDEXSingersByLastNameONSingers(LastName);CREATETABLEAlbums(SingerIdINT64NOTNULL,AlbumIdINT64NOTNULL,AlbumTitleSTRING(MAX),)PRIMARYKEY(SingerId,AlbumId);CREATEINDEXAlbumsByTitleONAlbums(AlbumTitle);

In contrast, this batch will use many schema versions, becauseUnrelatedIndexrequires backfilling (since its base table must have already existed), and thatforces all the following indexes to also require backfilling (even thoughthey're in the same batch as their base tables):

CREATETABLESingers(SingerIdINT64NOTNULL,FirstNameSTRING(1024),LastNameSTRING(1024),)PRIMARYKEY(SingerId);CREATETABLEAlbums(SingerIdINT64NOTNULL,AlbumIdINT64NOTNULL,AlbumTitleSTRING(MAX),)PRIMARYKEY(SingerId,AlbumId);CREATEINDEXUnrelatedIndexONUnrelatedTable(UnrelatedIndexKey);CREATEINDEXSingersByFirstNameONSingers(FirstName);CREATEINDEXSingersByLastNameONSingers(LastName);CREATEINDEXAlbumsByTitleONAlbums(AlbumTitle);

It would be better to move the creation ofUnrelatedIndex to the end of thebatch, or to a different batch, to minimize schema versions.

Wait for API requests to complete

When makingprojects.instances.databases.updateDdl (RESTAPI) orUpdateDatabaseDdl (RPC API) requests, useprojects.instances.databases.operations.get (REST API)orGetOperation (RPC API), respectively, to wait foreach request to complete before starting a new request. Waiting for each requestto complete allows your application to track the progress of your schemaupdates. It also keeps the backlog of pending schema updates to a manageablesize.

Bulk loading

If you are bulk loading data into your tables after they are created, it isusually more efficient to create indexes after the data is loaded. If you areadding several indexes, it might be more efficient to create the database withall tables and indexes in the initial schema, as described in theoptions forlarge updates.