Manage column store content manually

This page describes how to manage the content of the columnar engine's column store by manually adding and removing columns.

Based on your workload, you can manually add columns to the column store or drop some or all of the columns of a table from the column store. Query evaluation automatically uses the stored columnar data to answer queries.

When choosing which tables and columns to add to the column store, consider both the size of the column store and the shape of the workload. Good candidates for selection include large tables that are frequently scanned. Within these tables, identify any large non-unique indexes used by the OLAP workload. You can add these indexes' columns to the column store and potentially drop the indexes, thereby eliminating the performance cost associated with their maintenance on the primary instance.

Use one of the following methods to manage the content of the columnar engine's column store:

Manage column store content by updating database flags
Manage column store content using SQL functions

See What data you can add to the column store for information about what data types and data sources you can use when adding tables and columns to the column store.

Before you begin

You must have one of these IAM roles in the Cloud project you are using:
- roles/alloydb.admin (the AlloyDB Admin predefined IAM role)
- roles/owner (the Owner basic IAM role)
- roles/editor (the Editor basic IAM role)
If you don't have any of these roles, contact your Organization Administrator to request access.

Manage column store content by updating database flags

You can manage column store content manually by updating the google_columnar_engine.relations database flag. The flag has a single value that specifies all of your column store's data sources. During the restart, columns specified in this flag are automatically populated into the column store.

You can use this flag together with auto-columnarization. If the columnar engine has memory available after populating the columns you specify through the google_columnar_engine.relations flag, then auto-columnarization adds further columns to the column store, as needed.

For more information on setting an instance's database flags using either Google Cloud CLI or the Google Cloud console, see Configure an instance's database flags.

Add columns using flags

To add columns to the column store, define an instance's google_columnar_engine.relations flag. Set its value to a comma-separated list of items, with each item specifying a list of columns to include from a specific table, in this format:

DATABASE_NAME.SCHEMA_NAME.TABLE_NAME(COLUMN_LIST)

DATABASE_NAME, SCHEMA_NAME, and TABLE_NAME identify the table containing the columns. COLUMN_LIST is a comma-separated list of the names of the columns to add to the column store. To add all of the table's columns, omit the parentheses and COLUMN_LIST.

The following example adds large_table's columns col1 and col2 to the column store. The table is from the database named my_db and the schema named public.

gcloud alloydb instances update my-instance \
    --database-flags=^:^\
         google_columnar_engine.relations='my_db.public.large_table(col1,col2)'\
         :google_columnar_engine=on\
         :[ ... ] \
    --region=us-central1 \
    --cluster=my-cluster \

Note the use of alternate delimiter syntax, which lets you use comma characters within a Google Cloud CLI argument value.

Drop columns using flags

To drop columns from the column store, set a new value for the google_columnar_engine.relations flag described in the previous section, leaving out the columns that you wish to drop.

To drop all of the columns from the column store, unset the google_columnar_engine.relations flag from your instance.

The following example amends the column store defined in the previous example, specifying only the column col1. This effectively drops col2 from the column store.

gcloud alloydb instances update my-instance \
    --database-flags=^:^\
         google_columnar_engine.relations='my_db.public.large_table(col1)'\
         :google_columnar_engine=on\
         :[ ... ]\
    --region=us-central1 \
    --cluster=my-cluster \

Manage column store content using SQL functions

You can manage column store content manually by using SQL functions.

Add columns using SQL functions

Run the google_columnar_engine_add SQL function to add columns to the column store.

This method adds the specified columns to the column store and manages the columns in the connected node only. The new columns do not persist within the store across instance restarts.

This method does not change the google_columnar_engine.relations database flag. Auto columnarization does not consider columns added by this SQL function.

psql Client

SELECT google_columnar_engine_add(
    'TABLE_NAME',
    'COLUMN_LIST'
);

'TABLE_NAME': A string containing the name of the table. If the table is in a schema other than public, specify the name of the schema, a dot, and the name of the table; for example, 'myschema.mytable'.
'COLUMN_LIST': A string containing a comma-separated list of the names of the columns you want to add. If you want to add all of the table's columns to the column store, omit this parameter.

Drop columns using SQL functions

Run the google_columnar_engine_drop SQL function as follows:

psql Client

SELECT google_columnar_engine_drop(
    'TABLE_NAME',
    'COLUMN_LIST'
);

Replace the following:

'TABLE_NAME': A string containing the name of the table. If the table is in a schema other than public, specify the name of the schema, a dot, and the name of the table; for example, 'myschema.mytable'.
'COLUMN_LIST': A string containing a comma-separated list of the names of the columns you want to add. If you want to add all of the table's columns to the column store, omit this parameter.

You can use the google_columnar_engine_drop function to remove columns added by editing the google_columnar_engine.relations database flag. However, when you do so, those columns will be added back to the columnar store when the instance is restarted.