You need to split a large users table column "name" into "first_name" and "last_name" on a table with 50 million rows, with no downtime. Walk through your plan.

What the interviewer wants to hear

• Expand and contract across several deploys
• Add nullable columns without long locks
• Dual write from the application, then backfill in small batches
• Switch reads and verify data before dropping the old column
• Each step must be compatible with the previous app version and reversible

Model answer

I would use expand and contract over several releases. Step one: add first_name and last_name as nullable columns with no default that rewrites the table, so the ALTER is quick and takes only a brief lock; I set a lock_timeout so it fails fast instead of blocking traffic. Step two: deploy code that writes to both the old and new columns on every create and update, while still reading the old one. Step three: backfill old rows in small batches, for example 5,000 rows at a time by primary key, with pauses so replication lag and load stay low; the job must be resumable. Step four: verify that the new columns match, then deploy code that reads from the new columns. Step five, in a later release once nothing uses it: stop writing name and drop it. At every step the previous app version still works, so a rollback is always safe.

More hard Full Stack Developer questions

• You are building a multi-tenant B2B SaaS app. How would you design data isolation between tenants, and what are the trade-offs?
• Your app works well for 1,000 users. Traffic is expected to grow to 1 million users. How would you prepare the system, and in what order?
• An order flow touches your database, a payment provider and an email service. How do you keep the system consistent when any step can fail?
• For a large e-commerce site in Next.js, how would you choose the rendering and caching strategy for the home page, product pages, cart and account pages?
• Design an e-commerce checkout end to end: cart, payment with a provider like Stripe, inventory and order confirmation. What can go wrong and how do you make it reliable?
• Your startup’s Next.js monolith is growing and some engineers want to split it into microservices. How do you decide, and what would you do?
• Ten minutes after a deploy, 500 errors jump from 0.1% to 8% on the main API. Walk through how you handle the incident.
• Customers need to import CSV files with up to two million rows into your app. Design the feature end to end.
• You are opening your product’s API to outside partners. How do you design authentication, authorization, rate limiting and change management for it?