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

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

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

Single-server

You can configure the columnar engine storage cache on either dedicated or shared devices.

Dedicated devices

On dedicated devices, to enable AlloyDB Omni columnar engine storage cache for a single-server container, you need to provision disks and create a file system, then mount the cache directory inside AlloyDB Omni, and finally enable the columnar storage cache.

Provision disks and create a file system

To provision disks and create a file system for AlloyDB Omni columnar engine storage cache, you create a file system on a disk or multiple disks and mount it inside a container with AlloyDB Omni. Additionally, you can use utilities like mdadm or lvm to pool capacity together using multiple disks and use any file system. The following steps demonstrate using lvm and ext4 on a Ubuntu Compute Engine instance using NVMe SSDs.

  1. Create a volume group from all available physical devices:

    nvme_prefix="STORAGE_PREFIX"
nvme_list=$(ls "$nvme_prefix"*)
sudo vgcreate VOLUME_GROUP ${nvme_list}

    Replace the following:

    • STORAGE_PREFIX: the prefix of the target local disks path that are attached to a virtual machine using the nonvolatile memory express (NVMe) interface—for example, on Google Cloud, the NVMe device paths always start with /dev/nvme0n.
    • VOLUME_GROUP: the name of a volume group where your SSDs are combined—for example, omni-disk-cache-volume.

  2. To create a logical volume from the free capacity of the volume group from the preceding step, use the following command:

    sudo lvcreate -n LOGICAL_VOLUME -l 100%FREE VOLUME_GROUP

    Replace LOGICAL_VOLUME with the name of a logical volume that is treated as a partition by the LVM—for example, omni_disk_cache_device.

  3. Create the ext4 file system on the logical volume. If needed, you can specify other ext4 options subject to data safety. 
    sudo mkfs.ext4 /dev/VOLUME_GROUP/LOGICAL_VOLUME

  4. To create a directory that serves as a mount point on the host machine and mount the file system, use the following command:

    sudo mkdir /OMNI_COLUMNAR_STORAGE_CACHE_DIRECTORY
sudo mount /dev/VOLUME_GROUP/LOGICAL_VOLUME /OMNI_COLUMNAR_STORAGE_CACHE_DIRECTORY

    Replace OMNI_COLUMNAR_STORAGE_CACHE_DIRECTORY with the name of the directory or a path to the directory serving as a mount point—for example, omni_columnar_storage_cache_directory.

Mount columnar storage cache directory inside AlloyDB Omni

Before enabling disk cache for AlloyDB Omni running in a container, you must mount the cache directory inside AlloyDB Omni.

For information about installing AlloyDB Omni from a Docker image and customizing it, see Customize your AlloyDB Omni installation.

To mount the OMNI_COLUMNAR_STORAGE_CACHE_DIRECTORY inside your Docker container running AlloyDB Omni, use the following command:

Docker

  sudo docker run --name CONTAINER_NAME 

    -e POSTGRES_PASSWORD=PASSWORD 

    -e PGDATA=/var/lib/postgresql/data/pgdata 

    -v DATA_DIR:/var/lib/postgresql/data 

    -v /OMNI_COLUMNAR_STORAGE_CACHE_DIRECTORY:/CACHE_DIRECTORY_PATH_INSIDE_CONTAINER 

    -d google/alloydbomni

Replace the following:

  • CONTAINER_NAME: the name to assign the new AlloyDB Omni container—for example, my-omni.
  • PASSWORD: the password for your PostgreSQL database root administrator.
  • DATA_DIR: the file system path that you want AlloyDB Omni to use for its data directory.
  • CACHE_DIRECTORY_PATH_INSIDE_CONTAINER: the cache directory inside the AlloyDB Omni container that maps to the mount point on the host machine—for example, based on the value of the cache directory inside the container, either /omni_disk_cache_directory, similar to OMNI_COLUMNAR_STORAGE_CACHE_DIRECTORY, or /disk/cache/inside/container.

Podman

  podman run --name CONTAINER_NAME 

    -e POSTGRES_PASSWORD=PASSWORD 

    -e PGDATA=/var/lib/postgresql/data/pgdata 

    -v DATA_DIR:/var/lib/postgresql/data 

    -v /OMNI_COLUMNAR_STORAGE_CACHE_DIRECTORY:/CACHE_DIRECTORY_PATH_INSIDE_CONTAINER 

    -d docker.io/google/alloydbomni

Replace the following:

  • CONTAINER_NAME: the name to assign the new AlloyDB Omni container—for example, my-omni.
  • PASSWORD: the password for your PostgreSQL database root administrator.
  • CACHE_DIRECTORY_PATH_INSIDE_CONTAINER: the cache directory inside the AlloyDB Omni container that maps to the mount point on the host machine—for example, based on the value of the cache directory inside the container, either /omni_columnar_storage_cache_directory, similar to OMNI_COLUMNAR_STORAGE_CACHE_DIRECTORY, or /disk/cache/inside/container.

To grant full access permissions to the mounted OMNI_COLUMNAR_STORAGE_CACHE_DIRECTORY, use the following commands:

Docker

  sudo docker exec -it CONTAINER_NAME chown postgres:postgres /CACHE_DIRECTORY_PATH_INSIDE_CONTAINER
  sudo docker exec -it CONTAINER_NAME chmod -R a+rw  /CACHE_DIRECTORY_PATH_INSIDE_CONTAINER

Podman

  sudo podman exec -it CONTAINER_NAME chown postgres:postgres /CACHE_DIRECTORY_PATH_INSIDE_CONTAINER
  sudo podman exec -it CONTAINER_NAME chmod -R a+rw  /CACHE_DIRECTORY_PATH_INSIDE_CONTAINER

Enable AlloyDB Omni columnar storage cache for AlloyDB Omni running in a container

To enable AlloyDB Omni columnar storage cache on dedicated devices for your database, set the appropriate Grand Unified Configuration (GUC) parameters after verifying that the mounted cache directory is accessible from inside the Docker container.

  1. Set the columnar storage cache directory:

    ALTER SYSTEM SET google_columnar_engine.omni_storage_cache_directory = OMNI_COLUMNAR_STORAGE_CACHE_DIRECTORY;

    Replace OMNI_COLUMNAR_STORAGE_CACHE_DIRECTORY with the absolute path to the dedicated directory where you want AlloyDB Omni to store columnar engine storage cache files. Verify this directory exists and has appropriate write permissions for the database user.

  2. Configure the storage cache size:

    ALTER SYSTEM SET google_columnar_engine.storage_cache_size = STORAGE_CACHE_SIZE;

    Replace STORAGE_CACHE_SIZE with the size of the columnar storage cache size you want, in megabytes. By default, the entire size of the dedicated device is allocated to the columnar engine, without affecting the disk cache. The maximum allowed value for this flag is 1000 * google_columnar_engine.memory_size_in_mb whichever is minimum.

  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

Shared devices

Before configuring the columnar storage cache to shared devices with disk cache, you must enable AlloyDB Omni disk cache.

Configure storage cache on shared devices as follows:

  1. Configure storage cache:

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

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.

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