PostgreSQL: tablespace

PostgreSQL provides a simple description of table space for two reasons:

• Expand the storage space, jump out of the OS's file system size limit, and constantly expand the available space
• Let the database administrator optimize the system performance according to the application characteristics of the system.

 

The tablespace of PostgreSQL is the dedicated OS directory (PATH) for PostgreSQL ). Nothing mysterious. Creating multiple tablespaces in a logical file system makes little sense, because you cannot control a single file in a logical file system.

 

Create tablespace tbl_name location 'OS _ dir_path_str'; -- The OS _path must be empty and the Directory authorized by the PostgreSQL account. The user who creates a tablespace must be a superuser. After creating a tablespace, you can grant the create permission for the tablespace to common users!

 

Specify tablespace and default tablespace

• Table space objects: tables, indexes, and databases: when creating these objects, You can explicitly specify the tablespace tals_name clause to indicate the table space used by the object;
• If this parameter is not specified, the system uses the currently valid default tablespace.
• Similarly, the index and internal temporary table of the temporary table and temporary table also have the default temporary table space. When you specify system parameters for temporary tablespace, you can specify multiple tablespaces. PostgreSQL will randomly use them.
• Two default tablespace pg_global and pg_default initialized by initdb.exe
• The default tablespace pg_default in the database is used to store the default space of system directory objects, user tables, user table indexes, and temporary tables, temporary table indexes, and internal temporary tables. It is the default tablespace of the template0 and template1 template databases.
• The default tablespace pg_global in the database is used to store the default space of the shared system directory.
• The default tablespace of the database can be changed in the create database using the tablespace tbs_anme clause.

 

 

Tablespaces are independent of a single database and are used by all databases. Therefore, the tablespace can be dropped only when no objects are used!

 

PostgreSQL allows you to simplify the implementation of tablespaces through symbolic links. On OS that do not support symbolic links, you can only explicitly create the required tablespaces!