PostgreSQL: How to Create and Use Tablespace?

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

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:

Create a new table in specific tablespace:

Change the default tablespace:

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

Check the list of tablespaces: