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 takes a SHARE lock: "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" (sql-createindex (opens in new window), explicit-locking (opens in new window)). CREATE INDEX CONCURRENTLY avoids the write block but "must perform two scans of the table" and "cannot" run inside a transaction block; on failure it leaves an "invalid" index that "will still consume update overhead" (sql-createindex (opens in new window)).

Classify a plain CREATE INDEX on PostgreSQL as category 3: Warning with no size signal, Critical on a large table. Because the concurrent form cannot run in a transaction, recommending it also means moving the statement out of the migration runner's transactional path.

This check does not transfer to MySQL. Adding a secondary index on MySQL 8.0 InnoDB is In Place and permits concurrent DML: "The table remains available for read and write operations while the index is being created" (innodb-online-ddl-operations (opens in new window)). A plain ADD INDEX is category 1, Info.

Check 2 - Full table rewrite under an exclusive lock

ALTER TABLE takes an ACCESS EXCLUSIVE lock "unless explicitly noted", which "Conflicts with locks of all modes", so it blocks reads as well as writes (sql-altertable (opens in new window), explicit-locking (opens in new window)). A rewrite under that lock is what turns a fast statement into an outage (sql-altertable (opens in new window)):

• Type change: "Changing the type of an existing column will normally cause the entire table and its indexes to be rewritten", except a binary-coercible USING-less change, which skips the rewrite but still rebuilds indexes.
• Certain defaults: a volatile DEFAULT, a stored generated column, an identity column, or a constrained domain type "will cause the entire table and its indexes to be rewritten"; a virtual generated column never does.

Version gate. PostgreSQL 11 added a column with a non-null constant default "without doing a table rewrite" - the value is stored in the table's metadata, "making the ALTER TABLE very fast even on large tables" (release 11.0 (opens in new window), sql-altertable (opens in new window)). So ADD COLUMN x int NOT NULL DEFAULT 0 is category 1 on PostgreSQL 11 and later, and category 3 Critical on 10 and earlier. Do not classify it without the version.

MySQL 8.0 InnoDB divergence. Type change is worse: "only supported with ALGORITHM=COPY", no concurrent DML, rebuilds the table. Column addition is better: INSTANT is default from 8.0.12 (INPLACE before), last position only before 8.0.29; the INPLACE fallback still rebuilds, and concurrent DML "is not permitted when adding an auto-increment column" (innodb-online-ddl-operations (opens in new window)).

Check 3 - Statistics gap after a large data change

ANALYZE refreshes the planner statistics in pg_statistic, and "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" (sql-analyze (opens in new window)). Running it at the end of a bulk-load migration even when autovacuum is enabled is a practitioner convention, not a documented requirement. Flag its absence after a bulk INSERT, UPDATE, or DELETE as Warning, and say which it is: a documented gap if autovacuum is off on the target, a convention otherwise.

Check 4 - Partition key touched

Partition pruning "is driven only by the constraints defined implicitly by the partition keys, not by the presence of indexes" (ddl-partitioning (opens in new window)). So any statement that changes the partition key column (its type, its semantics, or the partitioning expression) puts every pruned plan at risk, and no index can compensate. Classify a statement touching a column named in PARTITION BY RANGE / LIST / HASH (...) as category 3, Warning, and require a plan check (compare EXPLAIN before and after) rather than asserting whether pruning breaks.

Check 5 - VARCHAR boundary crossing (MySQL)

VARCHAR needs one length byte below 256 bytes and two at 256 or more, so "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"; decreasing size "requires a table copy (ALGORITHM=COPY)" (innodb-online-ddl-operations (opens in new window)). So varchar(100) to varchar(500) crosses the boundary and copies; varchar(300) to varchar(900) does not. Classify a boundary-crossing widen as category 3 Critical on MySQL, a narrowing as category 6 and 7 (a copy plus potential truncation). Byte counts are bytes, not characters, 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 (opens in new window): "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 (opens in new window)), 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 (opens in new window)). 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 (opens in new window)). 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 (opens in new window)). 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 (opens in new window)). 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 (opens in new window)). 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 (opens in new window)).
• 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 (opens in new window)), 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

The consolidated PostgreSQL and MySQL 8.0 InnoDB lock / rewrite tables, and the three divergences that flip a severity, are in references/engine-quick-reference.md. A full worked classification (PostgreSQL 16, five statements across users, orders, products) is in the same file.

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.