Review: Address Table Migration and Backfill Written by an AI Assistant

Customers can only store one address, which is held in three columns on customers. Your team asked an AI coding assistant to move addresses into their own table so that orders can record the shipping address used. It opened this pull request with a schema migration and a one-off backfill script. You are the reviewer, and nothing merges without your approval.

The assistant's description:

Adds an addresses table, links it from customers and orders, and indexes the new foreign key on orders. The migration is a single transaction, so it either fully applies or does nothing. backfill.py copies every existing address in batches of 5,000, committing per batch so it is safe to re-run, then removes the old columns so there is only one source of truth.

Where it runs: PostgreSQL 14. customers has 40 million rows; line1, city and postcode are nullable, and about 3% of customers have never given an address. orders has 300 million rows and takes up to 1,500 inserts per second at peak. The deploy pipeline runs migrations against the live database, then does a rolling deploy in which pods running the old code keep serving for about 20 minutes; the old code reads and writes the three address columns. A nightly privacy job erases about 2,000 customers a day with DELETE FROM customers; their orders are kept with customer_id set to NULL.

Your task

• Flag every problem you would block the merge for. The change has two files: switch between them with the file tabs. Click a line number to comment, and Shift-click to cover a range.
• For each one, choose the issue type and severity, and explain what fails and how to fix it.
• You are scored on the problems you catch, the right issue type and a clear explanation. Missing a critical problem fails the round.

Aim for 25 minutes, as in a real review round.

What lock does CREATE INDEX take on orders, how long does it take on 300 million rows, and how long is the ALTER TABLE orders lock held when both are in one transaction?

Run the backfill loop by hand with 10 customers and a batch size of 2. Which customers does the second SELECT return?

A pod running the old code saves a new customer's address five minutes after the backfill finished. Where does it go?

Follow the privacy job: delete a customer whose address an order points to. What do the foreign keys do?

Which new foreign key has no index behind it, and which statement has to look it up?

A strong reviewer blocks this change on two grounds before anything else: the migration stops all writes to orders for the length of an index build on 300 million rows, and the backfill skips half the customers and then drops the only copy of their addresses.

1. Index build blocks the orders table (migration.sql lines 21–22, critical). A plain CREATE INDEX takes a lock that blocks every insert and update on orders until the build finishes, which takes many minutes on 300 million rows. Because it runs in the same transaction as ALTER TABLE orders, the ACCESS EXCLUSIVE lock from line 18 is also held for the whole build, so even reads queue. Checkout is down for the duration. Use CREATE INDEX CONCURRENTLY in its own migration outside a transaction, and set a short lock_timeout for the ALTER TABLE statements.
2. OFFSET over a shrinking set skips rows (backfill.py line 20, critical). Each batch sets default_address_id, so those rows leave the WHERE default_address_id IS NULL set, and the next query then skips another 5,000 with OFFSET. Half the customers are never copied. Drop the offset (always take the first batch of what is left) or, better, page by key: WHERE id > %s ORDER BY id LIMIT %s.
3. Old columns dropped while still in use (backfill.py lines 41–45, critical). The old pods keep reading and writing line1, city and postcode for 20 minutes, and nothing copies addresses written after a customer was backfilled. Dropping the columns breaks those pods and destroys every address the backfill missed, including the half skipped above, with no way back. Use expand and contract: dual-write from the new code, verify that counts match, and drop the columns in a later release.
4. Backfill fails on customers without an address (migration.sql lines 9–11, major). The new columns are NOT NULL, but the old ones are nullable and 3% of customers have no address. The first such customer raises a not-null violation and the job stops. Skip customers whose address is NULL in the backfill, or decide on nullable columns.
5. Privacy deletes start failing (migration.sql line 19, major). Deleting a customer cascades to addresses, but orders.shipping_address_id references those addresses with the default NO ACTION, so the delete fails for any customer with an order. Decide what an erased customer's order keeps: ON DELETE SET NULL on the order reference, or keep anonymised address rows.
6. No index on addresses.customer_id (migration.sql line 7, major). PostgreSQL does not index the referencing side of a foreign key. Every customer delete must find matching addresses for the cascade, and loading a customer's addresses is the main read path, so both become sequential scans once the table holds 40 million rows. Add CREATE INDEX ON addresses (customer_id).
7. Row-by-row copy (backfill.py lines 27–37, major). Two statements and two round trips per customer is 80 million statements, which takes days and holds row locks on each batch of customers while it runs. Copy each batch with one set-based statement, for example INSERT ... SELECT ... RETURNING followed by UPDATE ... FROM.