Tablespaces
Tablespaces define the physical location of data in a file system.
Tablespaces can be created on different disk partitions or on different disks. For example, a frequently used index can be placed on a fast and highly available SSD drive, while a table with archived data that is rarely used or not performance critical can be stored on a slower disk system. Also, if there is not enough space on the partition on which the cluster was initialized, a tablespace can be created on another partition.
|
IMPORTANT
Although they may be located outside the main PostgreSQL data directory, tablespaces are part of a cluster and cannot be treated as an autonomous collection of data files. They depend on metadata contained in the main directory and cannot be attached to another database cluster or backed up individually. Also, if you lose a tablespace as a result of a file deletion or disk failure, the cluster may be unavailable or unable to start. Placing a tablespace on a temporary file system, such as a RAM disk, compromises the reliability of the entire cluster.
|
To define a tablespace, use the CREATE TABLESPACE command:
CREATE TABLESPACE tablespace1 LOCATION '/home/user1/tablespace1_data';Where /home/user1/tablespace1_data — the directory where the object files belonging to this tablespace are placed. It must be created, owned by the operating system user under which the ADPG is running, and be empty.
For a particular tablespace, you can also override the planner’s estimated cost of reading pages from tables, and the executor prefetching behavior set by configuration options for a particular tablespace. It is useful if a tablespace is located on a disk which is faster or slower than others. The available parameters are seq_page_cost, random_page_cost, effective_io_concurrency, and maintenance_io_concurrency. Use the WITH clause to override these parameters, for example:
CREATE TABLESPACE tablespace2 LOCATION '/home/user1/tablespace2_data' WITH (seq_page_cost=0.8, random_page_cost=6.0);ADPG works with data on disks only through file systems, the most popular are EXT3, EXT4, and XFS. Raw devices are not supported. It is good practice to create one tablespace per logical file system since you cannot control the location of individual files within a logical file system.
A database superuser must create a tablespace. After creation, you can allow ordinary database users to utilize the new tablespace. To do that, call the GRANT command to assign these users the CREATE privilege on the new tablespace.
Tables, indexes, and entire databases can be placed in a particular tablespace. To do this, a user with CREATE privilege for a given tablespace should pass the name of the tablespace as a parameter to an appropriate command (CREATE or ALTER).
For example, the following code creates the t1 table in tablespace1:
CREATE TABLE t1 (c1 integer) TABLESPACE tablespace1;The commands below move table t1 and database database1 to tablespaces tablespace2 and tablespace3 respectively:
ALTER TABLE t1 SET TABLESPACE tablespace2;
ALTER DATABASE database1 SET TABLESPACE tablespace3;You can use the default_tablespace parameter to set the tablespace to be used by default in CREATE TABLE and CREATE INDEX:
SET default_tablespace = tablespace2;Alternatively, you can add this parameter to postgresql.conf custom section on the Clusters → ADPG cluster → Services → ADPG → Primary configuration page in ADCM UI (see Configuration parameters).
PostgreSQL also provides the temp_tablespaces parameter, which determines tablespaces for temporary tables, indexes, and temporary files that are used for sorting large data sets. It can be a list of tablespace names, rather than only one so that PostgreSQL spreads the load associated with temporary objects over multiple tablespaces. Each time a temporary object is created, PostgreSQL will randomly select a name from the specified list.
During a cluster installation, two tablespaces are created. They are described in the table below.
| Name | Description |
|---|---|
pg_default |
Used by default for databases and templates ( |
pg_global |
This tablespace contains the system objects of the entire cluster. The |
The Data directory configuration parameter determines the first part of the directory path (/pg_data1).
Objects in the same database can be placed in multiple tablespaces. One tablespace can contain objects from multiple databases.
To get a list of cluster tablespaces, use the psql \db and \db+ meta-commands. You can also query the pg_tablespace system directory:
SELECT * FROM pg_tablespace;Since a tablespace can be used within any database, it cannot be dropped until all objects in all databases using the tablespace are removed. You need to delete all objects first. To drop an empty tablespace, call DROP TABLESPACE command.
DROP TABLESPACE tablespace2;PostgreSQL uses symbolic links to simplify the implementation of tablespaces. This means that tablespaces can be used only on systems that support symbolic links. The /pg_data1/adpg14/pg_tblspc directory contains symbolic links that point to non-built-in tablespaces defined in the cluster.