Creating tablespaces

A tablespace in PostgreSQL can be used to distribute database objects such as tables and indexes to different disks/locations. This is especially helpful in distributing the IO across multiple disks and avoiding IO saturation on a single disk. In this recipe, we shall see the steps involved in creating tablespaces in PostgreSQL.

Getting ready

A tablespace directory needs to be created on the file system before creating them in the database. We should have access to the operating system as a root user or a user with sudo access to create directories on the mount points that are owned by the root user.

When you create a tablespace in the master-slave replication cluster, which is using streaming replication, you must make sure that the tablespaces also exist on the standby server. Similarly, when you restore a backup from a PostgreSQL cluster that has got one or more tablespaces, you must make sure to consider creating the respective tablespace directories before performing the restore of the backup.

How to do it...

The following steps can be used to create a tablespace in PostgreSQL:

1. Create a directory as shown:

$ sudo mkdir -p /newtablespace
$ sudo chown postgres:postgres /newtablespace

2. Now create a tablespace using the newly created directory by running the following command:

$ psql -c "CREATE TABLESPACE newtblspc LOCATION '/newtablespace'"

3. Check the pg_tblspc directory to see a symlink to the new tablespace:

$ ls -l $PGDATA/pg_tblspc
 total 0
 lrwxrwxrwx. 1 postgres postgres 14 Nov 3 00:24 24611 -> /newtablespace

How it works

To create a tablespace, we must specify a location in which the tablespace must be created, as seen in step 1. We will see the benefits of having a separate tablespace when it is created on a different disk other than the disk being used by the data directory. Now, to create a tablespace using the newly created directory, we could simply use the command as seen in step 2.

When we create a tablespace, we see a new entry in the pg_tblspc directory that has a symlink to the new tablespace location as seen in the output of step 3. There will be many such entries when we create more tablespaces.

Once you have created tablespaces, you could simply validate all the tablespaces and their location using the shortcut \db as seen in the following log:

$ psql
 psql (13.1)
 Type "help" for help.

postgres=# \db
 List of tablespaces
 Name | Owner | Location
 ------------+----------+----------------
 newtblspc | postgres | /newtablespace
 pg_default | postgres |
 pg_global | postgres |
 (3 rows)

There's more...

In order to create a table in the new tablespace, we may just append the appropriate tablespace name to the CREATE TABLE command:

postgres=# create table employee (id int) TABLESPACE newtblspc;
 CREATE TABLE

If you create a table inside the new tablespace, here is how the relation path appears. In the following log, it shows how the table is pointing to the appropriate tablespace:

postgres=# select pg_relation_filepath('employee');
 pg_relation_filepath
 ---------------------------------------------
 pg_tblspc/24611/PG_13_201909212/14187/24612
 (1 row)

And now, if you describe the table, you should be able to see the tablespace in which the table got created:

postgres=# \d employee
 Table "public.employee"
 Column | Type | Collation | Nullable | Default
 --------+---------+-----------+----------+---------
 id | integer | | |
 Tablespace: "newtblspc"

With the preceding output, it is clear that the table is created inside the new tablespace.