As rows change, the columnar engine marks the affected column store data in
AlloyDB for PostgreSQL as invalid, and query acceleration gradually drops. To keep
queries fast, you can tune the automatic refresh threshold or manually refresh
tables and vector indexes.

The columnar engine doesn't immediately update the column store when table rows
are added, deleted, or updated. Instead, queries are planned and executed using
both column-oriented data from the column store and row-oriented data from the
row cache and database storage.

Over time, as more and more data blocks become invalid, query performance
improvements provided by the columnar engine are reduced to the point where the
columnar data needs to be refreshed to regain query performance improvements. When this
*refresh threshold* is reached, the columnar engine uses a background job to refresh the invalid data in the
column store. By default, the columnar engine uses a refresh threshold of
25, indicating that given content in the column store gets marked for refresh
when 25% of its content has become invalid. You can [change this refresh
threshold](https://docs.cloud.google.com/alloydb/docs/columnar-engine/maintain-content-freshness#change-refresh-threshold) to adjust the overhead of the automatic
refresh and potentially gain better ongoing query performance
for your application workload.

Perform a [manual refresh](https://docs.cloud.google.com/alloydb/docs/columnar-engine/maintain-content-freshness.md.txt#manual-refresh) of tables and HNSW and ScaNN vector
indexes when you want changes to a table to be reflected in the column store as
soon as possible after making the changes.

## Check invalid data percentage

As your application adds, deletes, or updates rows in a table, the columnar engine marks the affected data in the column store as invalid. You can check the percentage of invalid data for your tables and materialized views by querying the `g_columnar_relations` view. This helps you decide if you need to perform a manual refresh or adjust the automatic refresh threshold.

To view the invalidation percentage for all relations in the columnar store, execute the following query:

```
SELECT relation_name, invalid_percentage FROM g_columnar_relations;
```

To check the invalidation percentage for a specific table, add a `WHERE` clause to the query.

```
SELECT   relation_name,
  CASE
    WHEN total_block_count > 0 THEN (invalid_block_count * 100.0 / total_block_count)
    ELSE 0.0
  END AS invalid_block_percentage
FROM
  g_columnar_relations
WHERE
 relation_name = 'TABLE_NAME';
```

Replace `TABLE_NAME` with a string containing the name of the table or the materialized view. If the resource is in a schema other
than `public`, specify the name of the schema using the format
`SCHEMA_NAME.TABLE_NAME`---for example, `myschema.mytable`.

To check the invalidation percentage for a specific index, add a `WHERE` clause to the query.

```
SELECT   index_name,
  CASE
    WHEN total_block_count > 0 THEN (invalid_block_count * 100.0 / total_block_count)
    ELSE 0.0
  END AS invalid_block_percentage
FROM
  g_columnar_indexes
WHERE
  index_name = 'INDEX_NAME';
```

Replace `INDEX_NAME` with a string containing the name of the vector index.

## Change the refresh threshold of the columnar engine

To change the refresh threshold of the columnar engine, you must modify the value of the `google_columnar_engine.refresh_threshold_percentage` database flag for your AlloyDB instance.

### Console

1. In the Google Cloud console, go to the **Clusters** page.

   [Go to Clusters](https://console.cloud.google.com/alloydb/clusters)
2. Click the name of the cluster that contains the instance you want to configure.
3. In the **Instances in your cluster** section, find the instance and click **Edit**.
4. In the **Flags** section, either add a new flag or modify the existing `google_columnar_engine.refresh_threshold_percentage` flag.
5. Set the value to an integer between `0` and `100` (or `-1` to disable automatic refresh). This value represents the percentage of invalid data that triggers a refresh.
6. Click **Done**.

### gcloud

To use the gcloud CLI, you can
[install and initialize](https://docs.cloud.google.com/sdk/docs/install-sdk.md.txt) the Google Cloud CLI, or you
can use [Cloud Shell](https://docs.cloud.google.com/shell/docs/using-cloud-shell.md.txt).
To change the refresh threshold of the columnar engine, run the following command:

```
gcloud alloydb instances update INSTANCE_ID \
    --database-flags=google_columnar_engine.refresh_threshold_percentage=THRESHOLD \
    --cluster=CLUSTER_ID \
    --region=REGION_ID \
    --project=PROJECT_ID
```

> [!NOTE]
> **Note:** The `gcloud alloydb instances update` command overwrites all existing flags if they are not included in the `--database-flags` list. When adding or changing a flag, you must specify all flags you want to retain. For example, if your instance already has `flag1=A` and you run the previous command, it only contains `google_columnar_engine.refresh_threshold_percentage=THRESHOLD` set afterward. To keep the old flag and add the new one, you must include both: `--database-flags=flag1=A,google_columnar_engine.refresh_threshold_percentage=THRESHOLD`.

Replace the following:

- <var translate="no">INSTANCE_ID</var>: the ID of the instance.
- <var translate="no">THRESHOLD</var>: an integer value between `0` and `100` (or `-1` to disable automatic refresh). This value specifies the percentage of data blocks in content that must become invalid for the content to be marked for refresh.
- <var translate="no">CLUSTER_ID</var>: the ID of the cluster where the instance is located.
- <var translate="no">REGION_ID</var>: the region of the cluster.
- <var translate="no">PROJECT_ID</var>: the ID of the project.

## Manually refresh tables and vector indexes in the column store

You can manually refresh tables and vector indexes in the columnar engine at any time using dedicated SQL functions. The supported index types are ScaNN and HNSW.

> [!NOTE]
> **Note:** Refreshing columnar engine accelerated HNSW indexes can temporarily consume memory up to two times the index size.

To refresh a table's data in the columnar engine, execute the `google_columnar_engine_refresh` function.

```
SELECT google_columnar_engine_refresh('TABLE_NAME');
```

Replace `TABLE_NAME` with a string containing the name of the table or the materialized view. If the resource is in a schema other
than `public`, specify the name of the schema using the format
`SCHEMA_NAME.TABLE_NAME`---for example, `myschema.mytable`.

Similarly, to manually refresh a vector index in the columnar engine, execute the `google_columnar_engine_refresh_index` function.

```
SELECT google_columnar_engine_refresh_index(index => 'INDEX_NAME');
```

Replace `INDEX_NAME` with a string containing the name of the index to refresh.