Database Administration - PostgreSQL 9 Administration - page 168

You may also wish to set default_tablespaces for a user, so that tables are automatically
created there by issuing the following query:
ALTER USER eliza SET default_tablespace = 'new_tablespace';
Putting pg_xlog on a separate device
You may seek advice about putting the pg_xlog directory onto a separate device for
performance reasons. This sounds very similar to tablespaces, though there is no explicit
command to do this once you have a running database. Please look for the recipe in the
Performance chapter.
Tablespace-level tuning
As each tablespace has different I/O characteristics, we may wish to alter the planner cost
parameters for each tablespace. These can be set with the following command:
ALTER TABLESPACE new_tablespace SET
(seq_page_cost = 0.05, random_page_cost = 0.1);
Settings are roughly appropriate for an SSD drive, which assumes that the drive is faster than
an HDD by x10 for random reads, and x20 for sequential reads.
The values given need more discussion than we have time for here.
Moving objects between tablespaces
Moving data around between tablespaces may sometimes be required.
Getting ready
First, create your tablespaces. Once the old and new tablespaces exist, we can issue the
commands to move them.
How to do it...
Tablespaces can contain both permanent and temporary objects.
Permanent data objects are tables, indexes, and toast objects. We don't need to worry too
much about toast objects, because they are created and always live in the same tablespace
as their main table. So if you alter the tablespace of a table, it's toast objects will move also.
ALTER TABLE mytable SET TABLESPACE new_tablespace;