Migrations

What you will be able to do

Change the database of a running API without surprises. Write one numbered file per change, apply it once and never edit it, and rehearse it on large data. No change may block a busy table for long.

Before you startWhat to know and to have readyAbout 50 minutes

What you need to know

• Numbered migration files and the table schema_migrations, from From SQLite to PostgreSQL. If you took CI/CD for AI Systems, Migration safety taught them in more depth; you do not need it here.
• Migrations 005 and 006, from Integrity constraints.
• The pool and its connections, from Connection management.

What you need on your computer

• The project after Connection management: python -m pytest prints 74 passed.
• The large data loaded in your database, from that lesson’s guided practice. Two terminals.

After this lesson, your project matches the snapshot end-of-m03. The report that holds a lock, the edited migration and the scratch files are constructed cases. Tested on macOS (Apple silicon); the Windows commands are not tested. The times were measured on an Apple M4 computer with nothing else busy. Your times will differ; the shape stays the same.

Road works on a busy street

A city repairs its roads with numbered work orders. Each order is done once, and a finished order is never rewritten. If the repair was wrong, the city writes a new order. So anyone can read the list and know what the road is like today.

The same repair can be easy or hard. On a quiet test road, closing one lane for an hour bothers nobody. On the main street at rush hour, closing it for two minutes makes a queue of cars a kilometre long. Worse, a car that only wanted to turn right also waits, because it is stuck behind the queue.

A database change is road work on the street that the API drives on.

Migrations so far

A migration is one numbered SQL file that changes the database, applied by ticket_api/migrate.py. The project took this runner from the CI/CD course. The runner applies the files in the order of their names, records each one in schema_migrations, and runs each file in its own transaction. Your database has six of them: 001 to 004 from the setup, and 005 and 006 from Module 2.

Those rules give an order and a record. They do not answer three questions that the help desk now has. What if someone edits a file that already ran? How long does a change take on 300,000 tickets? And what does a change do to the API that reads the table at the same time?

The problem: fine on small data, a block on large data

Migration 006 adds two columns to tickets and fills them, in one transaction. On the small data, it took 0.01 seconds. Mei tested it again on the large data, while a reader asked for one ticket every 10 ms:

• Small data
• Large data
• Read wait (large)

Seconds per migration on the small data (200 tickets) and the large data (300,000 tickets), and the longest wait of a simple read while the migration ran on the large data. Medians of 3 runs of scripts.rehearse --from 005. 008 is the index migration of Module 4.

Text description of the chart

Three bars per migration. The first two are the seconds on the small and on the large data. The third is the longest wait of a read by key during the large run. 005 (constraints): 0.02 s small, 1.16 s large. Reads waited 0.88 s. 006 (two columns and their backfill): 0.01 s small, 1.83 s large. Reads of tickets waited 1.81 s. 007 (the cost as numeric): 0.02 s small, 0.37 s large. Reads of ai_runs waited 0.35 s. 008 (three indexes, built with CONCURRENTLY in Module 4): 0.02 s small, 0.97 s large. Reads waited 0 s. The labels round to one decimal; values below 0.05 s have no label.

What this tells us. On the small data, every migration looks instant: the small bars are too short to see. On the large data, 006 took 1.83 seconds, and for 1.81 of them no request could read a single ticket. The table also grew from 75 MB to 152 MB. 008 took almost a second too, but nobody waited: time is not the problem, blocking is.

Why did reads wait? Every statement takes a lock on the tables it uses: a mark that says what others may do with the table at the same time. A normal SELECT takes the weakest lock, which allows nearly everything. ALTER TABLE … ADD COLUMN takes the strongest, ACCESS EXCLUSIVE, which allows nothing, not even a read. PostgreSQL keeps every lock until the end of the transaction. In 006, the UPDATE of all 300,000 rows runs in the same transaction, so the strongest lock stays for the whole backfill.

And why did the table double? PostgreSQL never changes a row in place. An UPDATE writes a new version of each row, and the old version stays until a cleanup called VACUUM reuses its space. 300,000 updated rows made 300,000 new versions.

The idea: rehearse on representative data

The fix for surprises is a rehearsal. Before a migration runs on the real database, run it on a copy with data like the real data. That means the same tables, the same size and the same mix of values. That is representative data. The course’s large data is the help desk’s representative data.

