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.

After you set this flag, the instance automatically restarts.

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

If the column store becomes larger than the allocated portion of your instance's memory, AlloyDB automatically uses the instance's underlying cache layer to store the additional columnar data.

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 maximum of 50%. To find the recommended columnar engine memory size for your instance, see Recommend column store memory size.

You can also you 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 storage cache for the columnar engine

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

Instance tier Default columnar engine cache size
Primary instance Read replica instance
2-vCPU 18.75 GB 37.5 GB
4-vCPU 18.75 GB 37.5 GB
8-vCPU 18.75 GB 37.5 GB
16-vCPU 37.5 GB 37.5 GB
32-vCPU 75 GB 75 GB
64-vCPU 150 GB 150 GB
96-vCPU 300 GB 300 GB
128-vCPU 450 GB 450 GB

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:

Instance tier Minimum columnar engine cache size Maximum columnar engine cache size
Primary instance Read replica instance
2-vCPU 0 187.5 GB 375 GB
4-vCPU 0 187.5 GB 375 GB
8-vCPU 0 187.5 GB 375 GB
16-vCPU 0 375 GB 375 GB
32-vCPU 0 750 GB 750 GB
64-vCPU 0 1500 GB 1500 GB
96-vCPU 0 3000 GB 3000 GB
128-vCPU 0 4500 GB 4500 GB

Monitor storage cache data for the columnar engine

To view information about the columnar engine's storage cache, you can run one of the following SQL queries.

SQL query Description
SELECT google_columnar_engine_storage_cache_used(); Returns the size of the used columnar engine storage cache.
SELECT google_columnar_engine_storage_cache_available(); Returns the size of the unused columnar engine storage cache.
SELECT google_columnar_engine_storage_cache_total(); Returns the total amount of the configured size of storage cache for the columnar engine.

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

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

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, the columnar engine is set to automatically refresh the column store in the background when enabled. You may need to manually refresh the columnar store in certain situations, such as if auto-refresh doesn't refresh a relation with a high number of invalid blocks.

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.

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

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 being met.

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

  • Review your instance's audit logs.

  • Confirm that the tables or materialized views in our query are in the columnar engine.

  • Verify the usage of the columnar engine using the EXPLAIN statement.

What's next