How to modify the structure of a table?

The MySQL ALTER command is used to modify the structure of an existing table. It allows you to make various changes, such as adding, deleting, or modify columns within the table. Additionally, the ALTER command is also used to add and drop different constraints associated with an existing table.

Query:

Difficulty: 4/10

Topics: ALTER TABLE syntax, Online DDL, Schema migration strategies

Scenario Questions

0-2 years experience

1
How would you add a nullable email VARCHAR(255) column to an existing users table that already has 10k rows?
2
What happens if you try to change a VARCHAR(50) column to VARCHAR(30) when some existing rows have 40-character values?
3
You need to add an index on created_at for a reporting query. What's the exact command, and does it lock the table?

2-5 years experience

1
We're adding a preferences JSON column to a 50M-row accounts table for a new feature. Walk me through how you'd deploy this without downtime.
2
A migration to change a primary key from INT to BIGINT failed halfway through on a replica. How do you diagnose and recover?
3
You need to drop a legacy_status column that's referenced by a foreign key from an audit table. What steps do you take, and in what order?

5-8 years experience

1
Design a zero-downtime schema migration strategy for a 24/7 service with 100M+ row tables. Include tooling, validation, and rollback.
2
How do you coordinate schema changes across a sharded MySQL fleet where each shard has different data volumes?
3
Compare pt-online-schema-change, gh-ost, and native online DDL for a 500GB table. When would you pick each?

8+ years experience

1
We're splitting a monolith into microservices that share a MySQL cluster. How do you define schema ownership, migration approval, and breakage accountability across 10+ teams?
2
Design a schema versioning and rollback strategy for a multi-region active-active MySQL deployment with asynchronous replication.
3
How do you enforce schema change policies (code review, staging validation, canary deployment) across 50+ engineering teams without becoming a bottleneck?

Follow-up Questions

❓What happens if you run ALTER TABLE on a 100M-row table during peak traffic?
❓How do you roll back a column type change that's already been deployed to production?
❓When would you choose gh-ost over native online DDL?