Create and manage locality groups

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

This page describes how to create and manage Spanner locality groups. You can use locality groups to define the tiered storage policy for data in your database schema. For information about how tiered storage works, see Tiered storage.

Create a locality group

You can create a locality group without any tiered storage policy, or you can create a locality group to define the storage policy for data in your database schema.

If you create a locality group without a tiered storage policy, the locality group inherits the tiered storage policy of the default locality group. If you haven't manually set the storage policy of the default locality group, then the storage policy is set to SSD-only.

Console gcloud

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

   Instances

2. Select the instance in which you want to use tiered storage.

3. Select the database in which you want to use tiered storage.

4. In the navigation menu, click Spanner Studio.

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

6. Enter the CREATE LOCALITY GROUP DDL statement using GoogleSQL or PostgreSQL.

   For example, you can run the following to create a locality group, separate_storage, that stores columns in a separate file than the data for the rest of the columns:

   GoogleSQL PostgreSQL
   CREATE LOCALITY GROUP separate_storage;
   

   CREATE LOCALITY GROUP separate_storage;
   

   For example, you can run the following to create a locality group, ssd_only, that stores data on SSD storage:

   GoogleSQL PostgreSQL
   CREATE LOCALITY GROUP ssd_only OPTIONS (storage='ssd');
   

   CREATE LOCALITY GROUP ssd_only STORAGE 'ssd';
   

   For example, you can run the following to create a locality group, hdd_only, that stores data on HDD storage:

   GoogleSQL PostgreSQL
   CREATE LOCALITY GROUP hdd_only OPTIONS (storage='hdd');
   

   CREATE LOCALITY GROUP hdd_only STORAGE 'hdd';
   

7. Click Run.

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

For example, you can run the following to create a locality group, separate_storage, that stores columns in a separate file than the data for the rest of the columns:

GoogleSQL PostgreSQL

gcloud spanner databases ddl update example-db \
  --instance=test-instance \
  --ddl="CREATE LOCALITY GROUP separate_storage"

gcloud spanner databases ddl update example-db \
  --instance=test-instance \
  --ddl="CREATE LOCALITY GROUP separate_storage"

For example, you can run the following to create a locality group, ssd_only, that stores data on SSD:

GoogleSQL PostgreSQL

gcloud spanner databases ddl update example-db \
  --instance=test-instance \
  --ddl="CREATE LOCALITY GROUP ssd_only OPTIONS (storage='ssd')"

gcloud spanner databases ddl update example-db \
  --instance=test-instance \
  --ddl="CREATE LOCALITY GROUP ssd_only STORAGE 'ssd'"

For example, you can run the following to create a locality group, hdd_only, that stores data on HDD storage:

GoogleSQL PostgreSQL

gcloud spanner databases ddl update example-db \
  --instance=test-instance \
  --ddl="CREATE LOCALITY GROUP hdd_only OPTIONS (storage='hdd')"

gcloud spanner databases ddl update example-db \
  --instance=test-instance \
  --ddl="CREATE LOCALITY GROUP hdd_only STORAGE 'hdd'"

Create an age-based policy for a locality group

A locality group with an age-based policy stores newer data in SSD storage for a specified time. After the specified time passes, Spanner migrates the data to HDD storage during its normal compaction cycle, which typically occurs over the course of seven days from the specified time. When using an age-based tiered storage policy, the minimum amount of time that data must be stored in SSD before it's moved to HDD storage is one hour.

To create an age-based locality group, use the CREATE LOCALITY GROUP DDL statement.

Console gcloud

1. On the Spanner Studio page, click New tab or use the empty editor tab.

2. Enter the CREATE LOCALITY GROUP DDL statement using GoogleSQL or PostgreSQL.

   For example, the following DDL statement creates a locality group, spill_to_hdd, that stores data on SSD storage for the first 10 days, and then migrates older data to HDD storage over the normal compaction cycle:

   GoogleSQL PostgreSQL
   CREATE LOCALITY GROUP spill_to_hdd
   OPTIONS (storage = 'ssd', ssd_to_hdd_spill_timespan = '10d');
   

   CREATE LOCALITY GROUP spill_to_hdd
   STORAGE 'ssd' SSD_TO_HDD_SPILL_TIMESPAN '10d';
   

