Instant ADD COLUMN, Until a FULLTEXT Index Shows Up: A MariaDB Migration That Froze Writes

Adding a nullable column to an InnoDB table is supposed to be a non-event. On MariaDB 10.3+ and MySQL 8.0+ it is usually an instant operation: a metadata change, no data copied, done in milliseconds no matter how big the table is.

Last week we shipped a release of a news CMS that added a few columns to its articles table. On most installations the migration finished before anyone noticed. On the larger ones, the article table (a few hundred MB) was rebuilt from scratch, the release took minutes instead of seconds, and every page view that tried to bump a view counter sat waiting until the rebuild finished.

The difference between the two groups was one line in the table definition:

FULLTEXT KEY ft_search (title, body)

This post is the short version of what happened, a reproduction you can run yourself, and the two habits that would have made it a non-event.

The setup

The schema looked roughly like this (names simplified):

CREATE TABLE articles (
  id     INT AUTO_INCREMENT PRIMARY KEY,
  title  VARCHAR(200),
  body   TEXT,
  hits   INT DEFAULT 0,
  -- ...
  FULLTEXT KEY ft_search (title, body)
) ENGINE=InnoDB;

The migration was written the way most migrations are written, one statement per change:

ALTER TABLE articles ADD COLUMN source_url VARCHAR(255) NULL;
ALTER TABLE articles ADD COLUMN is_sponsored TINYINT NOT NULL DEFAULT 0;
ALTER TABLE articles ADD COLUMN updated_by INT NULL;

Every article page runs something like UPDATE articles SET hits = hits + 1 WHERE id = ?. That detail matters later.

Reproducing it

I rebuilt the situation on a local MariaDB instance with two identical tables of 200,000 rows each (about 320 MB). The only difference: a1 has a FULLTEXT index, a2 does not.

CREATE TABLE a1 (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200), body TEXT, hits INT DEFAULT 0,
  FULLTEXT KEY ft (title, body)
) ENGINE=InnoDB;

CREATE TABLE a2 (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200), body TEXT, hits INT DEFAULT 0
) ENGINE=InnoDB;

First, ask the server what it is willing to do instead of letting it choose:

ALTER TABLE a2 ADD COLUMN c1 INT NULL, ALGORITHM=INSTANT;
-- Query OK

ALTER TABLE a1 ADD COLUMN c1 INT NULL, ALGORITHM=INSTANT;
-- ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation.
-- Try ALGORITHM=INPLACE

ALTER TABLE a1 ADD COLUMN c1 INT NULL, ALGORITHM=INPLACE, LOCK=NONE;
-- ERROR 1846 (0A000): LOCK=NONE is not supported.
-- Reason: Fulltext index creation requires a lock. Try LOCK=SHARED

That second error is the whole story. With a FULLTEXT index on the table, adding a column is not instant. The table gets rebuilt, and because the full-text index has to be rebuilt along with it, the server needs at least a shared lock: reads keep working, writes wait.

Then I timed it, with a second connection firing a single counter update one second into each ALTER:

Operation	Time	Concurrent UPDATE ... hits = hits + 1 waited
a1 (FULLTEXT), two columns in one ALTER	34.3 s	33.3 s
a1 (FULLTEXT), two columns in two ALTERs	177.1 s total	44.3 s (during the first one)
a2 (no FULLTEXT), two columns in one ALTER	0.06 s	–

Same table size, same columns. Without the FULLTEXT index the change took 60 milliseconds. With it, a single counter update was blocked for more than half a minute, on a laptop with nothing else running.

On a live site that is not one blocked update. It is every page view during that window, each holding a connection while it waits. Depending on your connection limits and timeouts, that is the difference between "the release was a bit slow" and "the site returned errors for a minute".

Why FULLTEXT changes the rules

InnoDB implements full-text search with a set of auxiliary tables and a hidden FTS_DOC_ID column that ties each row to its entries in the full-text index. The instant ADD COLUMN path works by changing only metadata and leaving existing rows physically untouched. Tables with a FULLTEXT index are excluded from that path, so the server falls back to rebuilding the table. During the rebuild it also rebuilds the full-text index, and that step can't run with concurrent writes, hence LOCK=SHARED.

None of this is a bug. It is documented behaviour in both MariaDB and MySQL. The problem is that it is easy to never hit it in development, where the table has a few hundred rows and every ALTER finishes instantly regardless of the algorithm.

Mistake 1: one statement per column

