PostgreSQL: How to Create and Use Tablespace?

 As DBA and Architect, Database Tablespace is a most important thing for storage optimization.

In this post, I am sharing my understanding of the PostgreSQL Tablespace.

A tablespace is a defined disk space location for storing the database metadata and user data. The default tablespaces are pg_global and pg_default. The pg_global tablespace is used for shared system catalogs and pg_default is the default for other databases.

Few advantages in short:

Can move important database metadata in a high-speed disk-like SSD Type

Can move important tables and indexes in a high-speed disk-like SSD Type

Can move temp tables and temp indexes in a sperate disk for better performance

Can manage and take the backup very easily

Not require to depend on one single disk block, and even single/default disk block/partition has also size limitation

Create a TABLESPACE:

1	CREATE TABLESPACE dbrnd LOCATION 'd:\';

Create a new table in specific tablespace:

1	CREATE TABLE tbl_test(rno int) TABLESPACE dbrnd;

Change the default tablespace:

1	SET default_tablespace = dbrnd;

Now, try to create another table which will create in newly created tablespace:

1	CREATE TABLE tbl_test2(rno int);

Check the list of tablespaces:

1	SELECT spcname FROM pg_tablespace;