In this article I’ll show you how to create tablespace in PostgreSQL using PosgtreSQL CREATE TABLESPACE statement.

What is a PostgreSQL tablespace?

Tablespace is common term for database engines. A tablespace is a physical location to store data. A database can have multiple tablespaces.

By default, each PostgreSQL database has two tablespaces:

• pg_deafult: deafult tablespace for user data
• pg_global: default tablespace for global data

Where to Use PostgreSQL Tablespaces?

We can use tablepaces with different purposes:

Splitting IO Workload: High IO workloads can cause a queue on storage, in that case, splitting IO workload to different storage units can improve the performance.

Table Partitioning: Big tables need to be partitioned by a key (like date, index etc.) and can be distributed to different tablespaces. Partitioning a table into different tablespaces can improve the maintenance and backup performance.

Archiving: Fast storage is an expensive resource. A tablespace on cheaper and slower device can be used to archive less accessed data.

PostgreSQL CREATE TABLESPACE Statement

To create a new tablespace in PostgreSQL you can use postgresql create tablespace statement as following:

CREATE TABLESPACE tablespace_name
OWNER owner_name
LOCATION tablespace_location;

You can refer to official documentation of PostgreSQL create tablespace statement for more information.

There are 3 parameters mentioned in this statement. The first parameter is tablespace_name, which is a unique identifier name for the tablespace you create. You can not use names starting with pg_, because this pattern is reserved for system tablespaces.

owner_name is the role that owns tablespace, in PostgreSQL only super users can create tablespaces but you can assign ownership of the tablespace to non-super users.

tablespace_location is the physical path of the tablespace. You should assign tablespace_location as an absolute path.

You can use tablespaces to store table data and index data. Also you can separate index and table data of a table. You can mention default tablespace while creating a database.

PostgreSQL CREATE TABLESPACE Examples

You can create tablespaces in PostgreSQL as follows:

The statement above creates a tablespace named as obs_data at location /var/lib/postgresql/ts2.

You can use \db command to list all tablespaces database owns:

postgres=# \db
               List of tablespaces
    Name    |  Owner   |        Location
------------+----------+-------------------------
 obs_data   | postgres | /var/lib/postgresql/ts2
 pg_default | postgres |
 pg_global  | postgres |
(3 rows)

\db+ command shows more details, such as size, about the spacespaces you have:

postgres=# \db+
                                          List of tablespaces
    Name    |  Owner   |        Location         | Access privileges | Options |  Size   | Description
------------+----------+-------------------------+-------------------+---------+---------+-------------
 obs_data   | postgres | /var/lib/postgresql/ts2 |                   |         | 0 bytes |
 pg_default | postgres |                         |                   |         | 30 MB   |
 pg_global  | postgres |                         |                   |         | 565 kB  |
(3 rows)

I want to alter a table in obs_data tablespace and insert some rows into it:

ALTER TABLE public.users SET TABLESPACE obs_data;
INSERT INTO public.users (mail, "name") VALUES('yigit@openbasesystems.com', 'Yiğit Özdemir');
INSERT INTO public.users (mail, "name") VALUES('yigit2@openbasesystems.com', 'Yiğit Özdenir');
INSERT INTO public.users (mail, "name") VALUES('yigit3@openbasesystems.com', 'Yiğit Özdezir');

Now run the \db+ command again and check the size:

postgres=# \db+
3                                         List of tablespaces
    Name    |  Owner   |        Location         | Access privileges | Options |  Size  | Description
------------+----------+-------------------------+-------------------+---------+--------+-------------
 obs_data   | postgres | /var/lib/postgresql/ts2 |                   |         | 12 kB  |
 pg_default | postgres |                         |                   |         | 30 MB  |
 pg_global  | postgres |                         |                   |         | 565 kB |
(3 rows)

Let’s go to directory and have a look:

As you can see, it creates folders, subfolders and files to store the data.

Tablespace Options

There are options you can set while creating a tablespace. Those options can be set while creating a tablespace:

• seq_page_cost
• random_page_cost
• effective_io_concurrency
• maintenance_io_concurrency

If you set this values, those values will override planners estimation of reaching data from tablespace.

On standard installations you don’t need to set this options while creating a tablespace. Only set those options if multiple types of storage is being used for different tablespaces.

I want to take a quick look to those options:

PostgreSQL Create Tablespace seq_page_cost Option

Sets the cost of fetching a page in sequential page fetching

PostgreSQL Create Tablespace random_page_cost Option

Sets the cost of fetching a page in non sequential page fetching

PostgreSQL Create Tablespace effective_io_concurrency Option

Configures the parallelism for synchronous disk reads and writes in PostgreSQL.

Create Tablespace maintenance_io_concurrency Option

Similar to effective_io_concurrency, but used for maintenance work that is done on behalf of many client sessions.

Summary

In this article we covered details about PostgreSQL creating a tablespace, all of your comments and suggestions are more then welcome. I’d like hear from you!

Subscribe to OpenBaseSystems to hear open source news, tips and tricks.