Tablespaces

Daria Barysheva

Overview

Tablespace is a system object that allows administrators to locate database entities (such as tables, indexes, spill files, etc.) in different directories or even on different disks. In other words, tablespaces tell where to place the physical files for logical objects. Due to using tablespaces, it is possible to have multiple file systems per machine and choose the optimal physical storage for different objects. For example, tablespaces can be used to place frequently-used tables on file systems based on the high-performance solid-state drives (SSD), and place other tables (e.g. storing archived data) on the standard hard drives (HDD).

A tablespace is a global object, so you can use it from any database — if you have the appropriate privileges.

There are several default tablespaces in every ADB cluster. These tablespaces are added after the DBMS is initialized. See their description in the table below.

Default tablespaces
Name Description

pg_global

Used by shared system catalogs

pg_default

Used by other database objects. For example, the template0 and template1 databases use it. Therefore, this is also the default tablespace for other databases, unless overridden by the TABLESPACE clause in the CREATE DATABASE command
CAUTION
It is not recommended to drop or modify the tablespaces listed above.

View existing tablespaces

To view the list of available tablespaces in the current ADB cluster, you can run the following commands:

  1. Connect to the ADB master host (via SSH, etc.) and log in under the default user gpadmin.

    $ sudo su - gpadmin

  2. Connect to the database via psql (or any other client program).

    $ psql adb

    The output is:

    psql (9.4.24)
Type "help" for help.

  3. Perform one of the following commands to list all existing tablespaces:

    • Run the SQL query against the pg_catalog.pg_tablespace catalog table.

      SELECT oid, * FROM pg_catalog.pg_tablespace;

      The result is listed below. The oid field corresponds to the unique tablespace identifier.

       oid  |  spcname   | spcowner | spcacl | spcoptions
------+------------+----------+--------+------------
 1663 | pg_default |       10 |        |
 1664 | pg_global  |       10 |        |
(2 rows)

    • If you work in the psql client program, you can use the \db+ meta-command to show the list of tablespaces.

      \db+

      Result:

                                   List of tablespaces
    Name    |  Owner  | Location  | Access privileges | Options | Description
------------+---------+-----------+-------------------+---------+-------------
 pg_default | gpadmin |           |                   |         |
 pg_global  | gpadmin |           |                   |         |
(2 rows)

      Pay attention to the Location field in the output of the last command:

      • For user-defined tablespaces, this field shows the tablespace physical location.

      • For default tablespaces, this field is empty. The location of the default tablespaces is the same as the data directory configured when the database cluster is initialized. To get it, you can show the value of the server configuration parameter data_directory.

        SHOW data_directory;

        Result:

            data_directory
-----------------------
 /data1/master/gpseg-1
(1 row)

Create a new tablespace

To create a new tablespace, it is necessary to use the CREATE TABLESPACE command, write the tablespace name, and then define the file system location where this tablespace should be stored — after the LOCATION keyword.

IMPORTANT

There are some requirements to the location that is chosen for a new tablespace:

  • It should exist on all cluster hosts — including master, standby master, and every segment host.

  • It should be empty.

  • It should be owned by the system user gpadmin.

  • It should not be on removable or transient storage. If you lose a tablespace (due to the directory removing, disk failure, etc.), the whole cluster can become unreadable.

The following example creates the new tablespace newspace in the /newspace directory.

CREATE TABLESPACE newspace LOCATION '/newspace';

The output should be similar to:

CREATE TABLESPACE

Now, if you run any command to list all tablespaces, you get the following:

  • SQL query:

    SELECT oid, * FROM pg_tablespace;
      oid  |  spcname   | spcowner | spcacl | spcoptions
-------+------------+----------+--------+------------
  1663 | pg_default |       10 |        |
  1664 | pg_global  |       10 |        |
 28508 | newspace   |       10 |        |
(3 rows)

  • psql command:

    \db+
                                 List of tablespaces
    Name    |  Owner  | Location  | Access privileges | Options | Description
------------+---------+-----------+-------------------+---------+-------------
 newspace   | gpadmin | /newspace |                   |         |
 pg_default | gpadmin |           |                   |         |
 pg_global  | gpadmin |           |                   |         |
(3 rows)

Internally, within the pg_tblspc subfolder of the main data directory, ADB stores all user-defined tablespaces as symlinks. To check it, log out from psql and run the following command. Use your own data directory instead of /data1/master/gpseg-1/:

$ ls -l /data1/master/gpseg-1/pg_tblspc

The result is listed below. 28508 is the tablespace OID.

total 0
lrwxrwxrwx 1 gpadmin gpadmin 11 Sep  7 08:43 28508 -> /newspace/1
NOTE
To create a new tablespace, you should be a superuser. After that you can allow ordinary users to use the added tablespace. To do that, grant them the CREATE privilege.

Use a tablespace to work with database objects

Create objects

