What Is PostgreSQL Tablespace?

In today’s article, we will discuss what PostgreSQL Tablespace is and why it is necessary and give an example.

After creating a directory, we give permissions to the postgres user on that directory.

Tablespaces in PostgreSQL allow database administrators to define locations in the file system where the files representing database objects can be stored.

Once created, a tablespace can be referred to by name when creating database objects.

Why is Tablespace Necessary?

If you did not specify a different directory during installation and the default directory is full, and if it cannot be expanded in any way, this will prevent your database from functioning.

However, if a tablespace is created on a different disk and the system is reconfigured, the issue will be resolved.

Some of your databases may need to run quickly, and you can improve performance by placing them on an SSD directory.

Alternatively, for an application that archives old data, you can create a tablespace on a slower disk to reduce costs.

The concept of tablespaces is similar to the filegroup mechanism in MSSQL.

However, unlike MSSQL, a single file exists within a single filegroup, and multiple files cannot be created.

Databases, schemas, tables, indexes, and sequences can be created within tablespaces.

To do this, a user with the CREATE privilege on that tablespace must provide the tablespace name as a parameter in the relevant command.

After this explanation, let’s move on to an example.

First, we create the directory for the tablespace.

After creating a directory, we give permissions to the postgres user on that directory.
After the tablespace is created, let’s check it with the help of the following command;
We created a tablespace as seen above.