The temporary tablespace of PostgreSQL, through the parameter temp_tablespaces are configured. PostgreSQL allows users to configure multiple temporary tablespaces.
When configuring multiple temporary tablespaces, use commas to separate them.
If temp is not configured_ Tablespaces parameter. The temporary tablespace corresponds to the default tablespace.
The temporary table space of PostgreSQL is used to store the indexes of temporary tables or tables, as well as temporary files such as sorting, aggregation, hash, etc. that may be generated when executing SQL.
In order to improve performance, it is generally recommended to put temporary table spaces in SSD s or IOPS, as well as partitions with high throughput.
example:
Temp is not currently configured_ Tablespaces, using the default tablespace.
postgres=# \l+ postgres List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description ----------+----------+----------+---------+-------+-------------------+---------+------------+-------------------------------------------- postgres | postgres | UTF8 | C | C | | 7456 kB | pg_default | default administrative connection database (1 row) postgres=# show temp_tablespaces ; temp_tablespaces ------------------ (1 row) postgres=# create temp table tmp1 (id int); CREATE TABLE postgres=# insert into tmp1 select generate_series(1,1000); INSERT 0 1000
Temporary tables are placed in the default tablespace.
postgres=# select pg_relation_filepath('tmp1'); pg_relation_filepath ---------------------- base/13241/t2_73746 (1 row)
Perform a large sort with temporary files in the default tablespace.
postgres=# select * from generate_series(1,10000000000) order by 1;
View temporary file directory
cd $PGDATA/base/pgsql_tmp $ ll -rw------- 1 digoal users 1.0G Mar 26 15:58 pgsql_tmp30315.0 -rw------- 1 digoal users 1.0G Mar 26 15:58 pgsql_tmp30315.1 ....
Create a new tablespace and set the temp of all users_ The tablespaces parameter is set to the new tablespace.
mkdir /disk1/digoal/tmptbs1 postgres=# create tablespace tmptbs location '/disk1/digoal/tmptbs'; CREATE TABLESPACE postgres=# alter role all set temp_tablespaces='tmptbs'; ALTER ROLE
Retest. Now all temporary files will be placed under the new tablespace.
psql postgres=# select * from generate_series(1,10000000000) order by 1; cd /disk1/digoal/tmptbs/PG_9.5_201510051/pgsql_tmp total 528M -rw------- 1 digoal users 513M Mar 26 16:05 pgsql_tmp31527.0 postgres=# create temp table t1(id int); CREATE TABLE postgres=# insert into t1 select generate_series(1,10000); INSERT 0 10000 postgres=# select pg_relation_filepath('t1'); pg_relation_filepath ------------------------------------------------- pg_tblspc/73749/PG_9.5_201510051/13241/t3_73750 (1 row)
The following is Greenplum's temporary table space
about filespace Use of
Greenplum doesn't have temp_tablespaces, where should temporary files be placed?
Greenplum will only use the word "filespace", and the temporary files are managed globally, that is to say, the temporary files of the whole GP cluster are placed in one place (filespace).
Different users in Greenplum cannot use different temporary file directories.
By default, temporary files are placed under the default tablespace
Temporary files (such as sorting, hashing, resulting work file s)
<filespace_directory>/<tablespace_oid>/<database_oid>/pgsql_tmp
cursor
<filespace_directory>/<tablespace_oid>/<database_oid>/
You can move temporary or transaction files to a specific filespace to improve database performance when running queries, creating backups, and to store data more sequentially. The dedicated filespace for temporary and transaction files is tracked in two separate flat files called gp_temporary_files_filespace and gp_transaction_files_filespace. These are located in the pg_system directory on each primary and mirror segment, and on master and standby. You must be a superuser to move temporary or transaction files. Only the gpfilespace utility can write to this file. Unless otherwise specified, temporary and transaction files are stored together with all user data. The default location of temporary files, <filespace_directory>/<tablespace_oid>/<database_oid>/pgsql_tmp is changed when you use gpfilespace --movetempfiles for the first time. Also note the following information about temporary or transaction files: • You can dedicate only one filespace for temporary or transaction files, although you can use the same filespace to store other types of files. • You cannot drop a filespace if it used by temporary files. • You must create the filespace in advance.
If you want to modify the location of Greenplum temporary files, do the following:
First, create the filespace, and then make sure there is no active session. Use gpfilespace -- movetempfilespace filespace_ The name command migrates temporary file directories.
To move temporary files using gpfilespace 1. Check that the filespace exists and is different from the filespace used to store all other user data. 2. Issue smart shutdown to bring the Greenplum Database offline. If any connections are still in progess, the gpfilespace --movetempfiles utility will fail. 3. Bring Greenplum Database online with no active session and run the following command: gpfilespace --movetempfilespace filespace_name The location of the temporary files is stored in the segment configuration shared memory (PMModuleState) and used whenever temporary files are created, opened, or dropped.
To move transaction files using gpfilespace 1. Check that the filespace exists and is different from the filespace used to store all other user data. 2. Issue smart shutdown to bring the Greenplum Database offline. If any connections are still in progess,the gpfilespace --movetransfiles utility will fail. 3. Bring Greenplum Database online with no active session and run the following command: gpfilespace --movetransfilespace filespace_name The location of the transaction files is stored in the segment configuration shared memory (PMModuleState) and used whenever transaction files are created, opened, or dropped. Creating a Tablespace After you create a filespace, use the CREATE TABLESPACE command to define a tablespace that uses that filespace. For example: =# CREATE TABLESPACE fastspace FILESPACE fastdisk;