Migration operation taxonomy

A migration is not one risk. It is a list of statements, each with its own lock mode, its own rewrite behavior, and its own blast radius. This skill gives you the classification scheme: how to split a migration into statements, sort each one into one of eight categories, attach a severity that a lock or rewrite fact justifies, and record which engine and which engine version the answer depends on.

Scope boundary

This skill classifies what a migration does and how dangerous that is.

It deliberately does not:

• write or rewrite migration SQL,
• choose a migration tool or framework,
• run, apply, or roll back a migration,
• read the repository to find migration files or downstream consumers.

If you need a severity for a statement, you are in the right place. If you need the statement itself authored or executed, you are not.

Step 1 - Fix the three inputs before classifying anything

Classification is meaningless without these. Record them explicitly, and say "unknown" rather than guessing.

If the engine is unknown, classify the statement in every engine you plausibly target and report the worst case, labelled per engine. Do not collapse two engines into one verdict.

Step 2 - Split the migration into statements

One row of output per statement, not per file. A single file mixing DROP COLUMN, ADD COLUMN ... NOT NULL, and ADD CONSTRAINT produces three classifications with three different severities. Multi-clause ALTER TABLE t ADD COLUMN a, ALTER COLUMN b TYPE ... splits into one row per clause, because the clauses carry different lock and rewrite behavior.

Step 3 - Sort each statement into one of eight categories

A statement can land in more than one category. DROP COLUMN on a populated column is both Breaking (category 5) and Data-loss (category 6); report it once at the higher severity and name both categories.

Severity rationale

Severity is not a feeling. Each band is anchored to a concrete consequence:

• Critical: the statement can take production down, destroy data that no backup step precedes, or break a live consumer with no coordination window. A Critical statement needs a plan, not a nit.
• Warning: measurable downtime or a performance regression that a team-side mitigation (off-peak scheduling, a concurrent variant, an index added first) can remove. The statement is correct; the timing is not.
• Info: the operation is safe at the sizes in play. Reported for completeness so the reader can see the classification was applied to every statement, not just the alarming ones.

Anything that only becomes Critical above some table size is a Warning until a size signal exists. Say so in the finding rather than inflating.

Step 4 - Apply the five engine hazard checks

These are the checks that decide whether a category-3 or category-4 statement is a Warning or a Critical.

Check 1 - Index build without the concurrent variant (PostgreSQL)

A plain CREATE INDEX acquires a SHARE lock, which per the PostgreSQL explicit-locking reference "protects a table against concurrent data changes" and is "Acquired by CREATE INDEX (without CONCURRENTLY)". The CREATE INDEX reference puts the effect plainly: "Other transactions can still read the table, but if they try to insert, update, or delete rows in the table they will block until the index build is finished."

CREATE INDEX CONCURRENTLY avoids that, at a cost the same page states: PostgreSQL "must perform two scans of the table, and in addition it must wait for all existing transactions that could potentially modify or use the index to terminate", and "a regular CREATE INDEX command can be performed within a transaction block, but CREATE INDEX CONCURRENTLY cannot". It also warns that on failure the command "will fail but leave behind an 'invalid' index" that "will be ignored for querying purposes because it might be incomplete; however it will still consume update overhead" (sql-createindex).

Classify a plain CREATE INDEX on a PostgreSQL target as category 3. Warning on a table with no size signal, Critical when a size signal shows a large table. The transaction-block restriction matters: a migration runner that wraps each migration in a transaction cannot run the concurrent form, so recommending it also means recommending the statement move out of the transactional path.

This check does not transfer to MySQL. Per the MySQL 8.0 InnoDB online DDL operations reference, adding a secondary index is In Place, permits concurrent DML, and does not rebuild the table: "The table remains available for read and write operations while the index is being created." On MySQL 8.0 a plain ADD INDEX is category 1, Info.

Check 2 - Full table rewrite under an exclusive lock

The PostgreSQL ALTER TABLE reference states the baseline: "An ACCESS EXCLUSIVE lock is acquired unless explicitly noted." That mode, per explicit-locking, "Conflicts with locks of all modes" and "guarantees that the holder is the only transaction accessing the table in any way", so it blocks reads as well as writes.

A rewrite on top of that lock is what turns a fast statement into an outage. From sql-altertable:

• Type change: "Changing the type of an existing column will normally cause the entire table and its indexes to be rewritten." The exception is narrow: "if the USING clause does not change the column contents and the old type is either binary coercible to the new type or an unconstrained domain over the new type, a table rewrite is not needed. However, indexes will still be rebuilt unless the system can verify that the new index would be logically equivalent to the existing one."
• Column addition with certain defaults: "Adding a column with a volatile DEFAULT (e.g., clock_timestamp()), a stored generated column, an identity column, or a column with a domain data type that has constraints will cause the entire table and its indexes to be rewritten. Adding a virtual generated column never requires a rewrite."

Version gate. The constant-default case is the one that changed. PostgreSQL 11 shipped "Allow ALTER TABLE to add a column with a non-null default without doing a table rewrite", noting "This is enabled when the default value is a constant" (PostgreSQL 11 release notes). The current manual states the resulting behavior: with a non-volatile DEFAULT "the default value is evaluated at the time of the statement and the result stored in the table's metadata ... making the ALTER TABLE very fast even on large tables", and "In neither case is a rewrite of the table required" (sql-altertable). So ADD COLUMN x int NOT NULL DEFAULT 0 is category 1 on PostgreSQL 11 and later, and category 3 Critical on PostgreSQL 10 and earlier. Do not classify it without the version.

MySQL 8.0 InnoDB divergence. A type change is worse, not better: "Changing the column data type is only supported with ALGORITHM=COPY", which does not permit concurrent DML and rebuilds the table (innodb-online-ddl-operations). Column addition is better: INSTANT "is the default algorithm as of MySQL 8.0.12, and INPLACE before that", and "Before MySQL 8.0.29, the INSTANT algorithm could only add a column as the last column of the table". The same page notes the in-place fallback still costs a rebuild: "The table is rebuilt if ALGORITHM=INPLACE is used to add a column", and concurrent DML "is not permitted when adding an auto-increment column".

Check 3 - Statistics gap after a large data change

ANALYZE "collects statistics about the contents of tables in the database, and stores the results in the pg_statistic system catalog. Subsequently, the query planner uses these statistics to help determine the most efficient execution plans for queries", and "Accurate statistics will help the planner to choose the most appropriate query plan, and thereby improve the speed of query processing" (sql-analyze). The same page ties the explicit call to autovacuum state: "When autovacuum is disabled, it is a good idea to run ANALYZE periodically, or just after making major changes in the contents of a table."

That is the documented rule. Running ANALYZE at the end of a bulk-load migration even when autovacuum is enabled, so that the first queries after deploy do not plan against pre-load statistics, is a practitioner convention, not a documented requirement. Flag its absence after a bulk INSERT, UPDATE, or DELETE as Warning and say which of the two it is: a documented gap if autovacuum is off on the target, a convention otherwise.

Check 4 - Partition key touched

Per the PostgreSQL partitioning reference, partition pruning is where "the planner will examine the definition of each partition and prove that the partition need not be scanned because it could not contain any rows meeting the query's WHERE clause", and critically "partition pruning is driven only by the constraints defined implicitly by the partition keys, not by the presence of indexes."

Because pruning depends on the partition key and nothing else, any statement that changes the partition key column (its type, its semantics, or the expression the table is partitioned by) puts every pruned plan in the system at risk, and no index can compensate. Classify a statement touching a column named in PARTITION BY RANGE (...), PARTITION BY LIST (...), or PARTITION BY HASH (...) as category 3, Warning, and require a plan check (compare EXPLAIN output before and after) rather than asserting that pruning will or will not break. The reference states what pruning depends on; it does not enumerate which alterations defeat it.

Check 5 - VARCHAR boundary crossing (MySQL)

Per innodb-online-ddl-operations: "For VARCHAR columns of 0 to 255 bytes in size, one length byte is required to encode the value. For VARCHAR columns of 256 bytes in size or more, two length bytes are required. As a result, in-place ALTER TABLE only supports increasing VARCHAR column size from 0 to 255 bytes, or from 256 bytes to a greater size. In-place ALTER TABLE does not support increasing the size of a VARCHAR column from less than 256 bytes to a size equal to or greater than 256 bytes."

So varchar(100) to varchar(500) crosses the boundary and falls back to a copy; varchar(300) to varchar(900) does not. Shrinking is always a copy: "Decreasing VARCHAR size using in-place ALTER TABLE is not supported. Decreasing VARCHAR size requires a table copy (ALGORITHM=COPY)."

Classify a boundary-crossing widen as category 3 Critical on MySQL, and a narrowing as category 6 and 7 (a copy plus potential truncation of existing values). Note that the byte counts are byte sizes, not character counts, so a multibyte charset reaches 256 bytes at fewer than 256 characters.

Category 4 and 8: what the foreign key statement actually locks

The source claim worth correcting: on PostgreSQL, ADD FOREIGN KEY does not take the default ACCESS EXCLUSIVE. Per sql-altertable: "Although most forms of ADD table_constraint require an ACCESS EXCLUSIVE lock, ADD FOREIGN KEY requires only a SHARE ROW EXCLUSIVE lock. Note that ADD FOREIGN KEY also acquires a SHARE ROW EXCLUSIVE lock on the referenced table, in addition to the lock on the table on which the constraint is declared."

That second sentence is the whole reason category 4 exists: the statement names one table and locks two, and SHARE ROW EXCLUSIVE "protects a table against concurrent data changes" (explicit-locking), so writes stop on a table nobody reading the migration would think to check. Warning, escalating to Critical when the referenced table is a hot write target.

The two-phase form is the mitigation, and its lock cost is documented: "With NOT VALID, the ADD CONSTRAINT command does not scan the table and can be committed immediately ... validation acquires only a SHARE UPDATE EXCLUSIVE lock on the table being altered. (If the constraint is a foreign key then a ROW SHARE lock is also required on the table referenced by the constraint.)" (sql-altertable). A migration split into ADD CONSTRAINT ... NOT VALID then VALIDATE CONSTRAINT moves from category 4 to category 2.

Category 8 is separate and survives either form. PostgreSQL does not index the referencing side for you: "Since a DELETE of a row from the referenced table or an UPDATE of a referenced column will require a scan of the referencing table for rows matching the old value, it is often a good idea to index the referencing columns too" (ddl-constraints). An ADD CONSTRAINT ... FOREIGN KEY (col) with no index on col is a Warning whose cost lands later, on every delete and key update against the referenced table.

On MySQL 8.0, the same statement has a different constraint: "The INPLACE algorithm is supported when foreign_key_checks is disabled. Otherwise, only the COPY algorithm is supported" (innodb-online-ddl-operations). So on a default MySQL configuration, adding a foreign key is a full table copy, and belongs in category 3 as well as category 4.

Category 5, 6, 7: the destructive end

DROP COLUMN is Breaking immediately and Data-loss eventually. The data does not go away when you think it does: "The DROP COLUMN form does not physically remove the column, but simply makes it invisible to SQL operations ... dropping a column is quick but it will not immediately reduce the on-disk size of your table, as the space occupied by the dropped column is not reclaimed. The space will be reclaimed over time as existing rows are updated" (sql-altertable). Classify it Critical for the consumer break, and note that the quick completion time is not evidence the operation was cheap or reversible.

TRUNCATE is category 6 and category 3 at once. It "quickly removes all rows from a set of tables. It has the same effect as an unqualified DELETE on each table, but since it does not actually scan the tables it is faster. Furthermore, it reclaims disk space immediately", and it "acquires an ACCESS EXCLUSIVE lock on each table it operates on, which blocks all other concurrent operations on the table" (sql-truncate). One nuance worth carrying into the finding: on PostgreSQL it is not unrecoverable mid-transaction, because "TRUNCATE is transaction-safe with respect to the data in the tables: the truncation will be safely rolled back if the surrounding transaction does not commit."

Category 7, ADD COLUMN ... NOT NULL with no DEFAULT. State the engine evidence honestly here, because the two engines document it differently.

• MySQL 8.0 is explicit for the modify form: making a column NOT NULL "Rebuilds the table in place. STRICT_ALL_TABLES or STRICT_TRANS_TABLES SQL_MODE is required for the operation to succeed. The operation fails if the column contains NULL values" (innodb-online-ddl-operations).
• The PostgreSQL ALTER TABLE reference does not state the outcome of ADD COLUMN ... NOT NULL with no DEFAULT on a populated table. What it does state is the rule the safe pattern rests on: "SET NOT NULL may only be applied to a column provided none of the records in the table contain a NULL value for the column. Ordinarily this is checked during the ALTER TABLE by scanning the entire table ... however, if a valid CHECK constraint exists (and is not dropped in the same command) which proves no NULL can exist, then the table scan is skipped" (sql-altertable), alongside the fact that with no column constraints specified "NULL is used as the DEFAULT".

