Managing Enums, Constraints and Indexes in Migrations

Name every constraint through a MetaData naming convention, hand-write migrations for enum label changes, and build indexes and constraints on live tables with CONCURRENTLY or NOT VALID inside a lock budget — these are the schema objects where Alembic's autogenerate is incomplete or unsafe. This topic belongs to Alembic async migrations and schema evolution.

Concept & Execution Model

Alembic's autogenerate is a comparison between two descriptions of a schema: the MetaData your models build, and what the database's catalog reports through SQLAlchemy's inspector. For tables and columns that comparison is close to complete. For the other objects a production PostgreSQL schema accumulates — enum types, check constraints, named indexes, generated columns — it ranges from reliable, through partial, to absent. This topic is about those objects, and about the migrations autogenerate either cannot write or writes in a form that is unsafe on a live table.

What autogenerate can and cannot see Three tiles. Reliably detected: added and removed tables, columns, nullable changes, and named indexes, unique constraints and foreign keys. Partially detected: column type changes, detected by default in Alembic 1.12 and later, and server defaults, off by default. Not detected: enum labels, check constraint bodies, computed column expressions, and how an index should be built, such as concurrently. reliably tables and columns nullable changes named indexes, uniques, FKs partially column types (compare_type) server defaults (opt-in) unnamed constraints not at all enum labels CHECK bodies, Computed SQL CONCURRENTLY, NOT VALID Everything in the right-hand tile needs a hand-written or hand-edited migration.

Three habits cover most of it. Name every constraint through a convention, so model and database agree on names and autogenerate can compare them. Treat enum label changes as hand-written migrations, because autogenerate never sees them. And treat any index or constraint on an existing large table as an operation with a lock profile, not just a line of DDL.

import enum

from sqlalchemy import CheckConstraint, Enum, ForeignKey, Index, MetaData
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column


class Base(DeclarativeBase):
    metadata = MetaData(
        naming_convention={
            "ix": "ix_%(table_name)s_%(column_0_N_name)s",
            "uq": "uq_%(table_name)s_%(column_0_N_name)s",
            "ck": "ck_%(table_name)s_%(constraint_name)s",
            "fk": "fk_%(table_name)s_%(column_0_N_name)s_%(referred_table_name)s",
            "pk": "pk_%(table_name)s",
        }
    )


class OrderStatus(enum.Enum):
    PENDING = "pending"
    PAID = "paid"
    SHIPPED = "shipped"


class Order(Base):
    __tablename__ = "orders"

    id: Mapped[int] = mapped_column(primary_key=True)
    customer_id: Mapped[int] = mapped_column(ForeignKey("customers.id"))
    status: Mapped[OrderStatus] = mapped_column(
        Enum(OrderStatus, name="order_status",
             values_callable=lambda members: [m.value for m in members])
    )
    total_cents: Mapped[int]

    __table_args__ = (
        CheckConstraint("total_cents >= 0", name="positive_total"),
        Index(None, "customer_id"),   # named by the convention: ix_orders_customer_id
    )

Each piece of that model has its own guide. Naming constraints with a metadata naming convention covers the templates and adopting them on a database that already has invented names. Adding a value to a Postgres enum in Alembic covers ALTER TYPE, names versus values, and removing labels. Creating indexes concurrently in Alembic migrations covers building the index above on a table that is already taking writes.

This topic sits within Alembic async migrations and schema evolution, next to the broader zero-downtime schema migration strategies that the lock-avoidance techniques here feed into.

Query Construction & Async Execution Patterns

Migration code is synchronous, even in a project whose application runs entirely on asyncpg. The async boundary is crossed exactly once, in env.py, where an AsyncConnection hands a synchronous facade to Alembic through run_sync(). Every op.* call, every op.get_bind() and every autocommit block inside a revision runs on that facade.

Migration code is synchronous, even under asyncpg Left: a classic env.py with a synchronous engine calls context.run_migrations, and op.create_index runs on a normal Connection. Right: an async env.py opens an AsyncConnection and calls run_sync with a function that configures the context and runs migrations; inside, op sees a synchronous Connection facade and the same op calls work unchanged, including autocommit_block. sync env.py engine.connect() as connection context.configure(connection=...) context.run_migrations() op.* on a Connection async env.py async with engine.connect() as conn await conn.run_sync(do_run_migrations) context.run_migrations() inside op.* on the same Connection API Revision files never need await. The async boundary is crossed once, in env.py.

The classic synchronous runner and the async runner differ only at that boundary:

# Sync env.py (excerpt)
from alembic import context
from sqlalchemy import create_engine

from shop.models import Base


def run_migrations_online() -> None:
    engine = create_engine(context.config.get_main_option("sqlalchemy.url"))
    with engine.connect() as connection:
        context.configure(
            connection=connection,
            target_metadata=Base.metadata,
            transaction_per_migration=True,
        )
        with context.begin_transaction():
            context.run_migrations()

