Create and manage data placements

Preview —Geo-partitioning

This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

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

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

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

Create a data placement

After youcreate your Spanner instance partitionsanddatabases, create your placement.

Console

  1. Go to theInstances 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, clickSpanner Studio.

  5. In theSpanner Studio page, clickNew tab or use the emptyeditor tab.

  6. Enter theCREATE PLACEMENT(GoogleSQL,PostgreSQL)DDL statement.

    For example, you can run the following to create a placement tableeuropeplacement in the instance partitioneurope-partition:

    GoogleSQL

    CREATEPLACEMENTeuropeplacementOPTIONS(instance_partition="europe-partition");

    PostgreSQL

    CREATEPLACEMENTeuropeplacementWITH(instance_partition='europe-partition');

    Optional: You can also use theObject Explorer pane to view, search,and interact with your Placement objects. For more information, seeExplore your data.

  7. ClickRun.

gcloud

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

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

GoogleSQL

gcloudspannerdatabasesddlupdateexample-db\--instance=test-instance\--ddl="CREATE PLACEMENT europeplacement OPTIONS (instance_partition='europe-partition')"

PostgreSQL

gcloudspannerdatabasesddlupdateexample-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 adual-region or multi-region. The new leader region must be one of the tworead-write regions within the dual-region or multi-region placement location.For more information, see theDual-region available configurationsandMulti-region available configurationstables.

If you don't set a leader region, your placement uses the default leader regionas specified by its location. For a list of the leader region for eachdual-region or multi-region location, see theDual-region available configurationsandMulti-region available configurationstables. The default leader region is denoted with anL. For example, thedefault leader region ofnam8 is in Los Angeles(us-west2). The followinginstructions explain how to set it to Oregon(us-west1).

Console

  1. Go to theInstances 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, clickSpanner Studio.

  5. In theSpanner Studio page, clickNew tab or use the emptyeditor tab.

  6. Enter theCREATE PLACEMENT(GoogleSQL,PostgreSQL)DDL statement.

    For example, you can run the following to create a placement tablenam8placement in the instance partitionnam8-partition with thedefault leader location set asus-west1:

    GoogleSQL

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

    PostgreSQL

    CREATEPLACEMENTnam8placementWITH(instance_partition='nam8-partition',default_leader='us-west1');

    Optional: You can also use theObject Explorer pane to view, search,and interact with your Placement objects. For more information, seeExplore your data.

  7. ClickRun.

gcloud

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

For example, create a placement tablenam8placement in the instancepartitionnam8-partition with the default leader location set asus-west1:

GoogleSQL

gcloudspannerdatabasesddlupdateexample-db\--instance=test-instance\--ddl="CREATE PLACEMENT nam8placement \     OPTIONS (instance_partition='nam8-partition', default_leader='us-west1')"

PostgreSQL

gcloudspannerdatabasesddlupdateexample-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 theplacement. After you have done so, you can use the Google Cloud console orgcloud CLI to drop the placement.

Console

  1. In the navigation menu, clickSpanner Studio.
  2. In theSpanner Studio page, clickNew tab or use the emptyeditor tab.

  3. Enter theDROP PLACEMENT(GoogleSQL,PostgreSQL) DDL statement.

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

    GoogleSQL

    DROPPLACEMENTeuropeplacement;

    PostgreSQL

    DROPPLACEMENTeuropeplacement;

gcloud

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

For example, drop placementeuropeplacement:

gcloudspannerdatabasesddlupdateexample-db\--instance=test-instance\--ddl="DROP PLACEMENT europeplacement"

Drop placement errors

If the placement is in use, then theDROP PLACEMENT operation fails with anerror message such as: "Statement failed: PlacementPLACEMENT_NAME cannot be dropped because it is in use byplacement tablePLACEMENT_TABLE_NAME.". If you encounterthis error, do the following:

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

  2. Either:

    • Move existing placement rows that use the placement you want to delete toa different placement with apartitioned DMLstatement like the following:

      UPDATEPLACEMENT_TABLE_NAMESETLOCATION=NEW_PLACEMENT_NAMEWHERELOCATION=ORIGINAL_PLACEMENT_NAME;

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

      DELETEFROMPLACEMENT_TABLE_NAMEWHERELOCATION=ORIGINAL_PLACEMENT_NAME;

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

Cancel aDROP PLACEMENT operation

You can cancel aDROP PLACEMENT operation anytime before the long-runningoperation completely deletes the placement from the database schema. For detailson how to obtain the long-running operation ID to check the status, or to cancelthe operation, seeManage and observe long-running operations.

