How would you add a column and an index to a 500 million row table without downtime?

What the interviewer wants to hear

• Understand which DDL takes heavy locks
• Add nullable columns or columns with constant defaults, which are fast in modern versions
• Use CREATE INDEX CONCURRENTLY or online DDL
• Use lock_timeout and retries
• Backfill in small batches
• Tools like gh-ost or pt-online-schema-change for MySQL

Model answer

First I check what locks each step takes. In PostgreSQL 11 and later, adding a nullable column or one with a constant default is a quick metadata change, but it still needs a brief ACCESS EXCLUSIVE lock, so I set lock_timeout to a few seconds and retry, so it does not queue behind a long query and block everyone. For the index I use CREATE INDEX CONCURRENTLY, which does not block writes, and I check afterwards that it is valid. If existing rows need values, I backfill in small batches with pauses to limit replication lag. In MySQL I use InnoDB online DDL with ALGORITHM=INSTANT or INPLACE where possible, or gh-ost for bigger changes. I follow an expand and contract approach so old and new app versions both work during rollout.

More medium Database Administrator questions

• A query that ran in 50 ms yesterday now takes 20 seconds. Nothing in the code changed. How do you investigate?
• Explain the SQL isolation levels and the anomalies each one allows.
• How do you set up and monitor replication, and what do you do when replica lag grows?
• A busy PostgreSQL table keeps getting bloated. How do you tune autovacuum?
• Walk me through how you read an EXPLAIN ANALYZE plan to find why a query is slow.
• How would you tune the main PostgreSQL memory settings on a new server?
• Which metrics and alerts would you set up for a production PostgreSQL or MySQL database?
• What are the main operational differences between running MySQL (InnoDB) and PostgreSQL?
• Developers send you schema changes and new queries to review. What do you look for?