Fix the rollout plan for a billion-row backfill

Review the AI's codeEasy10 min

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_size is 0 or less, return [].
  • The steps are, in this order:
    1. "ADD COLUMN tenant_id NULL": metadata only, no table rewrite.
    2. "ADD FK NOT VALID": checks new writes without scanning old rows.
    3. One "BACKFILL a-b" per batch, each a short transaction of its own.
    4. "VALIDATE FK": scans the old rows without blocking writes.
    5. "ADD CHECK NOT VALID" and then "VALIDATE CHECK": prove there are no nulls without blocking writes.
    6. "SET NOT NULL": cheap, because the validated check already proves it.
    7. "DROP CHECK".
  • A batch is written BACKFILL a-b and covers the ids from a to b, both included. The first batch starts at min_id, each batch covers batch_size ids, the next one starts right after it, and the last one is cut off at max_id. If min_id is greater than max_id, there are no backfill steps.

Examples

plan_safe_rollout(min_id: int, max_id: int, batch_size: int) -> list[str]
Example 1
Inputplan_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
Inputplan_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
Inputplan_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"]