PostgreSQL - How to create a tablespace.

Colleagues hello to all.

In today's article, we'll talk about how to create a tablespace in PostgreSQL, and why a tablespace is needed.

One of the biggest benefits of using tablespaces in PostgreSQL is to properly arrange the logic for placing database object files on the filesystem. Using tablespaces is useful for at least two reasons.

The first reason is when you may run out of space in the partition where the cluster itself was initialized, and it will not be possible to expand it. Therefore, you can create a tablespace in another partition and use it until you can expand the old partition.

The second reason has to do with optimizing the business processes of the database itself. For example, data that you use frequently can be placed on a very fast SSD drive. At the same time, archival data that are rarely used and speed is not important to them can be placed on a cheaper disk.

Article content:

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

1. Create  directories for tablespaces.

Directory for tablespaces for PostgreSQL must be created manually using Linux operating system.

$. mkdir -p /u01/pg_tablespace/inzhener_1

$. mkdir -p /u01/pg_tablespace/inzhener_2

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

2. Creating tablespaces in PostgreSQL.

Once we've created our directories, we can now create the tablespaces in the PostgreSQL database itself. The tablespaces will be named 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've created our tablespaces, it's best to make sure they're actually created successfully just in case. To view all  tablespaces that are in the database, you need to execute a sql query.

sql> SELECT oid, spcname FROM pg_tablespace;

As you can see, there are inzhener_1 and inzhener_2 on the image, which means that they were successfully created.

Thank you all, I hope that my article helped you in some way.