# Lesson 10: Data Organization / Tablespaces > Native Markdown source page: Data Organization / Tablespaces --- LLMS index: [llms.txt](/en/llms.txt) --- > [!NOTE] > This page is extracted from the official PDF into native Markdown. Slide text, code and speaker notes are kept in reading order; the original PDF and HTML remain the layout references. > **HTML source:** [dba1_10_data_tablespaces.html](https://edu.postgrespro.com/16/dba1-16-en/dba1_10_data_tablespaces.html) > **Archive:** [DBA1-handouts-16-en.zip](https://edu.postgrespro.com/16/DBA1-handouts-16-en.zip) > **PDF pages:** 14 ## Page 1: Data Organization / Tablespaces {#source-page-001} ### Copyright © Postgres Professional, 2017–2025 Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov, Aleksei 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. ## Page 2: Topics {#source-page-002} - Tablespaces and Directories - Creating, Modifying, and Deleting Tablespaces - Storing Data in the File Hierarchy - Moving Data ## Page 3: Tablespaces {#source-page-003} ![Source slide page 3: Tablespaces](images/page-003.png){#source-figure-003 caption="Source slide page 3: Tablespaces"} ### Speaker notes 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. ## Page 4: Directories {#source-page-004} ![Source slide page 4: Directories](images/page-004.png){#source-figure-004 caption="Source slide page 4: Directories"} ```text PGDATA/global/ ``` ```text PGDATA/base/dboid/ ``` ```text PGDATA/pg_tblspc/tsoid ``` ```text /path-to-catalog/ver/dboid/ ``` ### Speaker notes 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. ## Page 5: System Tablespaces {#source-page-005} Upon a cluster initialization, two tablespaces are created: ```sql => SELECT * FROM pg_tablespace; ``` ```text oid | spcname | spcowner | spcacl | spcoptions ------+------------+----------+--------+------------ 1663 | pg_default | 10 | | 1664 | pg_global | 10 | | (2 rows) ``` pg\_global — shared cluster objects pg\_default — default tablespace ### Custom Tablespaces A new tablespace needs an empty directory owned by the user postgres. ```sh student$ sudo -u postgres mkdir /var/lib/postgresql/ts_dir ``` Now, a new tablespace can be created: ```sql => CREATE TABLESPACE ts LOCATION '/var/lib/postgresql/ts_dir'; ``` ```sql CREATE TABLESPACE ``` The following psql command returns a list of all tablespaces: ```text => \db ``` ```text List of tablespaces Name | Owner | Location ------------+----------+---------------------------- pg_default | postgres | pg_global | postgres | ts | student | /var/lib/postgresql/ts_dir (3 rows) ``` Each database has a “default” tablespace. Let’s create a database and assign ts as its default: ```sql => CREATE DATABASE appdb TABLESPACE ts; ``` ```sql CREATE DATABASE ``` This makes all tables and indexes created within the database fall into ts, unless specified otherwise. Connect to the database: ```sql => \c appdb ``` ```text You are now connected to database "appdb" as user "student". ``` Create a table: ```sql => CREATE TABLE t1( id integer GENERATED ALWAYS AS IDENTITY, name text ); ``` ```sql CREATE TABLE ``` When creating objects, you may explicitly specify a tablespace for it: ```sql => CREATE TABLE t2( n numeric ) TABLESPACE pg_default; ``` ```sql CREATE TABLE ``` ```sql => SELECT tablename, tablespace FROM pg_tables WHERE schemaname = 'public'; ``` ## Page 6: Page 6 {#source-page-006} ```text tablename | tablespace -----------+------------ t1 | t2 | pg_default (2 rows) ``` An empty tablespace field means that the default tablespace is used. The second table has this field filled in. You can also specify a tablespace for an index: ```sql => CREATE INDEX ON t1(id) TABLESPACE pg_default; ``` ```sql CREATE INDEX ``` ```sql => SELECT indexname,tablespace FROM pg_indexes WHERE tablename='t1'; ``` ```text indexname | tablespace -----------+------------ t1_id_idx | pg_default (1 row) ``` Another way to assign a tablespace without defining it at object creation is to preemptively set a value of default\_tablespace parameter. A single tablespace may contain objects from multiple databases. ```sql => CREATE DATABASE configdb; ``` ```sql CREATE DATABASE ``` This database’s default tablespace will be pg\_default. ```sql => \c configdb ``` ```text You are now connected to database "configdb" as user "student". ``` ```sql => CREATE TABLE t( n integer ) TABLESPACE ts; ``` ```sql CREATE TABLE ``` ```sql => \d t ``` ```text Table "public.t" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- n | integer | | | Tablespace: "ts" ``` For temporary tables and their indexes, you can specify a separate tablespace as the default: ```text => SET temp_tablespaces = 'ts'; ``` ```text SET ``` ```sql => CREATE TEMP TABLE temp(s text); ``` ```sql CREATE TABLE ``` ```sql => \d temp ``` ```text Table "pg_temp_3.temp" Column | Type | Collation | Nullable | Default --------+------+-----------+----------+--------- s | text | | | Tablespace: "ts" ``` The temp\_tablespaces parameter accepts multiple tablespace names. When set, the server selects one randomly for each temporary object. ### Managing Objects within Tablespaces Tables (and other objects, such as indexes) can be moved between tablespaces. ```sql => \c appdb ``` ```text You are now connected to database "appdb" as user "student". ``` ## Page 7: Page 7 {#source-page-007} ```sql => ALTER TABLE t1 SET TABLESPACE pg_default; ``` ```sql ALTER TABLE ``` ```sql => SELECT tablename, tablespace FROM pg_tables WHERE schemaname = 'public'; ``` ```text tablename | tablespace -----------+------------ t2 | pg_default t1 | pg_default (2 rows) ``` Indexes can also be moved to a different tablespace during rebuild: ```text => REINDEX (TABLESPACE ts) TABLE t1; ``` ```text REINDEX ``` You can move all objects from one tablespace to another: ```sql => ALTER TABLE ALL IN TABLESPACE pg_default SET TABLESPACE ts; ``` ```sql ALTER TABLE ``` ```sql => SELECT tablename, tablespace FROM pg_tables WHERE schemaname = 'public'; ``` ```text tablename | tablespace -----------+------------ t2 | t1 | (2 rows) ``` Keep in mind that moving objects between tablespaces (unlike moving between schemas) is a physical operation that involves moving actual files from one directory to another. Access to the objects being moved is completely blocked for the duration of the operation. ### Tablespace Size We already know how to get the size of a database. Now, we can learn how to get the size of objects in a tablespace: ```sql => SELECT pg_size_pretty( pg_tablespace_size('ts') ); ``` ```text pg_size_pretty ---------------- 7588 kB (1 row) ``` Why is the size so large, despite the tablespace containing just a few empty tables? This is because ts is the default tablespace for the database appdb, so it is where the system catalog objects are stored. They take up space. You can get the size of the tablespaces by the psql command: ```text => \db+ ``` ```text List of tablespaces Name | Owner | Location | Access privileges | Options | Size | Description ------------+----------+----------------------------+-------------------+---------+------- --+------------- pg_default | postgres | | | | 36 MB | pg_global | postgres | | | | 589 kB | ts | student | /var/lib/postgresql/ts_dir | | | 7588 kB | (3 rows) ``` ### Dropping a Tablespace You can only delete a tablespace that is empty: ```sql => DROP TABLESPACE ts; ``` ## Page 8: Page 8 {#source-page-008} ```text ERROR: tablespace "ts" is not empty ``` Unlike with schemas, there is no CASCADE clause in the DROP TABLESPACE command. Objects within the tablespace may belong to multiple databases, while we are only connected to one. You can still figure out what databases contain dependent objects. This is where the system catalog comes in. First, find out and remember the tablespace ID: ```sql => SELECT oid FROM pg_tablespace WHERE spcname = 'ts'; ``` ```text oid ------- 16386 (1 row) ``` Next, get a list of databases that have objects in the tablespace we are going to remove: ```sql => SELECT datname FROM pg_database WHERE oid IN (SELECT pg_tablespace_databases(16386)); ``` ```text datname ---------- configdb appdb (2 rows) ``` Then, connect to each of the databases and get a list of objects from pg\_class: ```sql => \c configdb ``` ```text You are now connected to database "configdb" as user "student". ``` ```sql => SELECT relnamespace::regnamespace, relname, relkind FROM pg_class WHERE reltablespace = 16386; ``` ```text relnamespace | relname | relkind --------------+---------+--------- public | t | r (1 row) ``` The table is no longer needed, drop it. ```sql => DROP TABLE t; ``` ```sql DROP TABLE ``` Now, for the second database. Since ts is the default tablespace, the tablespace ID of the objects in pg\_class equals zero. These are the system catalog objects, as we already know: ```sql => \c appdb ``` ```text You are now connected to database "appdb" as user "student". ``` ```sql => SELECT count(*) FROM pg_class WHERE reltablespace = 0; ``` ```text count ------- 371 (1 row) ``` You can set another tablespace as default. This will physically move all the tables into the new one. You need to disconnect from the database first. ```sql => \c postgres ``` ```text You are now connected to database "postgres" as user "student". ``` ```sql => ALTER DATABASE appdb SET TABLESPACE pg_default; ``` ```sql ALTER DATABASE ``` Finally, the tablespace can be deleted. ```sql => DROP TABLESPACE ts; ``` ```sql DROP TABLESPACE ``` ## Page 9: Page 9 {#source-page-009} On behalf of the postgres user, we remove the directory created for the tablespace. ```sh student$ sudo -u postgres rm -rf /var/lib/postgresql/ts_dir ``` ## Page 10: Takeaways {#source-page-010} **Tablespaces organize physical data storage** - Logical (databases, schemas) and physical (tablespaces) forms of data organization are independent ## Page 11: Practice {#source-page-011} 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. ## Page 12: 1. New Tablespace {#source-page-012} ```sh student$ sudo -u postgres mkdir /var/lib/postgresql/ts_dir ``` ```sql => CREATE TABLESPACE ts LOCATION '/var/lib/postgresql/ts_dir'; ``` ```sql CREATE TABLESPACE ``` ### 2. Default Tablespace for template1 ```sql => ALTER DATABASE template1 SET TABLESPACE ts; ``` ```sql ALTER DATABASE ``` ### 3. New Database, Verification ```sql => CREATE DATABASE db; ``` ```sql CREATE DATABASE ``` ```sql => SELECT spcname FROM pg_tablespace WHERE oid = (SELECT dattablespace FROM pg_database WHERE datname = 'db'); ``` ```text spcname --------- ts (1 row) ``` The default tablespace is ts. Conclusion: the default tablespace is determined by the template from which the new database is cloned. ### 4. Symbolic Link ```sql => SELECT oid AS tsoid FROM pg_tablespace WHERE spcname = 'ts'; ``` ```text tsoid ------- 16386 (1 row) ``` ```sh student$ sudo -u postgres ls -l /var/lib/postgresql/16/main/pg_tblspc/16386 ``` ```text lrwxrwxrwx 1 postgres postgres 26 Nov 27 13:59 /var/lib/postgresql/16/main/pg_tblspc/16386 -> /var/lib/postgresql/ts_dir ``` ### 5. Dropping the Tablespace ```sql => ALTER DATABASE template1 SET TABLESPACE pg_default; ``` ```sql ALTER DATABASE ``` ```sql => DROP DATABASE db; ``` ```sql DROP DATABASE ``` ```sql => DROP TABLESPACE ts; ``` ```sql DROP TABLESPACE ``` ```sh student$ sudo -u postgres rm -rf /var/lib/postgresql/ts_dir ``` ## Page 13: Practice+ {#source-page-013} 1. Set the random\_page\_cost parameter for the pg\_default tablespace to 1.1. ### Speaker notes 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). ## Page 14: 1. Setting Random_page_cost for a Tablespace {#source-page-014} The default seq\_page\_cost and random\_page\_cost values are suitable for slower HDD drives. It is assumed that random page access is four times as costly as sequential page access: ```text => \dconfig *page_cost ``` ```text List of configuration parameters Parameter | Value ------------------+------- random_page_cost | 4 seq_page_cost | 1 (2 rows) ``` If you use drives with different properties, you can create different tablespaces with appropriate seq\_page\_cost and random\_page\_cost values for each. For example, quick SSD drives can have the random\_page\_cost value almost as low as seq\_page\_cost. ```sql => ALTER TABLESPACE pg_default SET (random_page_cost = 1.1); ``` ```sql ALTER TABLESPACE ``` Configuration adjustments made using the ALTER TABLESPACE command are stored in the pg\_tablespace table. You can view them in psql with the following command: ```text => \db+ ``` ```text List of tablespaces Name | Owner | Location | Access privileges | Options | Size | Description ------------+----------+----------+-------------------+------------------------+--------+- ------------ pg_default | postgres | | | {random_page_cost=1.1} | 29 MB | pg_global | postgres | | | | 589 kB | (2 rows) ``` The \*\_cost parameters can also be set in postgresql.conf. This will apply them to all tablespaces.