scripts/rehearse.py does it in one command. It makes a separate database, <your database>_rehearsal. It applies the migrations before the first one to test, loads the large data, and applies the rest one at a time. While each one runs, a reader asks for one row by key from each table, every 10 ms. The longest wait shows how long the migration blocked normal reads. Your own database does not change. The author’s run, from 005 on:

Loading the large data into tickets_rehearsal (migrations up to 004_help_desk_tables) ...
migration                         seconds  longest read wait (s):     tickets     messages      ai_runs  attachments
005_integrity_constraints            1.29                                 0.93         0.70         0.17         0.47
006_ticket_message_counts            2.11                                 2.08         0.00         0.00         0.01
007_ai_run_cost_numeric              0.44                                 0.00         0.00         0.41         0.00

This is one run, so its numbers differ a little from the chart’s medians. The shape is what matters: 006 blocks tickets, and 007 blocks ai_runs. 005 blocks several tables for under a second, because SET NOT NULL and new checks read every row under a strong lock.

Write the next change safely

006 has already run on every database of the course, so it stays as written. Changing an applied migration is the one thing a migration runner must forbid (see the new rules). The lesson is for the next change of this kind. Split it into small steps, so that no step holds a strong lock for long:

One transaction that adds and fills a column, against the same change in three steps.

Text description of the diagram

On the left, 006: the column and its backfill are one transaction, so the strongest lock lasts as long as the whole UPDATE. On the right, the safe order. First add the column with no default and no rule, which changes only the table’s description and takes milliseconds. Then fill it in batches; each batch is its own short transaction and takes only row locks, which do not block reads. Last, add the rule, when every row has a value.

This pattern is called expand and contract. First expand: add the new column, which nothing depends on yet. Then backfill it. Contract last: tighten the schema, here with the rule, when every row is ready. Migration safety in the CI/CD course uses the pattern to rename a column; here it protects a large table. The steps take longer in total; the Your turn task measures it. For the last step, PostgreSQL can add a check without a long block. ADD CONSTRAINT … NOT VALID takes a short lock and checks only new rows. VALIDATE CONSTRAINT later checks the old rows while reads and writes go on.

Larkfield: 007, money as numeric

Priya adds up the cost of the AI runs. The old tool stored each cost as real, a floating-point number of about 7 significant digits, kept in binary. Many decimal values cannot be stored exactly in binary. The value 0.002432 is stored as 0.0024319998919963837. The author ran the same sum six times on the large data, before 007:

SELECT sum(cost_usd), sum(cost_usd::numeric) FROM ai_runs;
95.059525|95.0596787
95.05952|95.0596787
95.059494|95.0596787
95.05954|95.0596787
95.05959|95.0596787
95.05959|95.0596787

The left column is the sum of real. It changes from run to run, because PostgreSQL adds the rows in parallel, in a different order each time, and each addition rounds. The right column is the exact sum, 95.0596787 dollars, every time. Be honest about the size: the difference is at most 0.0002 dollars over 449,734 runs. Nobody loses money here. The problem is that a report gives a different total each time, and that a stored cost is not the cost that was written. That is the wrong meaning for money.

So migration 007 changes the type. numeric(12, 8) stores up to 12 digits, 8 of them after the point, exactly, in decimal:

migrations/007_ai_run_cost_numeric.sql

-- Money is not a floating-point number. The old tool stored the cost of each AI
-- run as real (about 7 significant digits, in binary), so sums drift and some
-- values cannot be stored exactly. numeric(12, 8) stores the dollars exactly.
-- This rewrites the whole table and blocks it while it runs: time it on the
-- large data before you run it on a busy database.
ALTER TABLE ai_runs ALTER COLUMN cost_usd TYPE numeric(12, 8);

The rehearsal said: 0.37 seconds on the large data, with reads of ai_runs blocked for 0.35 seconds. That is acceptable for the help desk, at a quiet time. After 007, the sum is 95.05967870, every time.

The lock queue

Mei runs 007 on a copy of the large data, and something strange happens. Priya’s report has a transaction open on ai_runs: it read the table and has not committed yet. That is allowed: a read lock and other reads share the table. But 007 needs the strongest lock, so it waits for the report. And then every new read of ai_runs waits too, behind 007. PostgreSQL serves lock requests in order, so a read cannot pass a migration that is waiting. This is the turning car stuck behind the queue.

