Add a NOT NULL Column to a 50M-Row Prod Table

A migration adds a NOT NULL column to a huge live table. A teammate wants to run the ALTER directly. Defend the safe path.

intermediate14 min5 roundsDatabases

Answer key reviewed by Branko Petric

Read first: AWS Databases: RDS and DynamoDB

Your decision

You need to add a NOT NULL column with a default to a 50 million row table in production. A teammate wants to just run the ALTER TABLE during business hours. How do you do this without taking the app down?

The situation

A feature needs a new required column on your busiest table, which has about 50 million rows and serves constant reads and writes from the live app.

Context

The table is the hot path for the product. You are on PostgreSQL with read replicas. A teammate wants to ship the migration as a single ALTER TABLE that adds the column with a default and a NOT NULL constraint, run during the workday to keep things simple.

More: Database scenario interview questions for DevOps and SRE