Use of the PostgreSQL table space

First, Introduction

The purpose of a tablespace is to allow a database administrator to define a location where other non-data directories store database objects.

One of the usage scenarios is that if the machine is newly added SSD, but not enough for the entire instance, you can put some important and high frequency tables and indexes on the SSD, improve the query efficiency

Second, create TABLE space

First create the directory where you want to save the table space

# mkdir-p/export/tablespace1# chown-r postgres:/export/tablespace1/

Enter the database

postgres=# \db List of tablespaces Name | Owner | Location------------+----------+----------Pg_default |  Postgres | Pg_global | Postgres | (2 rows)

There are only two default table spaces in the data, and no other tablespace, now create a table space

postgres=# CREATE tablespace tbspl1 location '/export/tablespace1 ';  CREATE tablespacepostgres=# \db List of tablespaces Name |      Owner | Location------------+----------+---------------------Pg_default |  Postgres | Pg_global |  Postgres | TBSPL1 | Postgres | /export/tablespace1 (3 rows)

ThreeUseTable Space

Create a table table_a and view its location

postgres=# CREATE TABLE table_a (id int); CREATE tablepostgres=# Select Pg_relation_filepath (' table_a '); Pg_relation_filepath----------------------base/13003/17031 (1 row)

It can be seen that it is in the base directory of the data directory and is modified to tablespace TBSPL1

postgres=# ALTER TABLE table_a set tablespace tbspl1;                  ALTER tablepostgres=# Select Pg_relation_filepath (' table_a '); Pg_relation_filepath----------------------------------------------pg_tblspc/17030/pg_9.4_201409291/13003/170 Panax Notoginseng (1 row)

The visible table is moved to the directory where the table space resides.

To create a table that uses tablespace:

postgres=# CREATE TABLE Table_b (id int) tablespace tbspl1;                      CREATE tablepostgres=# Select Pg_relation_filepath (' Table_b '); Pg_relation_filepath----------------------------------------------pg_tblspc/17030/pg_9.4_201409291/13003/170 (1 row)

Whether the index created by default is or is in the data directory, not related to the table

postgres=# CREATE index on table_b (ID);  CREATE indexpostgres=# \d table_b table "Public.table_b" Column | Type | Modifiers--------+---------+-----------ID | Integer | Indexes: "Table_b_id_idx" Btree (ID) tablespace: "Tbspl1" postgres=# Select Pg_relation_filepath (' Table_b_id_idx '); Pg_relation_filepath----------------------base/13003/17041 (1 row)

To create an index that uses a tablespace

postgres=# CREATE index on table_a (ID) tablespace tbspl1;  CREATE indexpostgres=# \d table_a table "Public.table_a" Column | Type | Modifiers--------+---------+-----------ID | Integer | Indexes: "Table_a_id_idx" Btree (ID), tablespace "Tbspl1" tablespace: "Tbspl1" postgres=# Select Pg_relation_filepath (' t              Able_a_id_idx '); Pg_relation_filepath----------------------------------------------pg_tblspc/17030/pg_9.4_201409291/13003/170 (1 row)

Use of the PostgreSQL table space