Our migration had three ALTER TABLE statements, which meant three full rebuilds, three times the write freeze. The fix is boring:

ALTER TABLE articles
  ADD COLUMN source_url   VARCHAR(255) NULL,
  ADD COLUMN is_sponsored TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN updated_by   INT NULL;

One statement, one rebuild. In the reproduction, two separate statements took 177 seconds total versus 34 for the combined one. You will see different numbers on your hardware, but the shape holds: rebuild cost scales with the number of statements, not the number of columns.

This is worth making a rule even for tables without FULLTEXT today. Indexes get added later, by someone else, for a search feature, and your migration habits don't get re-reviewed when that happens.

Mistake 2: letting the server pick the algorithm

When you write a bare ALTER TABLE ... ADD COLUMN, the server silently picks the cheapest algorithm it can use. If instant isn't available, it quietly does a rebuild. Nothing in the output tells you which one happened, except the time it took.

Make the expectation explicit:

ALTER TABLE articles
  ADD COLUMN source_url VARCHAR(255) NULL,
  ADD COLUMN updated_by INT NULL,
  ALGORITHM=INSTANT;

If instant is possible, nothing changes. If it isn't, the statement fails immediately with error 1845 instead of starting a multi-minute rebuild. A failed migration in a release script is annoying; a silent table rebuild on a busy table is an incident. You want the annoying one.

For a migration that is meant to be online but can't be instant, ask for the lock level you can actually tolerate:

ALTER TABLE articles ADD COLUMN x INT NULL, ALGORITHM=INPLACE, LOCK=NONE;

Same idea: fail fast if the server can't keep writes flowing, then decide consciously.

What to do when the rebuild is unavoidable

Once ALGORITHM=INSTANT has told you "no", you have a few options. None of them is free.

1. Schedule it. Run the single combined ALTER at the quietest hour. For many sites, a 30-second write pause at 4 a.m. is fine, as long as it is expected and short. Put a short lock_wait_timeout on the session so the ALTER gives up instead of queueing behind a long-running transaction:

SET SESSION lock_wait_timeout = 10;
ALTER TABLE articles ADD COLUMN ..., ADD COLUMN ...;

This protects against the other classic: the ALTER waits for a metadata lock held by some long query, and every new query on the table piles up behind the waiting ALTER.

2. Make the hot writes tolerate it. In our case the blocked writes were view counters. They don't need to hit the main table synchronously. Buffering them (a small counter table, a cache, or a queue that is flushed periodically) removes the most frequent write from the critical table. That helps far beyond migrations.

3. Drop and re-create the FULLTEXT index around the change. Drop the index, add the columns (now instant), add the index back. The column change becomes cheap, but re-creating the full-text index is itself a long operation, and search returns nothing until it completes. This only makes sense if search being unavailable for a while is acceptable and writes are not.

4. Use an online schema change tool. pt-online-schema-change and gh-ost copy the table in the background and swap it in at the end. They are built for exactly this, but they add operational complexity, need care with triggers and foreign keys, and are overkill for a 300 MB table.

A pre-flight check you can add to your release script

Before running migrations against a large installation, list the tables that can't get instant column changes:

SELECT DISTINCT s.table_name,
       ROUND((t.data_length + t.index_length) / 1048576) AS size_mb
FROM information_schema.statistics s
JOIN information_schema.tables t
  ON t.table_schema = s.table_schema AND t.table_name = s.table_name
WHERE s.table_schema = DATABASE()
  AND s.index_type = 'FULLTEXT'
ORDER BY size_mb DESC;

If a migration touches one of those tables and the table is larger than a few tens of MB, the migration needs a plan, not just a statement.

And the cheapest test of all: run the migration against a copy of your largest real dataset, not the development seed. Our development database had a few hundred articles. The rebuild there took less time than the HTTP request that triggered it.

Takeaways

• Instant ADD COLUMN is the default you get used to, not a guarantee. A FULLTEXT index on the table switches it off.
• Without instant, the table is rebuilt and, because of the full-text index, writes are blocked for the duration (LOCK=SHARED).
• Put all column changes for a table into one ALTER TABLE.
• Write ALGORITHM=INSTANT (or LOCK=NONE) explicitly so impossible expectations fail fast instead of turning into a silent rebuild.
• Test migrations against production-sized data and keep frequent, low-value writes like counters off your largest tables.

We hit this while rolling out a release of the news CMS we build at Alesta WEB (alestaweb.com). The fix in the end was two lines in the migration. Finding out why the release had stalled on the big sites took considerably longer.