All jobs

Add Lock Timeouts to Alembic Migrations on PostgreSQL

Yes. An agent makes each Alembic migration set a PostgreSQL lock timeout in its own transaction, retries a blocked upgrade, proves it, and opens a PR.

Run this on RushOpens the console with this prompt ready to send, in a Project that has your repository.

What the agent does

The agent reads how migrations run before it changes the rules they run under. In a FastAPI app laid out like fastapi/full-stack-fastapi-template, backend/app/alembic/env.py builds an engine from settings.DATABASE_URL with NullPool, configures the context with SQLModel.metadata and compare_type=True, and calls context.run_migrations() inside one context.begin_transaction(), so the whole chain of revisions under backend/app/alembic/versions runs as a single transaction. backend/alembic.ini sets only script_location and logging. backend/scripts/prestart.sh runs alembic upgrade head and then app/initial_data.py, and .github/workflows/test-backend.yml runs that script against a postgres:18 container before the tests. The latest revision, fe56fa70289e, adds a created_at column to user and item with op.add_column. On PostgreSQL each ALTER TABLE takes an ACCESS EXCLUSIVE lock; if a long read holds the table, the migration waits behind it, and every new query queues behind the migration. That is how a deploy turns into an outage.

It changes env.py so each migration runs with limits. Inside run_migrations_online, before context.run_migrations(), it executes SET LOCAL lock_timeout and SET LOCAL statement_timeout on the connection, with the values read from two new settings in backend/app/core/config.py, defaulted and documented in .env.example, so production can set a short lock wait and a longer statement limit. It passes transaction_per_migration=True to context.configure, so a chain of several revisions commits one at a time and a lock held by revision three does not keep revision one's changes uncommitted. SET LOCAL scopes both values to that transaction and nothing else on the connection.

When lock_timeout expires, PostgreSQL raises lock_not_available, which psycopg surfaces as psycopg.errors.LockNotAvailable and Alembic rolls back the migration cleanly. The agent makes prestart.sh retry alembic upgrade head a few times with a pause on that failure only, so a migration that lost the race to a long report retries after the report finishes instead of failing the deploy, while any other error still stops it on the first try. It also adds a note to backend/app/alembic/README on op.get_context().autocommit_block() for a future CREATE INDEX CONCURRENTLY, which cannot run inside the per-migration transaction.

It proves the behaviour in its sandbox with a PostgreSQL it installs there. With DATABASE_URL pointed at it, it runs the upgrade clean once, then opens a second session that takes a lock on item inside an open transaction, runs alembic upgrade head on a throwaway revision that alters item, and watches it fail with the lock error after the configured wait instead of hanging; it releases the lock and sees the retry in prestart.sh succeed. The throwaway revision is removed, and the agent runs scripts/tests-start.sh and then scripts/lint.sh, which runs mypy, ty and ruff.

Start it with one API call

POST https://api.phoenix.so/o/{org}/sessions with this body:

{
  "agent": "claude",
  "input": "Add lock and statement timeouts to our Alembic migrations on PostgreSQL. Read backend/pyproject.toml, backend/alembic.ini, backend/app/alembic/env.py, the revisions under backend/app/alembic/versions, backend/app/core/config.py, backend/scripts/prestart.sh and .github/workflows/test-backend.yml first. In env.py, set lock_timeout and statement_timeout with SET LOCAL before running migrations, from two new settings with defaults documented in .env.example, and configure transaction_per_migration. Make prestart.sh retry the upgrade a few times only when the failure is the lock timeout. Add a README note about autocommit_block for concurrent index builds. Start a PostgreSQL in the sandbox, hold a lock on the item table from another session, run the upgrade with a throwaway revision to see it fail fast and then succeed on retry, remove the throwaway revision, run the test script and scripts/lint.sh, and open a pull request with the output.",
  "resources": [
    {
      "url": "https://github.com/your-org/your-app"
    }
  ]
}
  1. Get an API key. In the console, open Settings, then API keys, and create a key for your org. Send it in the Authorization header as a Bearer token.
  2. Send the request. POST the body in request.json to https://api.phoenix.so/o/{org}/sessions, with your repository as the resources URL. The run starts in its own cloud sandbox.
  3. Read what came back. Follow the run on its live stream or wait for the run.completed webhook, then read the result and its trajectory.

What comes back

What comes back is a pull request that changes env.py, config.py, .env.example, prestart.sh and the Alembic README. The description shows the two SET LOCAL statements, the transaction_per_migration line, the retry loop, and the sandbox transcript of the blocked upgrade failing with the lock error and succeeding once the lock is released.

The run's record keeps both sessions, the one holding the lock and the one running Alembic, so a reviewer sees the wait end at the configured limit rather than reading a claim about it.

Before merging, a reviewer sets the production values: a lock wait short enough that a blocked migration gives up before the app's request queue fills, and a statement limit long enough for the slowest legitimate backfill. The pull request suggests reading them from the deploy environment rather than the code.

Run it on every Linear issue labelled deploy-safety

A team makes this a rule. In the Project for each repository, an automation with the prompt above and a linear trigger on linear.issue.labeled for the label deploy-safety starts a run when someone files "the migration hung behind the nightly export and requests queued for ten minutes". The trigger adds the issue after the prompt, so the run reads which table was involved and makes sure the limits and the retry cover it.

Each run opens its own pull request on its own branch and links the issue. A repository whose env.py already sets the timeouts gets a description saying so and a pull request only for whatever is missing, such as the per-migration transaction or the retry.

The use case this job follows, with its automation and trigger: Turn a Linear label into a PR.

Why run it on Rush?

A migration that waits on a lock looks fine in every test environment, because nothing else is running there. The only way to see the failure mode is to hold a lock and run the migration against it, and the only way to trust the fix is to watch it give up at the limit and succeed on retry. The agent does both in its own sandbox with a real PostgreSQL and keeps the transcript.

The change is a few lines in env.py and a loop in a script, so review is quick, and a team with several services on this template runs them all at once, each in its own sandbox with its own pull request.

Frequently asked questions

Why SET LOCAL and not a value in the connection string?

A connection-string option applies to every statement on that connection for its whole life. SET LOCAL lasts for the current transaction, which with transaction_per_migration is exactly one migration, and the application's own connections in core/db.py are untouched.

Does transaction_per_migration change what gets committed?

Yes, on purpose. Today the chain is one transaction, so a failure in the last revision rolls back all of them. With one transaction per migration, each revision commits as it finishes, so a retry resumes from the first one that failed. The pull request names this so a reviewer agrees to it.

What about CREATE INDEX CONCURRENTLY?

It cannot run inside a transaction, so a migration that needs it wraps that statement in op.get_context().autocommit_block(). The agent adds the pattern to the Alembic README rather than to a revision, since no current revision builds an index that way.

Will the retry loop mask real failures?

No. The loop retries only when the output contains the lock timeout error; a syntax error, a failed constraint or a bad connection still exits on the first attempt with the same code as before. The sandbox transcript in the pull request shows a lock failure retrying and nothing else.

More Alembic jobs

More jobs like this: Turn a Linear label into a PR

Keep reading