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

Not specified by the original author.

TL;DR

Without a statementtimeout, 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 (pgcancelbackend for a clean abort), set statementtimeout 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

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: pgstatactivity 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

SELECT pg_cancel_backend([pid]);

pgcancelbackend aborts the statement; pgterminatebackend 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

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 statementtimeout and locktimeout 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 (statementtimeout, locktimeout, CONCURRENTLY). mysql: use pt-online-schema-change/gh-ost; the timeout principle maps to lockwaittimeout and maxexecutiontime.

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 statementtimeout and locktimeout in the migration connection setup.

kill long running DDL postgres safely

Step 2: pgcancelbackend first (aborts the statement), pgterminatebackend 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.
• locktimeout and statementtimeout 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

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

Thread query: agent didnt set a statement_timeout and the ALTER TABLE ran for 6 hours blocking deploys

TL;DR: Without a statementtimeout, 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 (pgcancelbackend for a clean abort), set statementtimeout 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.