Tablespace

Дарья Барышева

Обзор

Табличное пространство (tablespace) — это системный объект, позволяющий администраторам размещать объекты баз данных (таблицы, индексы, spill-файлы и другое) в различных директориях и даже на различных дисках. Другими словами, tablespace определяет, где хранить физические файлы для логических объектов БД. Благодаря наличию tablespace возможно иметь несколько файловых систем на одной машине и выбирать наиболее оптимальный способ хранения для различных данных. Например, часто используемые таблицы можно хранить на высокопроизводительных SSD-дисках, а прочие таблицы (в первую очередь, с архивными данными) — на стандартных HDD-дисках.

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

Существует несколько tablespace, используемых по умолчанию в каждом ADB-кластере. Они добавляются автоматически на этапе инициализации СУБД. Их описание приведено в таблице ниже.

Используемые по умолчанию tablespace
Название Описание

pg_global

Используется для хранения общих системных каталогов

pg_default

Используется другими объектами баз данных. Например, шаблонные БД template0 и template1 используют это табличное пространство. Поэтому по умолчанию оно также применяется ко всем пользовательским таблицам, если при вызове команды CREATE DATABASE не указывается явно параметр TABLESPACE
ВНИМАНИЕ
Не рекомендуется удалять либо изменять табличные пространства, приведенные выше.

Просмотр существующих tablespace

Для просмотра существующих tablespace в текущем ADB-кластере необходимо выполнить следующие шаги:

  1. Подключиться к Master-серверу ADB (например, через SSH) под пользователем gpadmin, который создается по умолчанию.

    $ sudo su - gpadmin

  2. Подключиться к БД через psql (или любую иную клиентскую программу).

    $ psql adb

    Результат команды:

    psql (9.4.24)
Type "help" for help.

  3. Выполнить одну из следующих команд:

    • Сделать выборку из таблицы pg_tablespace.

      SELECT oid, * FROM pg_tablespace;

      Возможный результат приведен ниже. Поле oid соответствует уникальному идентификатору tablespace.

       oid  |  spcname   | spcowner | spcacl | spcoptions
------+------------+----------+--------+------------
 1663 | pg_default |       10 |        |
 1664 | pg_global  |       10 |        |
(2 rows)

    • При работе с терминальным клиентом psql для вывода списка всех tablespace можно воспользоваться мета-командой \db+.

      \db+

      Результат:

                                   List of tablespaces
    Name    |  Owner  | Location  | Access privileges | Options | Description
------------+---------+-----------+-------------------+---------+-------------
 pg_default | gpadmin |           |                   |         |
 pg_global  | gpadmin |           |                   |         |
(2 rows)

      Обратите внимание на столбец Location в выводе предыдущей команды:

      • Для созданных пользователем tablespace это поле будет содержать физическое расположение табличного пространства.

      • Для созданных по умолчанию tablespace это поле будет пустым. Размещение таких tablespace совпадает с основной директорией данных (data directory), созданной на этапе инициализации кластера. Чтобы найти местоположение этой директории, можно вывести значение конфигурационного параметра data_directory.

        SHOW data_directory;

        Вывод команды:

            data_directory
-----------------------
 /data1/master/gpseg-1
(1 row)

Создание tablespace

Чтобы создать новое табличное пространство, необходимо использовать команду CREATE TABLESPACE, указать имя tablespace и затем определить место его хранения в файловой системе — после ключевого слова LOCATION.

ВАЖНО

Существует несколько требований к директории, выбираемой для нового tablespace:

  • Она должна существовать на всех хостах кластера — включая Master, Standby Master и каждый Segment host.

  • Она должна быть пустой.

  • Ее владельцем должен являться системный пользователь gpadmin.

  • Она не должна находиться на съемном или временном носителе. В случае потери табличного пространства (из-за удаления каталога, сбоя дисков и так далее), весь кластер может стать неработоспособным.

Следующая команда создает новое табличное пространство newspace в директории /newspace.

CREATE TABLESPACE newspace LOCATION '/newspace';

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

CREATE TABLESPACE

Теперь при запуске ранее приведенных команд для вывода табличных пространств получим следующее:

adb=# SELECT oid, * FROM pg_tablespace;
  oid  |  spcname   | spcowner | spcacl | spcoptions
