Two main use case [1]
- First, if the partition or volume on which the cluster was initialized runs out of space and cannot be extended, a tablespace can be created on a different partition and used until the system can be reconfigured.
- Second, tablespaces allow an administrator to use knowledge of the usage pattern of database objects to optimize performance. For example, an index which is very heavily used can be placed on a very fast, highly available disk, such as an expensive solid state device. At the same time a table storing archived data which is rarely used or not performance critical could be stored on a less expensive, slower disk system.
To Create a Tablespace
CREATE TABLESPACE fastspace LOCATION '/mnt/sda1/postgresql/data';
To Change the Tablespace of an Existing Table and Index
The following psql command will create a list of alternate table command to change the table's tablespace [2]-- tabelspace is null - find table that doesn't have any tablespace set
select 'alter table '||schemaname||'.'||tablename||' set tablespace fastspace;' from pg_tables where schemaname='myschema' and tablespace is null;
select 'alter index '||schemaname||'.'||indexname||' set tablespace fastspace;' from pg_indexes where schemaname='myschema' and tablespace is null;
It will return something similar to this
?column?
-----------------------------------------------------------------------
alter table myschema.table1 set tablespace fastspace;
alter table myschema.table2 set tablespace fastspace;
alter table myschema.table3 set tablespace fastspace;
(3 rows)
?column?Then just run each generated command to change/set the tablespace.
-----------------------------------------------------------------------
alter index myschema.index1 set tablespace fastspace;
alter index myschema.index2 set tablespace fastspace;
alter index myschema.index2 set tablespace fastspace;
(3 rows)
Reference:
1. http://www.postgresql.org/docs/8.1/static/manage-ag-tablespaces.html
2. http://www.postgresql.org/message-id/01c001c9335f$06e991d0$274610ac@praveenkm
