Transactional DDL: PostgreSQL Has It, MySQL Doesn't, and Your Migrations Care

It was 2:14 on a Tuesday morning, and I was eleven minutes into a schema migration against a MySQL 8.0 primary. The migration had 14 statements. The first was an instant ADD COLUMN, done in under a second thanks to 8.0's instant add. The second was a secondary index build on the orders table, about 210 million rows and 90 gigabytes, which took eleven minutes and completed fine. The third statement was a CREATE UNIQUE INDEX on a column called external_id, and fourteen minutes in it died: duplicate entry, because two historical rows had NULL-adjacent junk that a backfill script was supposed to have cleaned and had not.

Here is what the database looked like at that moment: the new column existed. The new secondary index existed. The unique index did not. The deploy marker my pipeline writes as the last statement had not run, so the deployment system reported failure, but two-thirds of the schema change was permanently applied. There was no ROLLBACK that meant anything. The transaction I had wrapped the whole thing in had been silently committed by the very first ALTER, and committed again by every statement after it. I did not roll back to the old schema that night. I wrote a new migration, forward, at 3 a.m., with the backfill fixed, and I got lucky that the half-applied state was compatible with the old application code.

That incident is the cleanest illustration I own of one of the deepest practical differences between MySQL and PostgreSQL: PostgreSQL executes nearly all DDL inside real transactions, so a failed migration unwinds to nothing. MySQL commits implicitly around every DDL statement, so a failed migration is a ladder you have already climbed partway up. This article walks through both behaviors, the exceptions, what your migration tooling does with each, and the operational playbook the difference forces on you.

Why can't MySQL roll back a failed migration?

MySQL cannot roll back DDL because its DDL statements cause an implicit commit before and after they execute. This is documented behavior, not a bug: CREATE, ALTER, DROP, TRUNCATE, RENAME, and friends each end any open transaction, do their work, and commit again. If you type BEGIN, run three ALTER TABLE statements, and then type ROLLBACK, the rollback applies to nothing. Each ALTER already committed on its own.

You can watch this happen. Open a session, start a transaction, and check whether anything is actually open after a DDL statement:

-- MySQL 8.0
START TRANSACTION;
ALTER TABLE orders ADD COLUMN region_id int;

SELECT COUNT(*) AS open_transactions
FROM information_schema.innodb_trx;
-- returns 0: your transaction was implicitly committed by the ALTER

The architectural reason is that InnoDB's data dictionary operations were historically not transactional with respect to the SQL layer's DDL. MySQL 8.0 moved the data dictionary into InnoDB and made individual DDL statements atomic, meaning a single crashed ALTER no longer leaves orphan dictionary entries the way 5.7 sometimes did. That is a genuine improvement, and people oversell it: atomic DDL means one statement succeeds or fails cleanly. It does not mean a multi-statement migration is atomic. The implicit commit boundary between statements is still there, and it is exactly where my 2 a.m. failure lived.

What does PostgreSQL do differently with the same migration?

PostgreSQL runs DDL inside the surrounding transaction like any other statement, so a failed migration rolls back to exactly the schema you started with. Same incident, same three statements, different engine:

-- PostgreSQL
BEGIN;

ALTER TABLE orders ADD COLUMN region_id integer;
-- catalog change, instantaneous

CREATE INDEX orders_region_idx ON orders (region_id);
-- builds, still uncommitted

CREATE UNIQUE INDEX orders_external_id_uniq ON orders (external_id);
-- ERROR: duplicate key value violates unique constraint

ROLLBACK;
-- the column and both partial indexes vanish;
-- the schema is byte-for-byte what it was before BEGIN

The duplicate-key error on the third statement aborts the transaction, and the ROLLBACK, or the implicit rollback when your client disconnects or the tool cleans up, undoes the catalog changes from statements one and two. The deploy marker either exists with the full schema change or does not exist at all. There is no third state. After a failure like this I verify the schema, fix the backfill, and rerun the identical migration file. On MySQL, rerunning the identical file would have failed on the very first statement because the column already existed, which is why MySQL migrations so often accumulate defensive IF NOT EXISTS clauses and pre-flight inspection queries.

The exception list matters, because it is short and sharp. CREATE INDEX CONCURRENTLY, DROP INDEX CONCURRENTLY, and REINDEX CONCURRENTLY deliberately cannot run inside a transaction block, since they commit internally in phases to avoid holding a lock for the whole build. CREATE DATABASE and DROP DATABASE cannot run inside a transaction either. ALTER TYPE ... ADD VALUE has version-dependent rules: on older releases it was refused inside a transaction block entirely, and on PostgreSQL 12 and later it is allowed inside a transaction but the new enum value cannot be used until that transaction commits. The practical rule: plan any concurrently-built index as its own one-statement deployment step, outside the transactional unit, and check the documentation for your exact version before trusting an edge case.

