PostgreSQL Tablespaces

Introduction to PostgreSQL Tablespace

The PostgreSQL tablespace is a physical location on a drive or disk where the PostgreSQL stores data files containing database objects like indexes and tables etc. PostgreSQL uses it in order to map logical names to a physical location on disk or drive.

It has the following two default tablespaces:

pg_default: Stores all users data.

pg_global: Stores all global data.

How does Tablespaces work in PostgreSQL?

• The user who executes the CREATE TABLESPACE is the owner of the PostgreSQL tablespace.
• The SQL statement also allows us to assign the ownership of it to a different user by specifying the name in the OWNER clause.
• The path defined under the LOCATION clause is used to create the PostgreSQL tablespace; this path is an absolute path to an empty. In order to read and write data from/to the directory, the system user must own this directory.

How to create Tablespaces in PostgreSQL?

Let’s illustrate the following syntax for creating a new tablespace:

Syntax:

CREATE TABLESPACE tablespace_name
OWNER user_name
LOCATION directory_path;

Explanation:

1. tablespace_name

The name of the PostgreSQL tablespace to create a new PostgreSQL tablespace. We can not define the name which begins with pg_; the names starting with pg_ are reserved for PostgreSQL system tablespaces.

2. user_name

The name defined is the user who will own the newly created PostgreSQL tablespace.If the user_name is not defined, then the user executing the statement will own the newly created PostgreSQL tablespace. It can be created by superusers only, and they can assign the ownership of the tablespace to non-superusers.

3. directory_path

It will use the directory defined under the LOCATION clause. The directory path should be defined as an absolute path. Also, the defined directory should be owned by the user and must be empty.

Let’s have a look at the following snapshot showing default tablespaces:

The following example will create a tablespace named ‘edutablespace’ at a specified location.

CREATE TABLESPACE edutablespace
OWNER postgres
LOCATION 'C:\MyFolder\Articles\Tablespaces';

Illustrate the result of the above statement with the help of the following snapshot.

<img class="alignnone size-medium wp-image-321346" src="https://www.educba.com/academy/wp-content/uploads/2020/03/PostgreSQL-Tablespaces-2-300×133.jpg" alt="PostgreSQL Tablespaces 2" width="300" height="133" /

How to Alter Tablespaces in PostgreSQL?

In order to perform some alter operation on tablespace using the ALTER TABLESPACE statement, you should be a superuser or the owner of it. We can not change the location used by tablespace as it’s not supported by PostgreSQL yet.

Consider we have a tablespace that is created, and we want to change the tablespace definition with the help of the ALTER TABLESPACE as follows:

ALTER TABLESPACE action;

Explanation:

Action:

It allows us to perform some action like rename the tablespace name, change the owner of the tablespace and set some tablespace parameters.

1. To modify the tablespace name, we use the following statement:

ALTER TABLESPACE tablespace_name RENAME TO new_name;

The following statement renames edutablespace tablespace to edutablespace_new:

ALTER TABLESPACE edutablespace RENAME TO edutablespace_new;

Illustrate the result of the above statement with the help of the following snapshot.

2. To modify the tablespace owner, we use the following statement:

ALTER TABLESPACE tablespace_name OWNER TO new_owner;

Currently, the owner of the edutablespace_new is Postgres have a look at the following snapshot

The following statement changes the owner of the edutablespace_new tablespace to ‘educba.’

ALTER TABLESPACE edutablespace_new OWNER to educba;

Illustrate the result of the above statement with the help of the following snapshot

3. Modify the parameters of the tablespace.

ALTER TABLESPACE tablespace_name SET parameter = value;

How to delete Tablespaces in PostgreSQL?

In order to remove it, you should be the owner or the superuser.

Consider the following syntax:

DROP TABLESPACE IF EXISTS tablespace_name;

Explanation:

Define the name of it after the DROP TABLESPACE clause.

The IF EXISTS helps us to prevent the error of deleting a non-existent tablespace.

We need to make sure the tablespace is empty before removing, which means verify there are no database objects inside the tablespace.

DROP TABLESPACE edutablespace_new;

The above statement will drop the edutablespace_new tablespace.

Conclusion

We hope from the above article; you have learned how to create a tablespace using CREATE TABLESPACE statement and change the tablespace’s definition with the PostgreSQL ALTER help TABLESPACE statement.

This is a guide to PostgreSQL Tablespaces. Here we discuss the introduction to PostgreSQL Tablespaces and how to Alter them with examples. You may also have a look at the following articles to learn more –

1. What is PostgreSQL?
2. PostgreSQL Primary Key | How to Work?
3. PostgreSQL IF Statement | How to Work?
4. PostgreSQL Auto Increment
5. PostgreSQL Trunc() | How to Work?