Writing a migration

Every migration that runs DDL sets lock_timeout before its first DDL operation. That is the whole rule; the rest of this page is why, which form to use, and what the checker that gates it will and will not accept.

Migrations run at container boot, under an advisory lock, before gunicorn starts (management/commands/run_boot_sequence.py). A deploy therefore waits for the migration, and the web task's health check and its deployment circuit breaker are waiting behind that.

Why the bound exists

Most schema changes ask Postgres for an ACCESS EXCLUSIVE lock on the table. A request for that lock does not jump the queue: it waits behind every transaction already holding a lock on the table, and every later reader and writer then waits behind the request. One long-running analytics query on the chunk table is enough to turn a catalog-only ALTER into a full outage for as long as that query runs.

lock_timeout bounds only the wait for the lock, not the work once it is held. With it set, a migration that cannot get its lock in time raises, the boot sequence fails, the deployment circuit breaker rolls the previous image back, and nothing was ever blocked for more than the bound. Without it, the deploy hangs, the health check fails on a timer instead of an error, and every writer on the table is stuck in the meantime.

The default is three seconds. A migration that needs another value says why in its docstring.

What this changes for whoever is running the deploy: a deploy that hits the bound fails and rolls back with a lock-timeout error in the boot log. The response is to rerun it at a quieter moment, or to find and end the transaction that is holding the lock. It is not to raise the value - a longer bound buys a longer outage, not a better outcome.

The two forms

Which form is correct follows from whether the migration runs inside a transaction.

An atomic migration - Django's default - runs every operation in one transaction, so it uses the transaction-scoped form as its first operation:

operations = [
    migrations.RunSQL(
        sql="SET LOCAL lock_timeout = '3s'",
        reverse_sql=migrations.RunSQL.noop,
    ),
    ...
]

SET LOCAL dies with the transaction, so nothing has to clean it up.

A migration declaring atomic = False runs each operation in its own autocommit statement, where SET LOCAL applies to a transaction that ends immediately and is therefore a no-op. It uses the session form, and resets it at the end:

class Migration(migrations.Migration):
    atomic = False

    operations = [
        migrations.RunSQL(sql="SET lock_timeout = '3s'", reverse_sql=migrations.RunSQL.noop),
        ...
        migrations.RunSQL(sql="RESET lock_timeout", reverse_sql=migrations.RunSQL.noop),
    ]

The RESET is not optional. CONN_MAX_AGE is 300, so the connection the migration ran on goes back to the pool and serves ordinary requests with whatever session settings it was left holding.

A long RunPython rewrite inside an atomic = False migration is covered by the same session SET: each of its statements waits for its own row locks, and the bound is what keeps a batch from parking behind a worker's open transaction. Give such a migration a batch-level retry rather than a bigger bound, so a batch that loses the race is retried instead of waiting longer.

The one exemption: concurrent index builds

CREATE INDEX CONCURRENTLY and DROP INDEX CONCURRENTLY are exempt, and a migration whose only DDL is one of them needs no lock_timeout statement. A concurrent build runs for as long as the table needs - tens of minutes on the chunk table - and waits by design for every transaction that started before it. A web container waiting for the boot lock polls it, and its session is idle between tries, so a concurrent build in the migrating container cannot deadlock with it. Bounding that wait does not protect anything: it turns a slow build into a failed one, and an interrupted concurrent build leaves an INVALID index behind that fails every later boot until someone drops it by hand. migrations/0055_rebuild_hnsw_index_larger_m.py is the worked example, including its recovery instructions.

Reversing by hand

Nothing automated migrates backwards: the boot sequence, CI, and every script run migrate forward only, so a reverse is always a person at a shell during an incident. The bound does not come along. Both forms above give their lock_timeout statements reverse_sql=migrations.RunSQL.noop, and plain schema operations reverse with no bound either, so the reverse direction queues for its locks unbounded - exactly the outage the forward rule exists to prevent.

