Which schema changes can block reads or writes on a large, busy table, and what techniques keep the lock window short enough to be invisible to users?

## Two different questions "Is this DDL blocking?" decomposes into: **how strong is the lock**, and **how long is it held**. A statement can be dangerous for either reason, and the mitigations differ. ## Lock acquisition is the hidden hazard Even a purely metadata change needs an exclusive lock on the table. To get it, the statement must wait for every conflicting transaction to finish — including an analytics query that has been running for ten minutes, or an idle-in-transaction session that touched the table and never committed. Meanwhile, in engines with a fair lock queue, incoming queries queue *behind* the waiting DDL. The result is the classic incident: an ALTER that takes 3 ms takes down the service for ten minutes, because it sat in the queue and everything piled up behind it. The standard defence is to make waiting cheap: set a low `lock_timeout` (or the engine equivalent) so the DDL either grabs the lock almost immediately or fails, and wrap it in a retry loop with backoff. Failing fifty times costs nothing; waiting once costs an outage. Before running, check for long transactions and either wait them out or terminate them. ## Operations that rewrite or scan the whole table These hold their lock for a size-proportional duration: - **Changing a column type** in a way that changes storage (int → bigint, text → numeric). Usually a full rewrite, doubling the table's space during the operation. - **Adding a column with a non-constant default** — a function evaluated per row forces a rewrite. Adding a column with a *constant* default is metadata-only on current major engine versions, but was a rewrite on older ones. - **Setting NOT NULL** on an existing column: requires proving no NULLs exist, which is a full scan under a strong lock unless the engine can lean on an already-validated check constraint. - **Adding a CHECK or FOREIGN KEY constraint**: validating existing rows means scanning the table (and, for a foreign key, locking the referenced table too). - **Building an index** the ordinary way: reads the whole table and blocks writes for the duration. - **Reordering, clustering or repacking** a table. ## Techniques that keep the window short **Concurrent / online index builds.** All major engines offer a form that allows reads and writes while the index is built, at the cost of more total work, more than one pass, and — in some engines — the possibility of leaving an invalid index behind if it fails, which must be dropped and retried. Always the right default on a large table. **Add-then-validate for constraints.** Add the constraint in a not-yet-validated state (a fast metadata operation that enforces the rule for *new* rows), then validate the existing rows in a separate statement that takes a weaker lock and allows concurrent writes. Two steps, no long exclusive lock. **Avoid ALTER TYPE entirely.** Instead of changing a column's type in place, add a new column of the target type, dual write, backfill in batches, switch reads, drop the old one — the expand-contract sequence. It is more steps but every step is short. **Batch the data work.** Anything touching many rows belongs in bounded batches with commits between them, so no single transaction holds locks or accumulates undo/version data for long. **Online schema-change tools.** Where the engine's own facilities are insufficient, tools that build a shadow copy of the table, keep it in sync with triggers or by tailing the replication stream, and swap it in with a brief rename are standard practice. They trade extra disk and load for a lock window measured in milliseconds. **Take the small stuff seriously.** Wrap DDL in an explicit transaction where the engine supports transactional DDL, so a failure leaves nothing half-applied; run one change per migration so a retry is cheap; and never batch a fast metadata change together with a long-running one in the same transaction, because the transaction holds the strongest lock for the longest duration among them. ## Replication and follower impact A rewrite generates a large volume of write-ahead log or binlog, which floods replication and can push follower lag high enough to break read-your-writes routing or delay failover. On engines that replay DDL serially on the replica, a long DDL blocks *all* replay, so replicas fall behind for the whole duration even though the primary is fine. Throttling on measured lag is part of the plan, not a nicety. ## Rehearsal and observation Measure on a production-sized copy, not on a development database with a thousand rows: rewrite duration is proportional to size, so a change that takes 200 ms locally can take forty minutes live. During the run, watch lock waits, active query counts, error rates and replication lag, and have an abort path — for a batched job that means stopping the loop; for a single DDL it means cancelling and letting the engine roll back, which for a rewrite may itself take time. ## Vendor caveat The specific catalogue of what is metadata-only differs by engine and version, so the professional answer names the class of hazard and then says "and I check my engine's documented behaviour for this version" rather than asserting a universal table of safe operations.