Create and manage data placements

Note: This feature is available with the Spanner Enterprise Plus edition. For more information, see the Spanner editions overview.

This page describes how to create and manage data placements in Spanner.

For more information about how geo-partitioning works, see the Geo-partitioning overview.

Manage routing metadata

When your application needs to read or write data, Spanner uses routing metadata to find which servers hold the specific rows needed.

Choosing where routing metadata resides depends on whether most client requests are for data within the same region or outside the region, and whether you use a multi-region configuration. Routing metadata settings affect latency and can't be changed after you create placements.

The per_placement_routing_metadata option determines where routing metadata resides according to the following settings:

• TRUE: If set to TRUE, then routing metadata resides in the same instance partition as the data. Routing metadata is sharded by region. This reduces latency for in-region data requests (that is, the client and the user data requested are in the same region) so that Spanner doesn't have to make out-of-region calls for routing metadata. However, this setting might increase latency for out-of-region requests, for negative lookups where placement rows don't exist, and for queries that need a global index.

  We recommend this setting if you expect most requests to be made for user data within the same region as the client, and your default partition does not have read-write or read-only regions that are colocated with all your instance partitions.

• FALSE: Routing metadata is centralized in the default partition.

  We recommend this setting if you expect clients to frequently look up out-of-region data; for example, a client in the EU region looking up data for users in the US.

  Also consider this setting if your default instance partition uses a multi-region configuration that provides read-write or read-only regions that are colocated with all your instance partitions. With this setting, a replica of the full routing metadata is stored with every replica of the default instance partition. Therefore all regions with instance partitions have a replica of the routing metadata. This lets both in-region and out-of-region requests look up routing metadata locally.

Note: Prior to July 2026, the default value was FALSE. As of July 2026, the default is TRUE. You might see different latency results between databases created before and after the default value change. We recommend that you set this option explicitly to avoid unexpected changes.

Set the per_placement_routing_metadata option

Note: Setting this option fails if there are already existing data placements.

To set per_placement_routing_metadata in the Google Cloud console, update this option in your database schema. For information about how to update a schema, see Make schema updates.

The per_placement_routing_metadata option accepts boolean values. If this option is TRUE, then routing metadata resides in the same instance partition as the data. If FALSE, then routing metadata resides in the default instance partition.

The following example shows a GoogleSQL ALTER DATABASE command to set this option:

ALTER DATABASE DATABASE_NAME SET OPTIONS ( per_placement_routing_metadata = METADATA_BOOLEAN)

Replace the following:

• DATABASE_NAME: the name of your database.
• METADATA_BOOLEAN: the per_placement_routing_metadata value. If TRUE, then routing metadata resides in the same instance partition as the data. If FALSE, then routing metadata resides in the default instance partition.

Create a data placement

After you create your Spanner instance partitions and databases and set the per_placement_routing_metadata option, create your placement.

Console

1. Go to the Instances page in the Google Cloud console.

   Instances

2. Select the instance with user-created instance partition(s).

3. Select the database that you want to partition data.

4. In the navigation menu, click Spanner Studio.

5. In the Spanner Studio page, click New tab or use the empty editor tab.

6. Enter the CREATE PLACEMENT (GoogleSQL, PostgreSQL) DDL statement.

   For example, you can run the following to create a placement table europeplacement in the instance partition europe-partition:

   GoogleSQL

   CREATE PLACEMENT europeplacement OPTIONS (instance_partition="europe-partition");
   

   PostgreSQL

   CREATE PLACEMENT europeplacement WITH (instance_partition='europe-partition');
   

   Optional: You can also use the Object Explorer pane to view, search, and interact with your Placement objects. For more information, see Explore your data.

7. Click Run.

gcloud

To create a placement with the Google Cloud CLI command, use gcloud spanner databases ddl update.

For example, create a placement in the instance partition europe-partition:

GoogleSQL

gcloud spanner databases ddl update example-db \
  --instance=test-instance \
  --ddl="CREATE PLACEMENT europeplacement OPTIONS (instance_partition='europe-partition')"

PostgreSQL

gcloud spanner databases ddl update example-db \
  --instance=test-instance \
  --ddl="CREATE PLACEMENT europeplacement WITH (instance_partition='europe-partition')"

Set the default leader for a placement

You can set the default leader region of a placement if its location is in a multi-region. The new leader region must be one of the two read-write regions within the multi-region placement location. For more information, Multi-region available configurations tables.

If you don't set a leader region, your placement uses the default leader region as specified by its location. For a list of the leader region for each multi-region location, see the Multi-region available configurations tables. The default leader region is denoted with an L. For example, the default leader region of nam8 is in Los Angeles(us-west2). The following instructions explain how to set it to Oregon(us-west1).

Console

1. Go to the Instances page in the Google Cloud console.

   Instances

