The detector at alembic_postgresql_enum/detection_of_changes/enum_alteration.py always emits SyncEnumValuesOp, regardless of how the enum changed. For pure appends (the common case — adding a new variant), SyncEnumValuesOp is dramatically more expensive than necessary: it renames the type, creates a new one, then casts every column referencing the enum to the new type, which rewrites every row under ACCESS EXCLUSIVE.
We just hit this in production. Migration added one new variant to an enum. The referencing table has ~2.9M rows. The full rewrite exceeded our deploy's startup-probe deadline, the migrator was killed mid-rewrite, and instances served traffic against the old schema → 500s on every request that used the new variant.
PostgreSQL has ALTER TYPE name ADD VALUE IF NOT EXISTS 'X', which is O(1), no table rewrite, no exclusive lock on the referencing table.
Notably, enum_alteration.py:2 already documents this as the intended behavior:
Alembic extension to generate ALTER TYPE ... ADD VALUE statements to update SQLAlchemy enums.
…but the code emits SyncEnumValuesOp instead.
Proposal
When old_values is an ordered prefix of new_values (or, with ignore_enum_values_order=True, when set(old) ⊂ set(new)) and enum_values_to_rename is empty, emit a new AddEnumValuesOp that renders to one op.execute("ALTER TYPE ... ADD VALUE IF NOT EXISTS '...'") per added value. Everything else still routes to SyncEnumValuesOp as today.
Design notes / things to decide
- API surface — opt-in vs default-on? Default-off (
Config.use_alter_type_for_appends=False) is a zero-behavior-change minor release. Default-on is safer for users but a behavior change → would want a major bump. I'd lean default-on given the risk profile, but happy with either. Prefer your call.
- PG version.
ALTER TYPE ... ADD VALUE inside a transaction requires PG ≥ 12 (PG 11 has been EOL since Nov 2023). Easiest: fast path assumes PG ≥ 12; fall back to SyncEnumValuesOp otherwise. Docstring warns that the newly-added value can't be referenced in the same transaction.
- Downgrade. PostgreSQL has no
DROP VALUE, so AddEnumValuesOp.reverse() returns a SyncEnumValuesOp with values swapped. Asymmetric but unavoidable.
Happy to send a PR if the proposal sounds good — want to confirm API preference before I write it.
The detector at
alembic_postgresql_enum/detection_of_changes/enum_alteration.pyalways emitsSyncEnumValuesOp, regardless of how the enum changed. For pure appends (the common case — adding a new variant),SyncEnumValuesOpis dramatically more expensive than necessary: it renames the type, creates a new one, then casts every column referencing the enum to the new type, which rewrites every row underACCESS EXCLUSIVE.We just hit this in production. Migration added one new variant to an enum. The referencing table has ~2.9M rows. The full rewrite exceeded our deploy's startup-probe deadline, the migrator was killed mid-rewrite, and instances served traffic against the old schema → 500s on every request that used the new variant.
PostgreSQL has
ALTER TYPE name ADD VALUE IF NOT EXISTS 'X', which is O(1), no table rewrite, no exclusive lock on the referencing table.Notably,
enum_alteration.py:2already documents this as the intended behavior:…but the code emits
SyncEnumValuesOpinstead.Proposal
When
old_valuesis an ordered prefix ofnew_values(or, withignore_enum_values_order=True, whenset(old) ⊂ set(new)) andenum_values_to_renameis empty, emit a newAddEnumValuesOpthat renders to oneop.execute("ALTER TYPE ... ADD VALUE IF NOT EXISTS '...'")per added value. Everything else still routes toSyncEnumValuesOpas today.Design notes / things to decide
Config.use_alter_type_for_appends=False) is a zero-behavior-change minor release. Default-on is safer for users but a behavior change → would want a major bump. I'd lean default-on given the risk profile, but happy with either. Prefer your call.ALTER TYPE ... ADD VALUEinside a transaction requires PG ≥ 12 (PG 11 has been EOL since Nov 2023). Easiest: fast path assumes PG ≥ 12; fall back toSyncEnumValuesOpotherwise. Docstring warns that the newly-added value can't be referenced in the same transaction.DROP VALUE, soAddEnumValuesOp.reverse()returns aSyncEnumValuesOpwith values swapped. Asymmetric but unavoidable.Happy to send a PR if the proposal sounds good — want to confirm API preference before I write it.