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.
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.
Enable the columnar engine
To use columnar engine on the primary instance or a read pool
instance, you'll need to set the instance's
google_columnar_engine.enabled
flag to on
. To enable the columnar engine using the Google Cloud console,
follow these steps:
Console
In the Google Cloud console, go to the Clusters page.
Click a cluster in the Resource Name column.
In the Overview page, go to Instances in your cluster and then click Edit primary in the box for the primary instance.
In the Edit primary instance panel, click Add flag.
Select the google_columnar_engine.enabled flag from the New database flag list.
Set the value to on.
Click Done.
Click Update instance.
After you set this flag, the instance automatically restarts.
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.
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 this 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.
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
.
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.
For more information about setting database flags, see Configure an instance's database flags.
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 command:
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 columnar engine using the Google Cloud console, follow these steps:
Console
In the Google Cloud console, go to the Clusters page.
Click a cluster in the Resource Name column.
In the Overview page, go to Instances in your cluster and then click Edit primary in the box for the primary instance.
In the Edit primary instance panel, click Add flag.
Select the google_columnar_engine.enabled flag from the New database flag list.
Set the value to off.
Click Done.
Click Update instance.
After you disable this flag, the instance automatically restarts.
Troubleshoot the columnar engine
Issue | Troubleshooting |
---|---|
Columns are not getting populated. | If you're not seeing columns getting
populated in the columnar engine, there
is either an unsupported data type or
the requirements of the columnar engine
aren't being met. To find the cause of
this issue, review your instance's
audit logs.
Confirm that the tables or materialized views in your query are in the columnar engine. Verify the usage of the columnar engine using the EXPLAIN
statement. |
What's next
Learn about auto-columnarization.