# Async env.py (excerpt)
import asyncio

from alembic import context
from sqlalchemy.ext.asyncio import create_async_engine

from shop.models import Base


def do_run_migrations(connection) -> None:
    context.configure(
        connection=connection,
        target_metadata=Base.metadata,
        transaction_per_migration=True,
    )
    with context.begin_transaction():
        context.run_migrations()


async def run_migrations_online() -> None:
    engine = create_async_engine(context.config.get_main_option("sqlalchemy.url"))
    async with engine.connect() as connection:
        await connection.run_sync(do_run_migrations)
    await engine.dispose()


asyncio.run(run_migrations_online())

transaction_per_migration=True appears in both, and it matters more in this topic than anywhere else. Enum additions and concurrent index builds use autocommit_block(), which commits the transaction that is open when it is entered. With one transaction per migration, that commits only the current revision's earlier work. With the default single transaction for the whole run, it commits every migration applied so far in that run.

Queries inside migrations — checking whether an index is valid, counting rows that violate a new constraint, reading enum_range() — use op.get_bind() and ordinary text() or Core constructs, executed synchronously:

import sqlalchemy as sa
from alembic import op


def upgrade() -> None:
    bind = op.get_bind()
    violations = bind.execute(
        sa.text("SELECT count(*) FROM orders WHERE total_cents < 0")
    ).scalar_one()
    if violations:
        raise RuntimeError(f"{violations} orders violate positive_total; backfill first")
    op.create_check_constraint("positive_total", "orders", "total_cents >= 0")

Failing a migration early with a precise message is much better than letting ADD CONSTRAINT fail after a full table scan with a message that names only the constraint.

State Management & Session Boundaries

Migrations and the running application share a database, and schema objects are where their timelines collide. A migration changes the schema at one moment; the application fleet changes over the minutes of a rolling deploy. For a while, old code runs against the new schema, and occasionally new code runs against the old one.

Inside or outside the migration transaction Three bands. Transactional and safe together: creating tables, adding nullable columns, adding NOT VALID constraints, renaming. Transactional but holding strong locks for a table scan: plain CREATE INDEX, adding a validated foreign key or check, changing a column type. Must run outside a transaction: CREATE and DROP INDEX CONCURRENTLY, and on PostgreSQL before 12, ALTER TYPE ADD VALUE. inside, quick: CREATE TABLE, ADD COLUMN (nullable), NOT VALID constraints short locks; roll back cleanly with the rest of the revision inside, but scans under lock: CREATE INDEX, validated FK/CHECK, ALTER TYPE correct on small or new tables; an outage on large busy ones outside: CREATE/DROP INDEX CONCURRENTLY, ALTER TYPE ADD VALUE (< PG 12) autocommit_block(); one per revision; cannot be rolled back with the rest

Each kind of object has its own compatibility rule.

Enum labels must be added before any code writes them, and readers must tolerate labels they do not know. An old release loading a row with a new label raises LookupError: 'refunded' is not among the defined enum values inside the ORM's result processing, which fails the whole query, not just that row. Ship the migration and a tolerant reader first, and the writer in a later release.

Constraints must be added after all code that could violate them is gone. A check constraint added while old code still writes negative totals turns those writes into IntegrityErrors in production. The safe order is: deploy code that respects the rule, backfill, add the constraint NOT VALID, validate.

Indexes have no compatibility risk for correctness, only for performance. Dropping an index old code still relies on is the danger, so drop indexes one release after the last query that used them is gone.

Inside the application, sessions do not notice schema changes — but they do cache. SQLAlchemy's compiled-statement cache and asyncpg's prepared-statement cache are both per connection, and a migration that changes a column's type can invalidate prepared statements on connections that were open before it ran. asyncpg reports this as InvalidCachedStatementError: cached statement plan is invalid due to a database schema or configuration change. SQLAlchemy's asyncpg dialect responds by invalidating its prepared-statement caches, so the statement fails once and a retry succeeds. Deploys that run migrations against a live fleet should expect a brief burst of these, and pool_pre_ping does not prevent them, because the connections are healthy — only their cached plans are stale.

Migration sessions themselves should be short and single-purpose. A data backfill run inside a schema migration holds that migration's locks for as long as the backfill takes, so large backfills belong in a separate, batched job between the migrations that bracket them, as writing data migrations safely describes.

Advanced Schema Routing and Type Extensions

Two situations push these objects beyond a single schema: multi-tenant layouts that repeat the same tables in many PostgreSQL schemas, and custom types that Alembic has to render in migration files.

