Manage column store content manually

Stay organized with collections Save and categorize content based on your preferences.

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

Prerequisites

  • 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.

Add columns to the column store

Based on your workload, you can manually add some or all of the columns of a table to 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. 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.

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.

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.

For more information on setting an instance's database flags, see Configure an instance's database flags.

Examples

This example adds big_table's columns col1 and col2 to the column store. The table is from the database postgres and the schema public.

gcloud beta alloydb instances update my-instance \
    --database-flags=^:^google_columnar_engine.relations='postgres.public.big_table(col1,col2)[:flag2=value2...]' \
    --region=us-central1 \
    --cluster=my-cluster \
    --project=my-project

This example modifies google_columnar_engine.relations.

gcloud beta alloydb instances update my-instance \
    --database-flags=^:^google_columnar_engine.relations='postgres.public.big_table(col1,col2)[:flag2=value2...]' \
    --region=us-central1 \
    --cluster=my-cluster \
    --project=my-project

Note the use of alternate delimiter syntax, which lets you use comma characters within a flag value.

Remove columns from the column store

Remove columns manually added to the column store by removing their specification from the google_columnar_engine.relations database flag.