-------+------------+----------+--------+------------
  1663 | pg_default |       10 |        |
  1664 | pg_global  |       10 |        |
 28508 | newspace   |       10 |        |
(3 rows)

adb=# \db+
                             List of tablespaces
    Name    |  Owner  | Location  | Access privileges | Options | Description
------------+---------+-----------+-------------------+---------+-------------
 newspace   | gpadmin | /newspace |                   |         |
 pg_default | gpadmin |           |                   |         |
 pg_global  | gpadmin |           |                   |         |
(3 rows)

На уровне системы ADB хранит все пользовательские tablespace как символические ссылки (symlink) в папке pg_tblspc основной директории данных (data directory). Чтобы убедиться в этом, выйдите из psql и запустите следующую команду. В команде необходимо прописать собственную директорию данных вместо /data1/master/gpseg-1/:

$ ls -l /data1/master/gpseg-1/pg_tblspc

Результат приведен ниже. 28508 — OID созданного табличного пространства.

total 0
lrwxrwxrwx 1 gpadmin gpadmin 11 Sep  7 08:43 28508 -> /newspace/1
ПРИМЕЧАНИЕ
Создание табличных пространств доступно только суперпользователю. Но в дальнейшем вы можете предоставить доступ на использование нового tablespace и обычным пользователям. Для этого предоставьте им привилегию CREATE для выбранного tablespace.

Использование tablespace для работы с объектами БД

Создание объектов

ADB позволяет определять tablespace для баз данных, таблиц и индексов. Для этого необходимо использовать параметр TABLESPACE при запуске команды CREATE для перечисленных типов объектов. Ниже приведены несколько примеров:

  • Базы данных. Следующая команда создает новую БД test_space_db в табличном пространстве newspace. Этот tablespace будет использоваться по умолчанию для всех системных каталогов БД, а также всех таблиц и индексов, создаваемых внутри БД (кроме случаев явного указания TABLESPACE при их создании). Если параметр TABLESPACE не указан при вызове команды CREATE DATABASE, новая база данных будет использовать tablespace своей шаблонной БД (по умолчанию — pg_default).

    CREATE DATABASE test_space_db TABLESPACE newspace;

    Результат:

    CREATE DATABASE

    Подключитесь к созданной БД, используя psql:

    adb=# \c test_space_db
You are now connected to database "test_space_db" as user "gpadmin".

  • Таблицы:

    • Если при создании таблицы не указывать явно tablespace, для нее будет использоваться табличное пространство, назначенное по умолчанию для текущей базы данных. Например, следующая команда создает таблицу test_space_t1 в табличном пространстве newspace.

      CREATE TABLE test_space_t1(a INT, b TEXT) DISTRIBUTED BY(a);

      Результат:

      CREATE TABLE

    • Допускается также выбирать табличное пространство, отличное от заданного по умолчанию для БД. Следующая команда создает таблицу test_space_t2 в пространстве pg_default:

      CREATE TABLE test_space_t2(a INT, b TEXT) TABLESPACE pg_default DISTRIBUTED BY(a);

      Результат:

      CREATE TABLE

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

    • Использование tablespace, назначенного по умолчанию для текущей БД:

      CREATE INDEX test_space_idx2 ON test_space_t1 (b);

      Результат:

      CREATE INDEX

    • Использование явно определенного tablespace:

      CREATE INDEX test_space_idx ON test_space_t2 (b) TABLESPACE pg_default;

      Результат приведен ниже:

      CREATE INDEX

Вы также можете использовать конфигурационный параметр default_tablespace — для определения табличного пространства, которое следует выбирать по умолчанию для всех последующих команд CREATE TABLE и CREATE INDEX (в которых явно не определен требуемый tablespace). Этот параметр не оказывает влияния на команду CREATE DATABASE, так как для базы данных табличное пространство по умолчанию берется из шаблонной БД, на основе которой создается текущая (если не указана явно опция TABLESPACE). В следующем примере показано, как установить параметр "на лету" для текущего сеанса работы с БД, но, как и для всех session-параметров, вы также можете определить его значение на уровне роли, базы данных и системы в целом.

SET default_tablespace = newspace;

Проверка корректности заполнения tablespace