You can define tablespaces for databases, tables, and indexes. To do this, use the TABLESPACE option when running the CREATE command for these types of objects. Some examples are listed below:

  • Databases. The following command creates a new database test_space_db in the newspace tablespace. This tablespace becomes the default one for all system catalogs of that database and for all tables and indexes created within the database (unless the TABLESPACE clause is given for them). If the TABLESPACE clause is not defined in the CREATE DATABASE command, a new database uses the same tablespace as the template database it is copied from (pg_default, by default).

    CREATE DATABASE test_space_db TABLESPACE newspace;

    Result:

    CREATE DATABASE

    You can connect to the created database via psql:

    \c test_space_db

    Result:

    You are now connected to database "test_space_db" as user "gpadmin".

  • Tables:

    • If you do not explicitly specify a tablespace for a table, the database’s default tablespace is used. For example, the following command creates the test_space_t1 table in the newspace tablespace.

      CREATE TABLE test_space_t1(a INT, b TEXT) DISTRIBUTED BY(a);

      Result:

      CREATE TABLE

    • You can also specify another tablespace for a table — other than the selected one for the database. The command below creates the test_space_t2 table in the pg_default tablespace:

      CREATE TABLE test_space_t2(a INT, b TEXT) TABLESPACE pg_default DISTRIBUTED BY(a);

      Result:

      CREATE TABLE

  • Indexes. As with tables, you can create indexes either in the default database’s tablespace or in the specified one.

    • Using the default database’s tablespace:

      CREATE INDEX test_space_idx2 ON test_space_t1 (b);

      Result:

      CREATE INDEX

    • Using the explicitly specified tablespace:

      CREATE INDEX test_space_idx ON test_space_t2 (b) TABLESPACE pg_default;

      The result is shown below:

      CREATE INDEX

You can also set the server configuration parameter default_tablespace — to specify the default tablespace for all subsequent CREATE TABLE and CREATE INDEX commands that do not explicitly specify a tablespace. It does not apply to the CREATE DATABASE command, as for databases the default tablespace is always copied from the template database (if the TABLESPACE option is not defined). The following example shows how to set the parameter on the fly within a database session, but as for all session parameters you can define its value at the role, the database, and the system levels too.

SET default_tablespace = newspace;

Check tablespaces

In order to check whether the tablespace is defined for database objects correctly, you can use the following commands:

  • Databases. To see the list of databases with their tablespaces, you can run the psql command \l+:

    \l+

    The command result is listed below. Pay attention to the tablespace column.

                                                                       List of databases
     Name      |  Owner  | Encoding |  Collate   |   Ctype    |  Access privileges  |  Size  | Tablespace |                Description
---------------+---------+----------+------------+------------+---------------------+--------+------------+--------------------------------------------
 adb           | gpadmin | UTF8     | en_US.utf8 | en_US.utf8 | =Tc/gpadmin        +| 422 MB | pg_default |
               |         |          |            |            | gpadmin=CTc/gpadmin |        |            |
 diskquota     | gpadmin | UTF8     | en_US.utf8 | en_US.utf8 |                     | 283 MB | pg_default |
 postgres      | gpadmin | UTF8     | en_US.utf8 | en_US.utf8 |                     | 280 MB | pg_default | default administrative connection database
 template0     | gpadmin | UTF8     | en_US.utf8 | en_US.utf8 | =c/gpadmin         +| 276 MB | pg_default | unmodifiable empty database
               |         |          |            |            | gpadmin=CTc/gpadmin |        |            |
 template1     | gpadmin | UTF8     | en_US.utf8 | en_US.utf8 | =c/gpadmin         +| 278 MB | pg_default | default template for new databases
               |         |          |            |            | gpadmin=CTc/gpadmin |        |            |
 test_space_db | gpadmin | UTF8     | en_US.utf8 | en_US.utf8 |                     | 284 MB | newspace   |
(6 rows)

  • Tables. To see the tablespace defined for the specified table, you can run the following query against the pg_catalog.pg_tables table:

    SELECT * FROM pg_catalog.pg_tables WHERE tablename='test_space_t2';

    Result:

 schemaname |   tablename   | tableowner | tablespace | hasindexes | hasrules | hastriggers
------------+---------------+------------+------------+------------+----------+-------------
 public     | test_space_t2 | gpadmin    | pg_default | t          | f        | f
(1 row)

  • Indexes. To see the tablespace defined for the specified index, you can run the following query against the pg_catalog.pg_indexes table:

    SELECT * FROM pg_catalog.pg_indexes WHERE tablename = 'test_space_t2';

    Result:

 schemaname |   tablename   |   indexname    | tablespace |                              indexdef
------------+---------------+----------------+------------+---------------------------------------------------------------------
 public     | test_space_t2 | test_space_idx | pg_default | CREATE INDEX test_space_idx ON public.test_space_t2 USING btree (b)