Create a table with a placement key

Console

  1. In the navigation menu, clickSpanner Studio.
  2. In theSpanner Studio page, clickNew tab or use the empty editor tab.

  3. Enter theCREATE TABLE (GoogleSQL,PostgreSQL) DDL statement.

    For example, you can create aSingers table that usesa placement key to partition singer data:

    GoogleSQL

    CREATETABLESingers(SingerIdINT64NOTNULL,SingerNameSTRING(MAX)NOTNULL,...LocationSTRING(MAX)NOTNULLPLACEMENTKEY)PRIMARYKEY(SingerId);

    PostgreSQL

    CREATETABLESingers(SingerIdbigintPRIMARYKEY,SingerNamevarchar(1024),...Locationvarchar(1024)NOTNULLPLACEMENTKEY);

gcloud

To create a table, usegcloud spanner databases ddl update.

For example, you can create aSingers table that usesa placement key to partition singer data:

GoogleSQL

gcloudspannerdatabasesddlupdateexample-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

gcloudspannerdatabasesddlupdateexample-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 keyto a table after it has been created. However, you can use theALTER TABLE (GoogleSQL,PostgreSQL) DDL statement to change other fields inthe 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 theversion_retention_period for thedatabase to pass. For more information, seePoint-in-time recovery.Then, following these steps:

Console

  1. In the navigation menu, clickSpanner Studio.
  2. In theSpanner Studio page, clickNew tab or use the empty editor tab.

  3. Enter theDROP TABLE (GoogleSQL,PostgreSQL) DDL statement.

    For example, drop theSingers table:

    DROPTABLESingers;

gcloud

To drop a table, usegcloud spanner databases ddl update.

For example, drop theSingers table:

gcloudspannerdatabasesddlupdateexample-db\--instance=test-instance\--ddl="DROP TABLE Singers"

Insert a row in a placement table

Console

  1. In the navigation menu, clickSpanner Studio.
  2. In theSpanner Studio page, clickNew tab or use the empty editor tab.

  3. Enter theINSERT INTO (GoogleSQL,PostgreSQL) DDL statement.

    For example, add a singer, Marc Richards, to theSingers table andpartition it ineuropeplacement:

    INSERTINTOSingers(SingerId,SingerName,Location)VALUES(1,'Marc Richards','europeplacement')

gcloud

To write data to a table, usegcloud spanner rows insert.

For example, add a singer, Marc Richards, to theSingers table andpartition it ineuropeplacement:

gcloudspannerrowsinsert--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, clickSpanner Studio.
  2. In theSpanner Studio page, clickNew tab or use the empty editor tab.

  3. UseDML ormutations to update data in aplacement table.

    For example, update the name ofsingerid=1 in theSingers table toCatalina Smith:

    UPDATESingerssSETs.name='Catalina Smith'WHEREs.id=1;

gcloud

To update data in a placement table,usegcloud spanner rows update.

For example, update the name ofsingerid=1 in theSingers table toCatalina Smith:

gcloudspannerrowsupdate--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, clickSpanner Studio.
  3. In theSpanner Studio page, clickNew tab or use the empty editor tab.

  4. UseDML ormutations to move data to thenew instance partition.

    For example, movesingerid=1 in theSingers table toasiaplacement:

    UPDATESingerssSETs.location='asiaplacement'WHEREs.id=1;

gcloud

After creating the instance partition and placement where you want to moveyour data, usegcloud spanner rows update.

For example, movesingerid=1 in theSingers table toasiaplacement:

gcloudspannerrowsupdate--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, clickSpanner Studio.
  2. In theSpanner Studio page, clickNew tab or use the empty editor tab.

  3. UseDML ormutations to delete data.

    For example, deletesingerid=1 in theSingers table:

    DELETEFROMSingerssWHEREs.id=1;

gcloud

To delete data, usegcloud spanner rows delete.

For example, deletesingerid=1 in theSingers table:

gcloudspannerrowsdelete--table=Singers--database=example-db\--instance=test-instance--keys=1

Query data in a placement table

Console

  1. In the navigation menu, clickSpanner Studio.
  2. In theSpanner Studio page, clickNew tab or use the empty editor tab.

  3. Run your query.

    For example, query theSingers table:

    SELECT*FROMSingerssWHEREs.SingerId=1;

gcloud

To query data, usegcloud spanner databases execute-sql.

For example, query theSingers table:

gcloudspannerdatabasesexecute-sqlexample-db\--instance=test-instance\--sql='SELECT * FROM Singers s WHERE s.SingerId=1'

What's next

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-12-15 UTC.