I filed this as a bug and not an improvement only because of how severe the slowdown is.

The starting point is a 3.0.x install with >5,000,000 posts using MySQL Fulltext as the search backed. Will switch to Sphinx after the update, but the search is surprisingly speedy on MySQL Fulltext.

For schema changes, if there is a Fulltext index on the table, InnoDB will create a new empty table with the new schema and then copy all the data to it, rebuilding the index.

During the update, the following queries were run:

ALTER TABLE `phpbb_posts` ADD COLUMN `post_visibility` tinyint(3) DEFAULT '0' NOT NULL

ALTER TABLE `phpbb_posts` ADD COLUMN `post_delete_time` int(11) UNSIGNED DEFAULT '0' NOT NULL

ALTER TABLE `phpbb_posts` ADD COLUMN `post_delete_reason` varchar(255) DEFAULT '' NOT NULL

With the index in place, each query takes between 12 and 24 hours to complete. Without the index, it takes ~4 minutes

Installing Schema: \phpbb\db\migration\data\v310\softdelete_p1; Time: 192.13 seconds

Installing Schema: \phpbb\db\migration\data\v310\softdelete_p1; Time: 202.60 seconds

Installing Schema: \phpbb\db\migration\data\v310\softdelete_p1; Time: 201.58 seconds

So the perfect solution is that for forums over a certain size that use Fulltext, if the update changes the posts table, we drop the index and then either rebuild it or probably just instruct the admin to rebuild it after. Another option is to warn the admin it will take forever otherwise and instruct them to drop the index manually and then recreate it after.