ALTER TABLE with MariaDB Xpand

Overview

MariaDB Xpand supports the ALTER TABLE statement to alter, change, or modify tables:

  • Xpand accepts table options in ALTER TABLE statements to alter table characteristics

  • Xpand avoids locking for ALTER TABLE statements

  • Xpand allows concurrent read and write queries during ALTER TABLE statements

Compatibility

  • MariaDB Xpand 5.3

  • MariaDB Xpand 6.0

  • MariaDB Xpand 6.1

Table Options

With MariaDB Xpand, the ALTER TABLE statement can be used to set table options:

ALTER TABLE hq_sales.invoices
   REPLICAS=3;

Locking

MariaDB Xpand uses Multi-Version Concurrency Control (MVCC) to avoid locking for ALTER TABLE statements.

For additional information, see "MVCC (Multi-Version Concurrency Control) with MariaDB Xpand" and "Locking with MariaDB Xpand".

Online Schema Changes

MariaDB Xpand supports online schema changes to allow concurrent read and write queries to access the table while the ALTER TABLE statement is executed:

  • Xpand maintains read consistency during the execution of ALTER TABLE statements

  • When read and write queries access the table before the ALTER TABLE completes, the queries see the original table schema

  • When reads and write queries access the table after the ALTER TABLE statement completes, the queries see the new table schema

For additional information, see "Online Schema Changes with MariaDB Xpand".

Best Practices

Although MariaDB Xpand is designed to perform online schema changes, MariaDB recommends following some best practices to minimize impact to your application:

  • Since ALTER TABLE statements can have long execution times, MariaDB recommends using a client in a screen session or other detachable terminal

  • Test the new schema with your application's queries in a non-production environment before making the change in production

  • Compare the EXPLAIN output before and after the schema change

  • Ensure there is sufficient storage available before starting the ALTER TABLE operation

  • Ensure there are no replication concerns before starting the ALTER TABLE operation

Estimate ALTER TABLE Storage Requirements

When MariaDB Xpand executes an ALTER TABLE statement, it requires storage space that depends on many factors, which are described in the sections below.

If your service requires additional storage prior to performing an ALTER TABLE statement, see "Allocate Disk Space for Fault Tolerance and Availability with MariaDB Xpand".

Storage for Full Table Copy

When MariaDB Xpand executes an ALTER TABLE statement, it should have sufficient storage to create a full copy of the table, including replicas.

The size of a table can be estimated from system.table_sizes:

SELECT * FROM system.table_sizes;

Undo Log Growth

When MariaDB Xpand executes an ALTER TABLE statement, it should have sufficient storage to allow the Undo Log to grow due to concurrent queries:

  • Xpand pauses Undo Log trim until the ALTER TABLE statement completes

  • The required space depends on the current Undo Log size and expected growth from concurrent INSERT, UPDATE, and DELETE queries

Binary Log Growth

When MariaDB Xpand executes an ALTER TABLE statement, it should have sufficient storage to allow the binary logs to grow from concurrent queries:

  • Xpand does not pause binlog trim during the ALTER TABLE statement, but Xpand does not free the corresponding storage until Undo Log trim resumes after the ALTER TABLE operation completes

Minimum Available Space

When MariaDB Xpand executes an ALTER TABLE statement, it should generally have a minimum of 10% free storage after accounting for all of the above factors:

  • Xpand requires at least 5% of free storage, because Xpand will kill long-running transactions (including the ALTER TABLE operation) if free storage falls below that

Interactions with Replication

ALTER TABLE statements with MariaDB Xpand can interact with replication.

Sequential Execution with Inbound Replication

When MariaDB Xpand is configured for inbound replication, replicated ALTER TABLE statements are executed sequentially within the replication stream:

  • Replicated writes are paused until the replicated replicated ALTER TABLE statement completes

  • If the ALTER TABLE statement takes a long time, slave lag can occur

  • Before performing large ALTER TABLE operations over inbound replication, check if your cluster's binary log retention is sufficient to save the binary logs until replication has caught up

Row-Based Replication with Inbound Replication

When MariaDB Xpand is configured for inbound replication with row-based replication (RBR), the table columns in the binary log must match the table columns in the table's schema in Xpand:

  • When a table's schema changes, but Xpand tries to apply binary log events that reference the old schema, replication errors can occur

  • If a schema change operation will modify a column when inbound row-based replication is configured, the schema change operation can be executed in the replication stream to force sequential execution with replicated writes

  • The following schema change operations do not impact row-based replication:

    • Adding indexes

    • Changing the storage type

    • Changing the number of slices

Monitor ALTER TABLE Progress

When MariaDB Xpand executes an ALTER TABLE statement, the progress of the operation can be monitored by querying the system.alter_progress system table:

SELECT * FROM system.alter_progress;

Special Cases

  • Xpand does not accept the CHECK constraint syntax in ALTER TABLE

  • Xpand does not support ALTER TABLE .. CONVERT

  • Xpand does not support dropping primary keys using ALTER TABLE .. DROP INDEX PRIMARY, but Xpand does support the operation using ALTER TABLE .. DROP PRIMARY KEY, LOCK=SHARED

  • Xpand does not support the % wildcard in database names