2. Select the instance with user-created instance partition(s).

3. Select the database that you want to partition data.

4. In the navigation menu, click Spanner Studio.

5. In the Spanner Studio page, click New tab or use the empty editor tab.

6. Enter the CREATE PLACEMENT (GoogleSQL, PostgreSQL) DDL statement.

   For example, you can run the following to create a placement table nam8placement in the instance partition nam8-partition with the default leader location set as us-west1:

   GoogleSQL

   CREATE PLACEMENT `nam8placement`
     OPTIONS (instance_partition="nam8-partition", default_leader="us-west1");
   

   PostgreSQL

   CREATE PLACEMENT nam8placement WITH (instance_partition='nam8-partition', default_leader='us-west1');
   

   Optional: You can also use the Object Explorer pane to view, search, and interact with your Placement objects. For more information, see Explore your data.

7. Click Run.

gcloud

To create a placement with the gcloud CLI command, use gcloud spanner databases ddl update.

For example, create a placement table nam8placement in the instance partition nam8-partition with the default leader location set as us-west1:

GoogleSQL

gcloud spanner databases ddl update example-db \
  --instance=test-instance \
  --ddl="CREATE PLACEMENT nam8placement \
     OPTIONS (instance_partition='nam8-partition', default_leader='us-west1')"

PostgreSQL

gcloud spanner databases ddl update example-db \
  --instance=test-instance \
  --ddl="CREATE PLACEMENT nam8placement WITH (instance_partition='nam8-partition', default_leader='us-west1')"

Drop a data placement

Before you drop a placement, you must remove all row data from the placement. After you have done so, you can use the Google Cloud console or gcloud CLI to drop the placement.

Console

1. In the navigation menu, click Spanner Studio.
2. In the Spanner Studio page, click New tab or use the empty editor tab.

3. Enter the DROP PLACEMENT(GoogleSQL, PostgreSQL) DDL statement.

   For example, you can run the following to drop the placement table europeplacement:

   GoogleSQL

   DROP PLACEMENT europeplacement;
   

   PostgreSQL

   DROP PLACEMENT europeplacement;
   

gcloud

To drop a placement with the gcloud CLI command, use gcloud spanner databases ddl update.

For example, drop placement europeplacement:

gcloud spanner databases ddl update example-db \
  --instance=test-instance \
  --ddl="DROP PLACEMENT europeplacement"

Drop placement errors

If the placement is in use, then the DROP PLACEMENT operation fails with an error message such as: "Statement failed: Placement PLACEMENT_NAME can't be dropped because it is in use by placement table PLACEMENT_TABLE_NAME.". If you encounter this error, complete the following steps:

1. Modify your application to stop inserting or updating rows with the placement you want to drop.

2. Either:

   • Move existing placement rows that use the placement you want to delete to a different placement with a partitioned DML statement like the following:

     UPDATE PLACEMENT_TABLE_NAME SET LOCATION = NEW_PLACEMENT_NAME
     WHERE LOCATION = ORIGINAL_PLACEMENT_NAME;
     

   • Delete the placement rows with a partitioned DML statement like the following:

     DELETE FROM PLACEMENT_TABLE_NAME
     WHERE LOCATION = ORIGINAL_PLACEMENT_NAME;
     

     The previous placement-specific DML statements only work with partitioned DML. They will fail as regular DML statements. For more information, see Limitations. You can also use the mutation API to move or drop placement rows.

Cancel a DROP PLACEMENT operation

You can cancel a DROP PLACEMENT operation anytime before the long-running operation completely deletes the placement from the database schema. For details on how to obtain the long-running operation ID to check the status, or to cancel the operation, see Manage and observe long-running operations.

Create a table with a placement key

Console

1. In the navigation menu, click Spanner Studio.
2. In the Spanner Studio page, click New tab or use the empty editor tab.

3. Enter the CREATE TABLE (GoogleSQL, PostgreSQL) DDL statement.

   For example, you can create a Singers table that uses a placement key to partition singer data:

   GoogleSQL

   CREATE TABLE Singers (
     SingerId INT64 NOT NULL,
     SingerName STRING(MAX) NOT NULL,
     ...
     Location STRING(MAX) NOT NULL PLACEMENT KEY
   ) PRIMARY KEY (SingerId);
   

   PostgreSQL

   CREATE TABLE Singers (
     SingerId bigint PRIMARY KEY,
     SingerName varchar(1024),
     ...
     Location varchar(1024) NOT NULL PLACEMENT KEY
   );
   

gcloud

To create a table, use gcloud spanner databases ddl update.

For example, you can create a Singers table that uses a placement key to partition singer data:

GoogleSQL