3. Click Run.

To create an age-based locality group with the gcloud CLI command, use gcloud spanner databases ddl update.

For example, the following DDL statement creates a locality group spill_to_hdd that stores data in SSD for the first 10 days, and then migrates older data to HDD over the normal compaction cycle.

GoogleSQL PostgreSQL

gcloud spanner databases ddl update example-db \
  --instance=test-instance \
  --ddl="CREATE LOCALITY GROUP spill_to_hdd OPTIONS (storage='ssd', ssd_to_hdd_spill_timespan='10d')"

gcloud spanner databases ddl update example-db \
  --instance=test-instance \
  --ddl="CREATE LOCALITY GROUP spill_to_hdd STORAGE 'ssd' SSD_TO_HDD_SPILL_TIMESPAN '10d'"

Set a tiered storage policy for your data

After you create your locality group, you can set the tiered storage policy for your data. The tiered storage policy determines the locality group that the data uses. You can set the tiered storage policy at the database, table, column, or secondary index-level. Each database object inherits its tiered storage policy from its parent, unless it's explicitly overridden.

If you create a locality group without a tiered storage policy, the locality group inherits the tiered storage policy of the default locality group. If you haven't manually set the storage policy of the default locality group, then the storage policy is set to SSD-only.

Set a database-level locality group

The default tiered storage policy is that all data is stored on SSD storage. You can change the database-level tiered storage policy by altering the default locality group. For GoogleSQL-dialect databases, your ALTER LOCALITY GROUP DDL statement must have default within backticks (`default`). You only need to include the backticks for the default locality group.

Console gcloud

1. On the Spanner Studio page, click New tab or use the empty editor tab.

2. Enter the ALTER LOCALITY GROUP DDL statement using GoogleSQL or PostgreSQL.

   For example, the following DDL statements alter the default locality group to use an age-based tiered storage policy. All data in the database is moved to HDD storage after 10 days.

   GoogleSQL PostgreSQL
   ALTER LOCALITY GROUP `default` SET OPTIONS (storage = 'ssd', ssd_to_hdd_spill_timespan = '10d');
   

   ALTER LOCALITY GROUP "default" STORAGE 'ssd' SSD_TO_HDD_SPILL_TIMESPAN '10d';
   

3. Click Run.

To alter the tiered storage policy of the default locality group with the gcloud CLI command, use gcloud spanner databases ddl update.

For example, the following DDL statements alter the default locality group to use an age-based tiered storage policy. All data in the database is moved to HDD storage after 10 days.

GoogleSQL PostgreSQL

gcloud spanner databases ddl update example-db \
  --instance=test-instance \
  --ddl="ALTER LOCALITY GROUP \`default\` SET OPTIONS (storage = 'ssd', ssd_to_hdd_spill_timespan = '10d');"

gcloud spanner databases ddl update example-db \
  --instance=test-instance \
  --ddl="ALTER LOCALITY GROUP \"default\" STORAGE 'ssd' SSD_TO_HDD_SPILL_TIMESPAN '10d';"

Set a table-level locality group

You can set a table-level tiered storage policy for your data that overrides the database-level tiered storage policy. The table-level tiered storage policy is also applicable to all columns in the table, unless you have set a column-level override tiered storage policy.

Console gcloud

1. On the Spanner Studio page, click New tab or use the empty editor tab.

