On 18th of January, Stephen Frost committed patch:

Add ALTER TABLESPACE ... MOVE command
 
This adds a 'MOVE' sub-command to ALTER TABLESPACE which allows moving sets of
objects from one tablespace to another.  This can be extremely handy and avoids
a lot of error-prone scripting.  ALTER TABLESPACE ... MOVE will only move
objects the user owns, will notify the user if no objects were found, and can
be used to move ALL objects or specific types of objects (TABLES, INDEXES, or
MATERIALIZED VIEWS).

He followed it, with another commit:

Add CREATE TABLESPACE ... WITH ... Options
 
Tablespaces have a few options which can be set on them to give PG hints
as to how the tablespace behaves (perhaps it's faster for sequential
scans, or better able to handle random access, etc).  These options were
only available through the ALTER TABLESPACE command.
 
This adds the ability to set these options at CREATE TABLESPACE time,
removing the need to do both a CREATE TABLESPACE and ALTER TABLESPACE to
get the correct options set on the tablespace.
 
Vik Fearing, reviewed by Michael Paquier.

First, if you're not familiar with tablespaces.

Normally all PostgreSQL data (and by default config files, though this has been modified in debian/ubuntu and possibly also other distributions) resides in single directory. For example /var/lib/postgresql/9.3/main.

But not all storage is created equal. You might have some additional SSD disks, or quite the contrary – some slow, but very large disks. And you'd want to put some of the data to another disk set.

This is what tablespaces are.

Default tablespace is simply $PGDATA/base directory. But you can have many other, created with:

CREATE tablespace xxx location '/wherver';

command. Afterwards you can move some tables/indexes to this new tablespace with:

ALTER TABLE/INDEX whatever SET tablespace xxx;

This is locking operation, but relatively fast (it's a copy on file level, not on database level).

Now, with these new patches, we get ability to:

• move all tables/indexes/materialized views from one tablespace to another
• create tablespace with some “things" set

The first thing – moving – it's just a nice thing to have, to run single command that will move everything you wanted to new location.

Example usage:

ALTER TABLESPACE OLD MOVE TABLES TO NEW;

That would move all tables (but not indexes or materialized views) from old tablespace to new.

Of course – it will lock all of the tables (that you're moving) for the whole duration of the move. So it's rather unpleasant (locking many tables for so long), but has the benefit that it's single command that can either do its job or not (i.e. either all tables are moved or none).

Now, what about creating tablespace with “things" set.

Help is pretty vague:

$ \h CREATE tablespace 
Command:     CREATE TABLESPACE
Description: define a NEW tablespace
Syntax:
CREATE TABLESPACE tablespace_name
    [ OWNER user_name ]
    LOCATION 'directory'
    [ WITH ( tablespace_option = VALUE [, ... ] ) ]

What are those “tablespace_option"s ?

In the full docs we can find that currently there are only 2 parameters that can be set:

• seq_page_cost
• random_page_cost

But this is generally what makes sense. New tablespace is not in another system. It doesn't have more usable cache (well, at least normally), and it doesn't influence how much ram pg can use for queries. What it can do is lower (or increase) access costs.

For example – when using very fast SSD drives – you'd generally lower both of these settings. On the other hand – adding 4TB, 5900rpm drive, would make sense in certain cases, but then you'd most likely want to increase, at least, random_page_cost.

All things said – it's a pretty cool add-on. And given the situation with cloud computing, where adding new storage to machine is a matter of couple of clicks – it makes a lot of sense to have this kind of configurability. Cool stuff, thanks guys.