One honest cost sheet item in the other direction: transactional DDL in PostgreSQL means the migration's locks live as long as the transaction. An ALTER TABLE takes an ACCESS EXCLUSIVE lock, and if your migration does a slow index build after it inside the same transaction, that exclusive lock is held for the whole build. A forgotten open migration session in psql can quietly block every query touching the table, which is the sort of pile-up that shows up clearly once you know how to read lock waits, as covered in the PostgreSQL locks and deadlocks guide. The fix is procedural: keep lock-heavy migrations short, set lock_timeout, and split concurrent index builds into their own step.

How do Flyway, Liquibase, and sqitch behave on each engine?

Every mainstream migration tool knows about this difference and encodes it, because it changes what failure recovery can even mean. Flyway checks whether the database supports transactional DDL. On PostgreSQL it wraps each migration script in a transaction, so a failed script rolls back and Flyway marks nothing applied. On MySQL it cannot, so a failed script is left in whatever partial state it reached, the migration is marked failed in the schema history table, and the repair command exists largely because MySQL failures leave debris behind. Liquibase has the same split: a changeset is wrapped in a transaction where the platform supports it, and on MySQL each change within the changeset commits as it goes.

Sqitch builds the transactionality into its design: on engines with transactional DDL it deploys each change and its verification inside one transaction, and a failed verify rolls the change back out. Its workflows are noticeably more comfortable on PostgreSQL for exactly this reason. The general pattern across all three tools is what I think of as transaction-per-change on PostgreSQL: each migration unit is all-or-nothing, the schema history table is always in sync with reality, and rerunning after a fix is safe. On MySQL the tools degrade gracefully into best-effort tracking of a schema that may be mid-change, and the recovery path is human.

Two operational consequences follow. First, on MySQL, never trust the tool's history table as proof of schema state after a failure; inspect the actual schema with SHOW CREATE TABLE and information_schema before deciding what the fix migration should do. Second, on PostgreSQL, do not defeat the tool by sprinkling CREATE INDEX CONCURRENTLY into a transactional migration file; the statement will error, abort the transaction, and roll back work that was fine. Pull concurrent builds into their own files and configure the tool to run those files outside a transaction, which both Flyway and Liquibase support explicitly.

What should a MySQL migration playbook look like without rollback?

Since a failed MySQL migration can only be fixed forward, the playbook is about making forward fixes safe and fast. Mine has five rules, all learned the expensive way. First, take a schema-only backup immediately before the migration, with mysqldump --no-data or your equivalent, so the intended start state is recorded precisely. Second, run the migration against a restored copy or a staging replica first, on production-scale data; my duplicate-key failure would have surfaced there, because the dirty rows were old enough to exist in any real copy of the table. Third, order statements from safest to riskiest, and preflight the risky ones: for a unique index, run the duplicate-detection query as a SELECT before the migration window, not during it.

-- MySQL: preflight before creating a unique index
SELECT external_id, COUNT(*) AS n
FROM orders
WHERE external_id IS NOT NULL
GROUP BY external_id
HAVING n > 1
LIMIT 20;

Fourth, make every migration file rerunnable: guard each statement with information_schema checks or IF NOT EXISTS where the syntax allows, so the forward-fix migration can be written calmly instead of as a bespoke 3 a.m. special. Fifth, keep the old application version tolerant of the new schema for one release in both directions, because on MySQL you will sometimes be running against a schema that is ahead of your deploy marker, and rollbacks of application code must not explode against it.

On the PostgreSQL side the playbook shrinks to: wrap the migration in a transaction, set lock_timeout and statement_timeout explicitly, preflight on staging anyway because a clean rollback still costs you the migration window, and keep concurrent index builds in separate files. Same discipline, smaller blast radius. The preflight queries are identical in spirit, and the duplicate check above works verbatim in PostgreSQL too.

How does MonPG fit into migration night?

The monitoring question during a migration is the same on both engines: is the database healthy while schema change is in flight, and did anything change after it landed. On PostgreSQL today, MonPG's PostgreSQL monitoring watches what matters during the window: lock waits piling up behind an in-progress ALTER, idle-in-transaction sessions that would block the migration from acquiring its lock, and the pg_stat_statements baseline from before the deploy so a query that got slower against the new schema shows up as evidence, not a vibe.

MonPG monitors PostgreSQL today; MySQL support is on the roadmap. When it lands, the migration-night counters it will surface are exactly the ones this article is about: open transactions and their age, so you can see the implicit-commit boundary for what it is, lock waits during the ALTER window, the history list and purge lag that a long index build leaves behind, and the before-and-after statement digests that tell you whether the new schema changed your query plans. Until then, run MySQL migrations with the playbook above and a schema backup in hand, because the engine will not give you a second chance at atomicity. PostgreSQL will, and it is one of the quiet reasons migrations on it feel like a different profession.