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.
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.
Related challenges
- Your Primary Region Is Down. Fail Over Now?
The primary region is degraded. A teammate wants to flip DNS to the standby immediately. Defend whether and how you fail over.
- Nightly Backups Run. Are You Actually Covered?
Automated backups have run for months. A teammate says you are fully covered for disasters. Defend whether you agree.
- Every Deploy Hammers the Database for 10 Minutes. Bigger DB?
Each deploy flushes the cache and the database melts for 10 minutes while it refills. A teammate wants to double the database size. Defend fixing the stampede instead of paying for peak.
- Failover Lost 5 Minutes of Writes. Promise Zero Data Loss?
An async-replica failover dropped five minutes of committed writes. Leadership demands zero data loss with zero performance impact. Defend the honest trade-off.
More: Database scenario interview questions for DevOps and SRE