Transactional DDL: Why PostgreSQL Rolls Back Failed Migrations

Transactional DDL means that schema changes take part in transactions like any other statement: a CREATE TABLE inside a transaction disappears if that transaction rolls back. PostgreSQL, SQL Server and SQLite offer it, with a short list of exceptions. MySQL, MariaDB and Oracle commit implicitly around each DDL statement, so a migration that fails on its third statement leaves the first two applied. This changes how you write migrations and how your migration tool recovers from failure.

What "transactional DDL" actually promises#

Data Definition Language covers CREATE, ALTER and DROP of tables, indexes, constraints, views and the rest of the catalog. An engine with transactional DDL treats catalog changes as ordinary writes: they are visible inside the transaction that made them, invisible to others until commit, and undone on rollback. The promise is atomicity across several statements, not just within one.

The following sequence behaves very differently across engines:

BEGIN;
CREATE TABLE owners (id INT PRIMARY KEY, last_name VARCHAR(50) NOT NULL);
CREATE TABLE pets (id INT PRIMARY KEY, owner_id INT NOT NULL REFERENCES owners (id));
CREATE INDEX idx_pets_owner ON pets (owner_idd);   -- typo: fails
COMMIT;

On PostgreSQL the transaction is aborted by the error, and after ROLLBACK neither table exists. On MySQL both tables exist and the failed index does not, and there is no transaction to roll back because each CREATE TABLE committed on its own.

Engine by engine#

PostgreSQL#

Almost everything is transactional, including ALTER TABLE variants that rewrite the table. The exceptions exist for good reasons: CREATE INDEX CONCURRENTLY works in several internal transactions so that it does not block writers, and therefore cannot run inside your transaction. Tools expose a switch for this: Liquibase has runInTransaction="false" on a changeSet, Flyway lets you configure whether a migration executes in a transaction, and Django lets you set atomic = False on a migration class.

SQL Server#

Table, index and constraint DDL is transactional and rolls back cleanly. What you cannot wrap are operations on the database itself. EF Core and DbUp both rely on this, applying each migration or script inside a transaction where they can.

SQLite#

SQLite's transactions cover DDL too, which makes it pleasant for tests. The pain is elsewhere: ALTER TABLE supports only rename table, rename column, add column and drop column, so any other change needs the "create new table, copy, drop, rename" dance. See what breaks when SQLite stands in for PostgreSQL.

MySQL and MariaDB#

Every DDL statement performs an implicit commit, both before it runs (committing whatever your session had pending) and after it finishes. MySQL's atomic DDL, introduced with the transactional data dictionary in 8.0, guarantees that a single statement either completes or leaves no trace even if the server crashes mid-way. It does not, and cannot, group statements. The same holds for MariaDB, which implemented its own atomic and crash-safe DDL.

Oracle#

Oracle commits implicitly before and after DDL. A migration that mixes an INSERT with a following ALTER TABLE commits the insert the moment the DDL starts, whether or not the DDL succeeds. This is one reason Oracle shops keep data changes and schema changes in separate scripts.

What a half-applied migration looks like#

Every migration tool keeps a history table: flyway_schema_history, databasechangelog, django_migrations, alembic_version, __EFMigrationsHistory and so on. On an engine with transactional DDL, the history row and the schema change are committed together, so the history is always truthful. Without transactional DDL, a failure leaves the schema partly changed and the history either missing the row or, in Flyway's case, holding a row marked as failed.

Recovery is manual on those engines. You inspect what was applied, finish or undo it by hand, and then tell the tool: flyway repair removes the failed history entry, Liquibase's changelog-sync or a manual delete from databasechangelog does the equivalent, and Django's migrate --fake records a migration as applied without running it. None of that is needed on PostgreSQL, where you fix the file and rerun.

How the tools behave#

• Flyway runs each migration in its own transaction where the engine allows it and marks the history row failed otherwise. Statements that refuse to run in a transaction need the per-migration transaction setting turned off, and mixing transactional and non-transactional statements in one file needs the mixed option.
• Liquibase runs each changeSet in a transaction by default; runInTransaction="false" opts out for things like concurrent index builds.
• Django wraps a migration in one transaction on PostgreSQL and SQLite, and documents that on MySQL a failed migration can leave the database in an intermediate state.
• Alembic uses transactional DDL where available and offers a transaction-per-migration mode.
• goose runs each migration in a transaction unless the file says -- +goose NO TRANSACTION; dbmate does the same with a transaction:false option on the up block; Sqitch generates PostgreSQL deploy scripts that begin and commit explicitly.
• golang-migrate runs the file as sent; on PostgreSQL you add BEGIN; and COMMIT; yourself if you want atomicity.

Writing migrations that survive either world#

You can write migrations that are safe on PostgreSQL and still recoverable on MySQL or Oracle by following a few habits.

1. One purpose per migration. A file that creates one table, or adds one column and its index, leaves little to clean up when it fails halfway.
2. Keep DDL and DML apart. A data backfill in the same file as an ALTER TABLE is committed by the DDL on Oracle and MySQL before you know whether the rest works.
3. Prefer idempotent statements where the dialect has them. CREATE TABLE IF NOT EXISTS and DROP INDEX IF EXISTS let you rerun a repaired migration without editing history tables.
4. Put the failing statement first. If one statement is likely to fail (a new constraint over existing data, say), run it before the statements that are hard to undo.
5. Test on the real engine. Run every migration set against a throw-away database of the same engine and version before it reaches a shared environment. Docker makes this cheap.
6. Know the exceptions on the transactional engines. CREATE INDEX CONCURRENTLY in a Flyway migration that runs in a transaction fails immediately; configure the exception rather than removing the concurrency.

Where SchemaDrawer fits#

A failed migration is easier to reason about when you can see the schema it was supposed to produce. SchemaDrawer runs your migration files in Docker against a fresh PostgreSQL, MySQL, MariaDB, SQL Server, Oracle or SQLite of the version you choose. If the tool fails, the job ends with the tool's own error output, the same message you would see locally, and nothing is left behind because the database is thrown away. If it succeeds, you get an SVG diagram of the tables, columns, keys and foreign keys the migration set produces. Running the set against both PostgreSQL and MySQL is a quick way to catch statements that rely on one engine's behaviour.

Key takeaways#

• PostgreSQL, SQL Server and SQLite roll DDL back with the transaction; MySQL, MariaDB and Oracle commit around every DDL statement.
• Atomic DDL in MySQL protects a single statement against crashes; it is not transactional DDL.
• Tools can only be as atomic as the engine lets them; on non-transactional engines, plan for manual repair.
• Small, single-purpose, idempotent migrations with DDL and DML separated behave well everywhere.