Columnar Indexes for MariaDB Xpand
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".
MariaDB Xpand 6.0
MariaDB Xpand 6.1
MariaDB Xpand 6 can use Columnar indexes concurrently with row-based secondary indexes, which makes Xpand suitable for hybrid workloads.
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.
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.
MariaDB Xpand 6 compresses all Columnar indexes.
The compression algorithm can be queried using the
columnar_compression_algorithm system variable, which defaults to
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
consistent_orderhas no effect on tables with columnar indexes
UNIQUEindex 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