Объекты в табличном пространстве

При изучении табличных пространств упоминалось хранение объектов внутри каталогов пространств, но подробно оно не рассматривалось. В этом разделе будут изучены способы создания и перемещения объектов в табличных пространствах, а также инструменты для получения информации о них.

Создание объекта в табличном пространстве

В прошлом разделе был приведен пример создания базы данных в табличном пространстве, теперь рассмотрим примеры создания двух самых распространенных объектов — таблиц и индексов, с указанием пространства.

Перед началом заново подготовим табличное пространство. Для удобства воспользуемся уже знакомым newtabspace.

[postgres@ServerName ~]$ mkdir -p /pgdata/06/newdisk

postgres@postgres=# CREATE TABLESPACE newtabspace LOCATION '/pgdata/06/newdisk';

CREATED TABLESPACE

Создание объекта в ТП по умолчанию

При создании любого объекта всегда используется табличное пространство. Без явного указания объект будет размещен в пространстве, которое считается табличным пространством по умолчанию для текущей базы данных. В нашем случае для базы sch_db это как раз pg_default.

Рассмотрим этот случай на примере таблицы. Создадим таблицу tab_in_def.

postgres@sch_db=# CREATE TABLE tab_in_def ( id integer, msg text );

CREATE TABLE

Проверим информацию по созданной таблице с помощью каталога pg_tables. В нем содержится информация о всех таблицах текущей базы данных.

postgres@sch_db=# SELECT * FROM pg_tables WHERE tablename = 'tab_in_def' \gx

-[ RECORD 1 ]-------------
schemaname  | public
tablename   | tab_in_def
tableowner  | postgres
tablespace  |
hasindexes  | f
hasrules    | f
hastriggers | f
rowsecurity | f

Обратите внимание на поле tablespace. В нем указывается название пространства, в котором лежит объект. Если используется пространство по умолчанию, как в нашем случае, оно будет пустым. При использовании пользовательского — будет написано имя пространства.

Создание объекта в пользовательском ТП

Для реализации возможности удобной организации данных при создании таблиц, индексов и материализованных представлений можно явно указать, в каком табличном пространстве они должны размещать свои данные.

Создадим для таблицы tab_in_def индекс в пространстве newtabspace. Для этого нужно указать ключевое слово TABLESPACE и имя желаемого пространства.

postgres@sch_db=# CREATE INDEX ON tab_in_def(msg) TABLESPACE newtabspace;

CREATE INDEX

Проверим информацию по созданному индексу с помощью каталога pg_indexes. В нем содержится основная информация об индексах.

postgres@sch_db=# SELECT * FROM pg_indexes WHERE tablename = 'tab_in_def' \gx

-[ RECORD 1 ]--------------------------------------------------------------------------
schemaname | public
tablename  | tab_in_def
indexname  | tab_in_def_msg_idx
tablespace | newtabspace
indexdef   | CREATE INDEX tab_in_def_msg_idx ON public.tab_in_def USING btree (msg)

Как видно, в поле tablespace записано пространство newtabspace. Теперь сама таблица tab_in_def находится в пространстве pg_default, а ее индекс — newtabspace.

Посмотрим на полный путь к индексу и его файлу.

postgres@sch_db=# SELECT pg_relation_filepath('tab_in_def_msg_idx');

            pg_relation_filepath
---------------------------------------------
 pg_tblspc/16447/PG_15_202310091/16409/16446
(1 row)

postgres@sch_db=# \! ls -l $PGDATA/pg_tblspc/16447/PG_15_202310091/16409/16446

-rw------- 1 postgres postgres 8192 Oct 14 12:00 /pgdata/06/data/pg_tblspc/16447/PG_15_202310091/16409/16446

Обратите внимание на отличие путей при newtabspace и фактическом пути к tab_in_def_msg_idx. При создании табличного пространства указывается путь к каталогу в ОС, то есть физическое расположение данных. Для ускорения работы PostgreSQL формирует собственное дерево каталогов внутри указанного физического на основе уникальных идентификаторов. Посмотрим на вложенные в него каталоги.

• 16447 — oid табличного пространства newtabspace. Это ссылка на путь к физическому каталогу newtabspace;
• PG_15_202310091 — название подкаталога для базы данных sch_db. Имя подкаталога состоит из обозначения продукта (PostgreSQL — PG), версии продукта (15) и даты компиляции версии PostgreSQL (202310091);
• 16409 — oid базы данных sch_db;
• 16446 — oid объекта, в нашем случае индекса.