(1 row)
Specifics for the default database’s tablespace

 
The pg_catalog.pg_tables and pg_catalog.pg_indexes tables do not contain any data in the tablespace column for those tables and indexes that use the default database’s tablespace. The tablespace column in such cases is empty. This ensures that if the database is cloned with a different default tablespace, the pg_tables and pg_indexes entries will still match the location where the CREATE DATABASE will put the physically copied relation.

  • pg_catalog.pg_tables:

    SELECT * FROM pg_catalog.pg_tables WHERE tablename='test_space_t1';

    Result:

     schemaname |   tablename   | tableowner | tablespace | hasindexes | hasrules | hastriggers
------------+---------------+------------+------------+------------+----------+-------------
 public     | test_space_t1 | gpadmin    |            | f          | f        | f
(1 row)

  • pg_catalog.pg_indexes:

    SELECT * FROM pg_catalog.pg_indexes WHERE tablename = 'test_space_t1';

    Result:

     schemaname |   tablename   |    indexname    | tablespace |                               indexdef
------------+---------------+-----------------+------------+----------------------------------------------------------------------
 public     | test_space_t1 | test_space_idx2 |            | CREATE INDEX test_space_idx2 ON public.test_space_t1 USING btree (b)
(1 row)

Move objects

After a tablespace being defined for a table or an index, it can be changed into a different tablespace. Examples are listed below:

  • Tables:

    • To move the specified table into another tablespace, use the following query:

      ALTER TABLE test_space_t1 SET TABLESPACE pg_default;

      Result:

      ALTER TABLE

    • To move all tables in the current database from one tablespace into another, run the command:

      ALTER TABLE ALL IN TABLESPACE newspace SET TABLESPACE pg_default;

      The result is shown below. It can also contain the No matching relations in tablespace message if there are no any tables for the current database in the specified tablespace.

      ALTER TABLE
      CAUTION

      Be careful when using ALTER TABLE ALL. This command moves not only user-defined tables, but also system ones (such as pg_toast.*). If in the future you need to move the current database tables to another tablespace — you should again use the ALL statement.

  • Indexes:

    • To move the specified index into another tablespace, use the following query:

      ALTER INDEX test_space_idx2 SET TABLESPACE pg_default;

      Result:

      ALTER INDEX

    • To move all indexes in the current database from one tablespace into another, run the command:

      ALTER INDEX ALL IN TABLESPACE newspace SET TABLESPACE pg_default;

      The result is shown below. It can also contain the No matching relations in tablespace message if there are no any indexes for the current database in the specified tablespace.

      ALTER INDEX
      CAUTION

      Note that system catalogs are not moved by the ALTER TABLE ALL and ALTER INDEX ALL commands. To move system catalogs, use ALTER DATABASE or explicit ALTER TABLE/ALTER INDEX commands instead. The information_schema relations are not considered as a part of system catalogs and will be moved.

  • Databases. To change the default tablespace at the database level, you can use the ALTER DATABASE command with the SET TABLESPACE clause. This command physically moves all tables and indexes stored in the database’s old default tablespace to the new tablespace. Pay attention that tables and indexes stored in non-default tablespaces (which have been explicitly defined) are not affected. The following example creates a new tablespace and then assigns it to the database:

    • Create the new tablespace newspace2 in the /newspace2 directory:

      CREATE TABLESPACE newspace2 LOCATION '/newspace2';

    • Connect to any other database (for example, adb):

      \c adb

    • Change the default tablespace for the test_space_db database via the following command:

      ALTER DATABASE test_space_db SET TABLESPACE newspace2;

      Result:

      ALTER DATABASE
      CAUTION

      • To perform the ALTER DATABASE command, you should be the database owner or a superuser.

      • You should have the CREATE privilege for the new tablespace.

      • Before changing a tablespace for the currently open database, connect to any other database.

      • There should be no objects of the current database (including system tables) in the destination tablespace.

Move temporary objects

Tablespaces are also used to store temporary objects:

  • Temporary tables and indexes that are added explicitly via the CREATE TEMP TABLE command.

  • Temporary tables and spill files that are created automatically for query execution (including hash aggregates, hash joins, and sorting large datasets).

  • Temporary tables that are created automatically when refreshing the materialized view data.

The server configuration parameter temp_tablespaces allows you to control the location of all these temporary objects. This parameter can contain a comma-separated list of tablespace names, rather than only one — in such case a random list member is picked each time a temporary object is to be created. You can use this feature to spread the load associated with temporary objects over multiple tablespaces.

The default parameter value is an empty string, which corresponds to the default tablespace of the current database. As for all session parameters, you can define the temp_tablespaces value for the current session, role, database, or all system. The following command shows how to set the parameter within a database session:

SET temp_tablespaces = newspace;

The output looks like this:

SET

Drop a tablespace

To remove a tablespace, it is necessary to use the DROP TABLESPACE command and write the tablespace name:

DROP TABLESPACE newspace;

The output can be similar to:

DROP TABLESPACE
CAUTION

  • To drop a tablespace, you should be the tablespace owner or a superuser.

  • You can drop only an empty tablespace. Make sure that none of database objects use the tablespace. Also, the tablespace selected for removing should not store any temporary or transaction files.
Found a mistake? Seleсt text and press Ctrl+Enter to report it