Updated on 2024-06-03 GMT+08:00

ALTER TABLESPACE

Description

Modifies the attributes of a tablespace.

Precautions

  • Only the tablespace owner or a user granted with the ALTER permission can run the ALTER TABLESPACE command. By default, system administrators have the permission. To modify a tablespace owner, you must be the tablespace owner or system administrator and a member of the new_owner role.
  • The ALTER TABLESPACE operation on a row-store table cannot be performed in a transaction block.
  • To change the owner, you must also be a direct or indirect member of the new owning role.

    If new_owner is the same as old_owner, the current user will not be verified. A message indicating successful ALTER execution is displayed.

Syntax

  • The syntax of renaming a tablespace is as follows:
    ALTER TABLESPACE tablespace_name 
        RENAME TO new_tablespace_name;

  • The syntax of setting the owner of a tablespace is as follows:
    ALTER TABLESPACE tablespace_name 
        OWNER TO new_owner;

  • The syntax of setting the attributes of a tablespace is as follows:
    ALTER TABLESPACE tablespace_name 
        SET ( { tablespace_option = value } [, ... ] );

  • The syntax of resetting the attributes of a tablespace is as follows:
    ALTER TABLESPACE tablespace_name 
        RESET ( { tablespace_option } [, ... ] );

  • The syntax of setting the quota of a tablespace is as follows:
    ALTER TABLESPACE tablespace_name 
        RESIZE MAXSIZE { UNLIMITED | 'space_size'};

Parameters

  • tablespace_name

    Specifies the tablespace to be modified.

    Value range: an existing table name

  • new_tablespace_name

    Specifies the new name of the tablespace. The new name cannot start with PG_.

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

  • new_owner

    Specifies the new owner of the tablespace.

    Value range: an existing username

  • tablespace_option

    Sets or resets the parameters of a tablespace.

    Value range:

    • seq_page_cost: sets the optimizer to calculate the cost of obtaining disk pages in sequence. The default value is 1.0.
    • random_page_cost: sets the optimizer to calculate the cost of obtaining disk pages in a non-sequential manner. The default value is 4.0.
      • The value of random_page_cost is relative to that of seq_page_cost. It is meaningless when the value is equal to or less than the value of seq_page_cost.
      • The prerequisite for the default value 4.0 is that the optimizer uses indexes to scan table data and the hit ratio of table data in the cache is about 90%.
      • If the size of the table data space is smaller than that of the physical memory, decrease the value to a proper level. On the contrary, if the hit ratio of table data in the cache is lower than 90%, increase the value.
      • If random-access memory like SSD is adopted, the value can be decreased to a certain degree to reflect the cost of true random scan.

    Value range: Positive number of the floating-point type.

  • RESIZE MAXSIZE

    Resets the maximum size of tablespace.

    Value range:

    • UNLIMITED: No limit is set for the tablespace.
    • Determined by space_size. For details about the format, see CREATE TABLESPACE.
      • If the adjusted quota is smaller than the current tablespace usage, the adjustment is successful. You need to decrease the tablespace usage to a value less than the new quota before writing data to the tablespace.

Examples

  • Rename a tablespace.
    -- Create a tablespace.
    gaussdb=# CREATE TABLESPACE tbs_data1 RELATIVE LOCATION 'tablespace1/tbs_data1';
    
    -- Rename a tablespace.
    gaussdb=# ALTER TABLESPACE tbs_data1 RENAME TO tbs_data2;
    
    -- Query.
    gaussdb=# \db tbs_data2
                List of tablespaces
       Name    | Owner |       Location        
    -----------+-------+-----------------------
     tbs_data2 | omm   | tablespace1/tbs_data1
  • Set the tablespace owner.
    -- Create a user.
    gaussdb=# CREATE USER test PASSWORD '********';
    
    -- Change the owner of the tablespace.
    gaussdb=# ALTER TABLESPACE tbs_data2 OWNER TO test;
    
    -- Query.
    gaussdb=# \db tbs_data2
                List of tablespaces
       Name    | Owner |       Location        
    -----------+-------+-----------------------
     tbs_data2 | test  | tablespace1/tbs_data1
    (1 row)
  • Set tablespace attributes.
    -- Change the value of seq_page_cost.
    gaussdb=# ALTER TABLESPACE tbs_data2 SET (seq_page_cost = 10);
    
    -- Query.
    gaussdb=# SELECT * FROM pg_tablespace WHERE spcname = 'tbs_data2';
      spcname  | spcowner | spcacl |     spcoptions     | spcmaxsize | relative 
    -----------+----------+--------+--------------------+------------+----------
     tbs_data2 |    16778 |        | {seq_page_cost=10} |            | t
    (1 row)
  • Reset tablespace attributes.
    -- Reset the value of seq_page_cost to the default value.
    gaussdb=# ALTER TABLESPACE tbs_data2 RESET (seq_page_cost);
    
    -- Query.
    gaussdb=# SELECT * FROM pg_tablespace WHERE spcname = 'tbs_data2';
      spcname  | spcowner | spcacl | spcoptions | spcmaxsize | relative 
    -----------+----------+--------+------------+------------+----------
     tbs_data2 |    16778 |        |            |            | t
    (1 row)
  • Set the tablespace limit.
    -- Set the maximum size of a tablespace.
    gaussdb=# ALTER TABLESPACE tbs_data2 RESIZE MAXSIZE '10G';
    
    -- Query.
    gaussdb=# SELECT * FROM pg_tablespace WHERE spcname = 'tbs_data2';
      spcname  | spcowner | spcacl | spcoptions | spcmaxsize | relative 
    -----------+----------+--------+------------+------------+----------
     tbs_data2 |    16778 |        |            | 10485760 K | t
    (1 row)
    
    -- Delete the tablespace.
    gaussdb=# DROP TABLESPACE tbs_data2;
    
    -- Delete the user.
    gaussdb=# DROP USER test;