Table Options with MariaDB Xpand

Overview

MariaDB Xpand's table options are used in CREATE TABLE and ALTER TABLE statements to define table characteristics:

  • Xpand ignores some InnoDB-related table options in table definitions to allow easy migrations from InnoDB

  • Xpand accepts the REPLICAS table option to provide an increased level of fault tolerance for a given table

Compatibility

  • MariaDB Xpand 5.3

  • MariaDB Xpand 6.0

  • MariaDB Xpand 6.1

Table Options with CREATE TABLE

To set table options for a new table with MariaDB Xpand, set the table options in the CREATE TABLE statement:

CREATE TABLE hq_sales.invoices (
   invoice_id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
   branch_id INT NOT NULL,
   customer_id INT,
   invoice_date DATETIME(6),
   invoice_total DECIMAL(13, 2),
   payment_method ENUM('NONE', 'CASH', 'WIRE_TRANSFER', 'CREDIT_CARD', 'GIFT_CARD'),
   PRIMARY KEY(invoice_id)
) SLICES=6;

Table Options with ALTER TABLE

To set table options for an existing table with MariaDB Xpand, set the table options in the ALTER TABLE statement:

ALTER TABLE hq_sales.invoices
   REPLICAS=ALLNODES;

Accepted Table Options

MariaDB Xpand supports the following table options:

Table Option

Description

AUTO_INCREMENT

Specifies the next auto-increment value

CONTAINER

Specifies whether the table's representations are in-memory or on-disk:

  • Set to SKIPLIST to create in-memory representations

  • Set to LAYERED (default) to create on-disk representations

DISTRIBUTE

Specifies the distribution of a composite primary key or the distribution of other indexes (non-primary key) after a table has been created

ENGINE

Specifies the supported ENGINE types

REPLICAS

Specifies the number of replicas Xpand maintains for the table's slices:

  • Set to a number to maintain a specific number of replicas (defaults to 2)

  • Set to ALLNODES to maintain a replica of each slice on every node

SLICES

Specifies the number of slices Xpand maintains for each of the table's representations

Ignored Table Options

MariaDB Xpand ignores the following table options to allow easy migrations from InnoDB:

  • KEY_BLOCK_SIZE

  • ROW_FORMAT

  • TYPE