Zero-downtime index migration on a hot table?

WHAT THIS TESTS Whether you understand that DDL on a large hot table can take long-held locks, and that you must use online build mechanisms plus a rollout plan to avoid an outage.

A GOOD ANSWER COVERS Use the database's non-blocking index creation, such as CREATE INDEX CONCURRENTLY in Postgres or an online DDL algorithm in MySQL, so reads and writes continue while the index builds. Recognize the build is slower and can still take a brief lock at start or finish, so schedule it during lower traffic and watch lock waits, CPU, IO, and replica lag throughout. On replicated setups, understand how the DDL propagates and whether it stalls replication. Make the change additive and reversible: adding an index does not alter data, and dropping it is a fast metadata operation, so rollback is trivial. After creation, confirm the planner actually uses the index with representative queries before declaring success, and clean up if the concurrent build leaves an invalid index behind on failure.

COMMON WRONG ANSWERS Running a standard blocking CREATE INDEX, which holds an exclusive or write lock and freezes the table for minutes or hours. Ignoring replica lag, so followers fall behind and reads go stale. Assuming the index will be used without checking the query plan. Treating the migration as irreversible or coupling it with destructive schema changes in the same step.

LIKELY FOLLOW-UPS What lock does a concurrent build still need. How do you handle a failed concurrent build leaving an invalid index. How do you do this across read replicas. How would you add a NOT NULL column safely by contrast.

ONE CONCRETE EXAMPLE On a 500 million row orders table you run CREATE INDEX CONCURRENTLY on the customer_id column during the overnight low. You watch lock waits and replica lag on a dashboard. The build takes 40 minutes with no write blocking. You then run EXPLAIN on the target query to confirm the planner picks the new index. If anything looked wrong, DROP INDEX CONCURRENTLY would have rolled it back in seconds without touching the data.