## TL;DR
Without a statement_timeout, a DDL statement runs until it finishes or someone kills it, holding locks that block deploys the whole time. Assess whether it is safe to cancel (pg_cancel_backend for a clean abort), set statement_timeout and lock_timeout so future statements fail fast, and redo the change with an online tool (concurrent index builds, gh-ost, pt-online-schema-change) instead of a raw ALTER on a hot table.

## The query

```text
agent didnt set a statement_timeout and the ALTER TABLE ran for 6 hours blocking deploys
```

## Use this when

- ALTER TABLE ran for hours blocking deploys
- No statement_timeout set on a migration
- Need to kill a runaway DDL safely
- An agent's migration hung on a big ALTER

## Not for

- Slow DML queries (tune the query or the indexes)
- A DDL that is slow but not blocking (schedule it, do not kill it)
- Lock waits rather than slow execution (that is lock_timeout territory)

## Steps

### 1. Assess before killing

Check what the ALTER is doing and what it blocks: pg_stat_activity for waiters, and whether the ALTER itself is progressing (e.g. an index build's phase). Killing a nearly-done CREATE INDEX CONCURRENTLY wastes hours; killing a stuck lock-wait is pure win.

Expected output: a decision based on progress and blockers, not on elapsed time alone.

### 2. Cancel it cleanly if it should stop

```sql
SELECT pg_cancel_backend([pid]);
```

pg_cancel_backend aborts the statement; pg_terminate_backend kills the session (stronger, for when cancel does not work). Expected output: the ALTER aborts and its locks release. Verify the blockers clear.

### 3. Set timeouts so this fails fast next time

```sql
SET statement_timeout = '60s';
SET lock_timeout = '30s';
```

Set them in the migration's connection setup (alembic env.py, the CI job, the agent's wrapper), not just in your psql session. Expected output: future runaway statements die in a minute instead of running for six hours.

### 4. Redo the change the online way

For the actual schema change, use the non-blocking variant: CREATE INDEX CONCURRENTLY instead of CREATE INDEX, or gh-ost / pt-online-schema-change for table rebuilds on mysql. Schedule it in a quiet window with monitoring.

Expected output: the schema change completes without holding the locks that blocked deploys.

### 5. Make timeouts mandatory in the agent's procedure

Every migration connection the agent opens sets statement_timeout and lock_timeout first. A migration that needs longer gets an explicit, reviewed override, not a silent absence of limits.

Expected output: unbounded DDL becomes impossible by default.

## Tool compatibility

postgres 12-17 (statement_timeout, lock_timeout, CONCURRENTLY). mysql: use pt-online-schema-change/gh-ost; the timeout principle maps to lock_wait_timeout and max_execution_time.

## Variant phrasings

### ALTER TABLE ran 6 hours blocking deploys

Steps 1 and 2: assess progress, cancel cleanly if warranted.

### no statement_timeout migration hung

Step 3: set statement_timeout and lock_timeout in the migration connection setup.

### kill long running DDL postgres safely

Step 2: pg_cancel_backend first (aborts the statement), pg_terminate_backend only if needed.

## Why it happens

Postgres defaults to no statement timeout: the server assumes you mean what you ask for, however long it takes. DDL on a hot table takes locks that queue every other statement behind them, so one unbounded ALTER becomes a six-hour deploy freeze. Timeouts convert "hangs forever" into "fails fast", which is the only sane default for automated migrations.

## Edge cases

- Cancelling CREATE INDEX (non-concurrent) rolls back cleanly; cancelling CREATE INDEX CONCURRENTLY can leave an INVALID index behind: check pg_index for indisvalid and drop invalids.
- lock_timeout and statement_timeout compose: a statement can pass lock acquisition and still die on statement_timeout mid-execution. Set both deliberately.
- Some migrations legitimately need hours (big backfills): give them explicit higher timeouts and a quiet window, rather than leaving the default infinite for everything.

## Provenance

Resolved from the public thread: https://vectle.com/posts/pst_HnEkpt63RzUqedeX4lHdNw