CREATE TABLESPACE

Function

CREATE TABLESPACE creates a tablespace in a database.

Precautions

• The system administrator or a user who inherits the gs_roles_tablespace permission of the built-in role can create a tablespace.
• Do not run CREATE TABLESPACE in a transaction block.
• If executing CREATE TABLESPACE fails but the internal directory (or file) has been created, the directory (or file) will remain. You need to manually clear it before creating the tablespace again. If there are residual files of soft links for the tablespace in the data directory, delete the residual files, and then perform O&M operations.
• CREATE TABLESPACE cannot be used for two-phase transactions. If it fails on some nodes, the execution cannot be rolled back.
• For details about how to prepare for creating tablespaces, see the description of parameters below.
• In scenarios such as HCS, you are not advised to use user-defined tablespaces. This is because user-defined tablespaces are usually used with storage media other than the main storage (storage device where the default tablespace is located, such as a disk) to isolate I/O resources that can be used by different services. In HCS scenarios, storage devices use standard configurations and do not have other available storage media. If the customized tablespace is not properly used, the system cannot run stably for a long time and the overall performance is affected. Therefore, you are advised to use the default tablespace.

Syntax

CreateTablespace ::= CREATE TABLESPACE tablespace_name
    [ OWNER user_name ] [RELATIVE] LOCATION 'directory' [ MAXSIZE 'space_size' ]
    [with_option_clause];

The with_option_clause syntax for creating a general tablespace is as follows:

with_option_clause ::= WITH ( {filesystem= { 'general'| "general" | general} |
    random_page_cost = { 'value ' | value } |
    seq_page_cost = { 'value ' | value }}[,...]);

Parameter Description

• tablespace_name

  Specifies name of a tablespace to be created.

  The tablespace name must be distinct from the name of any existing tablespace in MogDB and cannot start with "pg", which are reserved for system catalog spaces.

  Value range: a string. It must comply with the identifier naming convention.

• OWNER user_name

  Specifies the name of the user who will own the tablespace. If omitted, the default owner is the current user.

  Only system administrators can create tablespaces, but they can use the OWNER clause to assign ownership of tablespaces to non-Sysadmin administrators.

  Value range: a string. It must be an existing user.

• RELATIVE

  Relative path. The LOCATION directory is relative to the data directory in each database node.

  Directory hierarchy: the relative path of the database node directory /pg_location/

  A relative path contains a maximum of two levels.

• LOCATION directory

  Specifies the directory used for the tablespace. The directory must meet the following requirements:

  • The MogDB system user must have the read and write permissions on the directory, and the directory must be empty. If the directory does not exist, the system automatically creates it.

  • The directory must be an absolute path, and does not contain special characters, such as dollar sign ($) and greater-than sign (>).

  • The directory cannot be specified under the database data directory.

  • The directory must be a local path.

    Value range: a string. It must be a valid directory.

• MAXSIZE 'space_size'

  Specifies the maximum value of the tablespace in a single database node.

  Value range: a string consisting of a positive integer and unit. The unit can be KB, MB, GB, TB, or PB currently. The unit of parsed value is KB and cannot exceed the range that can be expressed in 64 bits, which is 1 KB to 9007199254740991 KB.

• random_page_cost

  Specifies the cost of randomly reading the page overhead.

  Value range: 0 to 1.79769e+308

  Default value: value of the GUC parameter random_page_cost

• seq_page_cost

  Specifies the cost of reading the page overhead in specified order.

  Value range: 0 to 1.79769e+308

  Default value: value of GUC parameter seq_page_cost

Examples

-- Create a tablespace.
mogdb=# CREATE TABLESPACE ds_location1 RELATIVE LOCATION 'tablespace/tablespace_1';

-- Create user joe.
mogdb=# CREATE ROLE joe IDENTIFIED BY 'xxxxxxxxx';

-- Create user jay.
mogdb=# CREATE ROLE jay IDENTIFIED BY 'xxxxxxxxx';

-- Create a tablespace and set its owner to user joe.
mogdb=# CREATE TABLESPACE ds_location2 OWNER joe RELATIVE LOCATION 'tablespace/tablespace_1';

-- Rename the ds_location1 tablespace to ds_location3.
mogdb=# ALTER TABLESPACE ds_location1 RENAME TO ds_location3;

-- Change the owner of the ds_location2 tablespace.
mogdb=# ALTER TABLESPACE ds_location2 OWNER TO jay;

-- Delete the tablespace.
mogdb=# DROP TABLESPACE ds_location2;
mogdb=# DROP TABLESPACE ds_location3;

-- Delete users.
mogdb=# DROP ROLE joe;
mogdb=# DROP ROLE jay;

Helpful Links

CREATE DATABASE，CREATE TABLE，CREATE INDEX，DROP TABLESPACE，ALTER TABLESPACE

Suggestions

• create tablespace

  You are not advised to create tablespaces in a transaction.