PostgreSQL’s tablespaces allow defining where database objects are stored in the file system. In effect, a directory is specified for the storage of data files for database objects such as tables, sequences and indexes.

PostgreSQL uses the operating system’s filesystem for storage. This is a bit different from Oracle, which implements its own “file system”.

In PostgreSQL, one tablespace can be used by multiple databases; and one database can use multiple tablespaces, in a “many-to-many” relationship. In Oracle, one tablespace can be used by only one database, and one database can have multiple tablespaces, which is a “one-to-many” relationship.

1 When to use tablespace?

  • Control the disk layout because the original file system is almost full due to the continuous growth of data and for some reason cannot be extended. This is the time to create a new tablespace on a mounted other file system and move existing objects to the new tablespace.
  • Optimizing performance Tablespaces allow administrators to optimize performance based on the usage patterns of database objects. For example, tablespaces can be used to store data for indexes or tables that are used more frequently on a disk with higher IOPS (such as an expensive solid-state device); and to store data for tables that are used less frequently or are less demanding in terms of performance on a less expensive or slower disk.

In a nutshell, the use of tablespaces allows for the rational use of disk performance and space to manage database objects in an optimal physical storage manner.

2 Default tablespace

PostgreSQL initialization requires specifying a data directory ($PGDATA) with the following command.

1

$ initdb -D /usr/local/pgsql/data

After initialization is complete, this directory will contain everything (configuration files, data files, message queues, etc.) that PostgreSQL will have when it is to be started.

After PostgreSQL starts, all data files for database objects are stored under this folder.

1

$ pg_ctl -D /usr/local/pgsql/data -l server.log start

The contents of the folder are as follows.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

$ ls -lht /usr/local/pgsql/data

total 124K
drwx------ 2 postgres postgres 4.0K Mar 28 09:00 pg_stat_tmp
drwx------ 4 postgres postgres 4.0K Mar  8 17:52 pg_logical
drwx------ 2 postgres postgres 4.0K Mar  8 17:48 global
drwx------ 2 postgres postgres 4.0K Mar  8 17:47 pg_stat
-rw------- 1 postgres postgres   87 Mar  8 17:47 postmaster.pid
-rw------- 1 postgres postgres   59 Mar  8 17:47 postmaster.opts
drwx------ 6 postgres postgres 4.0K May 13  2021 base
drwx------ 2 postgres postgres 4.0K May 13  2021 pg_subtrans
drwx------ 3 postgres postgres 4.0K May 13  2021 pg_wal
drwx------ 2 postgres postgres 4.0K May 13  2021 pg_xact
-rw------- 1 postgres postgres 1.6K May 13  2021 pg_ident.conf
-rw------- 1 postgres postgres 4.7K May 13  2021 pg_hba.conf
-rw------- 1 postgres postgres   88 May 13  2021 postgresql.auto.conf
-rw------- 1 postgres postgres  28K May 13  2021 postgresql.conf
drwx------ 2 postgres postgres 4.0K May 13  2021 pg_dynshmem
drwx------ 4 postgres postgres 4.0K May 13  2021 pg_multixact
drwx------ 2 postgres postgres 4.0K May 13  2021 pg_notify
drwx------ 2 postgres postgres 4.0K May 13  2021 pg_replslot
drwx------ 2 postgres postgres 4.0K May 13  2021 pg_serial
drwx------ 2 postgres postgres 4.0K May 13  2021 pg_snapshots
drwx------ 2 postgres postgres 4.0K May 13  2021 pg_tblspc
drwx------ 2 postgres postgres 4.0K May 13  2021 pg_twophase
-rw------- 1 postgres postgres    3 May 13  2021 PG_VERSION
drwx------ 2 postgres postgres 4.0K May 13  2021 pg_commit_ts

In short, a tablespace is a way to tell the PostgreSQL server where the physical files of database objects are stored.

Use the \db+ command in psql to list the tablespace details.

1
2
3
4
5
6
7

postgres=# \db+
                                  List of tablespaces
    Name    |  Owner   | Location | Access privileges | Options |  Size  | Description
------------+----------+----------+-------------------+---------+--------+-------------
 pg_default | postgres |          |                   |         | 31 MB  |
 pg_global  | postgres |          |                   |         | 559 kB |
(2 rows)

