/ecto-migrations-safety

name: ecto-migrations-safety
description: "Use when migrating PostgreSQL tables that serve live traffic — index concurrently, @disable_ddl_transaction/@disable_migration_lock, lock_timeout, NOT NULL/foreign keys via NOT VALID, backfills, renames in rolling deploys. Not for migration basics (use ecto-essentials) or releases (use deployment-gotchas)."

Ecto Migrations Safety

> Verified against Elixir 1.20, Ecto 3.14 (ecto_sql 3.14), PostgreSQL 16 — October 2026

RULES — Follow these with no exceptions

1. **Index existing tables with `concurrently: true` plus both `@disable_ddl_transaction true` and `@disable_migration_lock true`**, one index per migration. With only the first, the migration hangs forever under the default migration lock. 2. **Set `lock_timeout` in every transactional migration that alters a busy table**: `after_begin/0` calling `repo().query!("SET LOCAL lock_timeout TO '5s'")`. Never `execute/1` there, because it breaks rollback of `change/0`. 3. **Add columns as nullable or with a constant default.** A volatile default (`gen_random_uuid()`, `clock_timestamp()`, `random()`) rewrites the whole table under an exclusive lock. 4. **`NOT NULL` takes three migrations**: `CHECK (col IS NOT NULL)` with `validate: false`, then `VALIDATE CONSTRAINT`, then `SET NOT NULL` through `execute/2`. Don't use `modify ... null: false` for this step: it also emits `ALTER COLUMN TYPE`, which rescans the table. 5. **Add foreign keys and check constraints to populated tables with `validate: false`**, then validate them in a later migration. 6. **Never rename or drop anything the running code reads.** Expand and contract across deploys. To rename only in Elixir, use `field :new_name, :string, source: :old_column`. 7. **Change a type in place only when PostgreSQL can skip the rewrite** (`varchar` → `text`). Any other change (`integer` → `bigint`) gets a new column, a backfill and a switch. 8. **Backfill in a separate data migration**: schemaless (`from p in "posts"`), batched, outside a DDL transaction. Never call app schemas or contexts from a migration. 9. **Make every migration reversible** (`execute/2`, `modify ... from:`, `remove/3` with a type) or write `up/0`/`down/0`. Keep migrations without a transaction to a single idempotent step, because they can fail half-applied. 10. **Run migrations before the new code takes traffic** (see the `deployment-gotchas` skill), and read the generated SQL with `mix ecto.migrate --log-migrations-sql` before you ship.

---

Why "it worked locally" isn't enough: the lock queue

Most `ALTER TABLE` forms take an `ACCESS EXCLUSIVE` lock. On a busy table, the danger isn't how long the DDL takes. It's the time spent **waiting** for the lock: while an `ALTER` waits behind one long-running query or idle transaction, every later `SELECT` on that table queues behind the `ALTER`. A 2 ms change becomes a full outage. Two fixes cover most cases:

• don't hold the lock for long: no table rewrites, no full scans under `ACCESS EXCLUSIVE`
• don't wait for it for long: set `lock_timeout`, let the migration fail, and retry later

| Operation (PostgreSQL 12+) | Rewrite or scan? | Blocks reads and writes? | |---|---|---| | `add :col, :type` (nullable, no default) | no | briefly | | `add :col, :type, default: "constant"` (constant default, even with `null: false`) | no | briefly | | `add :col, :uuid, default: fragment("gen_random_uuid()")` | **rewrite** | for the whole rewrite | | `modify :title, :text, from: :string` (varchar → text) | no | briefly | | `integer` → `bigint`, or any other non-binary-compatible type | **rewrite** | for the whole rewrite | | `SET NOT NULL` | **full scan**, unless a validated `CHECK (col IS NOT NULL)` exists | for the scan | | `create constraint(..., validate: false)` / `references(..., validate: false)` | no | briefly | | `VALIDATE CONSTRAINT` | scan | no (`SHARE UPDATE EXCLUSIVE`) | | `create index` | scan | blocks writes for the build | | `create index(..., concurrently: true)` | scan | no | | `rename`, `remove` | no | briefly, **but breaks old code** |

The rewrite and scan rows were measured on a 2M-row table (relfilenode changes, `DEBUG` output). Lock levels come from the PostgreSQL `ALTER TABLE` docs.

Concurrent indexes

defmodule MyApp.Repo.Migrations.AddPostsSlugIndex do
  use Ecto.Migration
  @disable_ddl_transaction true
  @disable_migration_lock true

  def change do
    create index(:posts, [:slug], concurrently: true)
  end
end

Rolling it back emits `DROP INDEX CONCURRENTLY`. Here is why both attributes are needed (each case tested):

| Attributes | Result | |---|---| | neither | `ERROR 25001 CREATE INDEX CONCURRENTLY cannot run inside a transaction block` | | only `@disable_ddl_transaction` | **hangs forever.** The default `migration_lock: :table_lock` keeps a transaction open on `schema_migrations` for the whole run, while the migration runs in a separate process. `CREATE INDEX CONCURRENTLY` waits for every older transaction to finish, including that one (`wait_event = virtualxid`). Ecto only prints an `IO.warn` | | both | works | | only `@disable_ddl_transaction`, plus repo `migration_lock: :pg_advisory_lock` | works, no warning |

• `@disable_migration_lock true` lets two nodes run the same migration at the same time. That's fine when one pre-deploy job runs migrations. If every node migrates on boot, set `config :my_app, MyApp.Repo, migration_lock: :pg_advisory_lock` instead. It's session-level, so it doesn't work behind PgBouncer in transaction pooling mode.
• `concurrently` is only for tables that already hold data. An index in the same migration as `create table` needs neither attribute.
• **A failed concurrent build leaves an `INVALID` index behind.** For example, `create unique_index(..., concurrently: true)` fails on duplicate rows. Re-running the migration then fails with `42P07 relation "..." already exists`. Find it with `SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid`, r