This document is an opensql technical document based on postgresql.
 

Overview

Administrators can use table spaces to control the disk layout of a PostgreSQL installation.

• If there is not enough space on the partition or volume where the cluster was initialized and expansion is not possible, you can create a tablespace on another partition to use until the system is reconfigured.
• Through table spaces, administrators can place high-performance disks and apply them to heavily used tables or indexes to improve performance, and store rarely used data on lower-performance disks.

This document provides instructions on how to use table spaces in OpenSQL. 

Method

Installation

To define a tablespace, use the CREATE TABLESPACE command.

CREATE TABLESPACE tablespace_name
    [ OWNER {new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER } ]
    LOCATION 'directory'
    [ WITH (tablespace_option = value [, ... ] ) ]
    
CREATE TABLESPACE fastspace LOCATION '/ssd1/postgresql/data';

Parameters

*tablespace_name*

• The name of the tablespace to be created.

• The name cannot start with pg_ (reserved for system tablespaces).

   

*user_name*

• The name of the role that will own the tablespace.
• If omitted, the default is the role executing the command.
• Only superusers can create tablespaces.

• Ownership of the tablespace can be assigned to a non-superuser role.

   

*directory*

• The directory to be used for the tablespace.
• The CREATE TABLESPACE command does not create the directory.
• The directory must be empty and owned by the PostgreSQL system user.

• It must be specified as an absolute path.

   

*tablespace_option*

• Currently available parameters are ‘seq_page_cost’, ‘random_page_cost’, ‘effective_io_concurrency’, and ‘maintenance_io_concurrency’.
• Setting these values overrides the planner's general cost estimates for reading pages from the table.
• The executor's prefetch behavior is determined by the value set for the parameter.

Creation Example 

Single

Below is a creation example.

1. To create a tablespace dbspace at the file system path /data/dbs,
   first create the directory and set ownership using operating system features.

mkdir /data/dbs
chown postgres:postgres /data/dbs

2. Then, execute the tablespace creation command within PostgreSQL.

CREATE TABLESPACE dbspace LOCATION '/data/dbs';

To create a tablespace owned by another database role, use the following command.

CREATE TABLESPACE indexspace OWNER genevieve LOCATION '/data/indexes';

• LOCATION must be an existing empty directory owned by the OS user running PostgreSQL.
• All objects created later within the tablespace will be stored in files under that directory.
• Once a directory is assigned to a tablespace, it can use disk space without limitation.
• The tablespace itself must be created by a database superuser, but after that, CREATE privileges can be granted to allow use by regular users.

Streaming Replication

Below is a creation example for Streaming Replication.

In a Streaming Replication environment, both Active and Standby environments must have disk allocation (directory structure) and permissions granted to the OS user running PostgreSQL, as above, and the CREATE TABLESPACE can be executed on the Active server.

1. To create a tablespace dbspace at the file system path /data/dbs, first create the directory and set ownership using operating system features. This must be done on both servers.

# On the Active server

mkdir /data/dbs
chown postgres:postgres /data/dbs

# On the Standby server

mkdir /data/dbs
chown/dbs

2. Then, execute the tablespace creation command on the Active server.

CREATE TABLESPACE dbspace LOCATION '/data/dbs';

3. Check that the tablespace was created successfully.
   You can check the tablespace list and capacity with the \db+ command.

postgres=# \\db+
                               Tablespace List
    Name    |  Owner   |        Location        | Access Privileges | Options |  Size  
 | Description 
------------+----------+-----------------------+-------------------+---------+--------
-+------
 pg_default | postgres |                       |                   |         | 485 MB 
 | 
 pg_global  | postgres |                       |                   |         | 560 kB 
 | 
 dbspace    | postgres | /data/dbs             |                   |         | 0 bytes
 | 
(3 rows)

You can assign tables, indexes, and entire databases to a specific tablespace as shown below.

CREATE TABLE foo(i int) TABLESPACE dbspace;
CREATE DATABASE test1 TABLESPACE dbspace;

Or use the default_tablespace parameter.

SET default_tablespace = space1;
CREATE TABLE foo(i int);

Check the total capacity of the tablespace. (This can also be checked with the \db+ command above)

-- psql -c "select spcname, pg_size_pretty(pg_tablespace_size(spcname)) from pg_tablespace;"

[postgres@localhost:~]$ psql -c "select spcname, pg_size_pretty(pg_tablespace_size(spcname)) from pg_tablespace;"
  spcname   | pg_size_pretty 
------------+----------------
 pg_default | 485 MB
 pg_global  | 560 kB
 dbspace    | 8777 kB
(3 rows)

• A created tablespace can be used in any database if the user has permission.
• A tablespace cannot be dropped until all objects in all databases using the tablespace have been removed.
• Tablespaces use symbolic links to simplify implementation. Therefore, they can only be used on systems that support symbolic links.

Caution

Even if located outside the default PostgreSQL data directory, a tablespace depends on metadata included in the database cluster, so it cannot be connected to another database cluster or backed up individually. 

Likewise, if files of a tablespace are deleted or a disk error occurs, the database cluster may become unreadable or fail to start. 

Placing tablespaces on temporary file systems such as RAM increases risk to stability.

 

If you create the directory and tablespace only on the Active server

The Standby server will shut down with a log indicating needs to be created because it cannot create the tablespace.