PostgreSQL - How to migrate table and index to new tablespace

Colleagues hello to all.

It happens, it happens when you need to move a table or index to another tablespace, this can happen for several reasons.

The first reason is when you run out of disk space on a partition in which case you can create a separate tablespace on another partition and move the table there. The second reason may be that you have created a new tablespace for the archived data and you just want to transfer the old data along with the table there.

Moving tables or indexes to another tablespace means a simple (block) copy of the data to a new location. The larger the table, the longer the move will take. The most important thing to remember when moving tables or indexes is that the table is completely locked (ACCESSEXCLUSIVELOCK) during the move.

SQL command to move a table.

sql> ALTER TABLE t_users SET TABLESPACE ts_statistic;

SQL index move command.

sql> ALTER INDEX i_users SET TABLESPACE ts_statistic;

Thank you all, I hope that my article helped you in some way.