What is a tablespace?
Tablespaces allow database administrators to have multiple file systems per machine and decide how to best use physical storage to store database objects. They are named locations within a filespace in which you can create objects. Tablespaces allow you to assign different storage for frequently and infrequently used database objects or to control the I/O performance on certain database objects. For example, place frequently-used tables on file systems that use high performance solid-state drives (SSD), and place other tables on standard hard drives.
Pg_default tablespace corresponds to the “base” directory in the database cluster. With an external tablespace is created a symbolic link is created in the {PGDATA}/pg_tblspc directory. File name of the symbolic link corresponds to the oid column of pg_tablespace catalog.
When a tablespace is created, sub-directory named “PG_{VERSION}_{YYYYMMDDN}” is created in the directory. “YYYYMMDD” part is not tablespace creation date, but it seems to be the date for the format.
|
|
postgres=# show default_tablespace;
default_tablespace
——————————
(1 row)
|
Creating a new tablespace in PostgreSQL
|
|
postgres=# create tablespace new_ts location ‘/u02/pgsql/data/new_ts’;
WARNING: tablespace location should not be inside the data directory
CREATE TABLESPACE
|