After a few months or years, you see the data in your database growing. And when the number of transactions increases along with the volume of data, you will wish to distribute objects, especially tables and indexes, across multiple tablespaces to scatter the IOPS. In this recipe, we shall see the steps involved in moving existing tables and indexes to different tablespaces.
Getting ready
To move tables and indexes to a different tablespace, we could use the ALTER TABLE syntax. We should be a superuser or an owner of the schema to run ALTER TABLE.
Running the ALTER TABLE command could cause downtime for the application as it acquires an exclusive lock on the table while moving it to a different tablespace. So the downtime needs to be planned appropriately before proceeding further. Additionally, we could use extensions such as pg_repack, which could be used to move tables and indexes to different tablespaces online. pg_repack will be discussed in future chapters.
How to do it...
The following steps can be performed to move a table to a different tablespace:
- To move a table to another tablespace, the syntax looks like the following:
ALTER TABLE percona.foo SET TABLESPACE newtblspc;
- To move an index to a new tablespace, the syntax looks like the following:
ALTER INDEX percona.foo_id_idx SET TABLESPACE newtblspc;
How it works
In order to move a table from one tablespace to another, we could simply use the ALTER TABLE command as seen in the following syntax:
ALTER TABLE <schemaname.tablename> SET TABLESPACE <tablespace_name>;
As an example, we could see the command that can be used to move a table, percona.foo, to a tablespace named newtblspc in step 1.
Similarly, in order to move an index to a tablespace, the syntax appears like the following:
ALTER INDEX <schemaname.indexname> SET TABLESPACE <tablespace_name>;
An example command to move the index percona.foo_id_idx to tablespace newtblspc can be seen in step 2.
