For a conceptual overview of the AlloyDB columnar engine, see AlloyDB Omni columnar engine overview .
Enable the columnar engine
To use columnar engine on an instance, set the instance's google_columnar_engine.enabled
flag to on
.
Kubernetes
To set the google_columnar_engine.enabled
flag to on
, modify your database cluster manifest to add the parameters
attribute to the primarySpec
section:
apiVersion
:
alloydbomni.dbadmin.goog/v1
kind
:
DBCluster
metadata
:
name
:
CLUSTER_NAME
spec
:
databaseVersion
:
"16.8.0"
primarySpec
:
parameters
:
google_columnar_engine.enabled
:
"on"
Replace CLUSTER_NAME
with the name of your database cluster. It is the same database cluster name you declared when you created it
.
Configure the size of the column store
While the columnar engine is enabled on an instance, AlloyDB Omni allocates a portion of the instance's memory to store its columnar data. Dedicating high-speed RAM to your column store verifies that AlloyDB Omni can access the columnar data as rapidly as possible.
Memory and storage cache together represent the overall capacity of the columnar engine.
Configure memory
You can set the allocation to a fixed size using the google_columnar_engine.memory_size_in_mb
flag.
Kubernetes
To set the google_columnar_engine.memory_size_in_mb
flag, modify your database cluster manifest to add the parameters
attribute to the primarySpec
section:
apiVersion
:
alloydbomni.dbadmin.goog/v1
kind
:
DBCluster
metadata
:
name
:
CLUSTER_NAME
spec
:
databaseVersion
:
"16.8.0"
primarySpec
:
parameters
:
google_columnar_engine.memory_size_in_mb
:
" COLUMN_MEMORY_SIZE
"
Replace the following:
-
CLUSTER_NAME: the name of your database cluster. It is the same database cluster name you declared when you created it . -
COLUMN_MEMORY_SIZE: the new size of the column storage, in megabytes—for example,256.
Configure storage cache
Kubernetes
To enable storage cache for your database, modify your database cluster manifest to add the columnarSpillToDisk
attribute to the features
section of the primarySpec
section:
apiVersion
:
alloydbomni.dbadmin.goog/v1
kind
:
DBCluster
metadata
:
name
:
CLUSTER_NAME
spec
:
databaseVersion
:
"16.8.0"
primarySpec
:
features
:
columnarSpillToDisk
:
cacheSize
:
STORAGE_CACHE_SIZE
ultraFastCache
:
cacheSize
:
ULTRAFAST_CACHE_SIZE
genericVolume
:
storageClass
:
" STORAGE_CLASS_NAME
"
...
Replace the following:
-
CLUSTER_NAME: the name of your database cluster. It is the same database cluster name you declared when you created it . -
STORAGE_CACHE_SIZE: the size of the columnar storage cache—for example,5Gi. If you don't specify a value for this field, 5% of the disk cache gets allocated to the columnar engine by default. -
ULTRAFAST_CACHE_SIZE: the size of the cache—for example,100Gi. It must be greater thanshared_buffers. This field is optional. If you don't specify the value of this field, AlloyDB Omni uses all space left on the disk, which applies to both AlloyDB Omni in a container and on a Kubernetes cluster. For more information about units of measure, see Memory resource units . -
STORAGE_CLASS_NAME: the name of the storage class of the ultra fast cache volume—for example,local-storage.
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 each 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 Omni 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. The maximum value is cpu_count * 2
.
Manually refresh your columnar engine
By default, when the columnar engine is enabled it refreshes the column store in the background.
To manually refresh the column engine, run the following SQL query:
SELECT
google_columnar_engine_refresh
(
relation
=
> ' 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
.
Kubernetes
To set the google_columnar_engine.enabled
flag to off
, modify your database cluster manifest to add the parameters
attribute to the primarySpec
section:
apiVersion
:
alloydbomni.dbadmin.goog/v1
kind
:
DBCluster
metadata
:
name
:
CLUSTER_NAME
spec
:
databaseVersion
:
"16.8.0"
primarySpec
:
parameters
:
google_columnar_engine.enabled
:
"off"
Replace CLUSTER_NAME
with the name of your database cluster. It is the same database cluster name you declared when you created it
.
What's next
-
Work through the Accelerating analytical queries with columnar engine in AlloyDB Omni Google CodeLab tutorial.
