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.

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.

The steps you use to set this flag on an instance depend on whether you run AlloyDB Omni in a container or on a Kubernetes cluster.

Single-server

To set the google_columnar_engine.enabled to on, do the following steps:

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, complete the following step to restart the database server now.

3. For the configuration parameters change to take effect, restart your running container with AlloyDB Omni.

   Docker

     sudo docker restart CONTAINER_NAME

   Podman

     sudo podman restart CONTAINER_NAME

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: "15.7.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 ensures 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.

The steps you use to configure memory of the column store for AlloyDB Omni depend on whether you run AlloyDB Omni in a container or on a Kubernetes cluster.

Single-server

To set the google_columnar_engine.memory_size_in_mb flag on an instance, do the following:

1. Configure memory by running the ALTER SYSTEM PostgreSQL command:

   ALTER SYSTEM SET google_columnar_engine.memory_size_in_mb = COLUMN_MEMORY_SIZE;
   

   Replace COLUMN_MEMORY_SIZE with the new size of the column storage, in megabytes—for example, 256.

2. For the configuration parameters change to take effect, restart your running container with AlloyDB Omni.

   Docker

   sudo docker restart CONTAINER_NAME

   Podman

   sudo podman restart CONTAINER_NAME

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: "15.7.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

Before configuring storage cache, you must enable AlloyDB Omni disk cache.

Note: This step is optional for enabling the columnar engine.

The steps you use to enable storage cache for AlloyDB Omni depend on whether you run AlloyDB Omni in a container or on a Kubernetes cluster.

Single-server

1. Configure storage cache as follows:

   ALTER SYSTEM SET google_columnar_engine.storage_cache_size = STORAGE_CACHE_SIZE;
   

   Replace STORAGE_CACHE_SIZE with the size of the storage cache size you want, in megabytes. By default, 5% of the disk cache is allocated to the columnar engine. The maximum allowed value for this flag is 50% of the total disk cache or 1000 * google_columnar_engine.memory_size_in_mb whichever is minimum.

2. For the configuration parameters change to take effect, restart your running container with AlloyDB Omni.

   Docker

   sudo docker restart CONTAINER_NAME

   Podman

   sudo podman restart CONTAINER_NAME

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: "15.7.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 than shared_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.

Note: If both spec.primarySpec.features.ultrafastCache.cacheSize and spec.primarySpec.features.columnarSpillToDisk.cacheSize are specified, their total sum equals the amount of reserved disk space. For example, if you specify 50 Gi for spec.primarySpec.features.columnarSpillToDisk.cacheSize and 200 Gi for spec.primarySpec.features.ultrafastCache.cacheSize, the total reserved disk space is 250Gi.

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

The steps you use to set this flag on an instance depend on whether you run AlloyDB Omni in a container on a single server, or on a Kubernetes cluster.

Single-server

To set the google_columnar_engine.enabled to off, do the following steps:

1. Run the ALTER SYSTEM PostgreSQL command:

ALTER SYSTEM SET google_columnar_engine.enabled = 'off'

1. For the configuration parameters change to take effect, restart your running container with AlloyDB Omni.

Docker

To restart an AlloyDB Omni container, run the docker container restart command:

  sudo docker restart CONTAINER_NAME

Replace CONTAINER_NAME with the name that you assigned to the AlloyDB Omni container when you installed it.

Podman

To restart an AlloyDB Omni container, run the podman container start command:

  sudo podman restart CONTAINER_NAME

Replace CONTAINER_NAME with the name that you assigned to the AlloyDB Omni container when you installed it.

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: "15.7.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.

Troubleshoot the columnar engine

Fix the 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 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 troubleshoot this issue, try the following:

• Confirm that the tables or materialized views in the 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.

• Work through the Accelerating analytical queries with columnar engine in AlloyDB Omni Google CodeLab tutorial.