Does SET TABLESPACE lock the table while it copies?
SET TABLESPACE copying the table under full lock
Warning: this works, but blocks traffic or rewrites data at scale.
What happens
Moving a table to another tablespace physically copies every block while holding ACCESS EXCLUSIVE. Copy time scales with table size; the table is unavailable throughout.
Why it is dangerous on a populated table
It completes, but the time it holds its lock or rewrites rows scales with the table, and on a busy one that is long enough for traffic to queue.
Fires on
ALTER TABLE orders SET TABLESPACE fast_ssd;The safe pattern
Move the table online with pg_repack --tablespace, which rebuilds it in the target tablespace without holding ACCESS EXCLUSIVE for the copy. Otherwise schedule the move in a maintenance window with a lock_timeout guard, the copy itself cannot be made non-blocking with plain SQL. Indexes can move separately (ALTER INDEX ... SET TABLESPACE) to shrink each outage.
-- online, no migration needed:
-- pg_repack --table=orders --tablespace=fast_ssd
-- or accept the copy in a maintenance window, guarded:
-- SET lock_timeout = '5s';
-- ALTER TABLE orders SET TABLESPACE fast_ssd;Fixtures
The rule ships with these files and the test suite runs them on every change: the first set must fire, the second must stay silent.
Fires (1)
ALTER TABLE orders SET TABLESPACE fast_ssd;Stays silent (1)
CREATE TABLE archive (id bigint PRIMARY KEY) TABLESPACE slow_hdd;How to check locally
This rule runs in the hosted service on Startup and above: add --remote with a team token, or use the GitHub Action. No install, nothing leaves your machine:
npx bolvrk check migration.sql --remote