Changing a plugin’s table structure is not simply a matter of adding a few SQL queries to a code update. In production, a migration may run against a large database, compete with user requests, exceed execution limits, or stop halfway through.
A reliable approach uses a separate schema version, sequential migrations, current-state checks, repeatable operations, and persisted progress. This makes updates safer to retry after an error, run in batches, and monitor through WP-CLI or WP-Cron.
Why register_activation_hook() Is Not Suitable for Regular Updates
register_activation_hook() runs when a plugin is activated, not whenever its files are updated. If a user updates a plugin from version 1.4 to 1.6, the activation code will generally not run again. As a result, a new column or index may be missing even though the updated code expects it to exist.
The activation hook is appropriate for initially creating tables, storing default settings, and scheduling a background task. However, schema updates need a separate mechanism that can identify the current database version and continue after an interruption.
You should also avoid running a long migration directly during the first administrator request. It may cause a PHP timeout, lock a table, or fail because of hosting restrictions. For large datasets, it is better to separate structural changes from data transformation.
- Use activation for initial setup, not as the only mechanism for schema updates.
- Migrations must run regardless of whether the plugin was deactivated and activated again.
- Move long-running operations to WP-Cron, WP-CLI, or another controlled process.
Store the Schema Version Separately from the Plugin Version
The plugin version describes a code release, while the schema version describes the state of the database. These are different concepts. One release may contain several sequential table changes, and a user may update across multiple versions at once.
For a simple installation, you can store the version number in a WordPress option such as my_plugin_db_version. With multisite, define the storage scope in advance: per site or across the entire network. If tables are created with a site-specific prefix, the version generally needs to be handled separately for each blog as well.
Migrations should run sequentially. For example, schema version 1 creates a table, version 2 adds a status column, and version 3 moves data into that column. Do not check only for version 3 and skip the earlier steps: those steps may include necessary data transformations.
- Use integers or another unambiguous format that is easy to compare.
- Record the new version only after the specific step has completed successfully.
- Do not increment the version in advance: if SQL or data processing fails, the step must remain available for another attempt.
- Separate structural migrations from heavy data migrations when they may take a long time.
$current_version = (int) get_option('my_plugin_db_version', 0);
$target_version = 3;
for ($version = $current_version + 1; $version <= $target_version; $version++) {
my_plugin_run_migration($version);
update_option('my_plugin_db_version', $version, false);
}
Design Every Migration to Be Idempotent
An idempotent migration can be safely run again. This is especially important when a process is interrupted after some SQL has executed but before the new version is recorded. A retry should not damage the table or duplicate data.
Before ALTER TABLE, check whether the table, column, or index already exists. Table and column names in SQL should be built only from controlled values, never from user data. Use prepared statements for values.
Data transformations must also be repeatable. For example, process records where the new field has not yet been populated, or use a stable identifier as a cursor. If a repeated request encounters an already processed record, it must not create a duplicate.
- Check that a table exists before creating it.
- Check that a column exists before using ADD COLUMN if the SQL dialect and MySQL version do not guarantee a safe IF NOT EXISTS option for that operation.
- Plan for a partially completed step when renaming columns.
- Do not rely only on the version number: also verify the table’s actual state.
- Logically separate DDL changes, data conversion, and index creation.
global $wpdb;
$table = $wpdb->prefix . 'my_plugin_items';
$table_exists = $wpdb->get_var(
$wpdb->prepare('SHOW TABLES LIKE %s', $table)
);
if ($table_exists === $table) {
$column_exists = $wpdb->get_var(
$wpdb->prepare('SHOW COLUMNS FROM `' . esc_sql($table) . '` LIKE %s', 'status')
);
if (!$column_exists) {
$wpdb->query('ALTER TABLE `' . esc_sql($table) . '` ADD COLUMN `status` varchar(20) NOT NULL DEFAULT \'new\'');
}
}
When to Use dbDelta() and When to Use Separate Migrations
dbDelta() from the WordPress package is well suited to basic table-definition synchronization: creating a table or applying simple structural changes. It is often used during initial installation and for uncomplicated schemas.
However, dbDelta() is not a complete migration system. It does not move data between columns, understand business rules for transforming values, or replace controlled column renames. Complex indexes, type changes, and removing old fields also require separate testing.
The table definition passed to dbDelta() must follow its parsing rules: each field and index should be written in the expected format, and the execution result should be checked. Even when dbDelta() does not return an explicit error, verify the table’s actual structure afterward.
- Use dbDelta() for initial table creation or simple structural synchronization.
- Write a separate migration with batch processing for data transformation.
- Do not remove an old column in the same release in which code may still read it.
- Test complex indexes and type changes against a copy of production data first.
require_once ABSPATH . 'wp-admin/includes/upgrade.php';
$sql = "CREATE TABLE {$wpdb->prefix}my_plugin_items (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
status varchar(20) NOT NULL,
created_at datetime NOT NULL,
PRIMARY KEY (id),
KEY status (status)
) {$wpdb->get_charset_collate()};";
dbDelta($sql);
Process Large Datasets in Batches
Adding a column is usually faster than populating it for hundreds of thousands of records. Do not combine these operations into one unrestricted UPDATE query: it may lock the table for a long time, place significant load on the transaction log, and end with a timeout.
A batch migration selects a limited number of rows, processes them, and stores its progress. For navigation, a keyset approach is preferable: remember the last processed id and select records with a greater id. OFFSET can become slower on large tables as processing advances.
After each batch, store the cursor or the number of processed records. If a cron run is interrupted, the next process can continue rather than starting over. Choose the batch size based on the load of the specific site instead of treating it as universal.
- Select records using a stable, indexed key.
- Update only the fields that are required.
- Do not load the entire table into PHP memory.
- Check for errors after each batch and record progress.
- Schedule migrations for periods of low traffic when working with critical tables.
$last_id = (int) get_option('my_plugin_migration_3_last_id', 0);
$batch_size = 200;
$rows = $wpdb->get_results(
$wpdb->prepare(
"SELECT id, legacy_value FROM {$table}
WHERE id > %d AND new_value IS NULL
ORDER BY id ASC LIMIT %d",
$last_id,
$batch_size
)
);
foreach ($rows as $row) {
$new_value = my_plugin_convert_value($row->legacy_value);
$wpdb->update($table, ['new_value' => $new_value], ['id' => $row->id]);
$last_id = (int) $row->id;
}
update_option('my_plugin_migration_3_last_id', $last_id, false);
Run Migrations Through WP-Cron or WP-CLI
WP-Cron is convenient for automatically running batches, but its execution depends on site visits and hosting settings. For large sites or predictable maintenance, use a system cron job that calls WP-CLI instead. In either case, the migration must be safe to run again without corrupting data.
Do not allow two identical processes to run in parallel. Before starting, use a short-lived lock with an expiration time or another locking mechanism. Also provide recovery from a stalled process: the lock must not remain indefinitely.
It is useful to have a separate status command showing the current version, the last processed id, the number of errors, and an approximate amount of remaining work. WP-CLI also makes it possible to run a migration manually on staging or production under administrator supervision.
- Each cron run should perform a limited amount of work.
- Do not rely on a browser request as the only way to complete a migration.
- Log errors without recording confidential data.
- Add a dry-run mode or a status command when the operation is critical.
- Remove temporary cron events and service options after completion.
if (!get_transient('my_plugin_migration_lock')) {
set_transient('my_plugin_migration_lock', 1, 5 * MINUTE_IN_SECONDS);
try {
my_plugin_process_next_batch();
} finally {
delete_transient('my_plugin_migration_lock');
}
}
Pre-Production Checks
Before a critical change, create a database backup and confirm that it can actually be restored. The existence of a dump file alone does not prove that recovery will succeed. For large tables, separately assess available disk space, execution time, and possible locking.
Test every version transition on a staging copy of the data: from the old schema to the new one, after partial execution, after an error in the middle of a batch, and during a retry. Also test updates across several versions, where the user does not run every release individually.
After the migration, check row counts, NULL values, duplicates, indexes, and the plugin’s main functionality. If a new column temporarily coexists with an old one, define when the old column can be safely removed—usually after a separate transition release.
- Create a backup before DDL operations and bulk updates.
- Test the migration on staging with data that is similar to production in both volume and structure.
- Prepare a rollback plan: restore the backup, disable the new code, or reverse the data transformation.
- Confirm that the database user has permission to perform the required operations.
- After release, monitor logs, query times, and plugin errors.
Common WordPress Plugin Migration Mistakes
The most common mistake is recording the new schema version before executing the SQL. The system then considers the migration complete and will not try it again, even though the table may still have its old structure.
Another problem is using one large UPDATE for all data or running a migration on every request without checking its state. This creates unpredictable load and can degrade site performance. It is also unsafe to add a column unconditionally: a retry will fail with a “column already exists” error.
Do not remove old fields immediately after adding new ones if different web nodes, cron processes, or cached PHP files may briefly run different code versions. First add a compatible structure, migrate the data, switch reads and writes, and remove the old field as a separate step.
- Do not consider plugin activation a guarantee that a migration will run after an update.
- Do not use dbDelta() as a replacement for data migrations.
- Do not run long operations in an administrative HTTP request without an execution limit.
- Do not keep progress only in the memory of the current process.
- Do not verify success solely by the absence of a PHP error—check the database’s actual state.
Frequently asked questions
Where is the best place to run migration checks?
You can perform a lightweight check while the plugin or an administrative request is loading, but not the long-running work itself. The check should detect that the schema version is outdated and schedule a batch through WP-Cron or pass execution to WP-CLI.
Is dbDelta() enough to update a table?
For simple table creation or basic structural synchronization, it often is. However, dbDelta() does not replace data migration, complex renaming, progress tracking, or recovery handling after partial execution.
What should I do if a migration fails after several batches?
Do not mark it as complete. The saved cursor should allow processing to continue from the last successful batch. A retry must check records that were already processed and avoid creating duplicates.
Is a backup necessary before adding a single column?
Yes, if the change is being made in production and the table is important to the plugin. Even a simple DDL operation can expose permission, disk-space, locking, or schema-compatibility problems.
Conclusion
A reliable WordPress plugin migration is a controlled process, not an SQL query hidden inside register_activation_hook(). Store a separate schema version, apply changes sequentially, and make every step idempotent.
For large tables, use batch processing with persistent progress, protection against parallel execution, and execution through WP-Cron or WP-CLI. Before production, always create and verify a backup, test the process on staging, and confirm the actual result in the database.