# Занятие 10: Организация данных / Табличные пространства > Исходная страница в нативном Markdown: Организация данных / Табличные пространства --- Индекс LLMS: [llms.txt](/ru/llms.txt) --- > [!NOTE] > Эта страница извлечена из официального PDF в нативный Markdown. Текст слайдов, код и комментарии сохранены в порядке чтения; исходные PDF и HTML остаются эталоном макета. > **HTML source:** [dba1_10_data_tablespaces.html](https://edu.postgrespro.ru/16/dba1-16/dba1_10_data_tablespaces.html) > **Archive:** [DBA1-handouts-16.zip](https://edu.postgrespro.ru/16/DBA1-handouts-16.zip) > **Страниц PDF:** 15 ## Page 1: Организация данных / Табличные пространства {#source-page-001} ### Авторские права © Postgres Professional, 2017–2024 Авторы: Егор Рогов, Павел Лузанов, Илья Баштанов, Алексей Береснев Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал) ### Использование материалов курса Некоммерческое использование материалов курса (презентации, демонстрации) разрешается без ограничений. Коммерческое использование возможно только с письменного разрешения компании Postgres Professional. Запрещается внесение изменений в материалы курса. ### Обратная связь Отзывы, замечания и предложения направляйте по адресу: edu@postgrespro.ru ### Отказ от ответственности Компания Postgres Professional не несет никакой ответственности за любые повреждения и убытки, включая потерю дохода, нанесенные прямым или непрямым, специальным или случайным использованием материалов курса. Компания Postgres Professional не предоставляет каких-либо гарантий на материалы курса. Материалы курса предоставляются на основе принципа «как есть» и компания Postgres Professional не обязана предоставлять сопровождение, поддержку, обновления, расширения и изменения. ## Page 2: Темы {#source-page-002} - Табличные пространства и каталоги - Создание, изменение и удаление табличных пространств - Хранение данных в файловой системе - Перемещение данных ## Page 3: Табличные пространства {#source-page-003} ![Source slide page 3: Табличные пространства](images/page-003.png){#source-figure-003 caption="Source slide page 3: Табличные пространства"} ### Комментарии преподавателя Табличные пространства (ТП) служат для организации физического хранения данных и определяют расположение данных в файловой системе. Например, можно создать одно ТП на медленных дисках для архивных данных, а другое — на быстрых дисках для данных, с которыми идет активная работа. При инициализации кластера создаются два ТП: pg\_default и pg\_global. Одно и то же ТП может использоваться разными базами данных, а одна база данных может хранить данные в нескольких ТП. При этом у каждой БД есть так называемое «ТП по умолчанию», в котором создаются все объекты, если не указано иное. В этом же ТП хранятся и объекты системного каталога. Изначально в качестве «ТП по умолчанию» используется ТП pg\_default, но можно установить и другое. ТП pg\_global особенное: в нем хранятся те объекты системного каталога, которые являются общими для кластера. ## Page 4: Каталоги {#source-page-004} ![Source slide page 4: Каталоги](images/page-004.png){#source-figure-004 caption="Source slide page 4: Каталоги"} ```text PGDATA/global/ ``` ```text PGDATA/base/dboid/ ``` ```text PGDATA/pg_tblspc/tsoid ``` ```text /путь-к-каталогу/ver/dboid/ ``` ### Комментарии преподавателя По сути, табличное пространство — это указание на каталог, в котором располагаются данные. Стандартные ТП pg\_global и pg\_default всегда находятся в PGDATA/global/ и PGDATA/base/ соответственно. При создании пользовательского ТП указывается произвольный каталог; для собственного удобства PostgreSQL создает на него символическую ссылку в каталоге PGDATA/pg\_tblspc/. Внутри каталога PGDATA/base/ данные дополнительно разложены по подкаталогам баз данных (для PGDATA/global/ это не требуется, так как данные в нем относятся к кластеру в целом). Внутри каталога пользовательского ТП появляется еще один уровень вложенности: версия сервера PostgreSQL. Это сделано для удобства обновления сервера на другую версию. Собственно объекты хранятся в файлах внутри этих каталогов — каждый объект в отдельных файлах. ## Page 5: Системные табличные пространства {#source-page-005} При создании кластера создаются два табличных пространства: ```sql => SELECT * FROM pg_tablespace; ``` ```text oid | spcname | spcowner | spcacl | spcoptions ------+------------+----------+--------+------------ 1663 | pg_default | 10 | | 1664 | pg_global | 10 | | (2 rows) ``` pg\_global — общие объекты кластера; pg\_default — табличное пространство по умолчанию. ### Пользовательские табличные пространства Для нового табличного пространства нужен пустой каталог, владельцем которого является пользователь postgres. ```sh student$ sudo -u postgres mkdir /var/lib/postgresql/ts_dir ``` Теперь можно создать табличное пространство: ```sql => CREATE TABLESPACE ts LOCATION '/var/lib/postgresql/ts_dir'; ``` ```sql CREATE TABLESPACE ``` Список табличных пространств можно получить и командой psql: ```text => \db ``` ```text List of tablespaces Name | Owner | Location ------------+----------+---------------------------- pg_default | postgres | pg_global | postgres | ts | student | /var/lib/postgresql/ts_dir (3 rows) ``` У каждой базы данных есть табличное пространство «по умолчанию». Создадим базу и назначим ей ts в качестве такого пространства: ```sql => CREATE DATABASE appdb TABLESPACE ts; ``` ```sql CREATE DATABASE ``` Теперь все создаваемые таблицы и индексы будут попадать в ts, если явно не указать другое. Подключимся к базе: ```sql => \c appdb ``` ```text You are now connected to database "appdb" as user "student". ``` Создадим таблицу: ```sql => CREATE TABLE t1( id integer GENERATED ALWAYS AS IDENTITY, name text ); ``` ```sql CREATE TABLE ``` При создании объекта можно указать табличное пространство явно: ```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) ``` Пустое поле tablespace указывает на табличное пространство по умолчанию, а у второй таблицы поле заполнено. Для индекса также можно указать табличное пространство: ```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) ``` Еще один способ задать табличное пространство без явного указания при создании объекта — предварительно установить значение параметра default\_tablespace. Одно табличное пространство может использоваться для объектов нескольких баз данных. ```sql => CREATE DATABASE configdb; ``` ```sql CREATE DATABASE ``` У этой БД табличным пространством по умолчанию будет 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" ``` Для временных таблиц и их индексов можно указать отдельное табличное пространство по умолчанию: ```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" ``` В параметре temp\_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) ``` Перенести индексы можно и при перестроении: ```text => REINDEX (TABLESPACE ts) TABLE t1; ``` ```text REINDEX ``` Можно переместить и все объекты из одного табличного пространства в другое: ```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) ``` > [!NOTE] > **Note** > Важно понимать, что перенос в другое табличное пространство (в отличие от переноса в другую схему) — физическая операция, связанная с копированием файлов данных из каталога в каталог. На время ее выполнения доступ к перемещаемому объекту полностью блокируется. ### Размер табличного пространства Мы уже рассматривали, как узнать объем, занимаемый базой данных. Можно узнать и объем объектов в табличном пространстве: ```sql => SELECT pg_size_pretty( pg_tablespace_size('ts') ); ``` ```text pg_size_pretty ---------------- 7588 kB (1 row) ``` Почему размер так велик, хотя в табличном пространстве всего несколько пустых таблиц? Поскольку ts является табличным пространством по умолчанию для базы appdb, в нем хранятся объекты системного каталога. Они и занимают место. В psql получить размер табличных пространств можно командой: ```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) ``` ### Удаление табличного пространства Табличное пространство можно удалить, но только в том случае, если оно пусто: ```sql => DROP TABLESPACE ts; ``` ## Page 8: Page 8 {#source-page-008} ```text ERROR: tablespace "ts" is not empty ``` В отличие от удаления схемы, в команде DROP TABLESPACE нельзя использовать предложение CASCADE: объекты табличного пространства могут принадлежать разным базам данных, а подключены мы только к одной. Но можно выяснить, в каких базах есть зависимые объекты. В этом нам поможет системный каталог. Сначала узнаем и запомним идентификатор табличного пространства: ```sql => SELECT oid FROM pg_tablespace WHERE spcname = 'ts'; ``` ```text oid ------- 16386 (1 row) ``` Затем получим список баз данных, в которых есть объекты из удаляемого пространства: ```sql => SELECT datname FROM pg_database WHERE oid IN (SELECT pg_tablespace_databases(16386)); ``` ```text datname ---------- configdb appdb (2 rows) ``` Дальше подключаемся к каждой базе данных и получаем список объектов из 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) ``` Таблица больше не нужна, удалим ее. ```sql => DROP TABLE t; ``` ```sql DROP TABLE ``` И вторая база данных. Поскольку ts является табличным пространством по умолчанию, у объектов в pg\_class идентификатор табличного пространства равен нулю. Это, как нам уже известно, объекты системного каталога: ```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) ``` Табличное пространство по умолчанию можно сменить; при этом все таблицы из старого пространства физически переносятся в новое. Предварительно надо отключиться от базы. ```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 ``` Вот теперь табличное пространство может быть удалено. ```sql => DROP TABLESPACE ts; ``` ```sql DROP TABLESPACE ``` ## Page 9: Page 9 {#source-page-009} От имени пользователя postgres удалим каталог, созданный для табличного пространства. ```sh student$ sudo -u postgres rm -rf /var/lib/postgresql/ts_dir ``` ## Page 10: Итоги {#source-page-010} **Табличные пространства — средство для организации физического хранения данных** - Логическое (базы данных, схемы) и физическое (табличные пространства) разделения данных независимы ## Page 11: Практика {#source-page-011} Почему при создании базы данных без предложения TABLESPACE табличным пространством по умолчанию становится pg\_default? - 1. Создайте новое табличное пространство. - 2. Измените табличное пространство по умолчанию для базы данных template1 на созданное пространство. - 3. Создайте новую базу данных. Проверьте, какое табличное пространство по умолчанию установлено для новой базы данных. - 4. Найдите в PGDATA символическую ссылку на каталог табличного пространства. - 5. Удалите созданное табличное пространство. ## Page 12: 1. Новое табличное пространство {#source-page-012} Каждая база данных имеет табличное пространство по умолчанию. В том числе и template1: ```text => \x \l+ template1 \x ``` ```text Expanded display is on. List of databases -[ RECORD 1 ]-----+----------------------------------- Name | template1 Owner | postgres Encoding | UTF8 Locale Provider | libc Collate | en_US.UTF-8 Ctype | en_US.UTF-8 ICU Locale | ICU Rules | Access privileges | =c/postgres + | postgres=CTc/postgres Size | 7516 kB Tablespace | pg_default Description | default template for new databases ``` ```text Expanded display is off. ``` По умолчанию новые базы данных создаются копированием template1, поэтому они получают такое же табличное пространство, как установлено для template1. Создадим новое табличное пространство. ```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. Табличное пространство по умолчанию для template1 ```sql => ALTER DATABASE template1 SET TABLESPACE ts; ``` ```sql ALTER DATABASE ``` ### 3. Новая база данных и проверка ```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) ``` Табличное пространство по умолчанию — ts. Вывод: если нет явного указания, табличное пространство по умолчанию определяется шаблоном, из которого клонируется новая база данных. ### 4. Символическая ссылка ```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 апр 10 17:10 ``` ## Page 13: 5. Удаление табличного пространства {#source-page-013} ```text /var/lib/postgresql/16/main/pg_tblspc/16386 -> /var/lib/postgresql/ts_dir ``` ```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 14: Практика+ {#source-page-014} 1. Установите параметр random\_page\_cost в значение 1.1 для табличного пространства pg\_default. ### Комментарии преподавателя 1. Используйте команду ALTER TABLESPACE ... SET: Параметры seq\_page\_cost и random\_page\_cost используются планировщиком запросов и задают примерную стоимость чтения с диска одной страницы данных при последовательном и произвольном доступе соответственно. Чем меньше соотношение между этими параметрами, тем чаще планировщик будет предпочитать индексный доступ последовательному сканированию таблицы. Более подробно параметры \*\_cost, и в частности random\_page\_cost, рассматриваются в курсе QPT «Оптимизация запросов». ## Page 15: 1. Установка random_page_cost для табличного пространства {#source-page-015} Значения по умолчанию параметров seq\_page\_cost и random\_page\_cost больше подходят для медленных HDD-дисков. Предполагается, что доступ к произвольной странице данных в четыре раза дороже последовательного: ```text => \dconfig *page_cost ``` ```text List of configuration parameters Parameter | Value ------------------+------- random_page_cost | 4 seq_page_cost | 1 (2 rows) ``` Если используются диски с разными характеристиками, для них можно создать разные табличные пространства и настроить подходящие соотношения этих параметров. Например для быстрых SSD-дисков значение random\_page\_cost можно уменьшить практически до значения seq\_page\_cost. ```sql => ALTER TABLESPACE pg_default SET (random_page_cost = 1.1); ``` ```sql ALTER TABLESPACE ``` Настройки, сделанные командой ALTER TABLESPACE, сохраняются в таблице pg\_tablespace. Их можно посмотреть в psql следующей командой: ```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) ``` Параметры \*\_cost можно установить и в postgresql.conf. Тогда они будут действовать для всех табличных пространств.