Introduction to PostgreSQL Tablespace
The PostgreSQL tablespace is a physical location on drive or disk where the PostgreSQL stores data files containing database objects like indexes and tables etc. It is used by PostgreSQL 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 directory, 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 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.
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 parameters of the tablespace.
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 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 the it, you should be the owner or the superuser.
Consider the following syntax:
DROP TABLESPACE IF EXISTS tablespace_name;
Explanation:
Define the name of the 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, change the tablespace’s definition with the help of PostgreSQL ALTER TABLESPACE statement.
Recommended Articles
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 –