2. Enter the CREATE TABLE DDL statement using GoogleSQL or PostgreSQL.

   For example, the following DDL statements create a table, Singers, that uses the locality group ssd_only:

   GoogleSQL PostgreSQL
   CREATE TABLE Singers (
     SingerId   INT64 NOT NULL,
     FirstName  STRING(1024),
     LastName   STRING(1024),
     SingerInfo BYTES(MAX)
   ) PRIMARY KEY (SingerId), OPTIONS (locality_group = 'ssd_only');
   

   CREATE TABLE Singers (
     SingerId   bigint PRIMARY KEY,
     FirstName  varchar(1024),
     LastName   varchar(1024),
     SingerInfo bytea
   ) LOCALITY GROUP ssd_only;
   

3. Click Run.

To set a table-level tiered storage policy with the gcloud CLI command, use gcloud spanner databases ddl update.

For example, the following DDL statements create a table, Singers, that uses the locality group ssd_only.

GoogleSQL PostgreSQL

gcloud spanner databases ddl update example-db \
  --instance=test-instance \
  --ddl="CREATE TABLE Singers ( SingerId   INT64 NOT NULL, \
        FirstName  STRING(1024), \
        LastName   STRING(1024), \
        SingerInfo BYTES(MAX) \
        ) PRIMARY KEY (SingerId), OPTIONS (locality_group = 'ssd_only');"

gcloud spanner databases ddl update example-db \
  --instance=test-instance \
  --ddl="CREATE TABLE Singers ( \
        SingerId bigint PRIMARY KEY, \
        FirstName  varchar(1024), \
        LastName   varchar(1024), \
        SingerInfo bytea \
        ) LOCALITY GROUP ssd_only;"

Set a column-level override tiered storage policy

You can set a column-level override tiered storage policy for your data.

Console gcloud

1. On the Spanner Studio page, click New tab or use the empty editor tab.

2. Enter the CREATE TABLE DDL statement with a column-level override tiered storage policy using GoogleSQL or PostgreSQL.

   For example, the following DDL statements create a Singers table that uses the locality group ssd_only. However, the Awards column overrides this table-level locality group and uses the spill_to_hdd locality group as its tiered storage policy:

   GoogleSQL PostgreSQL
   CREATE TABLE Singers (
     SingerId   INT64 NOT NULL,
     FirstName  STRING(1024),
     LastName   STRING(1024),
     Awards     ARRAY<STRING(MAX)> OPTIONS (locality_group = 'spill_to_hdd')
   ) PRIMARY KEY (SingerId), OPTIONS (locality_group = 'ssd_only');
   

   CREATE TABLE Singers (
     SingerId   bigint PRIMARY KEY,
     FirstName  varchar(1024),
     LastName   varchar(1024),
     Awards     varchar[] LOCALITY GROUP spill_to_hdd
   ) LOCALITY GROUP ssd_only;
   

3. Click Run.

To set a column-level override tiered storage policy with the gcloud CLI command, use gcloud spanner databases ddl update.

For example, the following DDL statements create a Singers table that uses the locality group ssd_only. However, the Awards column overrides this table-level tiered storage policy and uses the spill_to_hdd locality group as its tiered storage policy:

GoogleSQL PostgreSQL

gcloud spanner databases ddl update example-db \
  --instance=test-instance \
  --ddl="CREATE TABLE Singers ( \
      SingerId   INT64 NOT NULL, \
      FirstName  STRING(1024), \
      LastName   STRING(1024), \
      Awards     ARRAY<STRING(MAX)> OPTIONS (locality_group = 'spill_to_hdd') \
    ) PRIMARY KEY (SingerId), OPTIONS (locality_group = 'ssd_only');" \

gcloud spanner databases ddl update example-db \
  --instance=test-instance \
  --ddl="CREATE TABLE Singers ( \
      SingerId   bigint PRIMARY KEY, \
      FirstName  varchar(1024), \
      LastName   varchar(1024), \
      Awards     varchar[] LOCALITY GROUP spill_to_hdd \
    ) LOCALITY GROUP ssd_only;"

Set a secondary index-level override tiered storage policy

You can set a secondary index-level override tiered storage policy for your data.

Console gcloud

1. On the Spanner Studio page, click New tab or use the empty editor tab.

