Recipe - How to add/remove tablespaces

This tutorial describes the best practice on adding and removing tablespaces, and how to figure out which objects a tablespace contains.

Tablespaces allow us to store PostgreSQL data across different devices. We might want to do that for performance or administrative ease, or our database might just have run out of disk space.

Before we can create a useful tablespace, we need the underlying devices in a production-ready form.

Think carefully about the speed, volume, and robustness of the disks you are about to use. Make sure that they are configured correctly. Those decisions will affect your life for the next few months and years!

Disk performance is a subtle issue that most people think can be decided in a few seconds. We recommend reading Chapter 10, Performance and Concurrency, from this book, as well as additional books on the same topic.

Once you've done all of that, then you can create a directory for your tablespace. The directory must be:

• Empty
• Owned by the PostgreSQL-owning user ID
• Specified with an absolute path name

On Linux and Unix systems, you shouldn't use a mount point directly. Create a subdirectory and use that instead. That simplifies ownership and avoids some filesystem-specific issues, such as getting lost+found directories.

The directory also needs to follow sensible naming conventions so that we can clearly identify which tablespace goes with which server. Do not be tempted to use something simple, such as data, because it will make later administration more difficult. Be especially careful that test or development servers do not and cannot get confused with production systems.

Once you've created your directory, adding the tablespace is simple:

CREATE TABLESPACE new_tablespace
LOCATION '/usr/local/pgsql/new_tablespace';

The command to remove the tablespace is also simple and is as follows:

DROP TABLESPACE new_tablespace;

Every tablespace has a location assigned to it, with the exception of the pg_global and pg_default default tablespaces, for shared system catalogs and all other objects, respectively. They don't have a location because they live in a subdirectory of the data directory.

A tablespace can be dropped only when it is empty, so how do you know when a tablespace is empty?

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, and you cannot manipulate their privileges or ownership.

Indexes can exist in separate tablespaces as a performance option, though that requires explicit specification in the CREATE INDEX statement. The default is to create indexes in the same tablespace as the table to which they belong.

Temporary objects may also exist in a tablespace. These exist when users have explicitly created temporary tables or there may be implicitly created data files when large queries overflow their work_mem settings. These files are created according to the setting of the temp_tablespaces parameter. That might cause an issue because you can't tell for certain what the setting of temp_tablespaces is for each user. Users can change their setting of temp_tablespaces from the default value specified in the postgresql.conf file to something else.

We can identify the tablespace of each user object using the following query:

SELECT spcname
,relname
,CASE WHEN relpersistence = 't' THEN 'temp ' ELSE '' END ||
CASE
WHEN relkind = 'r' THEN 'table'
WHEN relkind = 'f' THEN 'foreign table'
WHEN relkind = 't' THEN 'TOAST table'
WHEN relkind = 'v' THEN 'view'
WHEN relkind = 'm' THEN 'materialized view'
WHEN relkind = 'S' THEN 'sequence'
WHEN relkind = 'c' THEN 'type'
ELSE 'index' END as objtype
FROM pg_class c join pg_tablespace ts
ON (CASE WHEN c.reltablespace = 0 THEN
(SELECT dattablespace FROM pg_database
WHERE datname = current_database())
ELSE c.reltablespace END) = ts.oid
WHERE relname NOT LIKE 'pg_toast%'
AND relnamespace NOT IN (SELECT oid FROM pg_namespace WHERE nspname
IN ('pg_catalog', 'information_schema'))
;

This displays an output like the following:

spcname | relname | objtype
new_tablespace | x | table
new_tablespace | y | table
new_tablespace | z | temp table
new_tablespace | y_val_idx | index

You may also want to look at the spcowner, relowner, relacl, and spcacl columns to determine who owns what and what they're allowed to do. The relacl and spcacl columns refer to the access control list that details the privileges available on those objects. The spcowner and relowner columns record the owners of the tablespace and tables/indexes, respectively.

A tablespace is just a directory where we store PostgreSQL data files. We use symbolic links from the data directory to the tablespace.

We exclude TOAST tables because they are always in the same tablespace as their parent tables, but remember that TOAST tables are always in a separate schema. You can exclude TOAST tables using the relkind column, but that would still include the indexes on the TOAST tables. TOAST tables and TOAST indexes both start with pg_toast, so we can exclude those easily from our queries.

The preceding query needs to be complex because pg_class entry for an object will show reltablespace = 0 when an object is created in the database's default tablespace. So, if you directly join pg_class and pg_tablespace, you end up losing rows.

Note that we can see that a temporary object exists, and we can see the tablespace in which it is created, even though we cannot refer to a temporary object in another user's session.

Some more notes on best practices follow.

A tablespace can contain objects from multiple databases, so it's possible to be in a position where there are no objects visible in the current database. The tablespace just refuses to go away, giving the following error:

ERROR: tablespace "old_tablespace" is not empty

You are strongly advised to make a separate tablespace for each database to avoid confusion. This can be especially confusing if you have the same schema names and table names in the separate databases.

How to avoid this? If you just created a new tablespace directory, you might want to create subdirectories within that for each database that needs space, and then change the subdirectories to tablespaces instead.

You may also wish to consider giving each tablespace a specific owner, using the following query:

ALTER TABLESPACE new_tablespace OWNER TO eliza;

This may help smooth administration.

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';