Set the bound on the session instead, in the environment of the process that runs the command - the shell inside the container or ECS task you are reversing from:

PGOPTIONS="-c lock_timeout=3s" python manage.py migrate <app> <target>

PGOPTIONS reaches Postgres through libpq at connection time, so it covers every statement the reverse runs, including RunPython reverses. A startup -c value also becomes the session's reset point, so a RESET lock_timeout along the way returns to 3s, not to unbounded.

From outside the container, deliver the variable to the process that opens the connection:

docker compose exec -e PGOPTIONS="-c lock_timeout=3s" web python manage.py migrate <app> <target>

Prefixing the host docker command instead sets it on the wrong process, where it reaches no database. The same trap has an ECS form: in an aws ecs execute-command shell the inline form above works, but a reverse run as a one-off run-task needs the variable in the task's container override, not on the CLI that launches it.

A reverse that then fails with a lock-timeout error is the bound working. Rerun it at a quieter moment, or end the blocking transaction - the same response as a forward deploy that hits the bound.

Dropping a column while the previous release is still running

A deploy replaces tasks one at a time, so for the length of the rollout the previous release's code is running against the new schema. Django SELECTs every concrete column of a model, so a dropped column breaks the previous release's reads of that table until its last task is gone - whether or not anything ever used the column.

So before dropping one, name the readers the old release still has.

A one-step RemoveField is acceptable when the only such reader is the worker's row load, and the queue's redrive budget covers the rollout window. That budget is the visibility timeout times maxReceiveCount: a read that fails leaves the message undeleted, it comes back after the visibility timeout, and it only reaches the DLQ once the receive count runs out. Write the numbers in the migration's docstring, so the next person can check the arithmetic rather than re-derive it. The numbers to do that arithmetic with today are 300 seconds of visibility timeout on the interactive queue and 900 on the bulk one, both set in infrastructure/base/sqs.tf, and a DLQ on the third receive, from the max_receive_count default in infrastructure/modules/sqs_queue/variables.tf. No migration in the tree is a worked example of this one-step case yet, so the next one to take it writes its own arithmetic down.

Anything else drops in two releases. A request-path reader has no redrive - the user just sees a 500 - and a column with a writer can lose a write. Those go through SeparateDatabaseAndState: the first release removes the field from model state only, leaving the column in place, and the release after drops the column, by which time no running code knows about it. connectors/0029_pendingconnectoraction_retire_connector_slug.py and connectors/0031_pendingconnectoraction_drop_connector_slug.py are the worked pair, and the harder case of the two, because that column had a writer and 0029 covers it with a BEFORE INSERT trigger that 0031 drops again. main_app/0162_documentingestionstepspan_and_drop_item_trace_id.py and main_app/0167_drop_documentingestionrunitem_trace_id.py are the read-only variant, where the first release only has to give the column a database default so inserts from code that no longer knows the field keep succeeding.

Composite foreign keys through RunSQL

Django's ForeignKey names one column, so a rule of the form "this pointer must name a row of the same parent" cannot be declared on the model. Postgres can hold it: a unique index over (parent_id, id) on the child table, and a foreign key from (id, pointer_id) on the parent, or (parent_id, pointer_id) on a sibling, referencing that pair. Write both as RunSQL pairs after the generated operations, each with a reverse_sql that drops what it created, and say in the migration's docstring which four columns the keys tie together and why the pair is immediate rather than deferred. main_app/0175_skill_drafts_and_editing_audit.py is the worked example: skillversion_skill_id_id_unique and the four ..._belongs_to_skill constraints under it. The service that writes those pointers keeps its own readable check in front of the constraint, so an admin sees a sentence and the constraint is the floor under it.

Applied migrations are not retrofitted

