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

Find the tablespaces

postgres=# SELECT oid, spcname FROM pg_tablespace ;
oid | spcname
--------+------------
1663 | pg_default
1664 | pg_global
114789 | new_ts
(3 rows)

Set New tablespace to database

postgres=# SET default_tablespace = new_ts;
SET
postgres=#

Test the tablespace

postgres=# create table naresh_new as select * from naresh;
SELECT 2

Check the file created in the new location

[postgres@postgreshelp 13275]$ pwd
/u02/pgsql/data/new_ts/PG_9.6_201608131/13275


[postgres@postgreshelp 13275]$ ls -lrt
total 8
-rw------- 1 postgres postgres 8192 Sep 23 22:18 114790
[postgres@postgreshelp 13275]$

More details with dictionary views

postgres=# select oid, * from pg_class where relname='rel_name';
Check the location of your table
postgres=# SELECT pg_relation_filepath('rel_name');
-[ RECORD 1 ]--------+-----------------------------------------------
pg_relation_filepath | pg_tblspc/114789/PG_9.6_201608131/13275/114790