PostgreSQL Creating Tablespaces

Summary: in this tutorial, you will learn how to create tablespaces by using the PostgreSQL CREATE TABLESPACE statement.

Introduction to PostgreSQL tablespace

A tablespace is a location on disk where PostgreSQL stores data files containing database objects e.g., indexes., tables, etc. PostgreSQL uses a tablespace to map a logical name to a physical location on disk.

PostgreSQL comes with two default tablespaces:

• pg_defaulttablespace stores all user data.
• pg_globaltablespace stores all global data.

The tablespaces allow you to control the disk layout of PostgreSQL. There are two main advantages of using tablespaces:

• First, if a partition on which the cluster was initialized is out of space, you can create a new tablespace on a different partition and use it until you reconfigure the system.
• Second, you can use the statistics of database objects’ usages to optimize the performance of databases. For example, you can place the frequent access indexes or tables on devices that perform very fast e.g., solid state devices, and put the tables containing archive data which is rarely used on a slower devices.

PostgreSQL CREATE TABLESPACE statement

To create new tablespaces, you use CREATE TABLESPACE statement as follows:

The name of the tablespace should not begin with pg_, because these names are reversed for the system tablespaces.

By default, the user who executed the CREATE TABLESPACE is the owner of the tablespace. The statement also allows assign the ownership of tablespace to another user specified in the ONWER clause.

The directory_path is the absolute path to an empty directory used for the tablespace. PostgreSQL system user must own this directory in order to read and write data into it.

Once a tablespace is created, you can specify it in the CREATE DATABASE, CREATE TABLE and CREATE INDEX statements to store data files of the objects in the tablespace.

PostgreSQL create tablespace examples

The following statement creates a new tablespace named dvdrental that has the physical location is c:/data/dvdrental.

Notice that we used the Unix-style slashes for the directory path.

To create new tablespace in pgAdmin, follow the steps below:

First, from the Tablespaces, right mouse click and choose New Tablespaces… menu item. A new Window will display.

Second, enter the name and owner of the tablespace in the properties tab, as well as the location in the Definition tab.

Click on the SQL tab, you will see the CREATE TABLESPACE command in detail.

Third, click OK button to create the tablespace.

Creating tablespace in UNIX

When you create a tablespace in UNIX systems, a permission error may occur even 777 permission granted to the tablespace directory. To fix this issue, you need to change to owner of the data directory to postgresuser by using the chwoncommand as the following:

It changes the owner of the /usr/data/tablespace_dir directory to postgresuser. Once the postgres user takes over data directory ownership, it will remove all other accesses e.g., 700.

In this tutorial, we have shown you how to create new tablespaces by using the PostgreSQL CREATE TABLE statement.