Select a documentation version: <button class="button button-with-icon cloud-select-dropdown__button button-primary" aria-haspopup="true">15.15.0</button>

- [Current (18.4.0)](https://docs.cloud.google.com/alloydb/omni/containers/current/docs/columnar-engine/configure.md.txt)
- [18.4.0](https://docs.cloud.google.com/alloydb/omni/containers/18.4.0/docs/columnar-engine/configure.md.txt)
- [18.3.0](https://docs.cloud.google.com/alloydb/omni/containers/18.3.0/docs/columnar-engine/configure.md.txt)
- [18.1.0](https://docs.cloud.google.com/alloydb/omni/containers/18.1.0/docs/columnar-engine/configure.md.txt)
- [17.10.0](https://docs.cloud.google.com/alloydb/omni/containers/17.10.0/docs/columnar-engine/configure.md.txt)
- [17.9.0](https://docs.cloud.google.com/alloydb/omni/containers/17.9.0/docs/columnar-engine/configure.md.txt)
- [17.7.0](https://docs.cloud.google.com/alloydb/omni/containers/17.7.0/docs/columnar-engine/configure.md.txt)
- [17.5.0](https://docs.cloud.google.com/alloydb/omni/containers/17.5.0/docs/columnar-engine/configure.md.txt)
- [16.14.0](https://docs.cloud.google.com/alloydb/omni/containers/16.14.0/docs/columnar-engine/configure.md.txt)
- [16.13.0](https://docs.cloud.google.com/alloydb/omni/containers/16.13.0/docs/columnar-engine/configure.md.txt)
- [16.11.0](https://docs.cloud.google.com/alloydb/omni/containers/16.11.0/docs/columnar-engine/configure.md.txt)
- [16.9.0](https://docs.cloud.google.com/alloydb/omni/containers/16.9.0/docs/columnar-engine/configure.md.txt)
- [16.8.0](https://docs.cloud.google.com/alloydb/omni/containers/16.8.0/docs/columnar-engine/configure.md.txt)
- [16.3.0](https://docs.cloud.google.com/alloydb/omni/containers/16.3.0/docs/columnar-engine/configure.md.txt)
- [15.18.0](https://docs.cloud.google.com/alloydb/omni/containers/15.18.0/docs/columnar-engine/configure.md.txt)
- [15.17.0](https://docs.cloud.google.com/alloydb/omni/containers/15.17.0/docs/columnar-engine/configure.md.txt)
- [15.15.0](https://docs.cloud.google.com/alloydb/omni/containers/15.15.0/docs/columnar-engine/configure.md.txt)
- [15.13.0](https://docs.cloud.google.com/alloydb/omni/containers/15.13.0/docs/columnar-engine/configure.md.txt)
- [15.12.0](https://docs.cloud.google.com/alloydb/omni/containers/15.12.0/docs/columnar-engine/configure.md.txt)
- [15.7.1](https://docs.cloud.google.com/alloydb/omni/containers/15.7.1/docs/columnar-engine/configure.md.txt)
- [15.7.0](https://docs.cloud.google.com/alloydb/omni/containers/15.7.0/docs/columnar-engine/configure.md.txt)
- [15.5.5](https://docs.cloud.google.com/alloydb/omni/containers/15.5.5/docs/columnar-engine/configure.md.txt)
- [15.5.4](https://docs.cloud.google.com/alloydb/omni/containers/15.5.4/docs/columnar-engine/configure.md.txt)

<br />

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.

<br />

For a conceptual overview of the AlloyDB columnar engine, see
[AlloyDB Omni columnar engine overview](https://docs.cloud.google.com/alloydb/omni/containers/15.15.0/docs/columnar-engine/overview.md.txt).

## Enable the columnar engine

To use columnar engine on an instance, set the instance's
[`google_columnar_engine.enabled`](https://docs.cloud.google.com/alloydb/omni/containers/15.15.0/docs/reference/columnar-engine-flags.md.txt#enabled)
flag to `on`.

### Single-server


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

1. Run [the `ALTER SYSTEM` PostgreSQL command](https://www.postgresql.org/docs/current/sql-altersystem.html):

   ```sql
   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](https://docs.cloud.google.com/alloydb/omni/containers/15.15.0/docs/columnar-engine/configure.md.txt#configure) 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

   ```bash
     sudo docker restart CONTAINER_NAME
   ```

   ### Podman

   ```bash
     sudo podman restart CONTAINER_NAME
   ```

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

> [!WARNING]
> **Warning:** The default value of `google_columnar_engine.memory_size_in_mb` is 1024 MB.

You can set the allocation to a fixed size using the
[`google_columnar_engine.memory_size_in_mb`](https://docs.cloud.google.com/alloydb/omni/containers/15.15.0/docs/reference/columnar-engine-flags.md.txt#memory-size-in-mb) flag.

### 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`](https://www.postgresql.org/docs/current/sql-altersystem.html) PostgreSQL command:

   ```sql
   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

   ```bash
   sudo docker restart CONTAINER_NAME
   ```

   ### Podman

   ```bash
   sudo podman restart CONTAINER_NAME
   ```

### Configure storage cache

> [!NOTE]
> **Note:** This step is optional for enabling the columnar engine.

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

<br />

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

   ```bash
   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:

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

   ```bash
   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:

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

<br />

##### Mount columnar storage cache directory inside AlloyDB Omni

<br />

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](https://registry.hub.docker.com/r/google/alloydbomni) and customizing it, see [Customize your AlloyDB Omni installation](https://docs.cloud.google.com/alloydb/omni/containers/15.15.0/docs/install.md.txt).

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

### Docker

```bash
  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

```bash
  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

```bash
  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

```bash
  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
  
```

<br />

##### 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 ensuring that the mounted cache directory is accessible from inside the Docker container.

1. Set the columnar storage cache directory:

   ```sql
   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. Ensure this directory exists and has appropriate write permissions for the database user.
2. Configure the storage cache size:

   ```sql
   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

   ```bash
   sudo docker restart CONTAINER_NAME
   ```

   ### Podman

   ```bash
   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](https://docs.cloud.google.com/alloydb/omni/containers/15.15.0/docs/configure-disk-cache.md.txt#enable-cache).

Configure storage cache on shared devices as follows:

1. Configure storage cache:

   ```sql
   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

   ```bash
   sudo docker restart CONTAINER_NAME
   ```

   ### Podman

   ```bash
   sudo podman restart CONTAINER_NAME
   ```

## 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`](https://docs.cloud.google.com/alloydb/omni/containers/15.15.0/docs/reference/columnar-engine-flags.md.txt#enable-vectorized-join)
flag to `on`.
To set this flag on an instance, run [the `ALTER SYSTEM` PostgreSQL command](https://www.postgresql.org/docs/current/sql-altersystem.html):

<br />

```sql
ALTER SYSTEM SET google_columnar_engine.enable_vectorized_join = 'on';
```

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

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

### Single-server


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

1. Run [the `ALTER SYSTEM` PostgreSQL command](https://www.postgresql.org/docs/current/sql-altersystem.html):

```sql
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](https://docs.docker.com/reference/cli/docker/container/restart/):

```bash
  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](https://docs.podman.io/en/latest/markdown/podman-restart.1.html):

```bash
  sudo podman restart CONTAINER_NAME
```

Replace `CONTAINER_NAME` with the name that you assigned
to the AlloyDB Omni container when you installed 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]
> **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](https://docs.cloud.google.com/alloydb/omni/containers/15.15.0/docs/install.md.txt#shared-memory-linux).

### macOS

Install a new AlloyDB Omni container,
[specifying a larger shared-memory value for the `--shm-size` flag](https://docs.cloud.google.com/alloydb/omni/containers/15.15.0/docs/install.md.txt#shared-memory-macos).

> [!CAUTION]
> **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](https://docs.cloud.google.com/alloydb/omni/containers/15.15.0/docs/columnar-engine/monitor.md.txt#relations-view) that the tables or materialized views in the query are in the columnar engine.
- Verify the usage of the columnar engine using the [`EXPLAIN`](https://docs.cloud.google.com/alloydb/omni/containers/15.15.0/docs/columnar-engine/monitor.md.txt) statement.

## What's next

- See the [full list of columnar engine database flags](https://docs.cloud.google.com/alloydb/omni/containers/15.15.0/docs/reference/columnar-engine-flags.md.txt).

- Work through the [Accelerating analytical queries with columnar engine in AlloyDB Omni](https://codelabs.developers.google.com/alloydb-omni-columnar) Google CodeLab tutorial.