Configure Spanner columnar engine

This page describes how to enable Spanner columnar engine on a database, table, or index, and accelerate file format generation.

Enable Spanner columnar engine (Google SQL)

Spanner columnar engine is enabled using a SET OPTIONS clause with the columnar_policy option. You can apply this option when you create or alter DATABASE, TABLE, or INDEX schema objects. SEARCH INDEX and VECTOR INDEX schema objects are never in columnar format.

Tables and indexes inherit the columnar_policy specified at the database level.

The columnar_policy option has the following flags:

  • 'enabled' or 'disabled' turns the columnar engine on or off for the specific schema object.
  • NULL (default) uses the columnar engine policy from the parent object, if one exists. NULL clears previous settings on a table object.

You can also omit OPTIONS to inherit the columnar_policy from the parent object.

The following example shows how to:

  • Create a database with the columnar policy enabled.
  • Define a Singers table that inherits the columnar policy from the database (omit the columnar_policy = NULL for the table option).
  • Define a Concerts table with the columnar policy explicitly disabled.
CREATE DATABASE Music;

ALTER DATABASE Music SET OPTIONS (columnar_policy = 'enabled');

CREATE TABLE Singers(
  SingerId INT64 NOT NULL,
  FirstName STRING(1024),
  LastName STRING(1024),
  BirthDate DATE,
  Status STRING(1024),
  LastUpdated TIMESTAMP,)
  PRIMARY KEY(SingerId);

CREATE TABLE Concerts(
  VenueId INT64 NOT NULL,
  SingerId INT64 NOT NULL,
  ConcertDate DATE NOT NULL,
  BeginTime TIMESTAMP,
  EndTime TIMESTAMP,)
  PRIMARY KEY(VenueId, SingerId, ConcertDate),
  OPTIONS (columnar_policy = 'disabled');

You can also use ALTER TABLE with the SET OPTIONS clause to enable or disable the columnar_policy on a table. The following example shows how to disable the policy in the Singers table:

ALTER TABLE Singers SET OPTIONS (columnar_policy = 'disabled');

Enable Spanner columnar engine (Postgres)

You can enable the Spanner columnar engine for all tables and indexes by issuing this statement:

ALTER DATABASE db_name SET spanner.columnar_policy TO enabled

You can enable the Spanner columnar engine for specific tables and indexes by adding the COLUMNAR POLICY enabled option when creating or altering a TABLE or INDEX schema object.