Create and manage locality groups

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

  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 

    CREATE LOCALITY GROUP separate_storage;

    PostgreSQL 

    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 

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

    PostgreSQL 

    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 

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

    PostgreSQL 

    CREATE LOCALITY GROUP hdd_only STORAGE 'hdd';

  7. Click Run.

gcloud

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 

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

PostgreSQL 

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 

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

PostgreSQL 

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 

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

PostgreSQL 

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. This time is relative to the data's commit timestamp. 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

  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 

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

    PostgreSQL 

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

  3. Click Run.

gcloud

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 

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')"

PostgreSQL 

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

  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 

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

    PostgreSQL 

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

  3. Click Run.

gcloud

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 

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');"

PostgreSQL 

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

  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 

    CREATE TABLE Singers (
  SingerId   INT64 NOT NULL,
  FirstName  STRING(1024),
  LastName   STRING(