Mock CTO interview: adding a required column to a 50-million-row table without downtime

# ======================================================================
# Q: CTO: "We want to add a mandatory column to a table with 50 million rows.
# How do you do it without downtime?"
#
# You: "In three releases. 1) A nullable column with lock_timeout, and the new
# code writes the value. 2) Backfill in batches with keyset pagination and a
# short pause, so I don't overload the database and the replicas. 3) A CHECK
# constraint NOT VALID, then VALIDATE in a separate migration, which does not
# block writes. Since PostgreSQL 12, SET NOT NULL uses the validated constraint
# and does not scan the table again."
# ======================================================================

# Step 2: management command for the backfill
last_pk = 0
while True:
    ids = list(Order.objects.filter(pk__gt=last_pk, channel__isnull=True)
               .order_by('pk').values_list('pk', flat=True)[:5000])
    if not ids:
        break
    Order.objects.filter(pk__in=ids).update(channel='web')
    last_pk = ids[-1]
    time.sleep(0.2)

# Step 3a: migration
operations = [AddConstraintNotValid('order', models.CheckConstraint(
    condition=Q(channel__isnull=False), name='order_channel_not_null'))]
# Step 3b: separate migration
operations = [ValidateConstraint('order', 'order_channel_not_null')]