AlloyDB Omni columnar engine overview

This page describes the AlloyDB Omni columnar engine and provides instructions about how to use the engine in container and Kubernetes cluster installations.

The AlloyDB Omni columnar engine accelerates SQL query processing of scans, joins, and aggregates by providing the following components:

  • A column store that contains table and materialized-view data for selected columns, reorganized into a column-oriented format.

  • A columnar query planner and execution engine that supports use of the column store in queries.

You can use the columnar engine on the primary instance, a read pool instance, or both. You can also use auto-columnarization to analyze your workload and automatically populate the column store with the columns that provide the best performance gain.

To use the columnar engine with a specific query, all columns in that query fragments, such as joins, scans, must be in the column store.

By default, the columnar engine is set to use 1 GB of your instance's memory. Depending on your workload, memory usage, and if you have a read pool configured, you can choose to reduce the columnar engine memory allocation on your primary instance and allocate more memory to the read pool instance.

To view and monitor memory usage by the columnar engine, see View column store memory usage. To modify the memory size used by the column store, see Configure the size of the column store. To find the recommended columnar engine memory size for your instance, see Recommend column store memory size.

Query types that benefit from the columnar engine

Certain queries can benefit from the columnar engine. The following is a list of operations and their query patterns that benefit most from the columnar engine:

Operation Query patterns
Table scan Selective filters, such as WHERE clauses.
A small number of columns from a larger table or materialized view.
Expressions such as LIKE, SUBSTR, or TRIM.
Aggregation functions Only expressions such as SUM, MIN, MAX, AVG, and COUNT.
At the beginning of the query of a columnar scan.
Ungrouped or grouped by columns.
ORDER-BY Only if the operator is at the beginning of the query of a columnar scan.
SORT Only if the operator is at the beginning of the query of a columnar scan and sorts only on the base columns of the table or the materialized view.
LIMIT Only if the operator is at the beginning of the query of a columnar scan and is before any SORT or GROUP BY operators.
INNER HASH JOIN Only if the keys used are columns and no join qualifiers are used.
Selective joins Only if the joins are at the beginning of the query of a columnar scan.

For more information on which queries work best with the columnar engine, whether the columnar engine was used by a query, and how it was used, see Verify usage of the columnar engine using EXPLAIN.

How to use the columnar engine

To use the columnar engine in AlloyDB Omni instance, you perform these high-level steps:

  1. Enable the engine on the instance.

    Enabling the engine is a one-time operation and requires a database restart.

  2. Add columns to the column store.

    To add columns to the column store, use one of the following methods:

  3. You can track what's in the column store using the g_columnar_relations view, and, after columns have been added, you can use the EXPLAIN statement to verify usage of the columnar engine in SQL queries.

For detailed instructions on how to use the columnar engine, see Configure the columnar engine.

What data you can add to the column store

There are some limitations on the data types and data sources you can use when adding columns to the column store.

Supported data types

The columnar engine supports only columns with the following built-in data types:

  • array
  • bigint
  • boolean
  • bytea
  • char
  • date
  • decimal
  • double precision
  • enum
  • float4
  • float8
  • integer
  • json
  • jsonb
  • numeric
  • real
  • serial
  • short
  • smallint
  • text
  • timestamp
  • uuid
  • varchar

The columnar engine ignores any attempts to manually add columns with unsupported data types to the column store.

Unsupported data sources

The columnar engine does not support tables or materialized views with the following attributes as data sources:

  • Non-leaf partitioned tables

  • Foreign tables

  • Tables or views with fewer than 5,000 rows

Columnar engine limitations

  • If you're running an analytical query on a column that has an index, the AlloyDB Omni optimizer might choose to use row-store.
  • Columns added manually to the column store aren't automatically removed. To force remove manually added columns, use google_columnar_engine_drop on your instance.
  • Auto-columnarization might dynamically add and remove columns based on query usage.
  • Not all data types are supported by the columnar engine. To see supported data types, see supported data types.

  • Frequent updates to rows invalidate columnar data. To validate a table or a materialized view in the columnar store, you can either reduce the update frequency, or schedule the columnar engine refreshes more frequently.

    You can compare the invalid_block_count and total_block_count columns in g_columnar_relations to check if your table or view is impacted. If you have frequent or high-volume changes to your table or view, the invalid_block_count will be high.

What's next