Manage column store content using auto-columnarization

This page describes how to manage the content of the columnar engine's column store using auto-columnarization.

By using auto-columnarization, the columnar engine automatically columnarizes the data in the column store and improves the overall performance of your hybrid transactional and analytical processing (HTAP) and online analytical processing (OLAP) workloads.

The columnar engine samples an application workload and analyzes queries to determine which columns are good hypothetical candidates for inclusion in the column store. It also populates columns automatically to the column store that provides the best performance gain after considering the current column store memory size.

New instances have auto-columnarization enabled by default, configured to recommend and populate columns to the column store once every hour. You can either run auto-columnarization immediately or change the default schedule of every hour to a desired time interval to run auto-columnarization.

When an instance restarts, the columnar engine clears the previously recommended and populated columns, and restarts auto-columnarization.

After auto-columnarization recommends columns, you can review the results of the recommendations by viewing recommended columns.

Run auto-columnarization immediately

You can run auto-columnariation immediately before the default schedule takes effect.

To run auto-columnarization immediately:

psql Client

SELECT google_columnar_engine_recommend();

Change the default schedule for auto-columnarization

To change an instance's default schedule to run auto-columnarization, set that instance's google_columnar_engine.auto_columnarization_schedule flag to a new time interval.

The flag takes the value EVERY NUMBER {HOURS|DAYS}. For example: EVERY 12 HOURS, or EVERY 2 DAYS.

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

You can view information about the size of recommended columns, list of recommended columns, and recommendation schedule.

To view the list of recommended columns:

psql Client

SELECT database_name, schema_name, relation_name, column_name FROM g_columnar_recommended_columns;

To view the column recommendation schedule:

psql Client

SELECT * FROM g_columnar_schedules;

Reset auto-columnarization and remove recommended columns

You can reset auto-columnarization, including all data it has gathered to generate recommendations.

This removes columns that were automatically added to the column store by auto-columnarization.

To reset auto-columnarization:

psql Client

SELECT google_columnar_engine_reset_recommendation(drop_columns => true);

Recommend column store memory size

The columnar engine can recommend the best column store memory size along with the list of columns for your workload that provides the highest performance gains.

You can update google_columnar_engine.memory_size_in_mb based on the recommendations.

To recommend the memory size for the column store:

psql Client

SELECT * FROM google_columnar_engine_recommend(mode => 'RECOMMEND_SIZE');

Disable auto-columnarization

To disable auto-columnarization on a primary or read pool instance, set the instance's google_columnar_engine.enable_auto_columnarization flag to off.

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