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.
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:
The below command shows more information such as size and access privileges:
The result will be similar to the image depicted below: