Configure the columnar engine in AlloyDB Omni

This page describes how to enable or disable the columnar engine on an AlloyDB Omni database cluster. It also covers how to configure an appropriate initial size for its column store.

To use the columnar engine when using AlloyDB for PostgreSQL on Google Cloud, see Configure the columnar engine.

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.

To set this flag on an instance, do the following:

1. Run the ALTER SYSTEM PostgreSQL command:

   ALTER SYSTEM SET google_columnar_engine.enabled = 'on'
   

2. If you want to adjust the columnar engine's configuration, then follow the instructions in the next section before you restart the database server. Otherwise, follow the next two steps to restart the database server now.

3. Stop the database server.

4. Start the database server.

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.

You can also you set the allocation to a fixed and specific size using the google_columnar_engine.memory_size_in_mb flag. To set this flag on an instance, do the following:

1. Run the ALTER SYSTEM PostgreSQL command:

   ALTER SYSTEM SET google_columnar_engine.memory_size_in_mb = COLUMN_STORE_SIZE;
   

   Replace COLUMN_STORE_SIZE with the new size of the column store, in megabytes.

2. Stop the database server.

3. Start the database server.

Enable vectorized join

Preview

This product or feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of the Service Specific Terms. Pre-GA products and features are available "as is" and might have limited support. For more information, see the launch stage descriptions.

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.

To set this flag on an instance, run the ALTER SYSTEM PostgreSQL command:

ALTER SYSTEM SET google_columnar_engine.enable_vectorized_join = '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, 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.

To set this flag on an instance, do the following:

1. Run the ALTER SYSTEM PostgreSQL command:

   ALTER SYSTEM SET google_columnar_engine.enabled = 'off'
   

2. Stop the database server.

3. Start the database server.

Troubleshoot the columnar engine

Fix an "insufficient shared memory" error

If you run AlloyDB Omni without enough shared memory for the columnar engine to use, then you might see this error:

Insufficient shared memory for generating the columnar formats.

You can address this issue by specifying the amount of shared memory that is available to the AlloyDB Omni container. The way that you do this differs depending upon your host operating system

Linux

Increase the size of your host machine's /dev/shm partition, using a technique such as editing your /etc/fstab file.

Note: This requires that you installed AlloyDB Omni with your /dev/shm directory mounted on the container, as shown in Customize your AlloyDB Omni installation.

macOS

Install a new AlloyDB Omni container, specifying a larger shared-memory value for the --shm-size flag.

Caution: Back up your data before switching to using a new container.

Fix columns not getting populated

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:

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

• See the full list of columnar engine database flags.

• Learn about auto-columnarization.