How to Create a Tablespace in PostgreSQL
In this article, we will talk about how to create a tablespace in PostgreSQL, and why a tablespace is needed.
One of the biggest advantages of using tablespaces in PostgreSQL is to properly organize the logic of placing database object files in the file system. It is useful to use tablespaces for at least two reasons.
The first reason is when you may run out of space in the partition on which the cluster itself was initialized, and there will be no way to expand it. Therefore, you can create a tablespace in another partition and use it until it becomes possible to expand the previous partition.
The second reason is related to the optimization of business processes of the database itself. For example, data that you often use can be placed on a very fast SSD drive. At the same time, archived data that is rarely used and speed is not important for them can be placed on a cheaper disk.
Contents of the article:
- Creating directories for tablespaces.
- Creating tablespaces in PostgreSQL.
- Viewing all tablespaces.
1. Create directories for tablespaces.
Directories for tablespaces for PostgreSQL must be created manually using the Linux operating system.
$. 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. Create tablespaces in PostgreSQL.
After we have created our directories, 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.