The convention pays forward only. Migrations that already ran everywhere that matters are left as they are, and the checker only ever runs on files a branch changed, so it never re-judges them. 0154_documentingestionrunevent_typed_columns.py is the standing example of the ordering rule: it sets SET LOCAL lock_timeout correctly but places it after an AlterField that has already queued for its lock, which is exactly what lock-timeout-after-ddl catches.

Testing the data step

A migration with a RunPython step gets a test that writes rows in the old shape and asserts what the step does to them. That test is evidence for the pull request that adds the migration, not a gate the pipeline runs forever, so it is marked and then kept out of every default run.

Every such test carries pytest.mark.migration_evidence. Put it on the module as a pytestmark line when the whole file is evidence, and on the individual test or class when the file also holds tests of today's schema, because a module-level mark there would pull those out of the pipeline too.

A plain pytest run, scripts/gate.sh, and the CI validate job never collect a marked test. One command collects exactly them:

docker compose exec -T web pytest --migration-tests apps/main_app/tests/test_your_migration.py

Run it before the pull request opens and paste its summary line into the body under ### Migration evidence. That line is what tells a reviewer the data step was actually exercised, because nothing in CI will run it for you.

scripts/check_migration_evidence_marker.py refuses a test file that drives a migration and carries the marker nowhere. It judges the file rather than the test, so a file with one marked class and a new unmarked one beside it passes the checker; keeping the mark on every migration test is on the author and the reviewer.

Conventions for a cheap test

Import the migration module and call the step's function against the current schema. campuscore_app/apps/main_app/tests/test_migration_0159_metadata_split.py is the worked example, and a test in that style costs under a second.

Replay the graph through MigrationExecutor only when the step reads a column the current schema no longer has. A replay costs seven to sixteen seconds, which is why these tests left the pipeline in the first place.

Write one forward pass that covers every accepted row shape at once, plus one replay per case that must stop the migration. Each refusal needs its own replay because the failure aborts the run.

On the way forward, migrate every app to its leaf rather than only the app under test. Rolling one app back unapplies every other app's migration that depends on it, and migrating only that app forward again leaves the rest of the graph unapplied for every test that follows. migrate_app in campuscore_app/campus_core/tests/migration_helpers.py is the one helper that does this; a migration test calls it rather than carrying its own copy.

Why they leave the pipeline

Once the pull request merges, the migration runs everywhere that matters and then never runs again. Re-proving it on every commit for the rest of the project buys nothing, and a suite that keeps every replay spends most of its slowest block on them. The test file stays in the tree as the record of what was verified, and the marker is what says so.

The checker

scripts/check_migration_lock_timeout.py takes migration file paths and parses each with ast; it imports nothing and runs nothing. It reads the operations list of the Migration class in order, recursing into SeparateDatabaseAndState(database_operations=[...]) and ignoring state_operations, which emit no SQL. It runs from scripts/gate.sh, from pre-commit, and from the migrations job in CI, in each case on the migration files changed against main.

Its five rules:

• ddl-without-lock-timeout - the migration runs DDL and never sets lock_timeout.
• lock-timeout-after-ddl - the first lock_timeout statement comes after the first DDL operation, which has already queued for its lock.
• session-set-inside-transaction - an atomic migration uses a bare SET, which would outlive it on a pooled connection.
• set-local-outside-transaction - an atomic = False migration uses SET LOCAL, which is a no-op there.
• session-set-without-reset - an atomic = False migration sets lock_timeout and does not end with RESET lock_timeout.

The checker is conservative wherever it cannot read the SQL. A RunSQL whose sql is a string literal, a list of literals, or a module-level string constant is judged by its statements; one built from an f-string or a helper call is counted as DDL, and so is any operation the checker cannot name. It is also conservative where it can read the SQL but does not recognise it - a DO $$ block counts as DDL - and where an operation emits no SQL at all on Postgres, such as an AlterField that only changes choices. A migration built from SQL helpers is therefore asked for one extra RunSQL statement, never to inline its SQL.