These two tablespaces (pg_default and pg_global) are created automatically after PostgreSQL is initialized. pg_default is the default tablespace for the template0 and template1 databases (and will therefore be the default tablespace for other databases); pg_global is the tablespace for the shared system catalog tables (pg_database, pg_authid, pg_tablespace, pg _shdepend, etc.) and their indexed tablespaces.

We notice that the above information does not have Location, because they always correspond to two subdirectories under the PostgreSQL data directory ($PGDATA): pg_default uses the base subdirectory, and pg_global uses the global subdirectory.

3 Using tablespaces

3.1 Creating a tablespace

To create a new tablespace, create a new empty folder (not under the PostgreSQL data folder $PGDATA), and the owner of the folder must be the postgres system user. The example is as follows.

1
2

$ mkdir -p /data/postgres/testspace
$ chown -R postgres:postgres /data/postgres/testspace

The superuser can use the CREATE TABLESPACE command to create a tablespace. The example is as follows.

1
2
3

$ psql -U postgres postgres

postgres=# CREATE TABLESPACE myspace LOCATION '/data/postgres/testspace';

At this point, consult the $PGDATA/pg_tblspc directory to see a symbolic link to the location of the folder corresponding to the new tablespace (the number 24577 is the OID of the tablespace).

1
2
3

$ ls -lht /usr/local/pgsql/data/pg_tblspc/

lrwxrwxrwx 1 postgres postgres 24 Mar 28 15:17 24577 -> /data/postgres/testspace

To allow a normal user to use a newly created tablespace, you must give the normal user the CREATE permission for that tablespace. The following example demonstrates the assignment of permissions to the normal user testuser.

1

postgres=# GRANT CREATE ON TABLESPACE myspace TO testuser;

Subsequently, all objects using the tablespace myspace will store their data in that folder (/data/postgres/testspace).

The following example demonstrates connecting to the database postgres using the normal user testuser, creating tables and assigning the tablespace myspace to them.

1
2
3

$ psql -U testuser postgres

postgres=> CREATE TABLE foo(id int) TABLESPACE myspace;

In addition to specifying tablespaces for tables, you can also specify tablespaces for indexes or databases. Examples are as follows.

1

postgres=> CREATE INDEX foo_idx ON foo(id) TABLESPACE myspace;

1

postgres=# CREATE DATABASE testdb TABLESPACE myspace;

3.2 Changing Tablespaces

Existing database objects can be moved from one tablespace to another using the corresponding ALTER statement.

The following example demonstrates the use of ALTER TABLE and ALTER INDEX to assign new tablespaces to tables and indexes.

1
2

postgres=> ALTER TABLE foo SET TABLESPACE pg_default;
postgres=> ALTER INDEX foo_idx SET TABLESPACE pg_default;

All tables or indexes in one tablespace can also be moved to another tablespace using the following statement.

1
2

postgres=> ALTER TABLE ALL IN TABLESPACE myspace SET TABLESPACE pg_default;
postgres=> ALTER INDEX ALL IN TABLESPACE myspace SET TABLESPACE pg_default;

When a tablespace is reassigned, the affected table or index is locked until the data move is complete.

3.3 Temporary tablespaces

Create two empty folders and set the owner to postgres.

1
2
3
4

$ mkdir /data/postgres/tempspace1
$ mkdir /data/postgres/tempspace2
$ chown -R postgres:postgres /data/postgres/tempspace1
$ chown -R postgres:postgres /data/postgres/tempspace2

Use superuser to create two new tablespaces, corresponding to the two folders just created; and assign CREATE permissions to the ordinary user testuser for these two tablespaces.

1
2
3
4
5
6
7

$ psql -U postgres postgres

postgres=# CREATE TABLESPACE tempspace1 LOCATION '/data/postgres/tempspace1';
postgres=# CREATE TABLESPACE tempspace2 LOCATION '/data/postgres/tempspace2';

postgres=# GRANT CREATE ON TABLESPACE tempspace1 TO testuser;
postgres=# GRANT CREATE ON TABLESPACE tempspace2 TO testuser;

Thus, logging in with a normal user, set the temp_tablespaces variable to tempspace1, tempspace2 (which can be set to multiple values).

1
2
3

$ psql -U testuser postgres

postgres=> SET temp_tablespaces = tempspace1, tempspace2;