Are there any disadvantages to using transaction_per_migration in production?

[megh-khaire]

The Alembic documentation doesn't go into much detail about transaction_per_migration, so I wanted to understand the tradeoffs before enabling it in production. From what I understand, the default behavior wraps all migrations in a single alembic upgrade head run into one transaction. This means if migration number 4 out of 5 fails, everything rolls back cleanly.

With transaction_per_migration=True, each migration file gets its own transaction. If 4 fails, migrations 1–3 are already committed and you're left in a partially-migrated state. My thinking is that transaction_per_migration=True should be safe in production as long as: Migrations are not tightly coupled across files (i.e., a partial upgrade to an intermediate revision leaves the database in a usable state)

For anyone using this in production: have you run into any issues or edge cases worth knowing about?

Context: This came up because I occasionally include UPDATE statements in my Alembic migrations to back-populate old or new columns, sometimes involving enum types. For example, adding a new value to a PostgreSQL enum and then using it in an UPDATE within the same migration.

This works fine when running migrations incrementally, but fails when setting up a fresh environment with alembic upgrade head. Since the default behavior (transaction_per_migration=False) wraps everything in a single transaction, PostgreSQL rejects the UPDATE because the new enum value hasn't been committed yet:

psycopg.errors.UnsafeNewEnumValueUsage: unsafe use of new value "CANCELLED" of enum type invoicestatus
HINT: New enum values must be committed before they can be used.

The fix is to use autocommit_block() for the enum addition, but that forces an implicit commit mid-transaction. This led me to consider enabling transaction_per_migration=True so the commit behavior is explicit and predictable.

[megh-khaire]

Also, a huge thank you to the maintainers and developers of Alembic. It's an incredibly solid tool and has made database migrations so much easier to manage. Really appreciate the work that goes into it :)

[zzzeek]

yes I think if you are having to use autocommit_block() (which was explciitly added for this annoying PG ENUM case) you might as well do transaction-per-migration. like you said, if all your migrations leave the DB in a usable state, you would be OK if you got through 3 of 4 migrations.

[megh-khaire]

Thanks @zzzeek !

[CaselIT]

I use it @ work and I don't have anything to negative to report. (Also initially caused by pg-enums)

I think having migrations that are tightly couple between them so that executing a and not b will leave the db unusable is bad practice regardless of the transaction_per_migration setting