PostgreSQL ALTER TABLESPACE Statement

Summary: In this tutorial, you’ll learn how to change the definition of a tablespace using the PostgreSQL ALTER TABLESPACE statement.

The ALTER TABLESPACE statement allows you to change the definition of a tablespace.

Renaming a tablespace #

To rename a tablespace, you use the following form of the ALTER TABLESPACE statement:

ALTER TABLESPACE tablespace_name
RENAME TO new_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

• First, specify the name of the tablespace you want to rename in the ALTER TABLESPACE clause.
• Second, provide the new name in the RENAME TO clause.

Note that only a superuser or the tablespace’s owner can change the tablespace.

We’ll create a new tablespace and rename it:

First, connect to the PostgreSQL server and create a new tablespace:

CREATE TABLESPACE demo_ts
LOCATION 'C:\pgdata\demo\';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Replace the directory path 'C:\pgdata\demo\' with your path.

Second, rename the tablespace demo_ts to demo:

ALTER TABLESPACE demo_ts
RENAME TO demo;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Changing the owner of a tablespace #

To change the owner of a tablespace, you use the following form of the ALTER TABLESPACE statement:

ALTER TABLESPACE tablespace_name
OWNER TO new_owner;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

• First, specify the tablespace name you want to change the owner in the ALTER TABLESPACE clause.
• Second, provide the new owner in the OWNER TO clause. It can be a role, or CURRENT_ROLE, CURRENT_USER, and SESSION_USER.

For example:

First, create a new role called nemo with the LOGIN privilege:

CREATE ROLE nemo
WITH
  LOGIN PASSWORD 'securepassword';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Replace the securepasswordpassword with your actual password.

Second, change the owner of the demo tablespace to nemo:

ALTER TABLESPACE demo OWNER TO nemo;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Third, verify the change by showing the information of the demo tablespace:

\db+ demoCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Setting or resetting tablespace parameters #

To set a new value to one or more tablespace’s parameters, you use the following form of the ALTER TABLESPACE statement:

ALTER TABLESPACE tablespace_name
SET
  option1 = value1,
  option2 = value2;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here are the available parameters:

• seq_page_cost – Defines the cost of sequentially reading a page from storage. The default value is 1.0.
• random_page_cost – Defines the cost of fetching a random page from storage. The default value is 4.0.
• effective_io_concurrency – Specifies how the number of parallel I/O operations PostgreSQL can handle. The default value is 1, suitable for HDD. Higher values are suitable for SSD such as 200.
• maintenance_io_concurrency – Controls parallel I/O during maintenance tasks. The default is 10.

If you want to reset the parameters to their default values, you can use the RESET clause:

ALTER TABLESPACE tablespace_name
RESET option1,
option2;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

• Use the PostgreSQL ALTER TABLESPACE to rename, change the owner, and set or reset a tablespace’s parameters.