PostgreSQL - ALTER TABLESPACE

In PostgreSQL, a tablespace is a storage location where database objects like tables and indexes are stored. Managing tablespaces effectively is crucial for optimizing performance and resource allocation in your database environment. PostgreSQL provides the ALTER TABLESPACEstatement, allowing you to rename a tablespace, change its ownership, or adjust its settings.

Here, we’ll explore the syntax, usage, and examples of the ALTER TABLESPACE statement in PostgreSQL to help you better manage your database storage.

ALTER TABLESPACE Statement in PostgreSQL

The ALTER TABLESPACE statement is used to modify the properties of an existing tablespace in PostgreSQL. It can perform the following actions:

• Rename a tablespace.
• Change the owner of a tablespace.
• Adjust configuration parameters for a tablespace.

Syntax:

ALTER TABLESPACE tablespace_name action;

The 'action' can be one of the following:

• To rename the tablespace, the ALTER TABLESPACE RENAME TO statement can be used as shown below:

ALTER TABLESPACE tablespace_name 
RENAME TO new_name;

• To change the owner of the tablespace, one can use the ALTER TABLESPACE OWNER TO statement:

ALTER TABLESPACE tablespace_name 
OWNER TO new_owner;

• The following statement changes the parameters for a tablespace:

ALTER TABLESPACE tablespace_name 
SET parameter_name = value;

Note: Only superusers or tablespace owners can execute the ALTER TABLESPACE statement.

PostgreSQL ALTER TABLESPACE Statement Examples

Let us take a look at some of the examples of ALTER TABLESPACE Statement in PostgreSQL to better understand the concept.

Example 1: Renaming a Tablespace

The following statement renames the tablespace 'ts_primary' to 'ts_secondary':

ALTER TABLESPACE ts_primary
RENAME TO ts_secondary;

Verify it using the below command:

\db+

Output:

Example 2: Changing the Owner of a Tablespace

The following statement changes the owner of the 'ts_secondary' from 'Postgres' to 'raju':

ALTER TABLESPACE ts_secondary 
OWNER TO raju;

Verify the above using the below command:

\db+

Output:

Important Points About PostgreSQL ALTER TABLESPACE

• Only superusers or the current owner of the tablespace can execute the ALTER TABLESPACE command.
• The ability to rename or change the owner of a tablespace provides flexibility in managing database storage during migrations, reorganizations, or ownership transfers.
• Setting parameters for tablespaces allows you to control specific settings like maintenance thresholds or autovacuum behavior.