A constructed case on the large data: a report holds ai_runs for 6 seconds while migration 007 waits for it. The read is from a connection that had run the same query many times.

Text description of the diagram

The report and the read only read, so they could share the table. But the migration is in the queue between them, and it needs the table for itself. So the read waits for the migration, which waits for the report. Without a limit, the read waited 5.9 seconds, and then it failed. With a lock timeout of 2 seconds, the migration gave up at 2.51 s and left the queue. The read got its row after 2.0 seconds, with no error.

Two lessons are in this picture.

A lock timeout bounds the damage. lock_timeout tells PostgreSQL how long a statement may wait for a lock. The new migrate.py sets it for every migration from MIGRATION_LOCK_TIMEOUT, by default 5s. A migration that waits longer stops with a clear message and changes nothing. You run it again at a quieter time. A stopped migration is cheap; a stopped API is not.

A type change breaks queries that a running app has prepared. psycopg prepares a query on the server after it has run 5 times on one connection. PostgreSQL then keeps its plan, with the type of each column in the result. After 007, the plan says real and the column says numeric. The author checked what happens next, in a constructed test. A single long-lived connection failed with cached plan must not change result type on every run, until it reconnected. The project’s API, with its pool, failed once per pooled connection that had prepared the query, and then worked again.

ADD COLUMN does the same to a prepared SELECT *, which is why the ticket list’s SELECT * FROM tickets failed once in the same test after a new column. So after a migration that changes what a query returns, restart the API or recycle the pool’s connections. Better, name your columns instead of SELECT *; Module 4 does this.

The runner’s new rules

The new ticket_api/migrate.py adds three rules to the old ones.

An applied migration never changes. The runner now stores a checksum of each file, a SHA-256 fingerprint of its bytes, in schema_migrations. If an applied file changes, nothing runs:

Migration stopped: 005_integrity_constraints.sql was changed after it was applied. Put it back as it was, and write the change as a new migration.

Why so strict? An edited file runs on new databases only. Old databases keep the old version, so the two kinds drift apart without anyone noticing. This is not a theory. While building this course’s project, the author edited 012_erasure.sql after it had been applied, and this rule stopped the next run with the same message. The fix was a new migration.

A migration does not wait forever. MIGRATION_LOCK_TIMEOUT, as above.

Some statements cannot run in a transaction. CREATE INDEX CONCURRENTLY builds an index without blocking writes, and PostgreSQL forbids it inside a transaction. A file whose first line is -- migrate: no-transaction runs outside one, one statement at a time. Module 4 needs this for its indexes. If a concurrent index build fails halfway, it can leave an index marked invalid. The runner checks for that and stops with the name of the index.

Many teams use a tool for this: Alembic with SQLAlchemy, or Flyway. They record versions and checksums in the same way. This course keeps its 150-line runner, so that you can read every rule.

Guided practice: 007 and the new runner

1. In ~/projects/ticket-api, with the virtual environment active, copy the four files of this lesson from the snapshot end-of-m03:

   macOS / Linux

   S=../nextia-learning-labs/C20/snapshots/end-of-m03
   for f in ticket_api/migrate.py migrations/007_ai_run_cost_numeric.sql scripts/rehearse.py tests/test_migrations.py; do
     cp "$S/$f" "$f"
   done

   Windows (PowerShell)

   $S = "..\nextia-learning-labs\C20\snapshots\end-of-m03"
   foreach ($f in 'ticket_api\migrate.py', 'migrations\007_ai_run_cost_numeric.sql', 'scripts\rehearse.py', 'tests\test_migrations.py') { Copy-Item "$S\$f" $f }

2. Ask what is pending: python -m ticket_api.migrate --status. The new runner also records the checksums of the six applied files, the first time it runs.

3. Rehearse from 005 on the large data: python -m scripts.rehearse --from 005. It takes about half a minute and does not change your database.

4. Add up the costs before the change: docker compose exec db psql -U tickets -d tickets -c "SELECT sum(cost_usd), sum(cost_usd::numeric) FROM ai_runs;". Run it two or three times.

