How can you optimize zero-downtime schema migrations for a table already receiving production traffic?

The general strategy is expand-and-contract: make additive, backward-compatible changes first, deploy application code that can handle both the old and new shape simultaneously, then remove the old shape only once nothing depends on it anymore — rather than changing the schema and application code in one atomic, simultaneous step.

Concretely, adding a new column should be done as nullable (or with a fast, non-blocking default) rather than NOT NULL immediately, since a NOT NULL constraint with a non-null default historically forced a full table rewrite; adding a column and backfilling it in small batches afterward avoids a long-held lock on a large table. Renaming a column is safer done as add-new-column, dual-write from the application, backfill, then drop-old-column, rather than a single ALTER TABLE ... RENAME that breaks any code still referencing the old name mid-deploy.

-- Step 1: additive, non-blocking
alter table orders add column status_v2 text;

-- Step 2: app dual-writes both columns, backfill in batches
update orders set status_v2 = status where status_v2 is null limit 1000;

-- Step 3: once fully migrated and app only reads status_v2
alter table orders drop column status;

Indexes deserve the same care: creating one with CREATE INDEX CONCURRENTLY avoids holding an exclusive lock for the index build's duration, at the cost of it taking longer and needing a retry if it's interrupted. The unifying principle is minimizing the time any single migration statement holds a lock that blocks concurrent reads or writes on a live table.