PostgreSQL – CREATE TABLESPACE

In PostgreSQL a tablespace is used to map a logical name to a physical location on disk. In the simplest word, we can understand tablespace as a location on the disk where all database objects of PostgreSQL are stored. These objects can be an index or a table etc.

PostgreSQL has two default tablespaces:

• pg_defaulttablespace is used to store user data.
• pg_globaltablespace is used to store the global data.

Tablespaces in general are used to manage and control the disk layout of PostgreSQL. There are two main advantages of using tablespaces:

• It comes in handy when an initialized cluster in a partition runs out of space. The tablespace can here further be used to create a new tablespace in a different partition altogether until your configuration is adjusted for the lack of space on the previous partition.
• The database performance can be optimized using tablespaces.

Syntax:
CREATE TABLESPACE tablespace_name
OWNER user_name
LOCATION directory_path;

It is also important to note that the name of the tablespace must not start with pg_, as these are reserved for the system tablespaces.

Example:

The following statement uses the CREATE TABLESPACE to create a new tablespace called gfg with the physical location c:\data\gfg.

CREATE TABLESPACE gfg
LOCATION 'C:\data\gfg';

To list all tablespaces in the current PostgreSQL database server, the following command can be used:

\db

Output:

The below command shows more information such as size and access privileges:

\db+

The result will be similar to the image depicted below: