Tablespaces in PostgreSQL

June 9, 2021

Tablespaces in PostgreSQL

A tablespace allows superusers to define an alternative location on the file system where the data files containing database objects (such as tables and indexes) can reside. The tablespace name must be distinct from the name of any existing tablespace in the database cluster. PostgreSQL uses a tablespace to map a logical name to a physical location on a disk. Tablespaces allow you to control the disk layout of PostgreSQL. There are two main advantages of using tablespaces:

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 statistics to optimize database performance. 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 slower devices.

Step 1. Create Directory Structure: Create directory structure and provide ownership to postgres OS User.

[root@test-machine02 ~]#
[root@test-machine02 ~]# cd /u01
[root@test-machine02 u01]# mkdir psql_tbls
[root@test-machine02 u01]# chown postgres:postgres psql_tbls
[root@test-machine02 u01]# mkdir psql2_tbls
[root@test-machine02 u01]# chown postgres:postgres psql2_tbls

Step 2. Create Tablespace: The directory $PGDATA/pg_tblspc contains symbolic links that point to each of the non-built-in tablespaces defined in the cluster. Please note you can map Only one Tablespace with One Location. Use command \db+ to get tablespace details. Please note OID (Object identifiers) of tablespace created in below example myts01: 23701 & myts02: 23704.

[root@test-machine02 ~]# su - postgres
Last login: Tue Jun  8 10:25:43 +03 2021 on pts/0
-bash-4.2$
-bash-4.2$ psql
psql (13.2)
Type "help" for help.

postgres=#
postgres=# create tablespace myts01 location '/u01/psql_tbls';
CREATE TABLESPACE

postgres=# create tablespace myts02 location '/u01/psql_tbls';
ERROR:  directory "/u01/psql_tbls/PG_13_202007201" already in use as a tablespace

postgres=# create tablespace myts02 location '/u01/psql2_tbls';
CREATE TABLESPACE
postgres=#

postgres=# \db+
                                      List of tablespaces
    Name    |  Owner   |    Location     | Access privileges | Options |  Size   | Description
------------+----------+-----------------+-------------------+---------+---------+-------------
 myts01     | postgres | /u01/psql_tbls  |                   |         | 8165 kB |
 myts02     | postgres | /u01/psql2_tbls |                   |         | 15 MB   |
 pg_default | postgres |                 |                   |         | 334 MB  |
 pg_global  | postgres |                 |                   |         | 367 kB  |
(4 rows)


postgres=# select spcname,oid , pg_tablespace_location(oid) from pg_tablespace;
  spcname   |  oid  | pg_tablespace_location
------------+-------+------------------------
 pg_default |  1663 |
 pg_global  |  1664 |
 myts01     | 23701 | /u01/psql_tbls
 myts02     | 23704 | /u01/psql2_tbls
(4 rows)

[root@test-machine02 pg_tblspc]# pwd
/var/lib/pgsql/13/data/pg_tblspc
[root@test-machine02 pg_tblspc]# ls -ltr
total 0
lrwxrwxrwx. 1 postgres postgres 14 May 25 14:03 23701 -> /u01/psql_tbls
lrwxrwxrwx. 1 postgres postgres 15 May 25 15:02 23704 -> /u01/psql2_tbls
[root@test-machine02 pg_tblspc]#

Step 3: Move DB to New Tablespace: In the below example, we will move sample DB dvdrental from default tablespace pg_default location $PGDATA/base to newly created tablespace myts01 location: /u01/psql_tbls. You can use function pg_relation_filepath to get the current location of Table files. After the move command, you will notice the location of tables changed from base/16384/16421 to pg_tblspc/23701/PG_13_202007201/16384/16421, Here 23701 is OID of new tablespace myts01 & 16384 is OID of database dvdrental and 16421 is OID of table actor.

postgres=# \l+ dvdrental
                                                 List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    | Access privileges | Size  | Tablespace | Description
-----------+----------+----------+-------------+-------------+-------------------+-------+------------+-------------
 dvdrental | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                   | 15 MB | pg_default |
(1 row)


postgres=# \connect dvdrental
You are now connected to database "dvdrental" as user "postgres".
dvdrental=#
dvdrental=# SELECT pg_relation_filepath('actor');
 pg_relation_filepath
----------------------
 base/16384/16421
(1 row)


dvdrental=# ALTER DATABASE dvdrental SET TABLESPACE myts01;
ERROR:  cannot change the tablespace of the currently open database
dvdrental=#

dvdrental=#
dvdrental=# \connect postgres
You are now connected to database "postgres" as user "postgres".
postgres=#
postgres=# ALTER DATABASE dvdrental SET TABLESPACE myts01;
ALTER DATABASE
postgres=# \l+ dvdrental
                                                 List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    | Access privileges | Size  | Tablespace | Description
-----------+----------+----------+-------------+-------------+-------------------+-------+------------+-------------
 dvdrental | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                   | 15 MB | myts01     |
(1 row)

postgres=# \connect dvdrental
You are now connected to database "dvdrental" as user "postgres".
dvdrental=#
dvdrental=# SELECT pg_relation_filepath('actor');
            pg_relation_filepath
---------------------------------------------
 pg_tblspc/23701/PG_13_202007201/16384/16421
(1 row)

[root@test-machine02 psql_tbls]# pwd
/u01/psql_tbls
[root@test-machine02 psql_tbls]# ls -lF PG_13_202007201
total 12
drwx------. 2 postgres postgres 8192 May 25 14:33 16384/
[root@test-machine02 psql_tbls]#

Step 4. Move Specific Table to New Tablespace: In the below example, we will move Table actor from tablespace myts01 to myts02. After move command you will notice Table files moved from /u01/psql_tbls/23701/PG_13_202007201/16384/16421 to /u01/psql2_tbls/23704/PG_13_202007201/16384/24255

postgres=# \connect dvdrental
You are now connected to database "dvdrental" as user "postgres".
dvdrental=#
dvdrental=# SELECT pg_relation_filepath('actor');
            pg_relation_filepath
---------------------------------------------
 pg_tblspc/23701/PG_13_202007201/16384/16421
(1 row)

dvdrental=# alter table actor set tablespace myts02 ;
ALTER TABLE
dvdrental=#
dvdrental=# SELECT pg_relation_filepath('actor');
            pg_relation_filepath
---------------------------------------------
 pg_tblspc/23704/PG_13_202007201/16384/24255
(1 row)

This document is just for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.

Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.

Tablespaces in PostgreSQL

Recent article