Database Administration - PostgreSQL 9 Administration - page 169

Indexes can exist in separate tablespaces, and moving a table leaves the indexes where they
are. Don't forget to run ALTER INDEX commands as well, one for each index, as follows:
ALTER INDEX mytable_val_idx SET TABLESPACE new_tablespace;
Temporary objects cannot be explicitly moved, so we take that to mean you want to "ensure
they are created somewhere else in the future". To do that you need to:
F Edit temp_tablespaces
F Reload the server to allow new configuration settings to take effect
There is no single command to do this that will work for all users.
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 data. Copying happens sequentially block-by-block,
and that performs 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 (AccessExclusiveLock) while the copy takes
place, so this can cause an effective outage for your application. Be very careful.
If you want to ensure that objects are created in the right place next time you create one, then
you can use the following:
SET default_tablespace = 'new_tablespace';
You can run this automatically for all users that connect to a database using
the following query:
ALTER DATABASE mydb SET default_tablespace = 'new_tablespace';
Take care that you do not run the next command by mistake though:
ALTER DATABASE mydb SET TABLESPACE new_tablespace;