Create and manage tablespaces
Background Information
By using tablespaces, administrators can control the disk layout of a database installation. This has the following advantages:
If the partition or volume where the database is initialized is full, and more space cannot be expanded logically, you can create and use tablespaces on different partitions until the system reconfigures the space.
Tablespaces allow administrators to arrange data location according to the usage patterns of database objects, thereby improving performance.
A frequently used index can be placed on a stable and fast disk, such as a solid-state device.
A table that stores archived data, rarely used or performance-critical, can be stored on a slower disk.
The administrator can set the occupied disk space through the table space. It is used to prevent the table space from occupying other space on the same partition when sharing the partition with other data.
The table space corresponds to a file system directory. It is assumed that the database node data directory /pg_location/mount1/path1 is an empty directory that the user has read and write permissions.
The use of table space quota management will affect the performance by about 30%. MAXSIZE specifies the quota size of each database node, and the error range is within 500MB. Please confirm whether it is necessary to set the maximum value of the table space according to the actual situation.
openGauss comes with two table spaces: pg_default and pg_global.
Default table space pg_default: The default table space used to store non-shared system tables, user tables, user table index, temporary tables, temporary table index, and internal temporary tables. The corresponding storage directory is the base directory under the instance data directory.
Shared tablespace pg_global: A tablespace used to store shared system tables. The corresponding storage directory is the global directory under the instance data directory.
Precautions:
In scenarios such as HCS, it is generally not recommended that users use custom tablespaces. User-defined tablespaces are usually used in conjunction with other storage media other than the main memory (that is, the storage device where the default tablespace is located, such as a disk) to isolate the IO resources that can be used by different businesses. In scenarios such as HCS, storage devices are Standardized configuration is adopted, and there is no other available storage medium. Improper use of custom table space is not conducive to the long-term stable operation of the system and affects the overall performance. Therefore, it is recommended to use the default table space.
Steps
create tablespace
Run the following command to create user jack.
openGauss=# CREATE USER jack IDENTIFIED BY 'xxxxxxxxx';
When the result displays the following information, it means the creation is successful.
CREATE ROLE
Execute the following command to create a tablespace.
openGauss=# CREATE TABLESPACE fastspace RELATIVE LOCATION 'tablespace/tablespace_1';
When the result displays the following information, it means the creation is successful.
CREATE TABLESPACE
Among them, "fastspace" is the newly created tablespace, and "tablespace/tablespace_1" is an empty directory where the user has read and write permissions.
The database system administrator executes the following command to grant the access authority of the "fastspace" tablespace to the data user jack.
openGauss=# GRANT CREATE ON TABLESPACE fastspace TO jack;
When the result is displayed as the following information, it means that the assignment is successful.
GRANT
Create objects in the tablespace
If the user has the CREATE permission of the table space, he can create database objects on the table space, such as tables and indexes.
Take creating a table as an example.
Method 1: Execute the following command to create a table in the specified tablespace.
openGauss=# CREATE TABLE foo(i int) TABLESPACE fastspace;
When the result displays the following information, it means the creation is successful.
CREATE TABLE
Method 2: First use set default_tablespace to set the default tablespace, and then create the table.
openGauss=# SET default_tablespace = 'fastspace';
SET
openGauss=# CREATE TABLE foo2(i int);
CREATE TABLE
Suppose you set "fastspace" as the default tablespace, and then create table foo2.
query tablespace
Method 1: Check the pg_tablespace system table. The following command can check all table spaces defined by the system and users.
openGauss=# SELECT spcname FROM pg_tablespace;
Method 2: Use the meta-command of the gsql program to query the tablespace.
openGauss=# \db
Query table space usage
Query the current usage of the tablespace.
openGauss=# SELECT PG_TABLESPACE_SIZE('example');
Return the following information:
pg_tablespace_size
2146304
(1 row)
Among them, 2146304 represents the size of the table space in bytes.
Calculate tablespace usage.
Table space usage = PG_TABLESPACE_SIZE/disk size of the directory where the table space is located.
modify tablespace
Run the following command to rename the tablespace fastspace to fspace.
openGauss=# ALTER TABLESPACE fastspace RENAME TO fspace;
ALTER TABLESPACE
drop tablespace
Run the following command to delete user jack.
openGauss=# DROP USER jack CASCADE;
DROP ROLE
Run the following commands to delete tables foo and foo2.
openGauss=# DROP TABLE foo;
openGauss=# DROP TABLE foo2;
When the result is displayed as the following information, it means the deletion is successful.
DROP TABLE
Run the following command to delete the tablespace fspace.
openGauss=# DROP TABLESPACE fspace;
DROP TABLESPACE
Note: The user must be the owner of the tablespace or the system administrator to delete the tablespace.
Top comments (0)