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.
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.
The contents of the folder are as follows.
In short, a tablespace is a way to tell the PostgreSQL server where the physical files of database objects are stored.
\db+ command in
psql to list the tablespace details.
These two tablespaces (
pg_global) are created automatically after PostgreSQL is initialized.
pg_default is the default tablespace for the
template1 databases (and will therefore be the default tablespace for other databases);
pg_global is the tablespace for the shared system catalog tables (
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 (
pg_default uses the
base subdirectory, and
pg_global uses the
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.
The superuser can use the
CREATE TABLESPACE command to create a tablespace. The example is as follows.
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).
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
Subsequently, all objects using the tablespace
myspace will store their data in that folder (
The following example demonstrates connecting to the database
postgres using the normal user
testuser, creating tables and assigning the tablespace
myspace to them.
In addition to specifying tablespaces for tables, you can also specify tablespaces for indexes or databases. Examples are as follows.
3.2 Changing Tablespaces
Existing database objects can be moved from one tablespace to another using the corresponding
The following example demonstrates the use of
ALTER TABLE and
ALTER INDEX to assign new tablespaces to tables and indexes.
All tables or indexes in one tablespace can also be moved to another tablespace using the following statement.
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
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.
Thus, logging in with a normal user, set the
temp_tablespaces variable to
tempspace1, tempspace2 (which can be set to multiple values).