warn-set-tablespace-rewrite

What triggers it

The AT_SetTableSpace subcommand of ALTER TABLE ... SET TABLESPACE or ALTER INDEX ... SET TABLESPACE — both parse as AlterTableStmt, distinguished by objtype.

What does not

Non-AlterTableStmt statements, and ALTER TABLE/ALTER INDEX statements whose commands don't include an AT_SetTableSpace subtype.

Where it applies

Applies to every PostgreSQL version MigrationPilot targets. It works on the SQL text alone — no database connection needed.

The lock, and what it blocks

ACCESS EXCLUSIVE, held from the first byte copied to the last — the relation is unavailable for reads and writes for the entire copy, and duration scales with size and disk throughput rather than anything the migration can bound.

Why it matters

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.

Unsafe, and safe

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

-- 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;

What it assumes

Assumes the relation is large enough for the copy to matter. On a small table or index the same statement finishes in milliseconds, and without --database-url the rule has no way to check size, so it flags a tablespace move on an empty table the same as one on a 500 GB table.

What the CLI prints

⚠ [MP095] WARNING (line 1)
  Table "users" is being moved to tablespace "fast_ssd". This copies every file under ACCESS EXCLUSIVE. "users" is unavailable for reads and writes until the copy finishes, and both copies occupy disk until it commits.

  Safe alternative:
  -- Move storage outside the migration, during a planned window, with a
  -- bounded lock wait so it fails fast instead of queueing behind traffic:
  SET lock_timeout = '5s';
  ALTER TABLE users SET TABLESPACE fast_ssd;
  RESET lock_timeout;
  
  -- For an index, rebuilding on the target tablespace keeps the old one
  -- serving queries until the new index is ready:
  -- CREATE INDEX CONCURRENTLY users_new ON <table> (<columns>) TABLESPACE fast_ssd;
  -- DROP INDEX CONCURRENTLY users;
  
  -- Check free space on the destination before starting: both copies coexist.

  Why: This is a physical file copy, not a catalog update. PostgreSQL holds ACCESS EXCLUSIVE from the first byte to the last, so the table is unavailable for reads and writes for as long as the copy takes: on a 500 GB table over ordinary disks, hours. Both copies exist until the move commits, so the destination needs the full size free and the source cannot be reclaimed early. A migration is the wrong place for it: there is no way to pause, resume, or bound the work, and cancelling midway rolls the whole copy back and leaves you where you started.
  Docs: https://migrationpilot.dev/rules/mp095

Generated by running the CLI's own formatter over the flagged example above, so it is the text the tool actually produces. A real run also reports the other rules that fire on the same statement; those blocks are left out here.

Turning it off

For one statement, put a comment on the line before it:

-- migrationpilot-disable MP095
ALTER TABLE users SET TABLESPACE fast_ssd;

For the whole project, in .migrationpilotrc.yml — by name or by id:

rules:
  MP095: false

# or keep it, and downgrade it
rules:
  MP095:
    severity: warning

Try it

Open this rule's flagged example in the playground. It runs in your browser — edit it and watch the finding appear and disappear.

Related rules

• MP006no-vacuum-fullanother full-relation rewrite under ACCESS EXCLUSIVE
• MP032ban-clusterCLUSTER rewrites the table the same way
• MP047ban-set-logged-unloggedSET LOGGED/UNLOGGED also rewrites
• MP004require-lock-timeoutbounding the wait on any blocking DDL