Alter TABLE without locking the table?

sql

database-migration

schema-change

online-ddl

byAlex Kataev·Dec 11, 2024

To perform non-blocking ALTER TABLE on a MySQL table, use the INPLACE algorithm:

ALTER TABLE tbl ADD COLUMN col INT, ALGORITHM=INPLACE, LOCK=NONE;

Remember: ALGORITHM=INPLACE reduces impact, and LOCK=NONE allows ongoing DML operations. Always check compatibility and test on non-production data!

Phase by phase: Gradual migration

When contemplating an extensive schema shift, a step-by-step approach could help:

Preparation: Create a new table mirroring the planned schema.
Migration: Gradually move data from the old to the new table.
Sync: Use triggers on the source table to keep the new one updated throughout.
Switch: Rename the original, then promote the new table.

Keep in mind that synchronization is key in this process. It helps minimize lock times and potential downtime.

The DBMS spectrum: Live updates and strategies

Given the diversity of database systems, varying operations are available:

For MySQL, pt-online-schema-change enables continuous table access during modification.
In PostgreSQL, CREATE INDEX CONCURRENTLY allows non-locking index creation.
MariaDB offers ALTER ONLINE TABLE or LOCK=NONE to keep operations rolling.

When it comes to adding a new field:

In-place modification saves disk space and I/O resources.
However, remember that adding a new field implies changing every row of the table.

Sync or sink: Synchronization intricacies

To keep updates in sync during gradual migration, use triggers or background jobs. Following the final swap:

Act promptly to recompile stored procedures and views that reference the table.
Evaluate how these adjustments may affect execution plans.

Specifics by database: Database-specific considerations

For MySQL Online DDL: Check the MySQL Reference Manual for specific commands and capabilities.
In Postgres: Have a rollback strategy in case of unexpected hitches during migrations.
For Percona Toolkit: Consult the detailed guidelines on using pt-online-schema-change.
MariaDB Knowledge Base provides a wealth of information on online DDL operations.

Insight or oversight: Schema change implications

Adjusting a table's schema is modifying its physical structure on the disk. This is not about locking a single field - we're redefining the structure of every record. Various tools or methods aim to alleviate this disruption.

Fire-fighters of hot alters: Hybrid tactics

Field locks can be quite the handful. Hence, DBMS technology has evolved techniques for dealing with "hot alters":

Implement triggers to ensure exact replica between original and modified tables.
Facebook's OSC tool and similar tools enable real-time schema modifications.
Be aware of concurrency with LOCK clauses in Online DDL to balance performance and change effectiveness.