Для проверки корректности заполнения табличного пространства в различных обектах БД используются следующие команды:

  • Базы данных. Чтобы увидеть список БД с назначенными для них табличными пространствами, можно запустить мета-команду \l+ в psql:

    \l+

    Результат запуска команды приведен ниже. Обратите внимание на столбец tablespace.

                                                                       List of databases
     Name      |  Owner  | Encoding |  Collate   |   Ctype    |  Access privileges  |  Size  | Tablespace |                Description
---------------+---------+----------+------------+------------+---------------------+--------+------------+--------------------------------------------
 adb           | gpadmin | UTF8     | en_US.utf8 | en_US.utf8 | =Tc/gpadmin        +| 422 MB | pg_default |
               |         |          |            |            | gpadmin=CTc/gpadmin |        |            |
 diskquota     | gpadmin | UTF8     | en_US.utf8 | en_US.utf8 |                     | 283 MB | pg_default |
 postgres      | gpadmin | UTF8     | en_US.utf8 | en_US.utf8 |                     | 280 MB | pg_default | default administrative connection database
 template0     | gpadmin | UTF8     | en_US.utf8 | en_US.utf8 | =c/gpadmin         +| 276 MB | pg_default | unmodifiable empty database
               |         |          |            |            | gpadmin=CTc/gpadmin |        |            |
 template1     | gpadmin | UTF8     | en_US.utf8 | en_US.utf8 | =c/gpadmin         +| 278 MB | pg_default | default template for new databases
               |         |          |            |            | gpadmin=CTc/gpadmin |        |            |
 test_space_db | gpadmin | UTF8     | en_US.utf8 | en_US.utf8 |                     | 284 MB | newspace   |
(6 rows)

  • Таблицы. Чтобы увидеть tablespace, назначенный для конкретной таблицы, можно выполнить следующий SQL-запрос для системной таблицы pg_tables:

    SELECT * FROM pg_tables WHERE tablename='test_space_t2';

    Результат:

 schemaname |   tablename   | tableowner | tablespace | hasindexes | hasrules | hastriggers
------------+---------------+------------+------------+------------+----------+-------------
 public     | test_space_t2 | gpadmin    | pg_default | t          | f        | f
(1 row)

  • Индексы. Чтобы увидеть tablespace, назначенный для конкретного индекса, можно выполнить следующий SQL-запрос для системной таблицы pg_indexes:

    SELECT * FROM pg_indexes WHERE tablename = 'test_space_t2';

    Результат:

 schemaname |   tablename   |   indexname    | tablespace |                              indexdef
------------+---------------+----------------+------------+---------------------------------------------------------------------
 public     | test_space_t2 | test_space_idx | pg_default | CREATE INDEX test_space_idx ON public.test_space_t2 USING btree (b)
(1 row)
Особенности хранения табличного пространства по умолчанию

 
Таблицы pg_tables и pg_indexes не содержат какой-либо информации в столбце tablespace для тех таблиц и индексов, которые используют табличное пространство, назначенное для БД по умолчанию. Столбец tablespace в этом случае пуст. Это гарантирует, что если база данных будет клонирована с другим табличным пространством по умолчанию, записи таблиц pg_class и pg_indexes по-прежнему будут соответствовать местоположению, в которое команда CREATE DATABASE переместит физически скопированные таблицы и индексы.

test_space_db=#  SELECT * FROM pg_tables WHERE tablename='test_space_t1';
 schemaname |   tablename   | tableowner | tablespace | hasindexes | hasrules | hastriggers
------------+---------------+------------+------------+------------+----------+-------------
 public     | test_space_t1 | gpadmin    |            | f          | f        | f
(1 row)

test_space_db=# SELECT * FROM pg_indexes WHERE tablename = 'test_space_t1';
 schemaname |   tablename   |    indexname    | tablespace |                               indexdef
------------+---------------+-----------------+------------+----------------------------------------------------------------------
 public     | test_space_t1 | test_space_idx2 |            | CREATE INDEX test_space_idx2 ON public.test_space_t1 USING btree (b)
(1 row)