5. Apply 007: python -m ticket_api.migrate. Then run the sum of step 4 again.

6. Run the tests: python -m pytest. Expect 79 passed. Your project now matches the snapshot end-of-m03.

7. Try the checksum rule. Add a line to an applied file and run the migrations:

   echo "-- a small edit" >> migrations/005_integrity_constraints.sql
   python -m ticket_api.migrate

   Then put the file back from the snapshot: cp "$S/migrations/005_integrity_constraints.sql" migrations/ (Windows: Copy-Item "$S\migrations\005_integrity_constraints.sql" migrations\). Run python -m ticket_api.migrate again.

8. Try the no-transaction rule with a scratch file. Create migrations/900_index.sql with one line, CREATE INDEX CONCURRENTLY tickets_subject_idx ON tickets (subject);, and run python -m ticket_api.migrate. Then delete the file.

Show the expected result

Step 2 lists applied for 001 to 006 and pending 007_ai_run_cost_numeric. Step 3 prints a table like the one above. Step 4 prints the exact sum 95.0596787 in the right column every time; the left column changes a little between runs. Step 5 prints applied 007_ai_run_cost_numeric and Database is up to date (7 migrations)., and the sum is then 95.05967870. Step 7 prints the Migration stopped: 005_integrity_constraints.sql was changed after it was applied. … message, and after you put the file back, Database is up to date (7 migrations). Step 8 prints:

Migration stopped: 900_index.sql failed: CREATE INDEX CONCURRENTLY cannot run inside a transaction block

Nothing of 900 stays, because the failed transaction rolled back. If python -m ticket_api.migrate --status still shows a 900 file, you did not delete it.

Your turn: add a column without blocking

Change one thing: write the next column the safe way. The help desk wants subject_length, the number of characters in each subject. Write two migrations: 900_ticket_subject_length.sql adds the column, and 901_backfill_subject_length.sql fills it in batches of 50,000 rows, each batch its own transaction. Rehearse them on the large data with python -m scripts.rehearse --from 900. Predict first: how long do reads of tickets wait, compared with 006?

Show a solution and the author’s result

migrations/900_ticket_subject_length.sql

-- Expand: a new column that may be empty. No default and no NOT NULL, so
-- PostgreSQL changes only the table's description, not its 300,000 rows.
ALTER TABLE tickets ADD COLUMN subject_length integer;

migrations/901_backfill_subject_length.sql (the first batch; repeat the UPDATE 7 times)

-- migrate: no-transaction
-- Backfill in batches: each UPDATE is its own short transaction, so no lock
-- is held for long. A statement that finds no empty row changes nothing.
UPDATE tickets SET subject_length = length(subject)
WHERE ticket_id IN (SELECT ticket_id FROM tickets WHERE subject_length IS NULL LIMIT 50000);

The first line of 901 makes the runner send each statement on its own, so each batch commits by itself. Seven batches cover 300,000 rows; the seventh finds nothing. The author’s rehearsal:

migration                         seconds  longest read wait (s):     tickets     messages      ai_runs  attachments
900_ticket_subject_length            0.01                                 0.00         0.00         0.00         0.00
901_backfill_subject_length          4.69                                 0.01         0.01         0.00         0.01

Reads of tickets waited 0.01 s, against about 2 s for 006. The backfill took longer in total, because each batch searches again for empty rows on a busy computer. That is the trade: more total time, no blocking. Then delete both files. They are practice; never run them with python -m ticket_api.migrate on your database. The rehearsal database was dropped at the end.

A migration that waits behind an open transaction

Common mistake

A migration waits for a lock that a forgotten session holds

Symptom (constructed): python -m ticket_api.migrate stops after a few seconds with a lock message, and nothing changed. Reproduce it with two terminals and a scratch migration. In terminal 1, open psql and leave a transaction open:

docker compose exec db psql -U tickets -d tickets

BEGIN;
SELECT count(*) FROM ai_runs;

In terminal 2, create migrations/900_reviewed.sql with the line ALTER TABLE ai_runs ADD COLUMN reviewed boolean;, and run the migrations with a short limit:

macOS / Linux

MIGRATION_LOCK_TIMEOUT=2s python -m ticket_api.migrate

Windows (PowerShell)