Tightening a rule on a live table Five steps. Release one adds the new check constraint NOT VALID, which applies to new writes immediately without scanning. A backfill fixes existing rows that violate it. Release two runs VALIDATE CONSTRAINT, which scans under a lock that permits writes. Release three drops the old constraint. Application code that depends on the rule ships only after validation. release 1 ADD CONSTRAINT ... NOT VALID new writes checked immediately backfill fix rows that violate the rule batched, outside the migration release 2 VALIDATE CONSTRAINT SHARE UPDATE EXCLUSIVE — writes continue release 3 drop the old rule, rely on the new one Validation fails loudly if the backfill missed a row, which is exactly when you want it to fail.

In schema-per-tenant layouts, enum types are schema-scoped. CREATE TYPE order_status in tenant_17 is a different type from the one in tenant_18, and adding a label means running ALTER TYPE tenant_17.order_status ADD VALUE ... in every schema. A migration that loops over tenants has to run each ALTER TYPE in its own autocommit statement, and it should be idempotent with IF NOT EXISTS, because a loop over hundreds of schemas is the migration most likely to be interrupted halfway. Index names, by contrast, only need to be unique per schema, so a naming convention produces the same ix_orders_customer_id everywhere, which is what you want.

import sqlalchemy as sa
from alembic import op


def upgrade() -> None:
    bind = op.get_bind()
    schemas = bind.execute(
        sa.text("SELECT schema_name FROM information_schema.schemata "
                "WHERE schema_name LIKE 'tenant\\_%'")
    ).scalars().all()
    with op.get_context().autocommit_block():
        for schema in schemas:
            op.execute(
                f'ALTER TYPE "{schema}".order_status ADD VALUE IF NOT EXISTS \'refunded\''
            )

The routing side of schema-per-tenant — schema_translate_map and per-request switching — is covered in switching schemas per request.

Custom types raise a different problem: autogenerate renders a column's type into the migration by its Python repr, so a TypeDecorator appears as shop.types.EncryptedString(length=255), and the migration file needs import shop.types. If the type later moves or is renamed, old migrations stop importing. The render_item hook in env.py can render custom types as their underlying implementation, which keeps migration files independent of application code:

from shop.types import EncryptedString


def render_item(type_, obj, autogen_context):
    if type_ == "type" and isinstance(obj, EncryptedString):
        autogen_context.imports.add("import sqlalchemy as sa")
        return f"sa.String(length={obj.impl.length})"
    return False  # default rendering for everything else


# in do_run_migrations(): context.configure(..., render_item=render_item)

The same hook is where teams render postgresql.ENUM(..., create_type=False) for enum types shared between tables, so the second table's migration does not try to create the type again.

Hybrid Architectures & Migration Strategies

Most schemas that reach this topic started without a naming convention, with enum columns defined before anyone decided between names and values, and with indexes created by whatever autogenerate emitted. Moving them to the conventions above is a sequence of small, safe releases rather than one large migration.

Legacy schema objects, and their 2.0 form Left, legacy: constraints named by the database, such as orders_customer_id_fkey, Enum columns storing member names by accident, and indexes created inside the migration transaction. Right, 2.0: a naming convention on the declarative base, values_callable chosen deliberately or VARCHAR with a named CHECK, and concurrent index builds in autocommit blocks for existing tables. legacy schema orders_customer_id_fkey (invented) Enum(Status) storing NAMES CREATE INDEX in the transaction autogenerate proposes drops 2.0 schema fk_orders_customer_id_customers values_callable, or VARCHAR + ck_ CONCURRENTLY in autocommit_block() autogenerate is empty Rename first, then change behaviour: a rename migration is catalog-only and safe to ship alone.

Names first. Add the naming convention to the model, then write a migration that renames existing constraints and indexes to the conventional names. Renames only touch the catalog, so this is fast on any table size. The end state is verified by autogenerate producing an empty revision. Nothing else should change in this release.

Enum storage second. If an enum column stores member names and you want values, the change is a set of ALTER TYPE ... RENAME VALUE statements plus the values_callable change in the model, and both must land together — the migration and the release that reads the new labels cannot be separated by a rolling deploy without a compatibility shim. Many teams decide the names are fine and document the choice instead, which is a perfectly good outcome.

Build practices last. Add the CI check that rejects non-concurrent indexes on existing tables and validated constraints added without NOT VALID, so the practices stick.

Core and ORM mix naturally here, because migrations are Core. op.execute() with text() handles ALTER TYPE and VALIDATE CONSTRAINT; op.create_index() and op.create_check_constraint() handle what Alembic models directly; and the application's ORM models remain the source of truth that autogenerate compares against.

A 1.4-era project often also carries create_constraint=True behaviour it did not ask for: before 1.4, non-native Enum and Boolean types created CHECK constraints by default, with names the database invented. Those constraints survive the upgrade to 2.0 even though 2.0 no longer creates them, and autogenerate does not report them, because unnamed check constraints are not compared. List them with SELECT conname FROM pg_constraint WHERE contype = 'c', and either name them into the convention or drop them deliberately.

