warn-collation-change-rewrite

What It Detects

Changing a column COLLATE reorders the column, forcing a table rewrite and a rebuild of every index on it.

Why It's Dangerous

A collation is the definition of sort order, so changing it changes where every value in the column belongs. Every btree index on the column is rebuilt inside the same ACCESS EXCLUSIVE lock as the table rewrite, not as separate work you can schedule or run concurrently. Comparisons also answer differently afterwards: ORDER BY returns a different sequence, and a unique index under a collation that treats more strings as equal can start rejecting inserts that used to succeed.

Bad Example

ALTER TABLE users ALTER COLUMN name TYPE TEXT COLLATE "en_US";
-- Table rewritten, every index on name rebuilt, all under ACCESS EXCLUSIVE

Good Example

-- Expand-contract keeps the index builds online.
ALTER TABLE users ADD COLUMN name_new TEXT COLLATE "en_US";
-- backfill in batches...
CREATE INDEX CONCURRENTLY idx_users_name_new ON users (name_new);
-- swap the columns in a short transaction once the data is in place

Configuration

Disable this rule:

# .migrationpilotrc.yml
rules:
  MP089: false

Or change its severity:

# .migrationpilotrc.yml
rules:
  MP089:
    severity: warning