Classify it Critical, and write the finding as "verify on the target engine" rather than asserting a failure mode the PostgreSQL reference does not print. The recommendation is the same either way: add the column nullable, backfill in batches, then SET NOT NULL, ideally behind a CHECK constraint so the final step skips the full scan.

Per-engine quick reference

PostgreSQL, current manual (fetched 2026-07-19), version notes called out where behavior changed.

MySQL 8.0 InnoDB, all rows from innodb-online-ddl-operations.

The three divergences that flip a severity

1. Index creation. Blocks writes on PostgreSQL without CONCURRENTLY; permits concurrent DML on MySQL 8.0. Category 3 versus category 1.
2. Column type change. Rewrites on PostgreSQL under ACCESS EXCLUSIVE, and on MySQL forces ALGORITHM=COPY with no concurrent DML. Critical on both, but for different reasons, and the MySQL mitigation (a copy-based online schema change tool) is not the PostgreSQL one.
3. Adding a foreign key. A metadata-cheap SHARE ROW EXCLUSIVE on PostgreSQL that spreads to a second table, versus a full table copy on a default-configured MySQL. Warning versus Critical.

Worked example

Target: PostgreSQL 16, users 40M rows, orders 180M rows, products 2M rows, sizes taken from a prior data-load migration in the same series.

ALTER TABLE users DROP COLUMN legacy_status;
ALTER TABLE orders ADD COLUMN shipped_at timestamptz NOT NULL;
ALTER TABLE orders ADD COLUMN region text NOT NULL DEFAULT 'unknown';
CREATE INDEX ix_orders_status ON orders (status);
ALTER TABLE products ADD CONSTRAINT fk_category
  FOREIGN KEY (category_id) REFERENCES categories(id);

Classification:

Note how statements 2 and 3 differ by one clause and three severity bands, and how statement 4 would be Info against MySQL 8.0.

Expected output shape

One row per statement, then a count block. Severity, category, and basis are all required; a row with a severity and no basis is not a classification.

| # | Severity | File:Line | Statement | Category | Basis (engine + version) | Recommendation |
|---|---|---|---|---|---|---|
| 1 | Critical | V42__cleanup.sql:1 | DROP COLUMN users.legacy_status | 5 Breaking, 6 Data-loss | PG 16: column made invisible, space not reclaimed | Stage the drop behind a deprecation release |
| 4 | Critical | V42__cleanup.sql:8 | CREATE INDEX ix_orders_status ON orders (status) | 3 Locking | PG 16: SHARE lock blocks writes for the build; orders 180M rows | Use CREATE INDEX CONCURRENTLY, outside any transaction block |

Engine: PostgreSQL 16
Size signals: users 40M, orders 180M, products 2M (from V38 data load)

Critical: 3
Warning:  1
Info:     1

Highest-severity category present: 6 Data-loss
Unclassifiable statements: 0

If the engine or version could not be established, the block says so and every version-gated severity is reported as a range, not a value.

Anti-patterns

• Classifying a file instead of its statements. A file gets the severity of its worst statement, but the reader needs to know which one.
• Asserting a lock or rewrite behavior without naming the engine. "This blocks writes" is not a classification. "On PostgreSQL 16 this takes a SHARE lock, which blocks writes" is.
• Treating engine behavior as timeless. Constant-default ADD COLUMN and instant ADD COLUMN both changed inside the supported version ranges. A severity that does not carry a version is a guess.
• Guessing a table size to justify a Critical. No size signal means the finding says "Warning, escalates to Critical above roughly the point where the lock hold becomes visible to users; size unverified".
• Collapsing categories 5 and 6. A DROP CONSTRAINT breaks consumers without losing data; a TRUNCATE loses data without breaking a schema contract. Different mitigations.
• Reporting only the frightening statements. Category 1 rows are the evidence that the taxonomy was applied to everything.

Limitations

• The categories cover DDL and bulk DML. They do not cover data correctness inside a backfill (whether the UPDATE computes the right value), or application-level compatibility beyond the schema surface.
• Severity depends on a table size signal this skill does not obtain. It tells you which fact you need and what to do when you do not have it.
• Engine coverage here is PostgreSQL and MySQL 8.0 InnoDB, the two whose divergence causes the most misclassification. Other engines (SQL Server, Oracle, SQLite, MariaDB) need their own lock and rewrite references before any severity is assigned; do not transfer a PostgreSQL severity to them by analogy.
• MySQL rows above are the 8.0 manual. MySQL 8.4 and later, and MariaDB, document their online DDL separately and are not covered by the cited page.