Columnar Indexes for MariaDB Xpand

Overview

MariaDB Xpand 6 provides Columnar indexes for hybrid workloads that need to leverage Columnar indexes and row-based secondary indexes concurrently:

  • Xpand 6 allows Columnar indexes to be defined on normal tables with most data types

  • Columnar indexes can be used for ad-hoc queries, where the fields in a query are not known in advance

  • Columnar indexes can be used for operational analytics, where real-time analytics are performed on transactional data

  • Columnar indexes are compressed by default

  • A Delta Store is used to optimize updates to Columnar indexes

This page explains the architecture related to Columnar indexes.

For information on how to use Columnar indexes, see "COLUMNAR Indexes with MariaDB Xpand".

Compatibility

  • MariaDB Xpand 6

Hybrid Workloads

MariaDB Xpand 6 can use Columnar indexes concurrently with row-based secondary indexes, which makes Xpand suitable for hybrid workloads.

Ad-Hoc Queries

Columnar indexes in MariaDB Xpand 6 can be used for ad-hoc queries, where the fields in a query are not known in advance.

Columnar indexes and row-based secondary indexes have different filtration capabilities:

  • For row-based secondary indexes, filtration is highly dependent on column order. A composite (multi-column) row-based secondary index can only efficiently filter on an indexed column if all preceding columns are also part of the filter condition. For example, if a row-based secondary index is defined with INDEX (a, b, c), and the query filters with WHERE b = 1 AND c = 2, the index cannot efficiently filter the results because the first indexed column is not part of the filter condition.

  • For Columnar indexes, efficient filtration is not dependent on column order. If a Columnar index is defined with COLUMNAR INDEX (a, b, c), and the query filters with WHERE b = 1 AND c = 2, the Columnar index can efficiently filter the results.

The filtration capabilities of composite (multi-column) Columnar indexes make them suitable for ad-hoc queries. Instead of defining multiple row-based secondary indexes that index different columns in different orders, a single Columnar index can be defined to handle many different ad-hoc queries.

Operational Analytics

Columnar indexes in MariaDB Xpand 6 can be used for operational analytics, where real-time analytics are performed on transactional data.

Columnar indexes and row-based secondary indexes are best suited for different types of workloads:

  • Row-based secondary indexes outperform Columnar indexes for common transactional queries.

  • Columnar indexes outperform row-based secondary indexes for analytical queries that perform range scans and aggregations.

Xpand 6 can use both types of indexes concurrently, which makes Xpand 6 a single database solution for operational analytics. Instead of streaming or replicating data from Xpand to a separate analytical database, Xpand 6 can be used for both transactional and analytical queries.

Columnar indexes can increase commit latency. If low commit latency is a requirement, it might be better to replicate to MariaDB ColumnStore for analytics, instead of using Columnar indexes.

Compression

MariaDB Xpand 6 compresses all Columnar indexes.

The compression algorithm can be queried using the columnar_compression_algorithm system variable, which defaults to xlz.

Delta Store

MariaDB Xpand 6 uses a Delta Store to decrease the overhead of updates to Columnar indexes. A Delta Store uses a row-based format to keep track of changed rows.

Since a Columnar index stores each column separately, updating a Columnar index can require more I/O than updating a row-based secondary index. To optimize updates to Columnar indexes, Xpand 6 creates a Delta Store for each Columnar index that stores recently changed rows for the index. When an indexed column is updated, the Delta Store is initially updated. Periodically, the changes are merged from the Delta Store into the Columnar index as a batch operation. The batch operation decreases the total I/O cost of the update and uses system resources more efficiently than directly updating the Columnar index for every update.

Columnar Index Features

With MariaDB Xpand 6, Columnar indexes can be used in the following cases:

  • A single Columnar index can be created for each table

  • Single column and composite (multi-column) Columnar indexes can be defined

  • All data types can be indexed in Columnar indexes, except:

Columnar indexes can't be used in the following cases:

  • Columnar indexes can't be defined with prefix lengths

  • Columnar indexes can't be defined for temporary tables

  • Generated columns can't be indexed with Columnar indexes

  • Foreign keys can't be defined on columns in Columnar indexes

Other special cases:

  • Xpand does not support columnar tables, only columnar indexes on row-based tables

  • The variable consistent_order has no effect on tables with columnar indexes

  • The UNIQUE index option is not available for columnar indexes

  • Columnar indexes are not supported on older versions of Xpand or MariaDB Enterprise Server, so cannot be used in replication or with backup/restore to those versions and databases