Using pt-online-schema-change in Practice: Schema Changes Without Locking

1. Why native online DDL does not solve every problem

Native online DDL with ALGORITHM=INPLACE works reliably as long as an operation stays within its configured limits. On a very write-heavy table, such as an active cart or session table with thousands of changes per minute, the internal row log can hit the configured ceiling innodb_online_alter_log_max_size. The result is a full rollback after a run that may have taken hours, with not a single change applied.

Even an inplace-capable operation still needs a brief but exclusive metadata lock at the start and end. On a table with permanently high concurrency, even that short moment can cause noticeable delays in the application. pt-online-schema-change was built for exactly these cases: a command line tool that works entirely outside InnoDB's own online DDL mechanism and therefore avoids its limitations.

2. The basic architecture: shadow table instead of an internal rebuild

pt-online-schema-change first creates an empty copy of the target table with the new structure, the so called shadow table, usually prefixed like _tablename_new. It then applies the requested ALTER statement only to this new, still empty table, which completes almost instantly regardless of how large the original table is. Only after that does the actual copy work begin.

To avoid missing any changes to the original table during the copy phase, the tool installs three triggers on the original table: for INSERT, UPDATE and DELETE. Every change to the original table is mirrored synchronously in real time to the shadow table, while the existing data is copied in chunks in the background. This keeps the original and shadow table consistent at all times without needing a global lock.

# Dry run without changing anything in the database
pt-online-schema-change --dry-run --alter="ADD INDEX idx_status_created (status, created_at)" D=magento,t=sales_order_grid

# Actual execution after a clean dry run
pt-online-schema-change --execute --alter="ADD INDEX idx_status_created (status, created_at)" D=magento,t=sales_order_grid

3. The trigger mechanism in detail

The three triggers created by pt-online-schema-change are deliberately minimal. The INSERT trigger inserts each new row identically into the shadow table, the UPDATE trigger replicates changes to already copied rows, the DELETE trigger removes deleted rows from the shadow table too. Because these triggers run synchronously within the same transaction as the original write, consistency is guaranteed, at the cost of one extra write per application write.

This synchronous overhead is exactly the key difference from binlog-based alternatives like gh-ost. Under extremely high write load, such as several thousand inserts per second on a log or session table, the added trigger overhead can noticeably reduce the application's effective write throughput while the migration is running. For most Magento-typical tables with moderate write load, the effect is barely measurable.

4. Chunk copy and the nibble iterator

pt-online-schema-change does not copy existing data in one long transaction, but in small, sequential blocks bounded by the primary key. This so called nibble iterator continuously computes primary key value ranges and copies only the rows within one range in its own short transaction. That limits both the duration of individual locks and the scope a rollback would affect if something went wrong.

Chunk size can be fixed with --chunk-size or controlled adaptively with --chunk-time, so each chunk stays close to a target duration, for example 0.5 seconds. On systems with strongly fluctuating load, chunk size then adapts automatically, smaller chunks under high load, larger ones during quieter periods.

5. The atomic cutover at the end of the migration

Once all existing data has been copied and the triggers have caught up on every change made in the meantime, the most critical step follows: swapping the original and shadow table. pt-online-schema-change uses an atomic RENAME trick involving three tables at once, so at no point is either the old or the new table missing under its regular name. Application code issuing parallel requests ideally never notices the switch at all.

This rename step itself needs a brief exclusive metadata lock, typically in the range of a few milliseconds to a few seconds, depending on how many other sessions currently hold locks on the affected table. Afterwards the tool drops the old table along with the three triggers and finishes the migration. The option --no-drop-old-table keeps the old table around for a while as a safety net before it gets removed manually.

6. Foreign keys: the most common pitfall

Foreign key relationships are by far the most common source of errors with pt-online-schema-change. Because the shadow table is initially a standalone table with its own name, existing foreign keys from other tables still point at the old table, not automatically at the new one. The tool offers the --alter-foreign-keys-method option with two fundamentally different strategies.

The rebuild_constraints method changes the foreign key definition on every referencing table via ALTER TABLE, which itself takes time and becomes expensive with many referencing tables. The drop_swap method is faster but briefly disables foreign key checking entirely during the cutover, which in rare cases can lead to temporarily inconsistent references if writes happen concurrently. On heavily interlinked Magento tables like catalog_product_entity, both strategies deserve a close look before production use.

7. Trigger conflicts and other limitations

Because pt-online-schema-change installs its own triggers on the original table, it refuses to start by default if that table already has triggers of its own, for example from a custom module or an old Magento 1 migration. The reason is simple: MySQL allows multiple triggers per event, but the execution order and possible side effects between foreign and own triggers cannot be reliably predicted. The --preserve-triggers flag overrides this behavior deliberately, but only after carefully reviewing the existing trigger logic.

Other limitations affect tables without a primary key or unique index, where the nibble iterator cannot compute meaningful chunk boundaries, and replicated environments with active replication filters, which the tool treats as risky by default and only accepts with explicit confirmation. Tables with very wide TEXT or BLOB columns can also noticeably slow the copy phase through higher I/O per chunk.

# Explicitly choose the foreign key strategy instead of the default
pt-online-schema-change --execute --alter="MODIFY COLUMN sku VARCHAR(191) NOT NULL" --alter-foreign-keys-method=rebuild_constraints D=magento,t=catalog_product_entity

# Monitor replication lag on all replicas and pause automatically
pt-online-schema-change --execute --max-lag=5 --check-slave-lag=h=replica1.internal --alter="ADD COLUMN last_synced_at DATETIME NULL" D=magento,t=sales_order

8. A practical workflow with dry run and safety checks

A production run always starts with --dry-run, which performs every check and creates the shadow table but installs no triggers and copies no data. Only after a clean dry run does the real run with --execute follow. Among other things, the tool automatically checks whether replication filters are active, whether the target table requires referential integrity, and whether enough free disk space exists for the temporary shadow table.

For production environments with replicas, --max-lag together with --check-slave-lag is essential: the tool automatically pauses the copy phase once a monitored replica exceeds the configured lag threshold, and resumes only once the replica has caught back up. A pause file can additionally halt the entire migration manually at any time, for example during a scheduled maintenance window for other work.

9. When pt-online-schema-change still beats native online DDL

Native ALGORITHM=INSTANT remains the fastest and simplest choice for supported operations, an external tool brings no advantage there. But once an operation requires a full table copy anyway, such as an incompatible data type conversion, or once the native row log would hit its capacity limit on a very write-heavy table, pt-online-schema-change is often the lower-risk choice.

Another advantage is controllability: pause files, lag-based throttling and a clean dry run mode give far more control than a native ALTER TABLE, which once started can only be killed, not paused. For migrations under very high, constant write load on the target table, though, gh-ost with its binlog-based approach is often the even better alternative, since it avoids the extra trigger overhead entirely.

10. Summary

11. FAQ: pt-online-schema-change in Practice