Для получения абсолютного пути до tab_in_def_msg_idx нужно объединить путь в ОС и PostgreSQL: /pgdata/06/newdisk/PG_15_202310091/16409/16446.

Почему размер файла индекса 8 КБ?

Несмотря на то, что сама таблица tab_in_def пуста, физический размер файла с данными индекса 8 КБ — это ровно одна страница. Первая страница индекса всегда содержит структуры, необходимые для самого индекса, поэтому его размер не может быть равен нулю.

Хранение объекта в табличном пространстве

После создания объектов в табличных пространствах возникает задача — отслеживать, где именно хранятся их данные. В этом разделе рассмотрим способы получения информации о расположении объектов и их табличных пространствах: пути к файлам, слои, системные каталоги и утилиты диагностики.

Информация об объекте

При работе с данными порой необходимо узнать путь к файлу или посмотреть на его слои. Узнать путь относительно $PGDATA можно с помощью функции pg_relation_filepath(). Применив этот путь в системной оболочке можно посмотреть на размер файла и его слои.

postgres@sch_db=# SELECT pg_relation_filepath('tab_in_def');

pg_relation_filepath
---------------------
    base/16409/16441
(1 row)

postgres@sch_db=# \! ls -l $PGDATA/base/16409/16441

-rw------- 1 postgres postgres 0 Oct 14 10:16 /pgdata/06/data/base/16409/16441

Команда oid2name, как и предыдущие, ранее уже встречалась. Однако с помощью ее опций можно не только вывести общую информацию о базах данных и их табличных пространствах по умолчанию, но и сузить круг анализа.

postgres@sch_db=# \! oid2name -d sch_db -f 16441

В данном примере опция -d указывает на конкретную базу данных, объекты которой нужно проанализировать, а -f дает понять, что нужно вывести информацию о таблице с конкретным oid.

From database "sch_db":
Filenode Table Name
------------------------
16441 tab_in_def

Вывод команды без опций выглядит следующим образом.

postgres@sch_db=# \! oid2name

All databases:
Oid    Database  Name Tablespace
----------------------------------
5      postgres    pg_default
16409  sch_db      pg_default
4      template0   pg_default
1      template1   pg_default

В ситуациях, когда таблица размещена в пользовательском пространстве, бывает нужно проверить, что ее данные точно не лежат в пространстве по умолчанию для ее базы данных.

Воспроизведем такую ситуацию, создав новую таблицу с указанием табличного пространства newtabspace.

postgres@sch_db=# CREATE TABLE tab_in_newt ( id integer, msg text ) TABLESPACE newtabspace;

CREATE TABLE

Убедимся, что эта таблица не разместила свои данные в табличном пространстве pg_default, являющемся для sch_db пространством по умолчанию. Для этого сначала получим oid таблицы.

postgres@sch_db=# \! oid2name -d sch_db -t tab_in_newt

From database "sch_db":
 Filenode    Table Name
------------------------
    16449  tab_in_newt

Теперь пропишем путь к файлу таблицы, как если бы она располагалась в пространстве pg_default.

postgres@sch_db=# \! ls -l $PGDATA/base/16409/16449*

ls: cannot access к '/pgdata/06/data/base/16409/16449': No such file or directory

Как видим, команда выдала ошибку: не нашелся такой файл или директория. Это значит, что все данные таблицы tab_in_newt точно лежат в пространстве newtabspace.

Информация о ТП для объектов баз данных

При управлении данными важно знать, какие данные где лежат. Обратившись к каталогу pg_tables, можно получить информацию о всех таблицах текущей базы данных и табличных пространствах, в которых они лежат.

postgres@sch_db=# SELECT tablename, tablespace FROM pg_tables WHERE tablename ~ '^t';

В запросе выше для более удобного вывода используется шаблон поиска по имени. Выбираются все таблицы, имя которых начинается с t.

  tablename  | tablespace
-------------+-------------
 tab_in_def  |
 tab_in_newt | newtabspace
(2 rows)

Для таблицы tab_in_def пространство, в котором она хранит данные, совпадает с пространством по умолчанию для текущей базы данных sch_db. При создании таблицы tab_in_newt было указано пространство newtabspace, которое отличает от пространства sch_db, поэтому оно явно указано в каталоге pg_tables.

Попробуйте самостоятельно добиться в выводе явного указания пространства pg_default.

