Spanner: Column-Level Locality Groups

Spanner columns can now declare their own locality group in an options block, overriding their table's, and Atlas plans the SET OPTIONS statements that move a column between groups.

Atlas now manages Spanner locality groups at column granularity. A column declares its group in an options block, overriding the group of its table, so hot and cold data can live on different storage inside one table.

Declaring a Column's Group

The table below is in the hot group, while its Bio column overrides that and sits in cold:

locality "hot" {  storage = "ssd"}locality "cold" {  storage = "hdd"}table "Singers" {  schema         = schema.default  locality_group = locality.hot  column "SingerId" {    null = false    type = STRING(36)  }  column "Bio" {    null = true    type = STRING(1024)    options {      locality_group = locality.cold    }  }  primary_key {    columns = [column.SingerId]  }}

-- Add locality group "hot"CREATE LOCALITY GROUP `hot` OPTIONS (storage = 'ssd');-- Add locality group "cold"CREATE LOCALITY GROUP `cold` OPTIONS (storage = 'hdd');-- Create "Singers" tableCREATE TABLE `Singers` (  `SingerId` STRING(36) NOT NULL,  `Bio` STRING(1024) OPTIONS (locality_group = 'cold')) PRIMARY KEY (`SingerId`),OPTIONS (locality_group = 'hot');

A table does not need a group of its own. A column on a table without one can still declare a group, and the rest of the table stays on the default storage.

Moving Between Groups

Changing a group is planned in place, for the table and for each column that overrides it:

-- Modify locality group in table "Singers"ALTER TABLE `Singers` SET OPTIONS (locality_group = 'cold');-- Modify column "Bio" in table "Singers"ALTER TABLE `Singers` ALTER COLUMN `Bio` SET OPTIONS (locality_group = 'hot');

Dropping the options block returns the column to the group of its table:

-- Modify column "Bio" in table "Singers"ALTER TABLE `Singers` ALTER COLUMN `Bio` SET OPTIONS (locality_group = null);

Things to Know

• Setting a column to the same locality group as its table causes no change, because Spanner reports it the same as inheriting the table's group.
• Changing a column's type and locality group requires two statements, because locality group changes use a separate SET OPTIONS statement.
• When dropping a table and a locality group used by one of its columns, the table is dropped first.

Getting Started

Spanner support is part of Atlas Pro, and locality groups require the Enterprise edition of Spanner. Run atlas login, add an options block to the columns you want to place, and run atlas migrate diff or atlas schema apply. See the Spanner HCL reference for the locality block and its storage options.