Fix the rollout plan for a billion-row backfill
Your task
An AI wrote one transaction that adds a NOT NULL column tenant_id to a 1.2 billion row events table, backfills it and adds a foreign key. It took 3 seconds on staging. In production it would hold an exclusive lock for hours and block 40,000 writes a second. The rollout is now a sequence of steps, each chosen for the lock it takes.
An assistant wrote plan_safe_rollout(min_id, max_id, batch_size), which prints that sequence. It is in the editor. It has a bug. Find it and fix it.
Input and output:
min_id,max_id: the lowest and highest row id to backfill.batch_size: how many ids each backfill batch covers.- Return the steps, in order.
Rules the tests check:
- If
batch_sizeis 0 or less, return[]. - The steps are, in this order:
"ADD COLUMN tenant_id NULL": metadata only, no table rewrite."ADD FK NOT VALID": checks new writes without scanning old rows.- One
"BACKFILL a-b"per batch, each a short transaction of its own. "VALIDATE FK": scans the old rows without blocking writes."ADD CHECK NOT VALID"and then"VALIDATE CHECK": prove there are no nulls without blocking writes."SET NOT NULL": cheap, because the validated check already proves it."DROP CHECK".
- A batch is written
BACKFILL a-band covers the ids from a to b, both included. The first batch starts atmin_id, each batch coversbatch_sizeids, the next one starts right after it, and the last one is cut off atmax_id. Ifmin_idis greater thanmax_id, there are no backfill steps.
Examples
plan_safe_rollout(min_id: int, max_id: int, batch_size: int) -> list[str]Example 1
Input
plan_safe_rollout(1, 2500, 1000)Output
["ADD COLUMN tenant_id NULL", "ADD FK NOT VALID", "BACKFILL 1-1000", "BACKFILL 1001-2000", "BACKFILL 2001-2500", "VALIDATE FK", "ADD CHECK NOT VALID", "VALIDATE CHECK", "SET NOT NULL", "DROP CHECK"]Example 2
Input
plan_safe_rollout(1, 0, 1000)Output
["ADD COLUMN tenant_id NULL", "ADD FK NOT VALID", "VALIDATE FK", "ADD CHECK NOT VALID", "VALIDATE CHECK", "SET NOT NULL", "DROP CHECK"]Example 3
Input
plan_safe_rollout(5, 5, 10)Output
["ADD COLUMN tenant_id NULL", "ADD FK NOT VALID", "BACKFILL 5-5", "VALIDATE FK", "ADD CHECK NOT VALID", "VALIDATE CHECK", "SET NOT NULL", "DROP CHECK"]