Перемещение объектов

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

  • Таблицы:

    • Чтобы переместить конкретную таблицу в другой tablespace, используйте следующий запрос:

      ALTER TABLE test_space_t1 SET TABLESPACE pg_default;

      Результат:

      ALTER TABLE

    • Чтобы переместить все таблицы текущей БД из одного табличного пространства в другое, выполните команду:

      ALTER TABLE ALL IN TABLESPACE newspace SET TABLESPACE pg_default;

      Вывод команды представлен ниже. Он также может содержать сообщение No matching relations in tablespace, если в указанном tablespace не содержится таблиц текущей БД.

      ALTER TABLE
      ВНИМАНИЕ

      Будьте осторожны, используя команду ALTER TABLE ALL. Эта команда перемещает не только определенные пользователем, но и системные таблицы (такие как pg_toast.*). Если в будущем вам потребуется перенести таблицы текущей БД в другое табличное пространство — потребуется вновь использовать выражение ALL.

  • Индексы:

    • Чтобы переместить определенный индекс в другой tablespace, используйте запрос:

      ALTER INDEX test_space_idx2 SET TABLESPACE pg_default;

      Результат:

      ALTER INDEX

    • Чтобы переместить все индексы текущей БД из одного табличного пространства в другое, выполните команду:

      ALTER INDEX ALL IN TABLESPACE newspace SET TABLESPACE pg_default;

      Вывод команды представлен ниже. Он также может содержать сообщение No matching relations in tablespace, если в указанном tablespace не содержится индексов текущей БД.

      ALTER INDEX
      ВНИМАНИЕ

      Обратите внимание, что системные каталоги не перемещаются командами ALTER TABLE ALL и ALTER INDEX ALL. Чтобы их переместить, используйте команду ALTER DATABASE или ALTER TABLE/ALTER INDEX. Объекты information_schema не рассматриваются как часть системных каталогов и поэтому будут перемещены.

  • Базы данных. Чтобы изменить используемое по умолчанию табличное пространство на уровне БД, необходимо выполнить команду ALTER DATABASE с выражением SET TABLESPACE. Эта команда физически переносит таблицы и индексы, хранимые в текущем tablespace БД, в новый tablespace. Обратите внимание, что таблицы и индексы, сохраненные в иных tablespace (указанных явно при создании этих объектов) не переносятся. Приведенные ниже команды создают новое табличное пространство и назначают его для БД:

    • Создайте табличное пространство newspace2 в директории /newspace2:

      CREATE TABLESPACE newspace2 LOCATION '/newspace2';

    • Подключитесь к другой базе данных (например, adb):

      \c adb

    • Измените табличное пространство, используемое по умолчанию для БД test_space_db:

      ALTER DATABASE test_space_db SET TABLESPACE newspace2;

      Результат:

      ALTER DATABASE
      ВНИМАНИЕ

      • Для выполнения команды ALTER DATABASE необходимы права database owner или superuser.

      • Требуется привилегия CREATE для нового табличного пространства.

      • Перед изменением табличного пространства для открытой в текущей сессии БД подключитесь к любой иной БД.

      • В выбранном tablespace не должно содержаться объектов текущей БД (включая системные таблицы).

Перемещение временных объектов

Табличные пространства используются также для хранения временных объектов, включая:

  • Временные таблицы и индексы, создаваемые явно при помощи команды CREATE TEMP TABLE.

  • Временные таблицы и spill-файлы, создаваемые автоматически при выполнении запросов (например, hash aggregate и hash join).

  • Временные файлы, используемые для других целей — например, сортировки больших dataset.

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

Значением параметра по умолчанию является пустая строка, соответствующая табличному пространству, установленному по умолчанию для текущей БД. Как и для всех session-параметров, вы можете определить значение temp_tablespaces на уровне роли, базы данных и системы в целом. Следующая команда устанавливает значение параметра в рамках текущей сессии пользователя:

SET temp_tablespaces = newspace;

Результат:

SET

Удаление tablespace

Для удаления tablespace необходимо использовать команду DROP TABLESPACE, указав имя табличного пространства:

DROP TABLESPACE newspace;

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

DROP TABLESPACE
ВНИМАНИЕ

  • Для удаления табличного пространства необходимы права tablespace owner или superuser.

  • Только пустые табличные пространства могут быть удалены. Убедитесь, что удаляемый tablespace не используется объектами БД. Также табличное пространство не должно содержать временных и транзакционных файлов.
Нашли ошибку? Выделите текст и нажмите Ctrl+Enter чтобы сообщить о ней