PostgreSQL Table Spaces

A powerful feature of PostgreSQL is the ability to create table spaces and to move database objects between them. Each table space represents a physical storage location; so using table spaces you can tier data onto different grades of storage [SSD vs. fast disk vs. slow disk] as well as move data from storage being retired [like an iSCSI SAN] to a new storage [a Fiber-Channel SAN!].

The first step to creating a table space is to create and mount the underlying storage. For this example I create a new logical volume in the volume group FAST [an array of SSD drives].

# lvcreate /dev/FAST --name PGSQL1 --size 15G
Logical volume "PGSQL1" created
# mkfs -t xfs /dev/FAST/PGSQL1
# mkdir /var/lib/pgsql1
# chown postgres.postgres /var/lib/pgsql1
# mount /var/lib/pgsql1
# chown postgres.postgres /var/lib/pgsql1

Once the storage is mounted, and the postgresql user has the correct access [Important!], informing the server of the new table space is simple.

CREATE TABLESPACE FastSpace1 LOCATION '/var/lib/pgsql1';

Now in addition to the pg_default table space (whose location is provided to the server at start time - usually /var/lib/pgsql) I have a table space "FastSpace1". I can move objects such as tables and indexes from their current table space to the new one.

ALTER TABLE obj_property SET TABLESPACE FastSpace1;
ALTER TABLE lock SET TABLESPACE FastSpace1;
ALTER INDEX doc_pk SET TABLESPACE FastSpace1;

The important detail is that you can move tables and indexes independently. Especially for rotating storage this can provide a significant performance improvement by separating the index [which needs to be updated along with its table] and it's table onto different spindles. Doing so spreads the write load during updates and inserts.

As far as the application is concerned nothing changes - things just go faster. Keep your big static tables on inexpensive 'slow' storage and spread your hot-spots around.