Skip to content
Back to skills

Migration Timeouts

ASecurity

Why a migration never waits forever for a lock — the default lock_timeout (5s), statement_timeout (30m) and retry (5 attempts), the per-migration `-- forge:lock-timeout` / `-- forge:statement-timeout` header directives, CREATE INDEX CONCURRENTLY and its INVALID-index cleanup, and the run-wide flags.

  • 10 stars
  • 0 votes
  • 0 copies
  • 1 view
  • Added November 13, 2026
ai-agentsgosqlgit

Security analysis

A100/100

Scanned November 13, 2026

npx -y skills add reliant-labs/forge --skill migration-timeouts --agent claude-code

Installs into .claude/skills of the current project.

Are you the author of Migration Timeouts?

Add the live security badge to your README. It updates with every re-scan.

Security grade badge for Migration Timeouts
[![Security: A — Skills Directory](https://www.skillsdirectory.com/api/skills/reliant-labs-migration-timeouts/badge)](https://www.skillsdirectory.com/skills/reliant-labs-migration-timeouts)

More formats (shields.io, HTML) on the badges page. Keep it an A: scan every change in CI with Pro.

Download with Pro
SKILL.md
---
name: migration-timeouts
description: Why a migration never waits forever for a lock — the default lock_timeout (5s), statement_timeout (30m) and retry (5 attempts), the per-migration `-- forge:lock-timeout` / `-- forge:statement-timeout` header directives, CREATE INDEX CONCURRENTLY and its INVALID-index cleanup, and the run-wide flags.
---

# Lock and statement timeouts

`ALTER TABLE` takes an ACCESS EXCLUSIVE lock, which waits behind any session
that has touched the table — a slow report, a transaction an application left
open. Postgres queues lock requests, so while the migration waits **every later
query on that table queues behind the migration**, plain `SELECT`s included. One
long reader plus one `ALTER TABLE` is an outage for as long as the reader runs.
The migrator therefore never waits unboundedly:

| Setting | Default | Meaning |
|---|---|---|
| `lock_timeout` | `5s` | Longest one attempt waits for a lock. Bounds the pile-up behind the DDL. |
| `statement_timeout` | `30m` | Backstop against a runaway statement. Generous so a real backfill is not cut off. |
| attempts | `5` | Tries of a migration whose lock timed out, the first included. Waits 1s, 2s, 4s, 8s (±25% jitter, capped at 15s) between them. |

A timeout in the migrator is not a failure of the migration, it is the lock
being busy, so a timed-out attempt is rolled back completely (one file is one
transaction) and tried again; each retry is logged at WARN with the migration,
attempt and wait. When every attempt times out, the run fails with a
`*migratekit.LockTimeoutError` naming the migration and what to do, and the
schema is left **clean at the previous version**: nothing was applied, so the
next run is simply another try, with no `force` and no dirty flag. Only lock
timeouts are retried; any other error fails on the first attempt.

**Per-migration override** — a header line in the `.up.sql`, a Go duration
(`500ms`, `30s`, `5m`, `1h30m`) or `off`; a rationale may follow:

```sql
-- forge:lock-timeout 30s
-- forge:statement-timeout 2h — backfills 40M rows
```

A file's directive replaces the run's value for that file only. A malformed or
doubled directive fails the run before anything is applied, the way a malformed
`forge:adopts` does. Raise `statement-timeout` for a backfill that legitimately
runs long (better still, backfill in batches, one short transaction each); raise
`lock-timeout` only for a migration that must wait out something known and
brief. Run-wide values: `db migrate up --lock-timeout 10s --statement-timeout 1h
--lock-attempts 3` (the scaffolded binary's `db migrate up` has the same flags),
or `migratekit.Options.Run` / `migratekit.AutoMigrateWith` from Go.

**`CREATE INDEX CONCURRENTLY`** must be the only statement in its file, so it
runs outside a transaction block and the limits are set on the session, not with
`SET LOCAL`. It does not hold the table's lock for long, but it waits for every
transaction older than itself to end, so an idle-in-transaction session stalls
it and it times out like any DDL. A timed-out build leaves its index `INVALID`;
the migrator **drops it before each retry** (`DROP INDEX CONCURRENTLY`), so a
retry never meets "already exists" and a `CREATE INDEX CONCURRENTLY IF NOT EXISTS`
never quietly keeps a broken index. It drops exactly the index each
`CREATE INDEX CONCURRENTLY` in the file names, in the schema of the table it is
`ON` (resolved by postgres, so `schema_a.items_v` never touches
`schema_b.items_v`); every other invalid index is left alone. If the blocker also
stops the drop, the error lists it (`Leftover`) and the next run drops it first.

**When a migration is NOT retried.** A retry is safe only if a failed attempt
rolled back completely, and the migrator proves that from the file (it lexes the
SQL: comments, strings and function bodies do not count) instead of assuming it.
A file is retried only when every statement is plain transactional work
(`CREATE`/`ALTER`/`DROP`, `INSERT`/`UPDATE`/`DELETE`, `GRANT`/`REVOKE`, `SET`, …)
or it is a lone `CREATE INDEX CONCURRENTLY`. Everything else is **not** retried,
and its first lock timeout fails the run with the schema left dirty at that
version: its own `BEGIN` / `COMMIT` / `END` / `ROLLBACK` / `SAVEPOINT` /
`PREPARE TRANSACTION`; `CALL` and `DO` (a procedure or block may `COMMIT` inside);
`DROP INDEX CONCURRENTLY` (it commits the index invalid before it waits);
`REINDEX`, `VACUUM`, `CLUSTER`, `REFRESH … CONCURRENTLY`,
`ALTER TYPE … ADD VALUE`, `DETACH PARTITION … CONCURRENTLY`; and any statement
the migrator does not recognise. The same goes for a file run statement by
statement (`x-multi-statement`). Such a file also gets no invalid-index cleanup:
the cleanup resolves names in the session as it was before the file ran, so it
only runs for a lone `CREATE INDEX CONCURRENTLY`. Keep a migration to plain DDL,
or give a `CREATE INDEX CONCURRENTLY` a file of its own, to get the retry.

Grants (`db/grants/`, applied after the migrations) take locks too, so
they run under the same limits and the same retry. They are one statement, so a
retry is always safe; if every attempt times out the run fails with a
`LockTimeoutError` (`Grants` set), the migrations stay committed, and the next
run applies the grants.

The limits are set on the migration's session and the connection gets back
**the exact values it had** when the run ends, including on failure (not
`RESET`, which would drop a pool's own `SET statement_timeout`), because
`AutoMigrate` borrows a connection from the application's pool. A connection
that cannot be restored is discarded, not returned.

Attribution

Is this your skill, or is something wrong with this listing? Request removal or report an issue. Author removals are honored within 72 hours.

View all in ai-agents →

Comments

Loading comments…

Newsletter

Get the weekly list of safe new skills, and the ones we pulled.