Чтобы табличное пространство pg_default было явно указано в выводе pg_tables, нужно чтобы пространство по умолчанию такой базы данных было отлично от пространства pg_default.

Создадим новую базу данных в пространстве newtabspace.

postgres@sch_db=# CREATE DATABASE nts_db TABLESPACE newtabspace;

CREATE DATABASE

Теперь создадим две таблицы с разными пространствами для хранения данных.

postgres@sch_db=# \c nts_db

You are now connected to database "nts_db" as user "postgres".

postgres@nts_db=# CREATE TABLE t_def_ts (dtme timestamp DEFAULT now());

CREATE TABLE

postgres@nts_db=# CREATE TABLE t_new_ts (dtme timestamp DEFAULT now()) TABLESPACE pg_default;

CREATE TABLE

Проверим вывод.

postgres@nts_db=# SELECT tablename, tablespace FROM pg_tables WHERE tablename ~ '^t';

 tablename | tablespace
-----------+------------
  t_def_ts |
  t_new_ts | pg_default
(2 rows)

В базе nts_db создана таблица t_def_ts в табличном пространстве по умолчанию для этой базы — newtabspace. Таблица t_new_ts, напротив, в табличном пространстве, не являющемся для базы nts_db пространством по умолчанию, — pg_default. В выводе из представления pg_tables видно, что столбец tablespace пуст. Значит данные для этой таблицы располагаются в табличном пространстве по умолчанию для nts_db — newtabspace.

Если при создании базы данных было указано табличное пространство, отличное от pg_default, то именно явно указанное пространство и будет для этой базы данных табличным пространством по умолчанию. Имя табличного пространства pg_default выводится в строке pg_tables для таблицы t_new_ts, поскольку оно явно было указано для этой таблицы. Наоборот, для таблицы t_def_ts не выведено имя табличного пространства в столбце tablespace по причине того, что при создании этой таблицы было использовано табличное пространство, установленное для этой базы по умолчанию, — newtabspace.

Перемещение объектов между ТП

После анализа расположения объектов можно изменить их размещение — переместить между табличными пространствами. Перемещение поддерживается как для одиночных объектов (таблиц, индексов), так и для массовой операции над всеми объектами в пространстве.

Одиночное перемещение объекта

При одиночном перемещении нужно знать имя объекта, который нужно переместить, и имя пространства, где должен располагаться объект. В качестве примера переместим индекс таблицы tab_in_def в табличное пространство по умолчанию.

Чтобы в дальнейшем убедиться, что перемещение прошло успешно, проверим текущее расположение индекса.

postgres@sch_db=# SELECT pg_relation_filepath('tab_in_def_msg_idx');

            pg_relation_filepath
---------------------------------------------
 pg_tblspc/16447/PG_15_202310091/16409/16446
(1 row)

В пути до файла явно указан каталог, содержащий пользовательские пространства (pg_tblspc) и oid табличного пространства newtabspace, где сейчас лежит индекс tab_in_def_msg_idx.

Перемещение осуществляется за счет команды ALTER, после которого нужно указать тип объекта, его название, ключевые слова SET TABLESPACE и название табличного пространства. В нашем случае идет перемещение индекса, но оно возможно также и для таблиц, и других объектов.

postgres@sch_db=# ALTER INDEX tab_in_def_msg_idx SET TABLESPACE pg_default;

ALTER INDEX

Проверим табличное пространство для этого индекса с помощью каталога pg_indexes.

postgres@sch_db=# SELECT indexname, tablespace FROM pg_indexes WHERE tablename = 'tab_in_def';

indexname          | tablespace
-------------------+-------------
tab_in_def_msg_idx |
(1 row)

Как видно, пространство newtabspace пропало из таблицы, поскольку установленное новое пространство pg_default совпадает с пространством по умолчанию для базы данных sch_db.

Проверим путь к индексу еще раз.

postgres@sch_db=# SELECT pg_relation_filepath('tab_in_def_msg_idx');

pg_relation_filepath
---------------------
    base/16409/16448
(1 row)

Путь также изменился: теперь вместо каталога pg_tblspc используется base для пространства по умолчанию.

примечание

Следует понимать, что перенос выполняется посредством создания нового файла. Это хорошо видно по последнему сегменту в пути — изменился oid индекса. Все данные исходного файла последовательного копируются в новый. Таким образом, эта процедура создает загрузку на подсистему ввода-вывода и может продолжаться очень длительное время для больших объектов.

Массовое перемещение объектов

