Create index in huge MariaDB production database without table locking

Question

I have a table with 202M records where I need to add a few indexes and I can't find it anywhere (or maybe I don't understand the lingo) if that is possible to do, without locking, in MariaDB 10.3.

I found this post where I can see that that is possible in MySQL 5.6+, but my google foo didn't get my any info on MariaDB.

I tried using pt-online-schema-change but since I don't have any index (not even primary) that is not an option.

Answer 1

This is possible with the use of ALTER ONLINE TABLE.

ALTER ONLINE TABLE is equivalent to LOCK=NONE. Therefore, the ALTER ONLINE TABLE statement can be used to ensure that your ALTER TABLE operation allows all concurrent DML.

Further reading tells that adding primary keys is a "copy" operation as DB engine needs to copy the whole table to new file, but adding other indexes in an inplace operation.

InnoDB supports adding a primary key to a table with ALGORITHM set to INPLACE. The table is rebuilt, which means that all of the data is reorganized substantially, and the indexes are rebuilt. As a result, the operation is quite expensive. This operation supports the non-locking strategy. This strategy can be explicitly chosen by setting the LOCK clause to NONE. When this strategy is used, all concurrent DML is permitted.

InnoDB supports adding a plain index to a table with ALGORITHM set to INPLACE. The table is not rebuilt. This operation supports the non-locking strategy. This strategy can be explicitly chosen by setting the LOCK clause to NONE. When this strategy is used, all concurrent DML is permitted.

More info in MariaDB documentation.