Lock-safe DDL per engine

-- PostgreSQL
SET lock_timeout = '5s';
SET statement_timeout = '15min';

-- SQL Server
SET LOCK_TIMEOUT 5000;  -- milliseconds

SELECT indexrelid::regclass, indisvalid
FROM pg_index
WHERE indrelid = 'orders'::regclass AND NOT indisvalid;

ALTER INDEX IX_orders_customer ON orders PAUSE;
ALTER INDEX IX_orders_customer ON orders RESUME;

ALTER TABLE orders
  ADD COLUMN channel varchar(32) NULL,
  ALGORITHM=INPLACE, LOCK=NONE;

# Lock-safe DDL per engine

Verified 2026-09 against <https://www.postgresql.org/docs/current/sql-altertable.html>,
<https://learn.microsoft.com/en-us/sql/relational-databases/indexes/perform-index-operations-online>
and <https://dev.mysql.com/doc/refman/8.4/en/innodb-online-ddl-operations.html>.

The question for every statement is: **what lock does it take, and for how long?** A lock held for
ten milliseconds on a small table is invisible; the same statement on a hundred million rows is an
outage.

## Always set timeouts

Set these in the migration session, before any DDL:

```sql
-- PostgreSQL
SET lock_timeout = '5s';
SET statement_timeout = '15min';
```

```sql
-- SQL Server
SET LOCK_TIMEOUT 5000;  -- milliseconds
```

Why this matters more than the statement itself: DDL waits behind existing queries, and *new*
queries queue behind the waiting DDL. One slow report plus one unguarded `ALTER TABLE` stalls every
query on that table. With a lock timeout the migration fails quickly and harmlessly, and you retry
when the table is quiet.

## PostgreSQL

| Operation | Safe? | Notes |
|---|---|---|
| `ADD COLUMN` nullable | Yes | Metadata only |
| `ADD COLUMN … DEFAULT <constant>` | Yes | Metadata only in current versions; a *volatile* default rewrites |
| `ADD COLUMN … NOT NULL` without default | No | Rewrite under `ACCESS EXCLUSIVE` |
| `DROP COLUMN` | Yes | Metadata only; space is reclaimed later |
| `ALTER COLUMN … TYPE` | No | Rewrite; use the add-and-migrate recipe |
| `SET NOT NULL` | Scans | Prefer a `CHECK … NOT VALID` then `VALIDATE` |
| `ADD CONSTRAINT … NOT VALID` | Yes | Fast; applies to new rows |
| `VALIDATE CONSTRAINT` | Scans | Weaker lock; allows reads and writes |
| `CREATE INDEX` | No | Blocks writes |
| `CREATE INDEX CONCURRENTLY` | Yes | Cannot run in a transaction; may leave an `INVALID` index on failure |
| `RENAME` | Fast lock | Fast, but breaks the running code — sequence it, do not rely on speed |

After a concurrent build, verify it took:

```sql
SELECT indexrelid::regclass, indisvalid
FROM pg_index
WHERE indrelid = 'orders'::regclass AND NOT indisvalid;
```

Anything returned must be dropped (`DROP INDEX CONCURRENTLY`) and rebuilt — an invalid index is
maintained on writes but not used by queries, so it costs without helping.

Most migration tools wrap each migration in a transaction. `CREATE INDEX CONCURRENTLY` must be told
not to: EF Core `migrationBuilder.Sql(..., suppressTransaction: true)`, Flyway
`executeInTransaction=false`, Alembic with an autocommit block.

## SQL Server

| Operation | Safe? | Notes |
|---|---|---|
| `ADD` nullable column | Yes | Metadata only |
| `ADD` column with default | Usually | Metadata only for non-nullable with constant default in supported editions |
| `ALTER COLUMN` | Mostly no | Generally size-of-data; test on volume |
| `CREATE INDEX WITH (ONLINE = ON)` | Yes | Enterprise-class editions; add `RESUMABLE = ON` for long builds |
| `ADD CONSTRAINT … WITH NOCHECK` | Yes | Equivalent of `NOT VALID`; check it later |

A resumable index build can be paused and resumed, which makes a long build survive a maintenance
window boundary:

```sql
ALTER INDEX IX_orders_customer ON orders PAUSE;
ALTER INDEX IX_orders_customer ON orders RESUME;
```

## MySQL / InnoDB

State the algorithm and lock explicitly so an unsupported operation fails immediately instead of
silently copying the table:

```sql
ALTER TABLE orders
  ADD COLUMN channel varchar(32) NULL,
  ALGORITHM=INPLACE, LOCK=NONE;
```

If MySQL cannot do it in place it returns an error rather than running a copying rebuild — which is
exactly what you want to discover in the pipeline instead of in production.

## Watching a migration run

- **Blocked queries**: check for a waiting lock chain as soon as the DDL starts. In Postgres,
  `pg_locks` joined to `pg_stat_activity`; in SQL Server, `sys.dm_exec_requests`.
- **Replication lag**: a rewrite or a large backfill generates a lot of WAL/log. Read replicas fall
  behind, and anything reading from them starts serving stale data.
- **Have an abort plan**: know the statement you will run to cancel, and that cancelling is safe at
  that point. For a batched backfill, stopping between batches is always safe — which is the reason
  to batch.