Database XP / Change

Large tables need a different approach

An operation that is instant on a thousand rows locks a hundred million.

Adding a column, creating an index, or changing a type behaves entirely differently at scale, and the difference is not gradual. An operation that took a moment in testing can hold a lock for hours on a production table, which is discovered during a deployment window with everybody watching.

The behaviours differ by database and by version, and the specific rules are worth knowing for whatever you actually run rather than assumed from general knowledge. Some operations are cheap where they were once expensive, and the folklore lags the software by years.

The general safe patterns are consistent: add rather than modify, backfill in batches rather than in a single statement, create indexes without blocking where the database supports it, and split a type change into add, dual-write, backfill, switch, drop.

Table size should be known before the change is written, not discovered during it. A row count is a moment's work and it determines whether the migration is routine or requires a plan.

The other large-table consideration is what the application does while the migration runs. A change that is safe from the database's perspective can still break an application that expects the old shape, which is why the add, dual-write, backfill, switch sequence exists. Deploying schema and code together removes the option of rolling back one without the other.