Chapter 5 part 6 - Tablespaces

Tablespaces

A tablespace is a logical shortcut for a physical location. This feature was first introduced with the major release 8.0, recently with the 9.2 had a small adjustment to make the dba life easier. When a new relation is created without tablespace specification, the relation's tablespace is set to the GUC prameter default_tablespace or, if this is missing, is set to the database's default tablespace. Anyway, without any specification the default tablespace is the pg_default, corresponding to the $PGDATA/base directory.
In order to create a new tablespace the chosen directory must be owned by the os user which started the postgres process and must be specified as absolute path.
For example, having a folder named /var/lib/postgresql/pg_tbs/ts_test a tablespace we can create a new tablespace ts_test.

CREATE TABLESPACE ts_test 
OWNER postgres
LOCATION '/var/lib/postgresql/pg_tbs/ts_test' ;

Only superusers can create tablespaces. The OWNER clause is optional, if omitted the tablespace is owned by the user issuing the command.
The tablespaces are cluster wide, each database sees the same list in the pg_tablespace system table.
To create a relation into the tablespace ts_test just add the TABLESPACE clause followed by the tablespace name at creation time.

CREATE TABLE t_ts_test
        (
                i_id serial,
                v_value text
        )
TABLESPACE ts_test ;

It's possible to move a relation from a tablespace to another using the ALTER command.
For example, this is the command to move the previously created table to the pg_default tablespace.

ALTER TABLE t_ts_test SET TABLESPACE pg_default;

The move is transaction safe but requires an exclusive lock on the affected relation. If the relation have a significant size this means no access to the data for the time required by the move.
In addition, changing the tablespaces is not permitted when the backup is in progress, the exclusive lock is not compatible with the locks issued by the schema and data export.
The tablespace feature adds flexibility to the space management. Even if is still primitive a careful design can improve sensibly the performances, for example, putting tables and indices on different devices to maximise the disks bandwidth.
To remove a tablespace there is the DROP TABLESPACE command. The tablespace must be empty before the drop. There's no CASCADE clause to have the tablespace's contents dropped with the tablespace.

postgres=# DROP TABLESPACE ts_test;
ERROR:  tablespace "ts_test" is not empty

postgres=# ALTER TABLE t_ts_test SET TABLESPACE pg_default;
ALTER TABLE
postgres=# DROP TABLESPACE ts_test;
DROP TABLESPACE

In 6.4 we'll take a look to the how PostgreSQL implements the tablespaces on the physical side.