2. Enter the CREATE INDEX DDL statement with a secondary index-level override tiered storage policy using GoogleSQL or PostgreSQL.

   For example, the following DDL statements create a Singers table that uses the locality group ssd_only. The database also has a secondary index on all Singers in the database by their first and last name. The SingersByFirstLastName index overrides the table-level tiered storage policy and uses the spill_to_hdd locality group as its tiered storage policy:

   GoogleSQL PostgreSQL
   CREATE INDEX SingersByFirstLastName ON Singers(FirstName, LastName)
   OPTIONS (locality_group = 'spill_to_hdd');
   

   CREATE INDEX SingersByFirstLastName ON Singers(FirstName, LastName)
   LOCALITY GROUP spill_to_hdd;
   

3. Click Run.

To set a secondary index-level override tiered storage policy with the gcloud CLI command, use gcloud spanner databases ddl update.

For example, the following DDL statements create a Singers table that uses the locality group ssd_only. The database also creates a secondary index on all Singers in the database by their first and last name. The SingersByFirstLastName index overrides the table-level tiered storage policy and uses the spill_to_hdd locality group as its tiered storage policy:

GoogleSQL PostgreSQL

gcloud spanner databases ddl update example-db \
  --instance=test-instance \
  --ddl="CREATE INDEX SingersByFirstLastName ON Singers(FirstName, LastName) \
    OPTIONS (locality_group = 'spill_to_hdd');"

gcloud spanner databases ddl update example-db \
  --instance=test-instance \
  --ddl="CREATE INDEX SingersByFirstLastName ON Singers(FirstName, LastName) \
    LOCALITY GROUP spill_to_hdd;"

Set a column-level locality group

You can set a column-level locality group for your data even if the locality group doesn't have a tiered storage policy. Reading data from this column is faster than reading data that is grouped with other columns.

Console gcloud

1. On the Spanner Studio page, click New tab or use the empty editor tab.

2. Enter the CREATE TABLE DDL statement that assigns the column to a locality group using GoogleSQL or PostgreSQL.

   For example, the following DDL statements create a Songs table with a LyricsCompressed column that is stored separately in the hdd_only locality group:

   GoogleSQL PostgreSQL
   CREATE TABLE Songs (
     SingerId INT64 NOT NULL,
     SongId INT64 NOT NULL,
     Title STRING(MAX),
     Description STRING(MAX),
     LyricsCompressed BYTES(MAX) OPTIONS (locality_group = 'hdd_only')
   ) PRIMARY KEY (SingerId, SongId),
     INTERLEAVE IN PARENT Singers ON DELETE CASCADE,
     OPTIONS (locality_group = 'ssd_only');
   

   CREATE TABLE Songs (
     SingerId BIGINT NOT NULL,
     SongId BIGINT NOT NULL,
     Title VARCHAR,
     Description TEXT,
     LyricsCompressed BYTEA LOCALITY GROUP hdd_only,
     PRIMARY KEY (SingerId, SongId)
   ) LOCALITY GROUP ssd_only INTERLEAVE IN PARENT Singers ON DELETE CASCADE;
   

3. Click Run.

To set a column-level locality group for your data with the gcloud CLI command, use gcloud spanner databases ddl update.

For example, the following DDL statements create a Songs table with a LyricsCompressed column that is stored separately in the hdd_only locality group:

GoogleSQL PostgreSQL

gcloud spanner databases ddl update example-db \
  --instance=test-instance \
  --ddl="CREATE TABLE Songs ( \
      SingerId INT64 NOT NULL, \
      SongId INT64 NOT NULL, \
      Title STRING(MAX), \
      Description STRING(MAX),
      LyricsCompressed BYTES(MAX) OPTIONS (locality_group = 'hdd_only') \
    ) PRIMARY KEY (SingerId, SongId), \
      INTERLEAVE IN PARENT Singers ON DELETE CASCADE, \
      OPTIONS (locality_group = 'ssd_only');"

