warn-set-tablespace-rewrite

What It Detects

SET TABLESPACE copies the entire relation to new storage under ACCESS EXCLUSIVE, blocking all access for the duration.

Why It's Dangerous

This is a physical file copy, not a catalog update. PostgreSQL reads every file belonging to the relation and writes it to the new location while holding ACCESS EXCLUSIVE from the first byte to the last, so the table is unavailable for reads and writes the whole time — hours rather than minutes on a large table over ordinary disks. The old files are not removed until the move commits, so both copies exist simultaneously and the destination needs the relation's full size free.

Bad Example

ALTER TABLE users SET TABLESPACE fast_ssd;
-- users is offline until every file has been copied

Good Example

-- For an index, rebuilding on the target tablespace keeps the old one
-- serving queries until the new one is ready.
CREATE INDEX CONCURRENTLY idx_users_email_new
  ON users (email) TABLESPACE fast_ssd;
DROP INDEX CONCURRENTLY idx_users_email;
ALTER INDEX idx_users_email_new RENAME TO idx_users_email;

Configuration

Disable this rule:

# .migrationpilotrc.yml
rules:
  MP095: false

Or change its severity:

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