$env:MIGRATION_LOCK_TIMEOUT = "2s"; python -m ticket_api.migrate; Remove-Item Env:MIGRATION_LOCK_TIMEOUT

Migration stopped: 900_reviewed.sql waited more than 2s for a lock. Another transaction is using the table. Try again when it is quiet.

Cause: the open transaction in terminal 1 holds a lock on ai_runs. The migration needs the strongest lock, so it waits. Without the limit, every read of ai_runs from the API would queue behind it.

Diagnose: find the session that holds the table: SELECT pid, state, now() - xact_start AS open_for, query FROM pg_stat_activity WHERE state LIKE 'idle in transaction%';. A session that is idle in transaction for minutes is usually a person’s psql, a report, or a leaked connection.

Fix:

1. In terminal 1, end the transaction: ROLLBACK;, then \q.
2. Delete the scratch file migrations/900_reviewed.sql, so that it never runs. python -m ticket_api.migrate --status must list only 001 to 007.
3. In real work, find out who holds the lock, wait for a quiet time, and run the migration again. Do not raise the timeout until it passes: that only moves the queue onto the API.

For an AI service: analysis jobs, such as Priya’s report or an evaluation run over the AI-run table, often keep long transactions open. Schedule schema changes away from them, and keep the lock timeout short, so that a migration fails fast instead of stopping the API.

See it end to endDesign and tune storage for an open public datasetCase study

The Boston 311 case study changes a CHECK rule on 582,849 rows with NOT VALID and VALIDATE, so that reads and writes go on.

See it end to endVector search over an open document collectionCase study

The arXiv case study changes the embedding model with migrations: a new version, a backfill in batches, an index built concurrently, then one switch.

Knowledge check

Check your understanding

A teammate finds a typo in a comment of 005_integrity_constraints.sql and fixes it in the file. What happens on the next deployment, and what should they do instead?

Show the answer

The next python -m ticket_api.migrate stops before it applies anything: 005_integrity_constraints.sql was changed after it was applied. The file’s checksum no longer matches the one in schema_migrations. Put the file back exactly as it was, and live with the typo; a comment does not change the database. A real change goes into a new migration, such as 008_…, so that every database gets it in the same order.

Check your understanding

Predict: migration 006 took 0.01 s on the small data. Why can a test on the small data not show its 1.8 seconds of blocked reads, and what does the rehearsal do differently?

Show the answer

The time of 006 grows with the number of rows that its UPDATE rewrites: 200 tickets take milliseconds, 300,000 take seconds. The lock is the same in both cases, but on 200 rows it lasts too short for anyone to notice. scripts.rehearse runs the migrations on a copy with the large data, and a reader asks for a row every 10 ms during each migration. So the rehearsal shows both the time and how long normal reads waited.

Check your understanding

With MIGRATION_LOCK_TIMEOUT=2s, migration 007 stopped and did not change the cost type. Was that a failure? What should the team do next?

Show the answer

It was the safe outcome. Without the limit, reads of ai_runs waited 5.9 seconds and then failed. With it, the migration gave up after its 2 seconds of waiting, and reads waited at most 2.0 seconds, with no error. Next, find the transaction that held the table (pg_stat_activity, idle in transaction), and run 007 again when it has ended. After 007 succeeds, restart the API or recycle its connections, because the column’s type changed under queries that it had prepared.

Recap

• A migration is one numbered SQL file, applied once, in order, in its own transaction. Now the runner also stores a checksum, and an edited applied file stops everything.
• Small data hides cost. 006 took 0.01 s on 200 tickets and 1.83 s on 300,000. It blocked every read of tickets for 1.81 s, because ALTER TABLE holds the strongest lock to the end of its transaction.
• Rehearse each migration on representative data with scripts.rehearse. Add a column first, fill it in batches, and add the rule last.
• 007 stores the cost as numeric(12, 8). The real sum changed between runs; the exact sum is 95.0596787 dollars every time.
• A waiting migration makes every later read wait: 5.9 s and an error without a limit, at most 2.0 s with MIGRATION_LOCK_TIMEOUT=2s. After a type change, restart the API or recycle its connections.
• Your project matches end-of-m03: python -m pytest prints 79 passed. Module 4 makes the slow list fast, starting with its query plan.

Next: Read a query plan