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

- [Current (18.4.0)](https://docs.cloud.google.com/alloydb/omni/linux/current/docs/columnar-engine/configure.md.txt)
- [18.4.0](https://docs.cloud.google.com/alloydb/omni/linux/18.4.0/docs/columnar-engine/configure.md.txt)
- [18.3.0](https://docs.cloud.google.com/alloydb/omni/linux/18.3.0/docs/columnar-engine/configure.md.txt)
- [18.1.1](https://docs.cloud.google.com/alloydb/omni/linux/18.1.1/docs/columnar-engine/configure.md.txt)
- [18.1.0](https://docs.cloud.google.com/alloydb/omni/linux/18.1.0/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/linux/18.1.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/linux/18.1.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/linux/18.1.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 database server:

   ```bash
    sudo systemctl restart alloydbomni18
   ```

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

> [!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/linux/18.1.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 database server:

   ```bash
   sudo systemctl restart alloydbomni18
   ```

### Configure storage cache

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

### Single-server

#### Shared or dedicated devices

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

##### Provision and mount the cache directory

On dedicated devices, you need to provision disks, create a file system,
mount the cache directory on a host path (for example,
`/var/lib/postgresql/18/data/columnar_cache`), and ensure it has write
permissions for the `postgres` user.

##### Enable the columnar storage cache

1. Run `ALTER SYSTEM` to configure the cache directory path and size:

   ```sql
   ALTER SYSTEM SET google_columnar_engine.omni_storage_cache_directory = 'CACHE_DIR_PATH';
   ALTER SYSTEM SET google_columnar_engine.storage_cache_size = STORAGE_CACHE_SIZE;
   ```

   Replace the following:
   - `CACHE_DIR_PATH`: The path to the cache directory (for example, `/var/lib/postgresql/18/data/columnar_cache`).
   - `STORAGE_CACHE_SIZE`: The size of your columnar engine storage cache, in megabytes.
2. Restart your database server:

   ```bash
   sudo systemctl restart alloydbomni18
   ```

## 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/linux/18.1.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 columnar 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 database server:

   ```bash
   sudo systemctl restart alloydbomni18
   ```

## What's next

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

- Try out the codelab [Accelerating analytical queries with columnar engine in AlloyDB Omni](https://codelabs.developers.google.com/alloydb-omni-columnar).