How to Create a Tablespace in PostgreSQL: Step-by-Step Instructions

A tablespace in PostgreSQL is a logical structure that allows you to manage the physical layout of data on disk. Creating tablespaces can be useful in various scenarios, such as distributing data across different storage media, improving performance, or efficiently using available disk space.

Using tablespaces, a database administrator can determine exactly where specific tables, indexes, and other objects will be stored, which is especially important for large systems that require flexible resource management.

In this article, we will look at how easy it is to create a tablespace in PostgreSQL.

The article on the page is based on my many years of experience with this technology and is presented to you as a short instruction, but if you want to get acquainted with it in more detail, then I recommend that you go to the official documentation on PostgreSQL.

Contents of the article:

1. Creating directories for tablespaces.
2. Creating tablespaces in PostgreSQL.
3. View all tablespaces.

1. Creating directories for tablespaces.

Directories for tablespaces for PostgreSQL must be created manually using the Linux operating system. The owner of the directories must be the postgres user.

~$ mkdir -p /u01/pg_tablespace/inzhener_1

~$ mkdir -p /u01/pg_tablespace/inzhener_2

The directories inzhener_1 and inzhener_2 will belong to different tablespaces and will not be related to each other in any way.

2. Creating tablespaces in PostgreSQL.

After we have created directories for the database data, we can now create tablespaces in the PostgreSQL database itself. The tablespaces will be called inzhener_1 and inzhener_2.

sql> CREATE TABLESPACE inzhener_1 OWNER postgres LOCATION '/u01/pg_tablespace/inzhener_1';

sql> CREATE TABLESPACE inzhener_2 OWNER postgres LOCATION '/u01/pg_tablespace/inzhener_2';

3. View all tablespaces.

After we have created our tablespaces, it is better to make sure that they have indeed been created successfully. To view all the tablespaces that are in the database, you need to run an SQL query.

sql> SELECT oid, spcname FROM pg_tablespace;

As we can see in the image, inzhener_1 and inzhener_2 are present, which means that they were successfully created.

As a result, today we looked at how easy it is to create a tablespace in a PostgreSQL database.