PostgreSQL 10 Administration Cookbook - Fourth Edition

How it works…

If you want to move a table and its indexes all in one pass, you can issue all the commands in a single transaction, as follows:

BEGIN;ALTER TABLE mytable SET TABLESPACE new_tablespace;ALTER INDEX mytable_val1_idx SET TABLESPACE new_tablespace;ALTER INDEX mytable_val2_idx SET TABLESPACE new_tablespace;COMMIT;

Moving tablespaces means bulk copying of data. Copying happens sequentially, block by block. That works well, but there's no way to avoid the fact that the bigger the table, the longer it will take.

Performance will be optimized if archiving or streaming replication is not active, as no WAL will be written in that case.

You should be aware that the table is fully locked (the AccessExclusiveLock lock) while the copy is taking ...