August 1, 2023
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 the disk where PostgreSQL stores data files containing database objects e.g., indexes, and tables.
PostgreSQL uses a tablespace to map a logical name to a physical location on disk.
PostgreSQL comes with two default tablespaces:
pg_defaulttablespace stores user data.
pg_globaltablespace stores global data.
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 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.
PostgreSQL CREATE TABLESPACE statement
To create new tablespaces, you use the
CREATE TABLESPACE statement as follows:
CREATE TABLESPACE tablespace_name OWNER user_name LOCATION directory_path;
The name of the tablespace should not begin with
pg_, because these names are reserved for the system tablespaces.
By default, the user who executes the
CREATE TABLESPACE is the owner of the tablespace. To assign another user as the owner of the tablespace, you specify it after the
directory_path is the absolute path to an empty directory used for the tablespace. PostgreSQL system users 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 TABLE and
CREATE INDEX statements to store data files of the objects in the tablespace.
PostgreSQL CREATE TABLESPACE examples
The following statement uses the
CREATE TABLESPACE to create a new tablespace called
ts_primary with the physical location
CREATE TABLESPACE ts_primary LOCATION 'c:\pgdata\primary';
Notice that this statement used the Unix-style slashes for the directory path. And the c:\pgdata\primary must exist before you execute the command.
To list all tablespaces in the current PostgreSQL database server, you use the
List of tablespaces Name | Owner | Location ------------+----------+------------------- pg_default | postgres | pg_global | postgres | ts_primary | postgres | c:\pgdata\primary (4 rows)
\db+ command shows more information such as size and access privileges:
List of tablespaces Name | Owner | Location | Access privileges | Options | Size | Description ------------+----------+-------------------+-------------------+---------+---------+------------- pg_default | postgres | | | | 124 MB | pg_global | postgres | | | | 625 kB | ts_primary | postgres | c:\pgdata\primary | | | 0 bytes | (4 rows)
The following statement creates the logistics database that uses the ts_primary tablespace:
CREATE DATABASE logistics TABLESPACE ts_primary;
TABLESPACE clause indicates the tablespace that the
logistics database will use to store data.
CREATE TABLE deliveries ( delivery_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, order_date DATE, customer_id INT ); INSERT INTO deliveries(order_date, customer_id) VALUES('2020-08-01',1);
ts_primary tablespace has some data, you can view its information using the following command in psql:
Name | Owner | Location | Access privileges | Options | Size | Description ------------+----------+-------------------+-------------------+---------+---------+------------- ts_primary | postgres | c:\pgdata\primary | | | 8033 kB | (1 row)
- A tablespace is a location on the storage device where PostgreSQL stores data files.
- Use the
CREATE TABLESAPCEstatement to create a new tablespace.