При массовом перемещении не нужно знать имена всех объектов, которые необходимо перенести. Достаточно применить ту же команду ALTER, но с указанием ключевого слова ALL. Для перемещения всех таблиц в другое табличное пространство используется связка ALTER TABLE ALL IN TABLESPACE. Для индексов — ALTER INDEX ALL IN TABLESPACE. Однако вместо массового перемещения индексов можно заново проиндексировать перенесенные таблицы с помощью команды REINDEX.

Перенесем таблицы базы данных sch_db из пространства pg_default в newtabspace. Но перед этим проверим их табличные пространства.

postgres@sch_db=# SELECT tablename, tablespace FROM pg_tables WHERE tablename ~ '^t';

  tablename  | tablespace
-------------+-------------
 tab_in_def  |
 tab_in_newt | newtabspace
(2 rows)

Таблиц в базе данных всего 2, к тому же их пространства отличаются, поэтому при массовом переносе по факту перемещена будет только одна таблица. Но это не повлияет на выполнение команды.

Чтобы перенести таблицы, после ALTER ALL указываем табличное пространство, из которого нужно перенести таблицы, и далее устанавливаем новое.

postgres@sch_db=# ALTER TABLE ALL IN TABLESPACE pg_default SET TABLESPACE newtabspace;

ALTER TABLE

Вновь проверим таблицы и их пространства.

postgres@sch_db=# SELECT tablename, tablespace FROM pg_tables WHERE tablename ~ '^t';

  tablename  | tablespace
-------------+-------------
 tab_in_def  | newtabspace
 tab_in_newt | newtabspace
(2 rows)

Перемещение прошло успешно, обе таблицы лежат в пространстве newtabspace.

примечание

Помните, что массовое перемещение объектов может быть достаточно длительной процедурой. Причем при копировании данных посредством подсистемы ввода-вывода доступ к данным будет заблокирован. Перемещение индексов можно заменить переиндексированием перенесенных в другое табличное пространство таблиц. Поэтому предварительно убедитесь в обоснованной необходимости массового перемещения.

Итоги

• При создании объекта без явного указания TABLESPACE он размещается в ТП по умолчанию для текущей базы данных;
• В каталогах pg_tables и pg_indexes пустое поле tablespace означает использование ТП по умолчанию; имя ТП выводится, если оно отличается от ТП базы данных;
• pg_relation_filepath(relation) — относительный путь к файлу объекта от $PGDATA;
• Утилита oid2name с опциями -d (база) и -f (файловый узел) позволяет получить информацию о конкретных объектах;
• Перемещение объекта: ALTER TABLE|INDEX name SET TABLESPACE tablespace_name — создает новый файл и копирует данные (нагрузка на I/O, блокировка доступа);
• Массовое перемещение: ALTER TABLE ALL IN TABLESPACE old SET TABLESPACE new — переносит все таблицы из одного ТП в другое;
• Массовое перемещение индексов можно заменить переиндексированием (REINDEX) перенесенных таблиц;
• При перемещении объекта меняется oid файлового узла, что видно в пути к файлу.

Самопроверка

Вопрос 1

Какое значение будет в поле tablespace каталога pg_tables, если таблица лежит в пространстве по умолчанию для базы?

Имя пространства pg_defaultПустое значениеoid пространстваЗначение NULLИмя текущего пространства

Вопрос 2

Какой командой перемещают индекс в другое табличное пространство?

MOVE INDEX ... TO TABLESPACEALTER INDEX ... SET TABLESPACEALTER INDEX ... MOVE TOCHANGE INDEX ... TABLESPACEALTER INDEX ... TABLESPACE

Вопрос 3

Какой командой выполняется массовое перемещение всех таблиц из одного табличного пространства в другое?

ALTER TABLE ALL IN TABLESPACE old SET TABLESPACE newMOVE ALL TABLES FROM TABLESPACE old TO newALTER TABLESPACE old MOVE ALL TO newALTER ALL TABLES SET TABLESPACE new

Вопрос 4

База данных nts_db создана с табличным пространством по умолчанию newtabspace. В ней создана таблица t1 без явного указания TABLESPACE. Что будет в поле tablespace каталога pg_tables для t1 и в каком табличном пространстве реально лежит таблица?

Пустое значение, таблица в pg_defaultПустое значение, таблица в провnewtabspacenewtabspace, таблица в newtabspacepg_default, таблица в pg_defaultЗначение NULL, таблица в pg_global