Over a period of time, a table or database may grow to be huge in size. The disk that contains the data directory may become saturated with IOPS. For this reason, we want to scatter selected tables or indexes across different tablespaces on another disk. While this can be done using ALTER TABLE, it is not an online operation. So, we can use pg_repack to move a table or an index online to another tablespace. In this recipe, we will discuss how this can be done in simple steps.
Getting ready
To move a table or an index to another tablespace online using pg_repack, we need the pg_repack extension created in the database that contains the table or the index. A table cannot be moved online if the table does not have a primary key or a not-null unique key. The target tablespace must be created in advance. pg_repack will not create the tablespace.
Create the target tablespace manually, if it does not exist already:
$ sudo mkdir -p /tblspc_dir...
