/

Console

• English
• Deutsch
• Español
• Español – América Latina
• Français
• Indonesia
• Italiano
• Português
• Português – Brasil
• עברית
• 中文 – 简体
• 中文 – 繁體
• 日本語
• 한국어

Sign in

• AlloyDB for PostgreSQL

Start free

Configure the columnar engine

This page describes how to enable or disable the columnar engine on an AlloyDB for PostgreSQL instance. It also covers how to configure an appropriate initial size for its column store, as well as memory and storage cache settings for optimal performance.

To use the columnar engine when using AlloyDB Omni, see Enable the columnar engine in AlloyDB Omni.

Each of these actions involves changing the value of a database flag on one of your AlloyDB instances. For more information about setting database flags, see Configure an instance's database flags.

For a conceptual overview of the AlloyDB columnar engine, see About the AlloyDB columnar engine.

Enable the columnar engine

To use columnar engine on an instance, set the instance's google_columnar_engine.enabled flag to on.

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

Configure the size of the column store

While the columnar engine is enabled on an instance, AlloyDB allocates a portion of the instance's memory to store its columnar data. Dedicating high-speed RAM to your column store ensures that AlloyDB can access the columnar data as rapidly as possible.

Memory and storage cache together represent the overall capacity of the columnar engine.

Configure memory for the columnar engine

In AlloyDB, you can load both relations, materialized views, and ScaNN indexes into the columnar engine to accelerate query performance.

By default, AlloyDB allocates 30% of the instance's memory to the column store, and automatically adjusts the total allocation if you resize the instance. You can modify the instance's memory to a recommended maximum of 50%, although 70% is allowed. To find the recommended columnar engine memory size for your instance, see Recommend column store memory size.

You can also set the allocation to a fixed and specific size using the google_columnar_engine.memory_size_in_mb flag. To have AlloyDB return to the default allocation, remove the flag from the instance.

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

Configure the loading of ScaNN indexes into the columnar engine

The columnar engine accelerates vector search when ScaNN indexes are populated into it.

To enable the columnar index cache, set the google_columnar_engine.enable_index_caching flag to on.

To disable this feature, set the flag to off.

Configure storage cache for the columnar engine

You must size the google_columnar_engine.storage_cache_size relative to the google_columnar_engine.memory_size_in_mb setting because the columnar engine memory is used for in-memory metadata for the storage cache. The recommended sizing ratio is approximately 100 to 1000 times the memory size. For example, with a google_columnar_engine.memory_size_in_mb of 1024 (1 GB), you must set google_columnar_engine.storage_cache_size between 100 GB and 1000 GB.

The columnar engine's storage cache is provisioned according to the instance tier.

The google_columnar_engine.storage_cache_size flag lets you adjust the columnar engine storage cache, with the limitations of the minimum and maximum values listed in the following table:

Monitor storage cache data for the columnar engine

To view and monitor data about the storage cache of the columnar engine, you can use the following commands:

Enable vectorized join

The columnar engine has a vectorized join feature that can improve the performance of joins by applying vectorized processing to qualifying queries.

After you enable the vectorized join, the AlloyDB query planner has the option to apply the vectorized join operator instead of the standard PostgreSQL hash join operator. The planner makes this decision by comparing the cost of executing the query using either method.

To enable vectorized join on an instance, set the instance's google_columnar_engine.enable_vectorized_join flag to on.

AlloyDB allocates one thread to the vectorized join feature by default. You can increase the number of threads available to this feature by setting the google_columnar_engine.vectorized_join_threads flag to a larger value.

Manually refresh your columnar engine

By default, when you enable the columnar engine, it is set to automatically refresh the column store in the background.

For workloads with a high rate of data modifications such as frequent INSERT, UPDATE, or DELETE operations, you may manually refresh the columnar store after these changes to keep the index up-to-date.

To manually refresh the column engine, run the following SQL query:

SELECT google_columnar_engine_refresh('TABLE_NAME');

Replace TABLE_NAME with the name of the table or the materialized view you want to manually refresh.

Disable the columnar engine

To disable the columbar engine on an instance, set the google_columnar_engine.enabled flag to off.

After you set this flag, the instance automatically restarts.

Troubleshoot the columnar engine

If columns don't populate in the columnar engine, then one of the following situations might be true:

• The columns you want to add include an unsupported data type.

• The requirements of the columnar engine aren't met.

To find the cause of this issue, try the following:

Review your instance's audit logs.

What's next

• See the full list of columnar engine database flags.

• Learn about auto-columnarization.

• Learn how to accelerate analytical queries using the AlloyDB columnar engine.