The tablespace in Postgres is the location on the disk where all the data of the database is stored. This data includes tables, triggers indexes, etc. The tablespace is actually needed by Postgres for mapping the logical name onto the physical address/location on the disk. There are two default tablespaces:

● Pg_default - This tablespace stores default data.

● Pg_global - This tablespace stores global data.

In this post, we will gain some understanding of how to create the tablespace in PostgreSQL.

How to Create a Tablespace in PostgreSQL?

We have already seen why tablespace is needed by Postgres. Now we will acquire some knowledge of creating a tablespace in Postgres. The basic syntax for creating a tablespace is given below:

CREATE TABLESPACE tabspace_name
OWNER user
LOCATION dir_path;

In the above syntax:

● The tablespace is created by using the CREATE TABLESPACE command. After this command, the name of the tablespace is specified.

● We have to write the owner’s name after the OWNER clause. Also if we want to assign another owner/user to the tablespace, we can declare it here.

● The directory path is also written after the LOCATION clause. This directory path is an absolute path of a directory that must be owned by the user so that the data can be read and written in this directory. Initially, this directory should be empty.

Let’s dive into the topic using an example to make it more clear.

Example: Creating a Tablespace in PostgreSQL

Open the SQL Shell or psql and write the following command to create a table space:

CREATE TABLESPACE example_tablespace
LOCATION 'C:\Program Files\PostgreSQL\15\data';

The above code will create a tablespace named “example_tablespace” using the physical address “C:\Program Files\PostgreSQL\15\data”. But you have to keep in mind that the directory location you provided must exist. The above query has successfully created a tablespace.

Now if we want to list all the table space in this Postgres database server we will execute the following command:

\db

By executing this command the shell will return you all the tablespaces created in the database server like this:

We can see that the example_tablespace we created is visible to us. If we want to get some additional information related to the tablespace we execute the following meta-command:

\db+

This will give more extensive information about the tablespace such as size and other options like this:

So this was all about creating a tablespace in PostgreSQL.

Advantages of Creating Tablespace

Tablespaces are beneficial in many ways. The most significant of them are:

● The tablespaces are used to derive the statistics. These stats are then used to optimize the performance parameters.

● The tablespace allows the administrators to manage the storage more efficiently.

Conclusion

To create a tablespace in PostgreSQL, the “CREATE TABLESPACE” statement is used with the “LOCATION” clause. Tablespace is the location on the disk where all the data for a database is stored. In this article, we have learned about tablespace and how to create a tablespace and retrieve detailed information about them using practical examples.