Lock Budgets for Schema Changes

Every recommendation in this topic comes back to one question: how long will this statement block writes to a table that is taking them? It is worth making that question explicit, with a budget, rather than answering it case by case.

How long each approach blocks writes Bar chart of write-blocking time on an illustrative 50 million row table. A plain CREATE INDEX blocks writes for the whole build, several minutes. Adding a validated foreign key blocks writes for a full scan. CREATE INDEX CONCURRENTLY blocks writes only for moments at its start and end. Adding a constraint NOT VALID then validating blocks writes only for a moment. CREATE INDEX (plain) minutes — the whole build ADD FOREIGN KEY (validated) the whole scan of orders CREATE INDEX CONCURRENTLY moments ADD ... NOT VALID + VALIDATE moments Illustrative. Readers are not blocked in any row; the cost that matters is blocked writers.

A lock budget is a limit on how long any migration statement may hold, or wait for, a lock that blocks writes on a production table. A common choice is a few seconds. Two PostgreSQL settings enforce it from inside the migration, and they are best set once, on the engine env.py creates for migrations, so every revision inherits them — and the application's own engine, which should not have a five-second lock timeout on ordinary work, is unaffected:

from sqlalchemy.ext.asyncio import create_async_engine

from alembic import context

migration_engine = create_async_engine(
    context.config.get_main_option("sqlalchemy.url"),
    connect_args={
        "server_settings": {
            # Fail fast instead of queueing: a waiting ALTER blocks every query behind it.
            "lock_timeout": "5s",
            # Bound statements that should be quick; long operations opt out explicitly.
            "statement_timeout": "60s",
        }
    },
)

lock_timeout is the more important of the two, and the reason is counter-intuitive. An ALTER TABLE waiting for its lock is not harmless: PostgreSQL's lock queue is first-come-first-served, so every ordinary query that arrives after the waiting ALTER queues behind it, even queries that would not conflict with whatever the ALTER is waiting for. A migration stuck behind one long-running report can take an API down in seconds. With lock_timeout, the migration fails quickly and can be retried when the report finishes.

Operations that legitimately take longer — a concurrent index build, a batched backfill — opt out explicitly with SET statement_timeout = 0 inside their revision. That keeps the default strict and makes the exceptions visible in review.

Against the budget, the choices in this topic sort themselves. Concurrent index builds and NOT VALID plus VALIDATE hold write-blocking locks only for moments. Plain index builds, validated foreign keys and column type changes hold them for a full table scan, and fit the budget only on small tables. Enum label additions and constraint renames are catalog-only and always fit.

Indexes that cover part of a table, or an expression rather than a column, have to be declared on the model before autogenerate can produce them. Creating partial and expression indexes from SQLAlchemy models covers postgresql_where, functional indexes, the postgresql_using variants for GIN and GiST, and making the query actually match the index it was built for.

Production Pitfalls & Anti-Patterns

  • ValueError: Constraint must have a name — autogenerate emitted op.drop_constraint(None, ...). Add a naming convention and rename existing constraints to it.
  • invalid input value for enum order_status: "refunded" — the Python enum gained a member with no migration. Hand-write ALTER TYPE ... ADD VALUE.
  • unsafe use of new value "refunded" — the label was added and used in one transaction. Use autocommit_block().
  • CREATE INDEX CONCURRENTLY cannot run inside a transaction block — missing autocommit block. Wrap the operation.
  • type "order_status" already exists — a second create_table or a re-run created the type again. Use postgresql.ENUM(..., create_type=False), and drop types in downgrade().
  • canceling statement due to lock timeout — the budget did its job. Find the blocker in pg_stat_activity and retry.

The pitfall that produces no error at all is an invalid index left by a failed concurrent build: maintained on every write, used by no query. Check pg_index.indisvalid after every deploy that builds one.

Frequently Asked Questions

Which schema changes does autogenerate miss?

Enum label changes, check constraint bodies, computed column expressions, and anything about how DDL should run, such as CONCURRENTLY or NOT VALID. Server default changes are only compared when compare_server_default=True.

Should every index in a migration be concurrent?

Every index on an existing table that takes writes, yes. Indexes on tables created in the same revision should be plain, because there is nothing to block and a plain build is faster and transactional.

Is it safe to rename constraints in production?

Yes. ALTER TABLE ... RENAME CONSTRAINT and ALTER INDEX ... RENAME TO change only the catalog. They take a brief exclusive lock, so run them with a lock_timeout like any other DDL.

Do these migrations work with an async env.py?

Yes, unchanged. Revisions run inside run_sync() on a synchronous connection facade, so op.*, op.get_bind() and autocommit_block() all behave as they do with a synchronous driver.