gcloud spanner databases ddl update example-db \
  --instance=test-instance \
  --ddl="CREATE TABLE Singers ( SingerId INT64 NOT NULL, SingerName STRING(MAX) NOT NULL, Location STRING(MAX) NOT NULL PLACEMENT KEY ) PRIMARY KEY (SingerId);"

PostgreSQL

gcloud spanner databases ddl update example-db \
  --instance=test-instance \
  --ddl="CREATE TABLE Singers ( SingerId bigint PRIMARY KEY, SingerName varchar(1024), Location varchar(1024) NOT NULL PLACEMENT KEY );"

Edit a table with a placement key

You can't drop a placement key from a table. You also can't add a placement key to a table after it has been created. However, you can use the ALTER TABLE (GoogleSQL, PostgreSQL) DDL statement to change other fields in the table, for example, by adding and dropping non-placement key columns.

Delete a table with a placement key

Before you delete a table with a placement key, you must first:

1. Delete all rows in the placement table.
2. Wait for the version_retention_period for the database to pass. For more information, see Point-in-time recovery. Then, following these steps:

Console

1. In the navigation menu, click Spanner Studio.
2. In the Spanner Studio page, click New tab or use the empty editor tab.

3. Enter the DROP TABLE (GoogleSQL, PostgreSQL) DDL statement.

   For example, drop the Singers table:

   DROP TABLE Singers;
   

gcloud

To drop a table, use gcloud spanner databases ddl update.

For example, drop the Singers table:

gcloud spanner databases ddl update example-db \
  --instance=test-instance \
  --ddl="DROP TABLE Singers"

Insert a row in a placement table

Console

1. In the navigation menu, click Spanner Studio.
2. In the Spanner Studio page, click New tab or use the empty editor tab.

3. Enter the INSERT INTO (GoogleSQL, PostgreSQL) DDL statement.

   For example, add a singer, Marc Richards, to the Singers table and partition it in europeplacement:

   INSERT INTO Singers(SingerId, SingerName, Location)
   VALUES (1, 'Marc Richards', 'europeplacement')
   

gcloud

To write data to a table, use gcloud spanner rows insert.

For example, add a singer, Marc Richards, to the Singers table and partition it in europeplacement:

gcloud spanner rows insert --table=Singers --database=example-db \
  --instance=test-instance --data=SingerId=1,SingerName='Marc Richards',Location='europeplacement'

Update a row in a placement table

Console

1. In the navigation menu, click Spanner Studio.
2. In the Spanner Studio page, click New tab or use the empty editor tab.

3. Use DML or mutations to update data in a placement table.

   For example, update the name of singerid=1 in the Singers table to Catalina Smith:

   UPDATE Singers s
   SET s.name='Catalina Smith'
   WHERE s.id=1;
   

gcloud

To update data in a placement table, use gcloud spanner rows update.

For example, update the name of singerid=1 in the Singers table to Catalina Smith:

gcloud spanner rows update --table=Singers --database=example-db \
  --instance=test-instance --data=SingerId=1,SingerName='Catalina Smith'

Move a row in a placement table

Console

1. Create a new instance partition and placement if you haven't already.
2. In the navigation menu, click Spanner Studio.
3. In the Spanner Studio page, click New tab or use the empty editor tab.

4. Use DML or mutations to move data to the new instance partition.

   For example, move singerid=1 in the Singers table to asiaplacement:

   UPDATE Singers s
   SET s.location='asiaplacement'
   WHERE s.id=1;
   

gcloud

After creating the instance partition and placement where you want to move your data, use gcloud spanner rows update.

For example, move singerid=1 in the Singers table to asiaplacement:

gcloud spanner rows update --table=Singers --database=example-db \
  --instance=test-instance --data=SingerId=1,Location='asiaplacement'

Delete a row in a placement table

Console

1. In the navigation menu, click Spanner Studio.
2. In the Spanner Studio page, click New tab or use the empty editor tab.

3. Use DML or mutations to delete data.

   For example, delete singerid=1 in the Singers table:

   DELETE FROM Singers s
   WHERE s.id=1;
   

gcloud

To delete data, use gcloud spanner rows delete.

For example, delete singerid=1 in the Singers table:

gcloud spanner rows delete --table=Singers --database=example-db \
  --instance=test-instance --keys=1

Query data in a placement table

Console

1. In the navigation menu, click Spanner Studio.
2. In the Spanner Studio page, click New tab or use the empty editor tab.

3. Run your query.

   For example, query the Singers table:

   SELECT * FROM Singers s WHERE s.SingerId=1;
   

gcloud

To query data, use gcloud spanner databases execute-sql.

For example, query the Singers table:

gcloud spanner databases execute-sql example-db \
  --instance=test-instance \
  --sql='SELECT * FROM Singers s WHERE s.SingerId=1'

What's next

• Learn more about geo-partitioning.

• Learn how to create and manage instance partitions.