Schema changes on large tables can take hours using traditional ADD COLUMN, which rebuilds the entire table, consumes significant I/O and memory, and may block concurrent DML workloads. Instant ADD COLUMN avoids these problems by modifying only the metadata in the data dictionary — completing in seconds on tables of any size, with no locks and almost no resource overhead.
How it works
Traditional ADD COLUMN (COPY or INPLACE mode) rebuilds the entire table. For a large table, this can take hours and block concurrent DML workloads.
Instant ADD COLUMN (INSTANT mode) modifies only the metadata in the data dictionary. The table data itself is never touched.
The following table summarizes the difference.
Traditional ADD COLUMN (COPY or INPLACE) | Instant ADD COLUMN (INSTANT) | |
Time | Requires a full table rebuild. Time increases with table size. | Modifies only metadata. Completes in seconds. |
Resource usage | Temporarily uses large amounts of I/O and memory. | Uses almost no additional resources. |
Business impact | May block online workloads during long-running transactions or under high concurrency. | Does not lock tables or block operations. |
Table size | Fast column addition is not supported for large tables. | Works on tables of any size. |
Prerequisites
Before you begin, make sure your instance meets one of the following version requirements. If your minor engine version does not meet the requirements, update the minor engine version.
MySQL 8.0: All minor engine versions are supported.
MySQL 5.7: The minor engine version must be 20250331 or later.
Limitations
Engine and table type
Only the InnoDB storage engine is supported.
Compressed tables, tables with full-text indexes, and temporary tables are not supported.
Operations
Multiple DDL operations cannot be combined in a single statement. For example, creating an index while adding a column is not supported.
Read-only instances
If your primary instance uses high availability and has read-only instances attached, set loose_innodb_instant_ddl_enabled to ON on both the primary and read-only instances. Otherwise, replication may stop on the read-only instances.
Default column position
MySQL version | Minor engine version | Default column position |
5.7 | 20250331 or later | New column is added as the last column. |
8.0 | Earlier than 20230630 | New column is added as the last column. |
20230630 or later | Column position can be specified. |
For MySQL 5.7 or MySQL 8.0 (earlier than 20230630), make sure no implicit primary key exists in the table. See the FAQ for details.
Enable Instant ADD COLUMN
MySQL 8.0 has Instant ADD COLUMN enabled by default. No configuration is needed.
For MySQL 5.7, enable the feature by setting the loose_innodb_instant_ddl_enabled parameter:
Go to the RDS Instances page. Select the region where your instance resides, then click the ID of your target instance.
In the left navigation pane, click Parameters.
On the Modifiable Parameters tab, search for
loose_innodb_instant_ddl_enabled. In the Running Value column, set the value to ON.NoteThis change takes effect immediately. No restart is required.
Click Apply Changes. In the dialog box, select when the change takes effect, then click OK.
Related operations
Use Instant ADD COLUMN
Force INSTANT mode by explicitly specifying the algorithm:
ALTER TABLE <table_name> ADD COLUMN <column_name> <data_type> <constraints>, ALGORITHM = INSTANT;Let RDS select the algorithm automatically — RDS for MySQL picks the optimal mode at runtime. If INSTANT is supported for your table, it is used:
ALTER TABLE <table_name> ADD COLUMN <column_name> <data_type> <constraints>;Replace the following placeholders with actual values:
Placeholder | Description |
| Name of the target table |
| Name of the new column |
| Data type of the new column, for example, |
| Optional constraints, for example, |
View tables that used Instant ADD COLUMN
MySQL 5.7:
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE INSTANT_COLS > 0;MySQL 8.0 (earlier than 20230630):
SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE INSTANT_COLS > 0;MySQL 8.0 (20230630 or later):
SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE TOTAL_ROW_VERSIONS > 0;View columns added by Instant ADD COLUMN
MySQL 5.7 — MySQL 5.7 adds the INNODB_SYS_INSTANT_COLUMNS table to the INFORMATION_SCHEMA database:
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_INSTANT_COLUMNS WHERE TABLE_ID =
(SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME = "<database_name>/<table_name>");MySQL 8.0 — A HAS_DEFAULT value of 1 indicates the column was added using Instant ADD COLUMN:
SELECT * FROM INFORMATION_SCHEMA.INNODB_COLUMNS WHERE TABLE_ID =
(SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME = "<database_name>/<table_name>");FAQ
Q1: My instance meets the requirements for Instant ADD COLUMN, but I get this error when adding a column: Feature not supported: 1845 ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.
A:
Cause: If a table has no primary key or unique key, RDS for MySQL adds an implicit primary key to improve replication efficiency. By default, this implicit primary key is the last column. That forces Instant ADD COLUMN to place the new column at a specific position. However, MySQL 5.7 and MySQL 8.0 (earlier than 20230630) do not support placing columns at specific positions.
Solution: For MySQL 5.7 or MySQL 8.0 (earlier than 20230630), make sure no implicit primary key exists in the table.