Data Organization
Tablespaces
16
Copyright
© Postgres Professional, 2017–2025
Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov, Alexey Beresnev
Translated by: Liudmila Mantrova, Alexander Meleshko, Elena Sharafutdinova
Photo: Oleg Bartunov (Phu Monastery and Bhrikuti Peak, Nepal)
Use of Course Materials
Non-commercial use of course materials (presentations, demonstrations) is
allowed without restrictions. Commercial use is possible only with the written
permission of Postgres Professional. It is prohibited to make changes to the
course materials.
Feedback
Please send your feedback, comments and suggestions to:
edu@postgrespro.ru
Disclaimer
Postgres Professional assumes no responsibility for any damages and losses,
including loss of income, caused by direct or indirect, intentional or accidental
use of course materials. Postgres Professional company specifically disclaims
any warranties on course materials. Course materials are provided “as is,” and
Postgres Professional company has no obligations to provide maintenance,
support, updates, enhancements, or modifications.
2
Topics
Tablespaces and Directories
Creating, Modifying, and Deleting Tablespaces
Storing Data in the File Hierarchy
Moving Data
3
Tablespaces
appdb
pg_catalog public
postgres
pg_catalog public
schema
pg_global
tablespace
tablespace
pg_default
tablespace
table
table
object
table
table
object
table
table
object
table
table
object
table
table
object
table
table
object
table
table
object
table
table
object
table
table
object
table
table
object
table
table
object
default
tablespace
default
tablespace
Tablespaces are used to organize the physical storage of data and
determine the location of data in the file hierarchy.
For example, one tablespace can use slow disks for archived data, and
another one can use fast disks with frequent activity.
On cluster initialization, two tablespaces are created: pg_default and
pg_global.
A tablespace can be used by multiple databases, and a database can use
multiple tablespaces at once.
Each database has a default tablespace where all database objects are
created (unless specified otherwise). System catalog objects are also stored
in the default tablespace. Databases will use the pg_default tablespace as
their default, unless another one is set by the user.
The pg_global tablespace is special as it stores only those objects that are
shared across the whole cluster.
4
Directories
appdb
postgres
pg_global
tablespace
tablespace
pg_default
tablespace
table
table
object
table
table
object
table
table
object
table
table
object
table
table
object
table
table
object
table
table
object
PGDATA/global/
PGDATA/base/dboid/
PGDATA/pg_tblspc/tsoid
/path-to-catalog/ver/dboid/
Essentially, a tablespace is a reference to the directory in which the data is
located. The standard tablespaces pg_global and pg_default are always
located in PGDATA/global/ and PGDATA/base/, respectively. When a custom
tablespace is created, an arbitrary catalog can be specified. For its
convenience, PostgreSQL also creates a symbolic link to the directory in
PGDATA/pg_tblspc/.
The PGDATA/base/ directory comprises different directories for each
database (unlike PGDATA/global/, which stores data referring to the whole
cluster).
Inside a custom tablespace directory, there is another level for different
PostgreSQL server versions. This is helpful during server upgrade.
Actual objects are stored in files in these directories, one or more files per
object.
6
Takeaways
Tablespaces organize physical data storage
Logical (databases, schemas) and physical (tablespaces) forms
of data organization are independent
7
Practice
Why does pg_default become the default tablespace when creating
a database without the TABLESPACE keyword?
1. Create a new tablespace.
2. Set it as the default tablespace for the template1 database.
3. Create a new database.
Check which default tablespace is set for the new database.
4. Find the symbolic link to the tablespace directory in PGDATA.
5. Delete the created tablespace.
8
Practice+
1. Set the random_page_cost parameter for the pg_default
tablespace to 1.1.
1. Use the ALTER TABLESPACE ... SET command:
The seq_page_cost and random_page_cost parameters are used by the
planner. They refer to the approximate cost of reading one page of data from
disk with sequential and random access, respectively.
The lower the ratio between these parameters, the more often the planner
will prefer index access over sequential table scanning.
The parameters *_cost and, more specifically, random_page_cost are
discussed in more detail in the Query Optimization course (QPT).