gcloud spanner databases ddl update example-db \
  --instance=test-instance \
  --ddl="CREATE TABLE Songs ( \
      SingerId BIGINT NOT NULL, \
      SongId BIGINT NOT NULL, \
      Title VARCHAR, \
      Description TEXT, \
      LyricsCompressed BYTEA LOCALITY GROUP hdd_only, \
      PRIMARY KEY (SingerId, SongId) \
    ) LOCALITY GROUP ssd_only INTERLEAVE IN PARENT Singers ON DELETE CASCADE;

Move data between storage options

You can move data between SSD and HDD storage. Moving data can take up to seven days. You can monitor the progress of the move by querying the built-in SPANNER_SYS.TABLE_SIZES_STATS_1HOUR table to check HDD and SSD storage usage for each table in your database. You can also monitor your storage usage by using the Cloud Monitoring spanner.googleapis.com/instance/storage/used_bytes metric to show the SSD and HDD breakdown for your database or instance. For more information, see tiered storage observability.

Move data from SSD to HDD storage

To move data from SSD to HDD storage, you can create a new locality group with an aged-based tiered storage policy, or change the tiered storage policy of an existing locality group. Moving data from SSD to HDD can take up to seven days. For more information, see Create an age-based policy for the locality group or Change the storage option.

Move data from HDD to SSD storage

To move data from HDD to SSD storage, you can change the storage option of an existing locality group or alter the locality group used by the table. Moving data from SSD to HDD can take up to seven days.

Alter the locality group used by a table

You can alter the locality group used by a table by setting a new or different locality group in the table options.

Console gcloud

1. On the Spanner Studio page, click New tab or use the empty editor tab.

2. Enter the ALTER TABLE DDL statement that changes the locality group used by the table using GoogleSQL or PostgreSQL.

   For example, the following DDL statements alter the locality group used by the table, Singers, to spill_to_hdd:

   GoogleSQL PostgreSQL
   ALTER TABLE Singers SET OPTIONS (locality_group = 'spill_to_hdd');
   

   ALTER TABLE Singers SET LOCALITY GROUP spill_to_hdd;
   

3. Click Run.

To alter the locality group used by a table with the gcloud CLI command, use gcloud spanner databases ddl update.

For example, the following DDL statements alter the locality group used by the table, Singers, to spill_to_hdd:

GoogleSQL PostgreSQL

gcloud spanner databases ddl update example-db \
  --instance=test-instance \
  --ddl="ALTER TABLE Singers SET OPTIONS(locality_group = 'spill_to_hdd');"

gcloud spanner databases ddl update example-db \
  --instance=test-instance \
  --ddl="ALTER TABLE Singers SET LOCALITY GROUP spill_to_hdd;"

Alter the locality group used by a table's column

You can alter the locality group used by a table's column by setting the locality group in the column options.

Console gcloud

1. On the Spanner Studio page, click New tab or use the empty editor tab.

2. Enter the ALTER TABLE DDL statement that changes the locality group used by the table using GoogleSQL or PostgreSQL.

   For example, the following DDL statements alter the locality group used by the table's column, LastName, to spill_to_hdd:

   GoogleSQL PostgreSQL
   ALTER TABLE Singers
   ALTER COLUMN LastName SET OPTIONS(locality_group = 'spill_to_hdd');
   

   ALTER TABLE Singers
   ALTER COLUMN LastName SET LOCALITY GROUP spill_to_hdd;
   

3. Click Run.

To alter the locality group used by a table with the gcloud CLI command, use gcloud spanner databases ddl update.

For example, the following DDL statements alter the locality group used by the table's column, LastName, to spill_to_hdd:

GoogleSQL PostgreSQL

gcloud spanner databases ddl update example-db \
  --instance=test-instance \
  --ddl="ALTER TABLE Singers ALTER COLUMN LastName SET OPTIONS(locality_group = 'spill_to_hdd');"

gcloud spanner databases ddl update example-db \
  --instance=test-instance \
  --ddl="ALTER TABLE Singers ALTER COLUMN SET LOCALITY GROUP spill_to_hdd;"

