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
.
Run the
ALTER SYSTEM
PostgreSQL command:ALTER SYSTEM SET google_columnar_engine.enabled = 'on'
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.
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:
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.
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
.
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
.
Run the
ALTER SYSTEM
PostgreSQL command:ALTER SYSTEM SET google_columnar_engine.enabled = 'off'
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.
macOS
Install a new AlloyDB Omni container, specifying
a larger shared-memory value for the --shm-size
flag.
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
Learn about auto-columnarization.