Alter a locality group

You can alter a locality group by changing its storage option or changing its age-based policy.

Change the storage option

You can change the storage option of a locality group from SSD to HDD or HDD to SSD.

Console gcloud

1. On the Spanner Studio page, click New tab or use the empty editor tab.

2. Enter the ALTER LOCALITY GROUP DDL statement using GoogleSQL or PostgreSQL.

   For example, the following DDL statements change the storage option of the locality group, separate_storage, to HDD:

   GoogleSQL PostgreSQL
   ALTER LOCALITY GROUP separate_storage SET OPTIONS (storage='hdd');
   

   ALTER LOCALITY GROUP separate_storage STORAGE 'hdd';
   

3. Click Run.

To change the storage option of a locality group with the gcloud CLI command, use gcloud spanner databases ddl update.

For example, the following DDL statements change the storage option of the locality group, separate_storage, to HDD:

GoogleSQL PostgreSQL

gcloud spanner databases ddl update example-db \
  --instance=test-instance \
  --ddl="ALTER LOCALITY GROUP separate_storage SET OPTIONS (storage = 'hdd');"

gcloud spanner databases ddl update example-db \
  --instance=test-instance \
  --ddl="ALTER LOCALITY GROUP separate_storage STORAGE 'hdd';"

Change the age-based policy

You can change the age-based policy of a locality group by extending or shortening the time that data is stored in SSD before it's moved to HDD storage.

Console gcloud

1. On the Spanner Studio page, click New tab or use the empty editor tab.

2. Enter the ALTER LOCALITY GROUP DDL statement using GoogleSQL or PostgreSQL.

   For example, the following DDL statements change the age-based policy of the locality group, spill_to_hdd, by extending the amount of time that data is stored in SSD to 20 days:

   GoogleSQL PostgreSQL
   ALTER LOCALITY GROUP spill_to_hdd SET OPTIONS (ssd_to_hdd_spill_timespan = '20d');
   

   ALTER LOCALITY GROUP spill_to_hdd SSD_TO_HDD_SPILL_TIMESPAN '20d';
   

3. Click Run.

To change the age-based policy of a locality group with the gcloud CLI command, use gcloud spanner databases ddl update.

For example, the following DDL statements change the age-based policy of the locality group, spill_to_hdd, by extending the amount of time that data is stored in SSD to 20 days:

GoogleSQL PostgreSQL

gcloud spanner databases ddl update example-db \
  --instance=test-instance \
  --ddl="ALTER LOCALITY GROUP spill_to_hdd SET OPTIONS (ssd_to_hdd_spill_timespan = '20d');"

gcloud spanner databases ddl update example-db \
  --instance=test-instance \
  --ddl="ALTER LOCALITY GROUP spill_to_hdd SSD_TO_HDD_SPILL_TIMESPAN '20d';"

Delete a locality group

You can't delete a locality group if it contains data. You must first move all data that's in the locality group to another locality group. For more information, see Alter the locality group used by the table.

Console gcloud

1. On the Spanner Studio page, click New tab or use the empty editor tab.

2. Enter the DROP LOCALITY GROUP DDL statement using GoogleSQL or PostgreSQL.

   For example, you can run the following to drop a locality group ssd_only:

   GoogleSQL PostgreSQL
   DROP LOCALITY GROUP ssd_only;
   

   DROP LOCALITY GROUP ssd_only;
   

3. Click Run.

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

For example, to drop the locality group ssd_only, run:

GoogleSQL PostgreSQL

gcloud spanner databases ddl update example-db \
  --instance=test-instance \
  --ddl="DROP LOCALITY GROUP ssd_only"

gcloud spanner databases ddl update example-db \
  --instance=test-instance \
  --ddl="DROP LOCALITY GROUP ssd_only"

What's next

• Learn more about tiered storage.
• Learn more about locality groups.
• Learn more about optimizing queries with timestamp predicate pushdown.